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

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

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

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

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

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

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

一、MyISAM表鎖

1. 查詢表級鎖爭用情況

show status like 'table%';

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

2. MySQL表級鎖的鎖模式

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

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

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

3. 如何加表鎖

加讀鎖：

lock table tbl_name read;

加寫鎖：

lock table tbl_name write;

釋放鎖：

unlock tables;

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

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

4. 并發(fā)插入

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

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

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

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

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

5. MyISAM鎖調(diào)度

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

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

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

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

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

（4）給系統(tǒng)參數(shù)max_write_lock_count設(shè)置一個合適的值，當(dāng)一個表的讀鎖達(dá)到這個值后，MySQL就暫時將寫請求的優(yōu)先級降低，給讀進(jìn)程一定獲得鎖的機(jī)會。

二、InnoDB鎖問題

1. 查詢InnoDB行鎖爭用情況

show status like 'innodb_row_lock%';

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

打開監(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 秒會向日志中記錄監(jiān)控的內(nèi)容，如果長時間打開會導(dǎo)致.err 文件變得非常的巨大，所以用戶在確認(rèn)問題原因之后，要記得刪除監(jiān)控表以關(guān)閉監(jiān)視器，或者通過使用“--console”選項來啟動服務(wù)器以關(guān)閉寫日志文件。

2. InnoDB的行鎖及加鎖方法

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

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

Set autocommit=0;

共享鎖（S）：

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

排他鎖（X）：

SELECT * FROM table_name WHERE ... FOR UPDATE

釋放鎖：

unlock tables;

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

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

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

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

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

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

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

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

4. 間隙鎖

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

除了通過范圍條件加鎖時使用間隙鎖外，如果使用相等條件請求給一個不存在的記錄加鎖，InnoDB也會使用間隙鎖！

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

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

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

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

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

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

6. InnoDB使用表鎖的情況及注意事項

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

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

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

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

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

（2）在用LOCK TABLES 對InnoDB 表加鎖時要注意，要將AUTOCOMMIT 設(shè)為0，否則MySQL不會給表加鎖；事務(wù)結(jié)束前，不要用UNLOCK TABLES釋放表鎖，因為UNLOCK TABLES會隱含地提交事務(wù)；COMMIT 或ROLLBACK 并不能釋放用LOCK TABLES加的表級鎖，必須用UNLOCK TABLES 釋放表鎖。

7. 關(guān)于死鎖

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

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

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

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

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

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

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

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

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