本文希望幫助讀者更加深刻地理解 MySQL 中的鎖和事務(wù)，從而在業(yè)務(wù)系統(tǒng)開發(fā)過程中更好地優(yōu)化與數(shù)據(jù)庫的交互。

[[275104]]

圖片來自 Pexels

鎖的分類及特性

數(shù)據(jù)庫鎖定機(jī)制簡(jiǎn)單來說，就是數(shù)據(jù)庫為了保證數(shù)據(jù)的一致性，而使各種共享資源在被并發(fā)訪問時(shí)變得有序所設(shè)計(jì)的一種規(guī)則。

對(duì)于任何一種數(shù)據(jù)庫來說都需要有相應(yīng)的鎖定機(jī)制，所以 MySQL 自然也不能例外。

MySQL 數(shù)據(jù)庫由于其自身架構(gòu)的特點(diǎn)，存在多種數(shù)據(jù)存儲(chǔ)引擎，每種存儲(chǔ)引擎所針對(duì)的應(yīng)用場(chǎng)景特點(diǎn)都不太一樣。

為了滿足各自特定應(yīng)用場(chǎng)景的需求，每種存儲(chǔ)引擎的鎖定機(jī)制都是為各自所面對(duì)的特定場(chǎng)景而優(yōu)化設(shè)計(jì)，所以各存儲(chǔ)引擎的鎖定機(jī)制也有較大區(qū)別。

MySQL 各存儲(chǔ)引擎使用了三種類型(級(jí)別)的鎖定機(jī)制：

• 表級(jí)鎖定
• 行級(jí)鎖定
• 頁級(jí)鎖定

表級(jí)鎖定(table-level)

表級(jí)別的鎖定是 MySQL 各存儲(chǔ)引擎中最大顆粒度的鎖定機(jī)制。該鎖定機(jī)制最大的特點(diǎn)是實(shí)現(xiàn)邏輯非常簡(jiǎn)單，帶來的系統(tǒng)負(fù)面影響最小。

所以獲取鎖和釋放鎖的速度很快。由于表級(jí)鎖定一次會(huì)將整個(gè)表鎖定，所以可以很好的避免困擾我們的死鎖問題。

當(dāng)然，鎖定顆粒度大所帶來最大的負(fù)面影響就是出現(xiàn)鎖定資源爭(zhēng)用的概率也會(huì)最高，致使并大度大打折扣。

使用表級(jí)鎖定的主要是 MyISAM，MEMORY，CSV 等一些非事務(wù)性存儲(chǔ)引擎。

行級(jí)鎖定(row-level)

行級(jí)鎖定最大的特點(diǎn)就是鎖定對(duì)象的顆粒度很小，也是目前各大數(shù)據(jù)庫管理軟件所實(shí)現(xiàn)的鎖定顆粒度最小的。

由于鎖定顆粒度很小，所以發(fā)生鎖定資源爭(zhēng)用的概率也最小，能夠給予應(yīng)用程序盡可能大的并發(fā)處理能力而提高一些需要高并發(fā)應(yīng)用系統(tǒng)的整體性能。

雖然能夠在并發(fā)處理能力上面有較大的優(yōu)勢(shì)，但是行級(jí)鎖定也因此帶來了不少弊端。

由于鎖定資源的顆粒度很小，所以每次獲取鎖和釋放鎖需要做的事情也更多，帶來的消耗自然也就更大了。

此外，行級(jí)鎖定也最容易發(fā)生死鎖。使用行級(jí)鎖定的主要是 InnoDB 存儲(chǔ)引擎。

頁級(jí)鎖定(page-level)

頁級(jí)鎖定是 MySQL 中比較獨(dú)特的一種鎖定級(jí)別，在其他數(shù)據(jù)庫管理軟件中也并不是太常見。

頁級(jí)鎖定的特點(diǎn)是鎖定顆粒度介于行級(jí)鎖定與表級(jí)鎖之間，所以獲取鎖定所需要的資源開銷，以及所能提供的并發(fā)處理能力也同樣是介于上面二者之間。另外，頁級(jí)鎖定和行級(jí)鎖定一樣，會(huì)發(fā)生死鎖。

在數(shù)據(jù)庫實(shí)現(xiàn)資源鎖定的過程中，隨著鎖定資源顆粒度的減小，鎖定相同數(shù)據(jù)量的數(shù)據(jù)所需要消耗的內(nèi)存數(shù)量是越來越多的，實(shí)現(xiàn)算法也會(huì)越來越復(fù)雜。

不過，隨著鎖定資源顆粒度的減小，應(yīng)用程序的訪問請(qǐng)求遇到鎖等待的可能性也會(huì)隨之降低，系統(tǒng)整體并發(fā)度也隨之提升。使用頁級(jí)鎖定的主要是 BerkeleyDB 存儲(chǔ)引擎。

總的來說，MySQL 這三種鎖的特性可大致歸納如下：

• 表級(jí)鎖：開銷小，加鎖快;不會(huì)出現(xiàn)死鎖;鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。
• 行級(jí)鎖：開銷大，加鎖慢;會(huì)出現(xiàn)死鎖;鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。
• 頁面鎖：開銷和加鎖時(shí)間界于表鎖和行鎖之間;會(huì)出現(xiàn)死鎖;鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般。

適用：從鎖的角度來說，表級(jí)鎖更適合于以查詢?yōu)橹?，只有少量按索引條件更新數(shù)據(jù)的應(yīng)用，如 Web 應(yīng)用。

而行級(jí)鎖則更適合于有大量按索引條件并發(fā)更新少量不同數(shù)據(jù)，同時(shí)又有并發(fā)查詢的應(yīng)用，如一些在線事務(wù)處理(OLTP)系統(tǒng)。

表級(jí)鎖定(MyISAM 舉例)

由于 MyISAM 存儲(chǔ)引擎使用的鎖定機(jī)制完全是由 MySQL 提供的表級(jí)鎖定實(shí)現(xiàn)，所以下面我們將以 MyISAM 存儲(chǔ)引擎作為示例存儲(chǔ)引擎。

MySQL 表級(jí)鎖的鎖模式

MySQL 的表級(jí)鎖有兩種模式：

• 表共享讀鎖(Table Read Lock)
• 表獨(dú)占寫鎖(Table Write Lock)

鎖模式的兼容性：

• 對(duì) MyISAM 表的讀操作，不會(huì)阻塞其他用戶對(duì)同一表的讀請(qǐng)求，但會(huì)阻塞對(duì)同一表的寫請(qǐng)求。
• 對(duì) MyISAM 表的寫操作，則會(huì)阻塞其他用戶對(duì)同一表的讀和寫操作。
• MyISAM 表的讀操作與寫操作之間，以及寫操作之間是串行的。當(dāng)一個(gè)線程獲得對(duì)一個(gè)表的寫鎖后，只有持有鎖的線程可以對(duì)表進(jìn)行更新操作。其他線程的讀、寫操作都會(huì)等待，直到鎖被釋放為止。

總結(jié)：表鎖，讀鎖會(huì)阻塞寫，不會(huì)阻塞讀。而寫鎖則會(huì)把讀寫都阻塞。

如何加表鎖

MyISAM 在執(zhí)行查詢語句(SELECT)前，會(huì)自動(dòng)給涉及的所有表加讀鎖，在執(zhí)行更新操作(UPDATE、DELETE、INSERT等)前，會(huì)自動(dòng)給涉及的表加寫鎖。

這個(gè)過程并不需要用戶干預(yù)，因此，用戶一般不需要直接用 LOCK TABLE 命令給 MyISAM 表顯式加鎖。

顯示加鎖：

• 共享讀鎖：lock table tableName read
• 獨(dú)占寫鎖：lock table tableName write
• 同時(shí)加多鎖：lock table t1 write，t2 read
• 批量解鎖：unlock tables

MyISAM 表鎖優(yōu)化建議

對(duì)于 MyISAM 存儲(chǔ)引擎，雖然使用表級(jí)鎖定在鎖定實(shí)現(xiàn)的過程中比實(shí)現(xiàn)行級(jí)鎖定或者頁級(jí)鎖定所帶來的附加成本都要小，鎖定本身所消耗的資源也是最少。

但是由于鎖定的顆粒度比較大，所以造成鎖定資源的爭(zhēng)用情況也會(huì)比其他的鎖定級(jí)別都要多，從而在較大程度上會(huì)降低并發(fā)處理能力。

所以，在優(yōu)化 MyISAM 存儲(chǔ)引擎鎖定問題的時(shí)候，最關(guān)鍵的就是如何讓其提高并發(fā)度。

由于鎖定級(jí)別是不可能改變的了，所以我們首先需要盡可能讓鎖定的時(shí)間變短，然后就是讓可能并發(fā)進(jìn)行的操作盡可能的并發(fā)。

①查詢表級(jí)鎖爭(zhēng)用情況

MySQL 內(nèi)部有兩組專門的狀態(tài)變量記錄系統(tǒng)內(nèi)部鎖資源爭(zhēng)用情況：

mysql> show status like 'table%'; 
+----------------------------+---------+ 
| Variable_name              | Value   | 
+----------------------------+---------+ 
| Table_locks_immediate      | 100     | 
| Table_locks_waited         | 11      | 
+----------------------------+---------+ 

這里有兩個(gè)狀態(tài)變量記錄 MySQL 內(nèi)部表級(jí)鎖定的情況，兩個(gè)變量說明如下：

• Table_locks_immediate：產(chǎn)生表級(jí)鎖定的次數(shù)。
• Table_locks_waited：出現(xiàn)表級(jí)鎖定爭(zhēng)用而發(fā)生等待的次數(shù);此值越高則說明存在著越嚴(yán)重的表級(jí)鎖爭(zhēng)用情況。

此外，MyISAM 的讀寫鎖調(diào)度是寫優(yōu)先，這也是 MyISAM 不適合做寫為主表的存儲(chǔ)引擎的原因。

因?yàn)閷戞i后，其他線程不能做任何操作，大量的更新會(huì)使查詢很難得到鎖，從而造成永久阻塞。

兩個(gè)狀態(tài)值都是從系統(tǒng)啟動(dòng)后開始記錄，出現(xiàn)一次對(duì)應(yīng)的事件則數(shù)量加 1。如果這里的 Table_locks_waited 狀態(tài)值比較高，那么說明系統(tǒng)中表級(jí)鎖定爭(zhēng)用現(xiàn)象比較嚴(yán)重，就需要進(jìn)一步分析為什么會(huì)有較多的鎖定資源爭(zhēng)用了。

②縮短鎖定時(shí)間

如何讓鎖定時(shí)間盡可能的短呢?唯一的辦法就是讓我們的 Query 執(zhí)行時(shí)間盡可能的短：

• 盡量減少大的復(fù)雜 Query，將復(fù)雜 Query 分拆成幾個(gè)小的 Query 分布進(jìn)行。
• 盡可能的建立足夠高效的索引，讓數(shù)據(jù)檢索更迅速。
• 盡量讓 MyISAM 存儲(chǔ)引擎的表只存放必要的信息，控制字段類型。
• 利用合適的機(jī)會(huì)優(yōu)化 MyISAM 表數(shù)據(jù)文件。

③分離能并行的操作

說到 MyISAM 的表鎖，而且是讀寫互相阻塞的表鎖，可能有些人會(huì)認(rèn)為在 MyISAM 存儲(chǔ)引擎的表上就只能是完全的串行化，沒辦法再并行了。

大家不要忘記了，MyISAM 的存儲(chǔ)引擎還有一個(gè)非常有用的特性，那就是 Concurrent Insert(并發(fā)插入)的特性。

MyISAM 存儲(chǔ)引擎有一個(gè)控制是否打開 Concurrent Insert 功能的參數(shù)選項(xiàng)：concurrent_insert，可以設(shè)置為 0，1 或者 2。

三個(gè)值的具體說明如下：

• concurrent_insert=2，無論 MyISAM 表中有沒有空洞，都允許在表尾并發(fā)插入記錄。
• concurrent_insert=1，如果 MyISAM 表中沒有空洞(即表的中間沒有被刪除的行)，MyISAM 允許在一個(gè)進(jìn)程讀表的同時(shí)，另一個(gè)進(jìn)程從表尾插入記錄。這也是 MySQL 的默認(rèn)設(shè)置。
• concurrent_insert=0，不允許并發(fā)插入。

可以利用 MyISAM 存儲(chǔ)引擎的并發(fā)插入特性，來解決應(yīng)用中對(duì)同一表查詢和插入的鎖爭(zhēng)用。

例如，將 concurrent_insert 系統(tǒng)變量設(shè)為 2，總是允許并發(fā)插入;同時(shí)，通過定期在系統(tǒng)空閑時(shí)段執(zhí)行 OPTIMIZE TABLE 語句來整理空間碎片，收回因刪除記錄而產(chǎn)生的中間空洞。

④合理利用讀寫優(yōu)先級(jí)

MyISAM 存儲(chǔ)引擎的讀寫是互相阻塞的，那么，一個(gè)進(jìn)程請(qǐng)求某個(gè) MyISAM 表的讀鎖，同時(shí)另一個(gè)進(jìn)程也請(qǐng)求同一表的寫鎖，MySQL 如何處理呢?

答案是寫進(jìn)程先獲得鎖。不僅如此，即使讀請(qǐng)求先到鎖等待隊(duì)列，寫請(qǐng)求后到，寫鎖也會(huì)插到讀鎖請(qǐng)求之前。

這是因?yàn)?MySQL 的表級(jí)鎖定對(duì)于讀和寫是有不同優(yōu)先級(jí)設(shè)定的，默認(rèn)情況下是寫優(yōu)先級(jí)要大于讀優(yōu)先級(jí)。

所以，如果我們可以根據(jù)各自系統(tǒng)環(huán)境的差異決定讀與寫的優(yōu)先級(jí)：

通過執(zhí)行命令 SET LOW_PRIORITY_UPDATES=1，使該連接讀比寫的優(yōu)先級(jí)高。

如果我們的系統(tǒng)是一個(gè)以讀為主，可以設(shè)置此參數(shù)，如果以寫為主，則不用設(shè)置。

通過指定 INSERT、UPDATE、DELETE 語句的 LOW_PRIORITY 屬性，降低該語句的優(yōu)先級(jí)。

雖然上面方法都是要么更新優(yōu)先，要么查詢優(yōu)先的方法，但還是可以用其來解決查詢相對(duì)重要的應(yīng)用(如用戶登錄系統(tǒng))中，讀鎖等待嚴(yán)重的問題。

另外，MySQL 也提供了一種折中的辦法來調(diào)節(jié)讀寫沖突，即給系統(tǒng)參數(shù) max_write_lock_count 設(shè)置一個(gè)合適的值，當(dāng)一個(gè)表的讀鎖達(dá)到這個(gè)值后，MySQL 就暫時(shí)將寫請(qǐng)求的優(yōu)先級(jí)降低，給讀進(jìn)程一定獲得鎖的機(jī)會(huì)。

這里還要強(qiáng)調(diào)一點(diǎn)：一些需要長時(shí)間運(yùn)行的查詢操作，也會(huì)使寫進(jìn)程“餓死”。

因此，應(yīng)用中應(yīng)盡量避免出現(xiàn)長時(shí)間運(yùn)行的查詢操作，不要總想用一條 SELECT 語句來解決問題，因?yàn)檫@種看似巧妙的 SQL 語句，往往比較復(fù)雜，執(zhí)行時(shí)間較長。

在可能的情況下可以通過使用中間表等措施對(duì) SQL 語句做一定的“分解”，使每一步查詢都能在較短時(shí)間完成，從而減少鎖沖突。

如果復(fù)雜查詢不可避免，應(yīng)盡量安排在數(shù)據(jù)庫空閑時(shí)段執(zhí)行，比如一些定期統(tǒng)計(jì)可以安排在夜間執(zhí)行。

InnoDB 默認(rèn)采用行鎖，在未使用索引字段查詢時(shí)升級(jí)為表鎖。MySQL 這樣設(shè)計(jì)并不是給你挖坑。它有自己的設(shè)計(jì)目的。

即便你在條件中使用了索引字段，MySQL 會(huì)根據(jù)自身的執(zhí)行計(jì)劃，考慮是否使用索引(所以 explain 命令中會(huì)有 possible_key 和 key)。

如果 MySQL 認(rèn)為全表掃描效率更高，它就不會(huì)使用索引，這種情況下 InnoDB 將使用表鎖，而不是行鎖。

因此，在分析鎖沖突時(shí)，別忘了檢查 SQL 的執(zhí)行計(jì)劃，以確認(rèn)是否真正使用了索引。

關(guān)于執(zhí)行計(jì)劃，第一種情況：全表更新。事務(wù)需要更新大部分或全部數(shù)據(jù)，且表又比較大。

若使用行鎖，會(huì)導(dǎo)致事務(wù)執(zhí)行效率低，從而可能造成其他事務(wù)長時(shí)間鎖等待和更多的鎖沖突。

第二種情況：多表級(jí)聯(lián)。事務(wù)涉及多個(gè)表，比較復(fù)雜的關(guān)聯(lián)查詢，很可能引起死鎖，造成大量事務(wù)回滾。

這種情況若能一次性鎖定事務(wù)涉及的表，從而可以避免死鎖、減少數(shù)據(jù)庫因事務(wù)回滾帶來的開銷。

行級(jí)鎖定

行級(jí)鎖定不是 MySQL 自己實(shí)現(xiàn)的鎖定方式，而是由其他存儲(chǔ)引擎自己所實(shí)現(xiàn)的，如廣為大家所知的 InnoDB 存儲(chǔ)引擎，以及 MySQL 的分布式存儲(chǔ)引擎 NDB Cluster 等都是實(shí)現(xiàn)了行級(jí)鎖定。

考慮到行級(jí)鎖定均由各個(gè)存儲(chǔ)引擎自行實(shí)現(xiàn)，而且具體實(shí)現(xiàn)也各有差別，而 InnoDB 是目前事務(wù)型存儲(chǔ)引擎中使用最為廣泛的存儲(chǔ)引擎，所以這里我們就主要分析一下 InnoDB 的鎖定特性。

InnoDB 鎖定模式及實(shí)現(xiàn)機(jī)制

總的來說，InnoDB 的鎖定機(jī)制和 Oracle 數(shù)據(jù)庫有不少相似之處。InnoDB 的行級(jí)鎖定同樣分為兩種類型，共享鎖和排他鎖，而在鎖定機(jī)制的實(shí)現(xiàn)過程中為了讓行級(jí)鎖定和表級(jí)鎖定共存，InnoDB 也同樣使用了意向鎖(表級(jí)鎖定)的概念，也就有了意向共享鎖和意向排他鎖這兩種。

當(dāng)一個(gè)事務(wù)需要給自己需要的某個(gè)資源加鎖的時(shí)候，如果遇到一個(gè)共享鎖正鎖定著自己需要的資源的時(shí)候，自己可以再加一個(gè)共享鎖，不過不能加排他鎖。

但是，如果遇到自己需要鎖定的資源已經(jīng)被一個(gè)排他鎖占有之后，則只能等待該鎖定釋放資源之后自己才能獲取鎖定資源并添加自己的鎖定。

而意向鎖的作用就是當(dāng)一個(gè)事務(wù)在需要獲取資源鎖定的時(shí)候，如果遇到自己需要的資源已經(jīng)被排他鎖占用的時(shí)候，該事務(wù)需要在鎖定行的表上面添加一個(gè)合適的意向鎖。

如果自己需要一個(gè)共享鎖，那么就在表上面添加一個(gè)意向共享鎖。而如果自己需要的是某行(或者某些行)上面添加一個(gè)排他鎖的話，則先在表上面添加一個(gè)意向排他鎖。

意向共享鎖可以同時(shí)并存多個(gè)，但是意向排他鎖同時(shí)只能有一個(gè)存在。

所以，可以說 InnoDB 的鎖定模式實(shí)際上可以分為四種：

• 共享鎖(S)
• 排他鎖(X)
• 意向共享鎖(IS)
• 意向排他鎖(IX)

我們可以通過以下表格來總結(jié)上面這四種鎖的共存邏輯關(guān)系：

如果一個(gè)事務(wù)請(qǐng)求的鎖模式與當(dāng)前的鎖兼容，InnoDB 就將請(qǐng)求的鎖授予該事務(wù);反之，如果兩者不兼容，該事務(wù)就要等待鎖釋放。

意向鎖是 InnoDB 自動(dòng)加的，不需用戶干預(yù)：

• 對(duì)于 UPDATE、DELETE 和 INSERT 語句，InnoDB 會(huì)自動(dòng)給涉及數(shù)據(jù)集加排他鎖(X)。
• 對(duì)于普通 SELECT 語句，InnoDB 不會(huì)加任何鎖。

事務(wù)可以通過以下語句顯示給記錄集加共享鎖或排他鎖：

共享鎖（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE 
排他鎖（X)：SELECT * FROM table_name WHERE ... FOR UPDATE 

用 SELECT ... IN SHARE MODE 獲得共享鎖，主要用在需要數(shù)據(jù)依存關(guān)系時(shí)來確認(rèn)某行記錄是否存在，并確保沒有人對(duì)這個(gè)記錄進(jìn)行 UPDATE 或者 DELETE 操作。

但是如果當(dāng)前事務(wù)也需要對(duì)該記錄進(jìn)行更新操作，則很有可能造成死鎖，對(duì)于鎖定行記錄后需要進(jìn)行更新操作的應(yīng)用，應(yīng)該使用 SELECT... FOR UPDATE 方式獲得排他鎖。

InnoDB 行鎖實(shí)現(xiàn)方式

InnoDB 行鎖是通過給索引上的索引項(xiàng)加鎖來實(shí)現(xiàn)的，只有通過索引條件檢索數(shù)據(jù)，InnoDB 才使用行級(jí)鎖，否則，InnoDB 將使用表鎖。

在實(shí)際應(yīng)用中，要特別注意 InnoDB 行鎖的這一特性，不然的話，可能導(dǎo)致大量的鎖沖突，從而影響并發(fā)性能。

下面通過一些實(shí)際例子來加以說明：

• 在不通過索引條件查詢的時(shí)候，InnoDB 確實(shí)使用的是表鎖，而不是行鎖。
• 由于 MySQL 的行鎖是針對(duì)索引加的鎖，不是針對(duì)記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，是會(huì)出現(xiàn)鎖沖突的。
• 當(dāng)表有多個(gè)索引的時(shí)候，不同的事務(wù)可以使用不同的索引鎖定不同的行，另外，不論是使用主鍵索引、唯一索引或普通索引，InnoDB 都會(huì)使用行鎖來對(duì)數(shù)據(jù)加鎖。
• 即便在條件中使用了索引字段，但是否使用索引來檢索數(shù)據(jù)是由 MySQL 通過判斷不同執(zhí)行計(jì)劃的代價(jià)來決定的，如果 MySQL 認(rèn)為全表掃描效率更高，比如對(duì)一些很小的表，它就不會(huì)使用索引。

這種情況下 InnoDB 將使用表鎖，而不是行鎖。因此，在分析鎖沖突時(shí)，別忘了檢查 SQL 的執(zhí)行計(jì)劃，以確認(rèn)是否真正使用了索引。

間隙鎖(Next-Key鎖)

當(dāng)我們用范圍條件而不是相等條件檢索數(shù)據(jù)，并請(qǐng)求共享或排他鎖時(shí)，InnoDB 會(huì)給符合條件的已有數(shù)據(jù)記錄的索引項(xiàng)加鎖。

對(duì)于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做“間隙(GAP)”，InnoDB 也會(huì)對(duì)這個(gè)“間隙”加鎖，這種鎖機(jī)制就是所謂的間隙鎖(Next-Key鎖)。

假如 emp 表中只有 101 條記錄，其 empid 的值分別是 1,2,...,100,101，下面的 SQL：

mysql> select * from emp where empid > 100 for update; 

這是一個(gè)范圍條件的檢索，InnoDB 不僅會(huì)對(duì)符合條件的 empid 值為 101 的記錄加鎖，也會(huì)對(duì) empid 大于 101(這些記錄并不存在)的“間隙”加鎖。

InnoDB 使用間隙鎖的目的：

• 防止幻讀，以滿足相關(guān)隔離級(jí)別的要求(關(guān)于事務(wù)的隔離級(jí)別)。對(duì)于上面的例子，要是不使用間隙鎖，如果其他事務(wù)插入了 empid 大于 100 的任何記錄，那么本事務(wù)如果再次執(zhí)行上述語句，就會(huì)發(fā)生幻讀。
• 為了滿足其恢復(fù)和復(fù)制的需要。很顯然，在使用范圍條件檢索并鎖定記錄時(shí)，即使某些不存在的鍵值也會(huì)被無辜的鎖定，而造成在鎖定的時(shí)候無法插入鎖定鍵值范圍內(nèi)的任何數(shù)據(jù)。在某些場(chǎng)景下這可能會(huì)對(duì)性能造成很大的危害。

除了間隙鎖給 InnoDB 帶來性能的負(fù)面影響之外，通過索引實(shí)現(xiàn)鎖定的方式還存在其他幾個(gè)較大的性能隱患：

• 當(dāng) Query 無法利用索引的時(shí)候，InnoDB 會(huì)放棄使用行級(jí)別鎖定而改用表級(jí)別的鎖定，造成并發(fā)性能的降低。
• 當(dāng) Query 使用的索引并不包含所有過濾條件的時(shí)候，數(shù)據(jù)檢索使用到的索引鍵所指向的數(shù)據(jù)可能有部分并不屬于該 Query 的結(jié)果集的行列，但是也會(huì)被鎖定，因?yàn)殚g隙鎖鎖定的是一個(gè)范圍，而不是具體的索引鍵。
• 當(dāng) Query 在使用索引定位數(shù)據(jù)的時(shí)候，如果使用的索引鍵一樣但訪問的數(shù)據(jù)行不同的時(shí)候(索引只是過濾條件的一部分)，一樣會(huì)被鎖定。

因此，在實(shí)際應(yīng)用開發(fā)中，尤其是并發(fā)插入比較多的應(yīng)用，我們要盡量?jī)?yōu)化業(yè)務(wù)邏輯，盡量使用相等條件來訪問更新數(shù)據(jù)，避免使用范圍條件。

還要特別說明的是，InnoDB 除了通過范圍條件加鎖時(shí)使用間隙鎖外，如果使用相等條件請(qǐng)求給一個(gè)不存在的記錄加鎖，InnoDB 也會(huì)使用間隙鎖。

死鎖

上文講過，MyISAM 表鎖是 deadlock free 的，這是因?yàn)?MyISAM 總是一次獲得所需的全部鎖，要么全部滿足，要么等待，因此不會(huì)出現(xiàn)死鎖。

但在 InnoDB 中，除單個(gè) SQL 組成的事務(wù)外，鎖是逐步獲得的，當(dāng)兩個(gè)事務(wù)都需要獲得對(duì)方持有的排他鎖才能繼續(xù)完成事務(wù)，這種循環(huán)鎖等待就是典型的死鎖。

在 InnoDB 的事務(wù)管理和鎖定機(jī)制中，有專門檢測(cè)死鎖的機(jī)制，會(huì)在系統(tǒng)中產(chǎn)生死鎖之后的很短時(shí)間內(nèi)就檢測(cè)到該死鎖的存在。

當(dāng) InnoDB 檢測(cè)到系統(tǒng)中產(chǎn)生了死鎖之后，InnoDB 會(huì)通過相應(yīng)的判斷來選這產(chǎn)生死鎖的兩個(gè)事務(wù)中較小的事務(wù)來回滾，而讓另外一個(gè)較大的事務(wù)成功完成。

那 InnoDB 是以什么來為標(biāo)準(zhǔn)判定事務(wù)的大小的呢?MySQL 官方手冊(cè)中也提到了這個(gè)問題，實(shí)際上在 InnoDB 發(fā)現(xiàn)死鎖之后，會(huì)計(jì)算出兩個(gè)事務(wù)各自插入、更新或者刪除的數(shù)據(jù)量來判定兩個(gè)事務(wù)的大小。也就是說哪個(gè)事務(wù)所改變的記錄條數(shù)越多，在死鎖中就越不會(huì)被回滾掉。

但是有一點(diǎn)需要注意的就是，當(dāng)產(chǎn)生死鎖的場(chǎng)景中涉及到不止 InnoDB 存儲(chǔ)引擎的時(shí)候，InnoDB 是沒辦法檢測(cè)到該死鎖的，這時(shí)候就只能通過鎖定超時(shí)限制參數(shù) InnoDB_lock_wait_timeout 來解決。

需要說明的是，這個(gè)參數(shù)并不是只用來解決死鎖問題，在并發(fā)訪問比較高的情況下，如果大量事務(wù)因無法立即獲得所需的鎖而掛起，會(huì)占用大量計(jì)算機(jī)資源，造成嚴(yán)重性能問題，甚至拖跨數(shù)據(jù)庫。我們通過設(shè)置合適的鎖等待超時(shí)閾值，可以避免這種情況發(fā)生。

通常來說，死鎖都是應(yīng)用設(shè)計(jì)的問題，通過調(diào)整業(yè)務(wù)流程、數(shù)據(jù)庫對(duì)象設(shè)計(jì)、事務(wù)大小，以及訪問數(shù)據(jù)庫的 SQL 語句，絕大部分死鎖都可以避免。

下面就通過實(shí)例來介紹幾種避免死鎖的常用方法：

• 在應(yīng)用中，如果不同的程序會(huì)并發(fā)存取多個(gè)表，應(yīng)盡量約定以相同的順序來訪問表，這樣可以大大降低產(chǎn)生死鎖的機(jī)會(huì)。
• 在程序以批量方式處理數(shù)據(jù)的時(shí)候，如果事先對(duì)數(shù)據(jù)排序，保證每個(gè)線程按固定的順序來處理記錄，也可以大大降低出現(xiàn)死鎖的可能。
• 在事務(wù)中，如果要更新記錄，應(yīng)該直接申請(qǐng)足夠級(jí)別的鎖，即排他鎖，而不應(yīng)先申請(qǐng)共享鎖，更新時(shí)再申請(qǐng)排他鎖，因?yàn)楫?dāng)用戶申請(qǐng)排他鎖時(shí)，其他事務(wù)可能又已經(jīng)獲得了相同記錄的共享鎖，從而造成鎖沖突，甚至死鎖。
• 在 REPEATABLE-READ 隔離級(jí)別下，如果兩個(gè)線程同時(shí)對(duì)相同條件記錄用 SELECT...FOR UPDATE 加排他鎖，在沒有符合該條件記錄情況下，兩個(gè)線程都會(huì)加鎖成功。

程序發(fā)現(xiàn)記錄尚不存在，就試圖插入一條新記錄，如果兩個(gè)線程都這么做，就會(huì)出現(xiàn)死鎖。這種情況下，將隔離級(jí)別改成 READ COMMITTED，就可避免問題。

• 當(dāng)隔離級(jí)別為 READ COMMITTED 時(shí)，如果兩個(gè)線程都先執(zhí)行 SELECT...FOR UPDATE，判斷是否存在符合條件的記錄，如果沒有，就插入記錄。

此時(shí)，只有一個(gè)線程能插入成功，另一個(gè)線程會(huì)出現(xiàn)鎖等待，當(dāng)?shù)谝粋€(gè)線程提交后，第二個(gè)線程會(huì)因主鍵重出錯(cuò)，但雖然這個(gè)線程出錯(cuò)了，卻會(huì)獲得一個(gè)排他鎖。這時(shí)如果有第三個(gè)線程又來申請(qǐng)排他鎖，也會(huì)出現(xiàn)死鎖。

對(duì)于這種情況，可以直接做插入操作，然后再捕獲主鍵重異常，或者在遇到主鍵重錯(cuò)誤時(shí)，總是執(zhí)行 ROLLBACK 釋放獲得的排他鎖。

什么時(shí)候使用表鎖

對(duì)于 InnoDB 表，在絕大部分情況下都應(yīng)該使用行級(jí)鎖，因?yàn)槭聞?wù)和行鎖往往是我們之所以選擇 InnoDB 表的理由。

但在個(gè)別特殊事務(wù)中，也可以考慮使用表級(jí)鎖：

• 事務(wù)需要更新大部分或全部數(shù)據(jù)，表又比較大，如果使用默認(rèn)的行鎖，不僅這個(gè)事務(wù)執(zhí)行效率低，而且可能造成其他事務(wù)長時(shí)間鎖等待和鎖沖突，這種情況下可以考慮使用表鎖來提高該事務(wù)的執(zhí)行速度。
• 事務(wù)涉及多個(gè)表，比較復(fù)雜，很可能引起死鎖，造成大量事務(wù)回滾。這種情況也可以考慮一次性鎖定事務(wù)涉及的表，從而避免死鎖、減少數(shù)據(jù)庫因事務(wù)回滾帶來的開銷。

當(dāng)然，應(yīng)用中這兩種事務(wù)不能太多，否則，就應(yīng)該考慮使用 MyISAM 表了。

在 InnoDB 下，使用表鎖要注意以下兩點(diǎn)：

• 使用 LOCK TABLES 雖然可以給 InnoDB 加表級(jí)鎖，但必須說明的是，表鎖不是由 InnoDB 存儲(chǔ)引擎層管理的，而是由其上一層──MySQL Server 負(fù)責(zé)的。

僅當(dāng) autocommit=0(不自動(dòng)提交，默認(rèn)是自動(dòng)提交的)、InnoDB_table_locks=1(默認(rèn)設(shè)置)時(shí)，InnoDB 層才能知道 MySQL 加的表鎖，MySQL Server 也才能感知 InnoDB 加的行鎖。

這種情況下，InnoDB 才能自動(dòng)識(shí)別涉及表級(jí)鎖的死鎖，否則，InnoDB 將無法自動(dòng)檢測(cè)并處理這種死鎖。

• 在用 LOCK TABLES 對(duì) InnoDB 表加鎖時(shí)要注意，要將 AUTOCOMMIT 設(shè)為 0，否則 MySQL 不會(huì)給表加鎖。

事務(wù)結(jié)束前，不要用 UNLOCK TABLES 釋放表鎖，因?yàn)?UNLOCK TABLES 會(huì)隱含地提交事務(wù)。

COMMIT 或 ROLLBACK 并不能釋放用 LOCK TABLES 加的表級(jí)鎖，必須用 UNLOCK TABLES 釋放表鎖。

正確的方式見如下語句，例如，如果需要寫表 t1 并從表 t 讀，可以按如下做：

SET AUTOCOMMIT=0; 
LOCK TABLES t1 WRITE, t2 READ, ...; 
[do something with tables t1 and t2 here]; 
COMMIT; 
UNLOCK TABLES; 

InnoDB 行鎖優(yōu)化建議

InnoDB 存儲(chǔ)引擎由于實(shí)現(xiàn)了行級(jí)鎖定，雖然在鎖定機(jī)制的實(shí)現(xiàn)方面所帶來的性能損耗可能比表級(jí)鎖定會(huì)要更高一些，但是在整體并發(fā)處理能力方面要遠(yuǎn)遠(yuǎn)優(yōu)于 MyISAM 的表級(jí)鎖定的。

當(dāng)系統(tǒng)并發(fā)量較高的時(shí)候，InnoDB 的整體性能和 MyISAM 相比就會(huì)有比較明顯的優(yōu)勢(shì)了。

但是，InnoDB 的行級(jí)鎖定同樣也有其脆弱的一面，當(dāng)我們使用不當(dāng)?shù)臅r(shí)候，可能會(huì)讓 InnoDB 的整體性能表現(xiàn)不僅不能比 MyISAM 高，甚至可能會(huì)更差。

①要想合理利用 InnoDB 的行級(jí)鎖定，做到揚(yáng)長避短，我們必須做好以下工作：

• 盡可能讓所有的數(shù)據(jù)檢索都通過索引來完成，從而避免 InnoDB 因?yàn)闊o法通過索引鍵加鎖而升級(jí)為表級(jí)鎖定。
• 合理設(shè)計(jì)索引，讓 InnoDB 在索引鍵上面加鎖的時(shí)候盡可能準(zhǔn)確，盡可能的縮小鎖定范圍，避免造成不必要的鎖定而影響其他 Query 的執(zhí)行。
• 盡可能減少基于范圍的數(shù)據(jù)檢索過濾條件，避免因?yàn)殚g隙鎖帶來的負(fù)面影響而鎖定了不該鎖定的記錄。
• 盡量控制事務(wù)的大小，減少鎖定的資源量和鎖定時(shí)間長度。
• 在業(yè)務(wù)環(huán)境允許的情況下，盡量使用較低級(jí)別的事務(wù)隔離，以減少 MySQL 因?yàn)閷?shí)現(xiàn)事務(wù)隔離級(jí)別所帶來的附加成本。

②由于 InnoDB 的行級(jí)鎖定和事務(wù)性，所以肯定會(huì)產(chǎn)生死鎖，下面是一些比較常用的減少死鎖產(chǎn)生概率的小建議：

• 類似業(yè)務(wù)模塊中，盡可能按照相同的訪問順序來訪問，防止產(chǎn)生死鎖。
• 在同一個(gè)事務(wù)中，盡可能做到一次鎖定所需要的所有資源，減少死鎖產(chǎn)生概率。
• 對(duì)于非常容易產(chǎn)生死鎖的業(yè)務(wù)部分，可以嘗試使用升級(jí)鎖定顆粒度，通過表級(jí)鎖定來減少死鎖產(chǎn)生的概率。

③可以通過檢查 InnoDB_row_lock 狀態(tài)變量來分析系統(tǒng)上的行鎖的爭(zhēng)奪情況：

mysql> show status like 'InnoDB_row_lock%'; 
+-------------------------------+-------+ 
| Variable_name                 | Value | 
+-------------------------------+-------+ 
| InnoDB_row_lock_current_waits | 0     | 
| InnoDB_row_lock_time          | 0     | 
| InnoDB_row_lock_time_avg      | 0     | 
| InnoDB_row_lock_time_max      | 0     | 
| InnoDB_row_lock_waits         | 0     | 
+-------------------------------+-------+ 

InnoDB 的行級(jí)鎖定狀態(tài)變量不僅記錄了鎖定等待次數(shù)，還記錄了鎖定總時(shí)長，每次平均時(shí)長，以及最大時(shí)長，此外還有一個(gè)非累積狀態(tài)量顯示了當(dāng)前正在等待鎖定的等待數(shù)量。

對(duì)各個(gè)狀態(tài)量的說明如下：

• InnoDB_row_lock_current_waits：當(dāng)前正在等待鎖定的數(shù)量。
• InnoDB_row_lock_time：從系統(tǒng)啟動(dòng)到現(xiàn)在鎖定總時(shí)間長度。
• InnoDB_row_lock_time_avg：每次等待所花平均時(shí)間。
• InnoDB_row_lock_time_max：從系統(tǒng)啟動(dòng)到現(xiàn)在等待最常的一次所花的時(shí)間。
• InnoDB_row_lock_waits：系統(tǒng)啟動(dòng)后到現(xiàn)在總共等待的次數(shù)。

對(duì)于這五個(gè)狀態(tài)變量，比較重要的三項(xiàng)是：

• InnoDB_row_lock_time_avg(等待平均時(shí)長)
• InnoDB_row_lock_waits(等待總次數(shù))
• InnoDB_row_lock_time(等待總時(shí)長)

尤其是當(dāng)?shù)却螖?shù)很高，而且每次等待時(shí)長也不小的時(shí)候，我們就需要分析系統(tǒng)中為什么會(huì)有如此多的等待，然后根據(jù)分析結(jié)果著手指定優(yōu)化計(jì)劃。

如果發(fā)現(xiàn)鎖爭(zhēng)用比較嚴(yán)重，如 InnoDB_row_lock_waits 和 InnoDB_row_lock_time_avg 的值比較高。

還可以通過設(shè)置 InnoDB Monitors 來進(jìn)一步觀察發(fā)生鎖沖突的表、數(shù)據(jù)行等，并分析鎖爭(zhēng)用的原因。

鎖沖突的表、數(shù)據(jù)行等，并分析鎖爭(zhēng)用的原因。具體方法如下：

mysql> create table InnoDB_monitor(a INT) engine=InnoDB; 

然后就可以用下面的語句來進(jìn)行查看：

mysql> show engine InnoDB status; 

監(jiān)視器可以通過發(fā)出下列語句來停止查看：

mysql> drop table InnoDB_monitor; 

設(shè)置監(jiān)視器后，會(huì)有詳細(xì)的當(dāng)前鎖等待的信息，包括表名、鎖類型、鎖定記錄的情況等，便于進(jìn)行進(jìn)一步的分析和問題的確定?？赡軙?huì)有讀者朋友問為什么要先創(chuàng)建一個(gè)叫 InnoDB_monitor 的表呢?

因?yàn)閯?chuàng)建該表實(shí)際上就是告訴 InnoDB 我們開始要監(jiān)控他的細(xì)節(jié)狀態(tài)了，然后 InnoDB 就會(huì)將比較詳細(xì)的事務(wù)以及鎖定信息記錄進(jìn)入 MySQL 的 errorlog 中，以便我們后面做進(jìn)一步分析使用。

打開監(jiān)視器以后，默認(rèn)情況下每 15 秒會(huì)向日志中記錄監(jiān)控的內(nèi)容，如果長時(shí)間打開會(huì)導(dǎo)致 .err 文件變得非常的巨大。

所以用戶在確認(rèn)問題原因之后，要記得刪除監(jiān)控表以關(guān)閉監(jiān)視器，或者通過使用“--console”選項(xiàng)來啟動(dòng)服務(wù)器以關(guān)閉寫日志文件。

查看死鎖、解除鎖

結(jié)合上面對(duì)表鎖和行鎖的分析情況，解除正在死鎖的狀態(tài)有兩種方法：

第一種

①查詢是否鎖表

show OPEN TABLES where In_use > 0; 

②查詢進(jìn)程(如果您有 SUPER 權(quán)限，您可以看到所有線程。否則，您只能看到您自己的線程)

show processlist 

③殺死進(jìn)程 id(就是上面命令的 id 列)

kill id 

第二種

①查看下在鎖的事務(wù)

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; 

②殺死進(jìn)程 id(就是上面命令的 trx_mysql_thread_id 列)

kill 線程ID 

例子：

• 查出死鎖進(jìn)程：SHOW PROCESSLIST
• 殺掉進(jìn)程：KILL 420821

其他關(guān)于查看死鎖的命令：

①查看當(dāng)前的事務(wù)

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; 

②查看當(dāng)前鎖定的事務(wù)

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 

③查看當(dāng)前等鎖的事務(wù)

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 

事務(wù)

MySQL 事務(wù)屬性

事務(wù)是由一組 SQL 語句組成的邏輯處理單元，事務(wù)具有 ACID 屬性：

• 原子性(Atomicity)：事務(wù)是一個(gè)原子操作單元。在當(dāng)時(shí)原子是不可分割的最小元素，其對(duì)數(shù)據(jù)的修改，要么全部成功，要么全部都不成功。
• 一致性(Consistent)：事務(wù)開始到結(jié)束的時(shí)間段內(nèi)，數(shù)據(jù)都必須保持一致狀態(tài)。
• 隔離性(Isolation)：數(shù)據(jù)庫系統(tǒng)提供一定的隔離機(jī)制，保證事務(wù)在不受外部并發(fā)操作影響的"獨(dú)立"環(huán)境執(zhí)行。
• 持久性(Durable)：事務(wù)完成后，它對(duì)于數(shù)據(jù)的修改是永久性的，即使出現(xiàn)系統(tǒng)故障也能夠保持。

事務(wù)常見問題

①更新丟失(Lost Update)

原因：當(dāng)多個(gè)事務(wù)選擇同一行操作，并且都是基于最初選定的值，由于每個(gè)事務(wù)都不知道其他事務(wù)的存在，就會(huì)發(fā)生更新覆蓋的問題。類比 Github 提交沖突。

②臟讀(Dirty Reads)

原因：事務(wù) A 讀取了事務(wù) B 已經(jīng)修改但尚未提交的數(shù)據(jù)。若事務(wù) B 回滾數(shù)據(jù)，事務(wù) A 的數(shù)據(jù)存在不一致性的問題。

③不可重復(fù)讀(Non-Repeatable Reads)

原因：事務(wù) A 第一次讀取最初數(shù)據(jù)，第二次讀取事務(wù) B 已經(jīng)提交的修改或刪除數(shù)據(jù)。導(dǎo)致兩次讀取數(shù)據(jù)不一致。不符合事務(wù)的隔離性。

④幻讀(Phantom Reads)

原因：事務(wù) A 根據(jù)相同條件第二次查詢到事務(wù) B 提交的新增數(shù)據(jù)，兩次數(shù)據(jù)結(jié)果集不一致。不符合事務(wù)的隔離性。

幻讀和臟讀有點(diǎn)類似，臟讀是事務(wù) B 里面修改了數(shù)據(jù)，幻讀是事務(wù) B 里面新增了數(shù)據(jù)。

事務(wù)的隔離級(jí)別

數(shù)據(jù)庫的事務(wù)隔離越嚴(yán)格，并發(fā)副作用越小，但付出的代價(jià)也就越大。這是因?yàn)槭聞?wù)隔離實(shí)質(zhì)上是將事務(wù)在一定程度上"串行"進(jìn)行，這顯然與"并發(fā)"是矛盾的。

根據(jù)自己的業(yè)務(wù)邏輯，權(quán)衡能接受的最大副作用。從而平衡了"隔離" 和 "并發(fā)"的問題。MySQL 默認(rèn)隔離級(jí)別是可重復(fù)讀。

臟讀，不可重復(fù)讀，幻讀，其實(shí)都是數(shù)據(jù)庫讀一致性問題，必須由數(shù)據(jù)庫提供一定的事務(wù)隔離機(jī)制來解決：

+------------------------------+---------------------+--------------+--------------+--------------+ 
| 隔離級(jí)別                      | 讀數(shù)據(jù)一致性         | 臟讀         | 不可重復(fù) 讀   | 幻讀         | 
+------------------------------+---------------------+--------------+--------------+--------------+ 
| 未提交讀(Read uncommitted)    | 最低級(jí)別            | 是            | 是           | 是           |  
+------------------------------+---------------------+--------------+--------------+--------------+ 
| 已提交讀(Read committed)      | 語句級(jí)              | 否           | 是           | 是           | 
+------------------------------+---------------------+--------------+--------------+--------------+ 
| 可重復(fù)讀(Repeatable read)     | 事務(wù)級(jí)              | 否           | 否           | 是           | 
+------------------------------+---------------------+--------------+--------------+--------------+ 
| 可序列化(Serializable)        | 最高級(jí)別，事務(wù)級(jí)     | 否           | 否           | 否           | 
+------------------------------+---------------------+--------------+--------------+--------------+ 

查看當(dāng)前數(shù)據(jù)庫的事務(wù)隔離級(jí)別：show variables like 'tx_isolation'。

mysql> show variables like 'tx_isolation'; 
+---------------+-----------------+ 
| Variable_name | Value           | 
+---------------+-----------------+ 
| tx_isolation  | REPEATABLE-READ | 
+---------------+-----------------+ 

事務(wù)級(jí)別的設(shè)置

1.未提交讀（READ UNCOMMITED） 解決的障礙：無； 引入的問題：臟讀 
    set SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
 
2.已提交讀 （READ COMMITED） 解決的障礙：臟讀； 引入的問題：不可重復(fù)讀 
    set SESSION TRANSACTION ISOLATION LEVEL read committed; 
 
3.可重復(fù)讀（REPEATABLE READ）解決的障礙：不可重復(fù)讀； 引入的問題： 
    set SESSION TRANSACTION ISOLATION LEVEL repeatable read; 
 
4.可串行化（SERIALIZABLE）解決的障礙：可重復(fù)讀； 引入的問題：鎖全表，性能低下 
    set SESSION TRANSACTION ISOLATION LEVEL repeatable read; 

總結(jié)：事務(wù)隔離級(jí)別為可重復(fù)讀時(shí)，如果有索引(包括主鍵索引)的時(shí)候，以索引列為條件更新數(shù)據(jù)，會(huì)存在間隙鎖間、行鎖、頁鎖的問題，從而鎖住一些行;如果沒有索引，更新數(shù)據(jù)時(shí)會(huì)鎖住整張表。

事務(wù)隔離級(jí)別為串行化時(shí)，讀寫數(shù)據(jù)都會(huì)鎖住整張表，隔離級(jí)別越高，越能保證數(shù)據(jù)的完整性和一致性，但是對(duì)并發(fā)性能的影響也越大。

對(duì)于多數(shù)應(yīng)用程序，可以優(yōu)先考慮把數(shù)據(jù)庫系統(tǒng)的隔離級(jí)別設(shè)為 Read Committed，它能夠避免臟讀取，而且具有較好的并發(fā)性能。

事務(wù)保存點(diǎn)，實(shí)現(xiàn)部分回滾

定義保存點(diǎn)，以及回滾到指定保存點(diǎn)前狀態(tài)的語法如下：

• 定義保存點(diǎn)：SAVEPOINT 保存點(diǎn)名。
• 回滾到指定保存點(diǎn)：ROLLBACK TO SAVEPOINT 保存點(diǎn)名。

1、查看user表中的數(shù)據(jù) 
 
mysql> select * from user; 
+-----+----------+-----+------+ 
| mid | name | scx | word | 
+-----+----------+-----+------+ 
| 1 | zhangsan | 0 | NULL | 
| 2 | wangwu    | 1 | NULL | 
+-----+----------+-----+------+ 
2 rows in set (0.05 sec) 
2、mysql事務(wù)開始 
 
mysql> BEGIN; -- 或者start transaction； 
Query OK, 0 rows affected (0.00 sec) 
3、向表user中插入2條數(shù)據(jù) 
 
mysql> INSERT INTO user VALUES ('3','one','0',''); 
Query OK, 1 row affected (0.08 sec) 
mysql> INSERT INTO user VALUES ('4,'two','0',''); 
Query OK, 1 row affected (0.00 sec) 
mysql> select * from user; 
+-----+----------+-----+------+ 
| mid | name | scx | word | 
+-----+----------+-----+------+ 
| 1 | zhangsan | 0 | NULL | 
| 2 | wangwu    | 1 | NULL | 
| 3 | one            | 0 | | 
| 4 | two             | 0 | | 
+-----+----------+-----+------+ 
4 rows in set (0.00 sec) 
4、指定保存點(diǎn)，保存點(diǎn)名為test 
 
mysql> SAVEPOINT test; 
Query OK, 0 rows affected (0.00 sec) 
5、向表user中插入第3條數(shù)據(jù) 
 
mysql> INSERT INTO user VALUES ('5','three','0',''); 
Query OK, 1 row affected (0.00 sec) 
mysql> select * from user; 
+-----+----------+-----+------+ 
| mid | name | scx | word | 
+-----+----------+-----+------+ 
| 1 | zhangsan | 0 | NULL | 
| 2 | wangwu | 1 | NULL | 
| 3 | one | 0 | | 
| 4 | two | 0 | | 
| 5 | three | 0 | | 
+-----+----------+-----+------+ 
5 rows in set (0.02 sec) 
6、回滾到保存點(diǎn)test 
 
mysql> ROLLBACK TO SAVEPOINT test; 
Query OK, 0 rows affected (0.31 sec) 
mysql> select * from user; 
+-----+----------+-----+------+ 
| mid | name | scx | word | 
+-----+----------+-----+------+ 
| 1 | zhangsan | 0 | NULL | 
| 2 | wangwu    | 1 | NULL | 
| 3 | one            | 0 | | 
| 4 | two            | 0 | | 
+-----+----------+-----+------+ 
4 rows in set (0.00 sec) 

我們可以看到保存點(diǎn) test 以后插入的記錄沒有顯示了，即成功團(tuán)滾到了定義保存點(diǎn) test 前的狀態(tài)。利用保存點(diǎn)可以實(shí)現(xiàn)只提交事務(wù)中部分處理的功能。

事務(wù)控制語句

BEGIN或START TRANSACTION；顯式地開啟一個(gè)事務(wù)； 
COMMIT；                  也可以使用COMMIT WORK，不過二者是等價(jià)的。COMMIT會(huì)提交事務(wù)，并使已對(duì)數(shù)據(jù)庫進(jìn)行的所有修改成為永久性的； 
ROLLBACK；                有可以使用ROLLBACK WORK，不過二者是等價(jià)的?；貪L會(huì)結(jié)束用戶的事務(wù)，并撤銷正在進(jìn)行的所有未提交的修改； 
SAVEPOINT identifier；    SAVEPOINT允許在事務(wù)中創(chuàng)建一個(gè)保存點(diǎn)，一個(gè)事務(wù)中可以有多個(gè)SAVEPOINT； 
RELEASE SAVEPOINT identifier；    刪除一個(gè)事務(wù)的保存點(diǎn)，當(dāng)沒有指定的保存點(diǎn)時(shí)，執(zhí)行該語句會(huì)拋出一個(gè)異常； 
ROLLBACK TO identifier；   把事務(wù)回滾到標(biāo)記點(diǎn)； 
SET TRANSACTION；   用來設(shè)置事務(wù)的隔離級(jí)別。InnoDB存儲(chǔ)引擎提供事務(wù)的隔離級(jí)別有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。 
 
 
  用 BEGIN, ROLLBACK, COMMIT來實(shí)現(xiàn) 
  BEGIN 開始一個(gè)事務(wù) 
  ROLLBACK 事務(wù)回滾 
  COMMIT 事務(wù)確認(rèn) 
  直接用 SET 來改變 MySQL 的自動(dòng)提交模式: 
  SET AUTOCOMMIT=0或者off 禁止自動(dòng)提交 
  SET AUTOCOMMIT=1或者on 開啟自動(dòng)提交