本文實(shí)例講述了MySQL鎖機(jī)制與用法。分享給大家供大家參考，具體如下：

MySQL的鎖機(jī)制比較簡(jiǎn)單，其最顯著的特點(diǎn)是不同的存儲(chǔ)引擎支持不同的鎖機(jī)制。比如，MyISAM和MEMORY存儲(chǔ)引擎采用的是表級(jí)鎖；BDB存儲(chǔ)引擎采用的是頁(yè)面鎖，但也支持表級(jí)鎖；InnoDB存儲(chǔ)引擎既支持行級(jí)鎖，也支持表級(jí)鎖，但默認(rèn)情況下采用行級(jí)鎖。

MySQL這3種鎖的特性可大致歸納如下：

（1）表級(jí)鎖：開銷小，加鎖快；不會(huì)出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。

（2）行級(jí)鎖：開銷大，加鎖慢；會(huì)出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。

（3）頁(yè)面鎖：開銷和加鎖時(shí)間界于表鎖和行鎖之間；會(huì)出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般。

僅從鎖的角度來(lái)說(shuō)，表級(jí)鎖更適合于以查詢?yōu)橹?，只有少量按索引條件更新數(shù)據(jù)的應(yīng)用，如Web應(yīng)用；而行級(jí)鎖則更適合于有大量按索引條件并發(fā)更新少量不同數(shù)據(jù)，同時(shí)又有并發(fā)查詢的應(yīng)用，如一些在線事務(wù)處理系統(tǒng)。

一、MyISAM表鎖

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

show status like 'table%';

如果table_locks_waited 的值比較高，則說(shuō)明存在著比較嚴(yán)重的表級(jí)鎖爭(zhēng)用情況。

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

MySQL 的表級(jí)鎖有兩種模式：表共享讀鎖和表獨(dú)占寫鎖。

當(dāng)一個(gè)session對(duì)某個(gè)表加了讀鎖之后，該session只能訪問(wèn)加鎖的這個(gè)表，而且只能進(jìn)行讀操作；其他session可以對(duì)這個(gè)表進(jìn)行讀操作，但是進(jìn)行寫操作會(huì)被阻塞，需要等待鎖的釋放。當(dāng)一個(gè)session對(duì)某個(gè)表加了寫鎖之后，該session只能訪問(wèn)加鎖的這個(gè)表，可以進(jìn)行讀操作和寫操作，其他session對(duì)這個(gè)表的讀和寫操作都會(huì)被阻塞，需要等待鎖的釋放。

MyISAM 表的讀操作與寫操作之間，以及寫操作之間是串行的。

3. 如何加表鎖

加讀鎖：

lock table tbl_name read;

加寫鎖：

lock table tbl_name write;

釋放鎖：

unlock tables;

MyISAM 在執(zhí)行查詢語(yǔ)句前，會(huì)自動(dòng)給涉及的所有表加讀鎖，在執(zhí)行更新操作前，會(huì)自動(dòng)給涉及的表加寫鎖，這個(gè)過(guò)程并不需要用戶干預(yù)，因此，用戶一般不需要直接用LOCK TABLE命令給MyISAM表顯式加鎖。給MyISAM表顯式加鎖，一般是為了在一定程度模擬事務(wù)操作，實(shí)現(xiàn)對(duì)某一時(shí)間點(diǎn)多個(gè)表的一致性讀取。

注意，當(dāng)使用LOCK TABLES時(shí)，不僅需要一次鎖定用到的所有表，而且，同一個(gè)表在SQL語(yǔ)句中出現(xiàn)多少次，就要通過(guò)與SQL語(yǔ)句中相同的別名鎖定多少次，否則也會(huì)出錯(cuò)！

4. 并發(fā)插入

MyISAM存儲(chǔ)引擎有一個(gè)系統(tǒng)變量concurrent_insert，專門用以控制其并發(fā)插入的行為，其值分別可以為0、1或2。

（1）當(dāng)concurrent_insert設(shè)置為0時(shí)，不允許并發(fā)插入。

（2）當(dāng)concurrent_insert設(shè)置為1時(shí)，如果MyISAM表中沒(méi)有空洞（即表的中間沒(méi)有被刪除的行），MyISAM允許在一個(gè)進(jìn)程讀表的同時(shí)，另一個(gè)進(jìn)程從表尾插入記錄。這也是MySQL的默認(rèn)設(shè)置。

（3）當(dāng)concurrent_insert設(shè)置為2時(shí)，無(wú)論MyISAM表中有沒(méi)有空洞，都允許在表尾并發(fā)插入記錄。

只需在加表鎖命令中加入“l(fā)ocal”選項(xiàng)，即：lock table tbl_name local read，在滿足MyISAM表并發(fā)插入條件的情況下，其他用戶就可以在表尾并發(fā)插入記錄，但更新操作會(huì)被阻塞，而且加鎖的用戶無(wú)法訪問(wèn)到其他用戶并發(fā)插入的記錄。

5. MyISAM鎖調(diào)度

當(dāng)寫進(jìn)程和讀進(jìn)程同時(shí)請(qǐng)求同一個(gè)MyISAM表的寫鎖和讀鎖時(shí)，寫進(jìn)程會(huì)優(yōu)先獲得鎖。不僅如此，即使讀請(qǐng)求先到鎖等待隊(duì)列，寫請(qǐng)求后到，寫鎖也會(huì)插到讀鎖請(qǐng)求之前！這是因?yàn)镸ySQL認(rèn)為寫請(qǐng)求一般比讀請(qǐng)求更重要。這也正是MyISAM表不太適合于有大量更新操作和查詢操作應(yīng)用的原因，因?yàn)榇罅康母虏僮鲿?huì)造成查詢操作很難獲得讀鎖，從而可能永遠(yuǎn)阻塞。

通過(guò)一下一些設(shè)置調(diào)節(jié)MyISAM的調(diào)度行為：

（1）通過(guò)指定啟動(dòng)參數(shù)low-priority-updates，使MyISAM引擎默認(rèn)給予讀請(qǐng)求以優(yōu)先的權(quán)利。

（2）通過(guò)執(zhí)行命令SET LOW_PRIORITY_UPDATES=1，使該連接發(fā)出的更新請(qǐng)求優(yōu)先級(jí)降低。

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

（4）給系統(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ì)。

二、InnoDB鎖問(wèn)題

1. 查詢InnoDB行鎖爭(zhēng)用情況

show status like 'innodb_row_lock%';

如果InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比較高，說(shuō)明鎖爭(zhēng)用比較嚴(yán)重，這時(shí)可以通過(guò)設(shè)置InnoDB Monitors來(lái)進(jìn)一步觀察發(fā)生鎖沖突的表、數(shù)據(jù)行等，并分析鎖爭(zhēng)用的原因。

打開監(jiān)視器：

CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;
Show innodb status\G;

停止監(jiān)視器：

DROP TABLE innodb_monitor;

打開監(jiān)視器以后，默認(rèn)情況下每15 秒會(huì)向日志中記錄監(jiān)控的內(nèi)容，如果長(zhǎng)時(shí)間打開會(huì)導(dǎo)致.err 文件變得非常的巨大，所以用戶在確認(rèn)問(wèn)題原因之后，要記得刪除監(jiān)控表以關(guān)閉監(jiān)視器，或者通過(guò)使用“--console”選項(xiàng)來(lái)啟動(dòng)服務(wù)器以關(guān)閉寫日志文件。

2. InnoDB的行鎖及加鎖方法

InnoDB的行鎖有兩種：共享鎖（S）和排他鎖（X）。為了允許行鎖和表鎖共存，實(shí)現(xiàn)多粒度鎖機(jī)制，InnoDB還有兩種內(nèi)部使用的意向鎖：意向共享鎖和意向排他鎖，這兩種意向鎖都是表鎖。一個(gè)事務(wù)在給數(shù)據(jù)行加鎖之前必須先取得對(duì)應(yīng)表對(duì)應(yīng)的意向鎖。

意向鎖是InnoDB自動(dòng)加的，不需用戶干預(yù)。對(duì)于UPDATE、DELETE 和INSERT 語(yǔ)句，InnoDB會(huì)自動(dòng)給涉及數(shù)據(jù)集加排他鎖（X）；對(duì)于普通SELECT語(yǔ)句，InnoDB 不會(huì)加任何鎖；事務(wù)可以通過(guò)以下語(yǔ)句顯式給記錄集加共享鎖或排他鎖。

Set autocommit=0;

共享鎖（S）：

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

排他鎖（X）：

SELECT * FROM table_name WHERE ... FOR UPDATE

釋放鎖：

unlock tables;

（會(huì)隱含提交事務(wù)）

當(dāng)一個(gè)事務(wù)獲得一個(gè)表的共享鎖時(shí)，其他事務(wù)可以查詢?cè)摫淼挠涗?，也可以?duì)該記錄加共享鎖。當(dāng)一個(gè)事務(wù)對(duì)表進(jìn)行更新操作時(shí)，若存在另一個(gè)事務(wù)也在該表加了共享鎖，則需要等待鎖的釋放，若另一個(gè)事務(wù)同時(shí)也對(duì)該表執(zhí)行了更新操作，則會(huì)導(dǎo)致死鎖，另一個(gè)事務(wù)退出，當(dāng)前事務(wù)完成更新操作。當(dāng)一個(gè)事務(wù)獲得一個(gè)表的排他鎖時(shí)，其他事務(wù)只能對(duì)該表的記錄進(jìn)行查詢，不能加共享鎖，也不能更新記錄，會(huì)出現(xiàn)等待。

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

InnoDB行鎖是通過(guò)給索引上的索引項(xiàng)加鎖來(lái)實(shí)現(xiàn)的，InnoDB 這種行鎖實(shí)現(xiàn)特點(diǎn)意味著：

（1）只有通過(guò)索引條件檢索數(shù)據(jù)，InnoDB才使用行級(jí)鎖，否則，InnoDB 將使用表鎖。

（2）由于MySQL的行鎖是針對(duì)索引加的鎖，不是針對(duì)記錄加的鎖，所以雖然是訪問(wèn)不同行的記錄，但是如果是使用相同的索引鍵，是會(huì)出現(xiàn)鎖沖突的。

（3）當(dāng)表有多個(gè)索引的時(shí)候，不同的事務(wù)可以使用不同的索引鎖定不同的行，另外，不論是使用主鍵索引、唯一索引或普通索引，InnoDB 都會(huì)使用行鎖來(lái)對(duì)數(shù)據(jù)加鎖。（雖然使用的是不同的索引，但是如果記錄已經(jīng)被其他session鎖定的話也是需要等待的。）

（4）即便在條件中使用了索引字段，但是否使用索引來(lái)檢索數(shù)據(jù)是由MySQL 通過(guò)判斷不同執(zhí)行計(jì)劃的代價(jià)來(lái)決定的，如果MySQL 認(rèn)為全表掃描效率更高，比如對(duì)一些很小的表，它就不會(huì)使用索引，這種情況下InnoDB將使用表鎖，而不是行鎖。

4. 間隙鎖

當(dāng)使用范圍條件檢索數(shù)據(jù)的時(shí)候，對(duì)于鍵值在條件范圍內(nèi)但并不存在的記錄，InnoDB也會(huì)進(jìn)行加鎖，這個(gè)鎖就叫“間隙鎖”。InnoDB使用間隙鎖的目的，一方面是為了防止幻讀，另一方面是為了滿足恢復(fù)和復(fù)制的需要。但是這種加鎖機(jī)制會(huì)阻塞符合條件范圍內(nèi)鍵值的并發(fā)插入，造成嚴(yán)重的鎖等待，所以應(yīng)該盡量避免使用范圍條件來(lái)檢索數(shù)據(jù)。

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

5. 恢復(fù)和復(fù)制的需要對(duì)InnoDB鎖機(jī)制的影響

MySQL通過(guò)BINLOG記錄執(zhí)行成功的INSERT、UPDATE、DELETE等更新數(shù)據(jù)的SQL語(yǔ)句，并由此實(shí)現(xiàn)MySQL數(shù)據(jù)庫(kù)的恢復(fù)和主從復(fù)制。MySQL的恢復(fù)機(jī)制（復(fù)制其實(shí)就是在Slave Mysql不斷做基于BINLOG的恢復(fù)）有以下特點(diǎn)：

（1）MySQL的恢復(fù)是SQL語(yǔ)句級(jí)的，也就是重新執(zhí)行BINLOG中的SQL語(yǔ)句。

（2）MySQL 的Binlog是按照事務(wù)提交的先后順序記錄的，恢復(fù)也是按這個(gè)順序進(jìn)行的。

所以MySQL的恢復(fù)和復(fù)制對(duì)鎖機(jī)制的要求是：在一個(gè)事務(wù)未提交前，其他并發(fā)事務(wù)不能插入滿足其鎖定條件的任何記錄，也就是不允許出現(xiàn)幻讀。

另外，對(duì)于一般的select語(yǔ)句，MySQL使用多版本數(shù)據(jù)來(lái)實(shí)現(xiàn)一致性，不需要加任何鎖，然而，對(duì)于“insert into target_tab select * from source_tab where ...”和“create table new_tab ...select ... From source_tab where ...”這種SQL語(yǔ)句，用戶并沒(méi)有對(duì)source_tab做任何更新操作，但MySQL對(duì)這種SQL語(yǔ)句做了特別處理，給source_tab加了共享鎖。這是因?yàn)?，不加鎖的話，如果這個(gè)SQL語(yǔ)句執(zhí)行期間，有另一個(gè)事務(wù)對(duì)source_tab做了更新并且先進(jìn)行了提交，那么在BINLOG中，更新操作的位置會(huì)在該SQL語(yǔ)句之前，使用這個(gè)BINLOG進(jìn)行數(shù)據(jù)庫(kù)恢復(fù)的話，恢復(fù)的結(jié)果就會(huì)與實(shí)際的應(yīng)用邏輯不符，進(jìn)行復(fù)制則會(huì)導(dǎo)致主從數(shù)據(jù)庫(kù)不一致。因?yàn)閷?shí)際上應(yīng)用插入target_tab或new_tab中的數(shù)據(jù)是另一個(gè)事務(wù)對(duì)source_tab更新前的數(shù)據(jù)，而BINLOG記錄的卻是先進(jìn)行更新再執(zhí)行select...insert...語(yǔ)句。如果上述語(yǔ)句的SELECT是范圍條件，InnoDB還會(huì)給源表加間隙鎖。所以這種SQL語(yǔ)句會(huì)阻塞對(duì)原表的并發(fā)更新，應(yīng)盡量避免使用。

6. InnoDB使用表鎖的情況及注意事項(xiàng)

對(duì)于InnoDB表，在絕大部分情況下都應(yīng)該使用行級(jí)鎖，但在個(gè)別特殊事務(wù)中，也可以考慮使用表級(jí)鎖，主要有以下兩種情況：

（1）事務(wù)需要更新大部分或全部數(shù)據(jù)，表又比較大，如果使用默認(rèn)的行鎖，不僅這個(gè)事務(wù)執(zhí)行效率低，而且可能造成其他事務(wù)長(zhǎng)時(shí)間鎖等待和鎖沖突，這種情況下可以考慮使用表鎖來(lái)提高該事務(wù)的執(zhí)行速度。

（2）事務(wù)涉及多個(gè)表，比較復(fù)雜，很可能引起死鎖，造成大量事務(wù)回滾。這種情況也可以考慮一次性鎖定事務(wù)涉及的表，從而避免死鎖、減少數(shù)據(jù)庫(kù)因事務(wù)回滾帶來(lái)的開銷。

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

（1）使用LOCK TABLES雖然可以給InnoDB加表級(jí)鎖，但表鎖不是由InnoDB存儲(chǔ)引擎層管理的，而是由其上一層──MySQL Server負(fù)責(zé)的，僅當(dāng)autocommit=0、innodb_table_locks=1（默認(rèn)設(shè)置）時(shí)，InnoDB層才能知道MySQL加的表鎖，MySQL Server也才能感知InnoDB加的行鎖，這種情況下，InnoDB才能自動(dòng)識(shí)別涉及表級(jí)鎖的死鎖；否則，InnoDB 將無(wú)法自動(dòng)檢測(cè)并處理這種死鎖。

（2）在用LOCK TABLES 對(duì)InnoDB 表加鎖時(shí)要注意，要將AUTOCOMMIT 設(shè)為0，否則MySQL不會(huì)給表加鎖；事務(wù)結(jié)束前，不要用UNLOCK TABLES釋放表鎖，因?yàn)?span style="color: #0000ff">UNLOCK TABLES會(huì)隱含地提交事務(wù)；COMMIT 或ROLLBACK 并不能釋放用LOCK TABLES加的表級(jí)鎖，必須用UNLOCK TABLES 釋放表鎖。

7. 關(guān)于死鎖

MyISAM表鎖是deadlock free的，這是因?yàn)镸yISAM總是一次獲得所需的全部鎖，要么全部滿足，要么等待，因此不會(huì)出現(xiàn)死鎖。但在InnoDB 中，除單個(gè)SQL 組成的事務(wù)外，鎖是逐步獲得的，這就決定了在InnoDB 中發(fā)生死鎖是可能的。

發(fā)生死鎖后，InnoDB一般都能自動(dòng)檢測(cè)到，并使一個(gè)事務(wù)釋放鎖并回退，另一個(gè)事務(wù)獲得鎖，繼續(xù)完成事務(wù)。但在涉及外部鎖，或涉及表鎖的情況下，InnoDB并不能完全自動(dòng)檢測(cè)到死鎖，這需要通過(guò)設(shè)置鎖等待超時(shí)參數(shù)innodb_lock_wait_timeout來(lái)解決。

通常來(lái)說(shuō)，死鎖都是應(yīng)用設(shè)計(jì)的問(wèn)題，通過(guò)調(diào)整業(yè)務(wù)流程、數(shù)據(jù)庫(kù)對(duì)象設(shè)計(jì)、事務(wù)大小，以及訪問(wèn)數(shù)據(jù)庫(kù)的SQL語(yǔ)句，絕大部分死鎖都可以避免。下面就通過(guò)實(shí)例來(lái)介紹幾種避免死鎖的常用方法。

（1）在應(yīng)用中，如果不同的程序會(huì)并發(fā)存取多個(gè)表，應(yīng)盡量約定以相同的順序來(lái)訪問(wèn)表，這樣可以大大降低產(chǎn)生死鎖的機(jī)會(huì)。

（2）在程序以批量方式處理數(shù)據(jù)的時(shí)候，如果事先對(duì)數(shù)據(jù)排序，保證每個(gè)線程按固定的順序來(lái)處理記錄，也可以大大降低出現(xiàn)死鎖的可能。

（3）在事務(wù)中，如果要更新記錄，應(yīng)該直接申請(qǐng)足夠級(jí)別的鎖，即排他鎖，而不應(yīng)先申請(qǐng)共享鎖，更新時(shí)再申請(qǐng)排他鎖，因?yàn)楫?dāng)用戶申請(qǐng)排他鎖時(shí)，其他事務(wù)可能又已經(jīng)獲得了相同記錄的共享鎖，從而造成鎖沖突，甚至死鎖。

（4）在REPEATABLE-READ隔離級(jí)別下，如果兩個(gè)線程同時(shí)對(duì)相同條件記錄用SELECT...FOR UPDATE加排他鎖，在沒(méi)有符合該條件記錄情況下，兩個(gè)線程都會(huì)加鎖成功。程序發(fā)現(xiàn)記錄尚不存在，就試圖插入一條新記錄，如果兩個(gè)線程都這么做，就會(huì)出現(xiàn)死鎖。這種情況下，將隔離級(jí)別改成READ COMMITTED，就可避免問(wèn)題。

（5）當(dāng)隔離級(jí)別為READ COMMITTED時(shí)，如果兩個(gè)線程都先執(zhí)行SELECT...FOR UPDATE，判斷是否存在符合條件的記錄，如果沒(méi)有，就插入記錄。此時(shí)，只有一個(gè)線程能插入成功，另一個(gè)線程會(huì)出現(xiàn)鎖等待，當(dāng)?shù)?個(gè)線程提交后，第2個(gè)線程會(huì)因主鍵重出錯(cuò)，但雖然這個(gè)線程出錯(cuò)了，卻會(huì)獲得一個(gè)排他鎖！這時(shí)如果有第3個(gè)線程又來(lái)申請(qǐng)排他鎖，也會(huì)出現(xiàn)死鎖。對(duì)于這種情況，可以直接做插入操作，然后再捕獲主鍵重異常，或者在遇到主鍵重錯(cuò)誤時(shí)，總是執(zhí)行ROLLBACK釋放獲得的排他鎖。

更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《MySQL數(shù)據(jù)庫(kù)鎖相關(guān)技巧匯總》、《MySQL存儲(chǔ)過(guò)程技巧大全》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》及《MySQL事務(wù)操作技巧匯總》

希望本文所述對(duì)大家MySQL數(shù)據(jù)庫(kù)計(jì)有所幫助。