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

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

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

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

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

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

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

一、MyISAM表鎖

1. 查詢表級鎖爭用情況

show status like 'table%';

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

2. MySQL表級鎖的鎖模式

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

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

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

3. 如何加表鎖

加讀鎖：

lock table tbl_name read;

加寫鎖：

lock table tbl_name write;

釋放鎖：

unlock tables;

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

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

4. 并發(fā)插入

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

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

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

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

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

5. MyISAM鎖調(diào)度

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

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

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

（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設置一個合適的值，當一個表的讀鎖達到這個值后，MySQL就暫時將寫請求的優(yōu)先級降低，給讀進程一定獲得鎖的機會。

二、InnoDB鎖問題

1. 查詢InnoDB行鎖爭用情況

show status like 'innodb_row_lock%';

如果InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比較高，說明鎖爭用比較嚴重，這時可以通過設置InnoDB Monitors來進一步觀察發(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)視器以后，默認情況下每15 秒會向日志中記錄監(jiān)控的內(nèi)容，如果長時間打開會導致.err 文件變得非常的巨大，所以用戶在確認問題原因之后，要記得刪除監(jiān)控表以關閉監(jiān)視器，或者通過使用“--console”選項來啟動服務器以關閉寫日志文件。

2. InnoDB的行鎖及加鎖方法

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

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

Set autocommit=0;

共享鎖（S）：

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

排他鎖（X）：

SELECT * FROM table_name WHERE ... FOR UPDATE

釋放鎖：

unlock tables;

（會隱含提交事務）

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

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

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

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

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

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

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

4. 間隙鎖

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

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

5. 恢復和復制的需要對InnoDB鎖機制的影響

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

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

（2）MySQL 的Binlog是按照事務提交的先后順序記錄的，恢復也是按這個順序進行的。

所以MySQL的恢復和復制對鎖機制的要求是：在一個事務未提交前，其他并發(fā)事務不能插入滿足其鎖定條件的任何記錄，也就是不允許出現(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í)行期間，有另一個事務對source_tab做了更新并且先進行了提交，那么在BINLOG中，更新操作的位置會在該SQL語句之前，使用這個BINLOG進行數(shù)據(jù)庫恢復的話，恢復的結果就會與實際的應用邏輯不符，進行復制則會導致主從數(shù)據(jù)庫不一致。因為實際上應用插入target_tab或new_tab中的數(shù)據(jù)是另一個事務對source_tab更新前的數(shù)據(jù)，而BINLOG記錄的卻是先進行更新再執(zhí)行select...insert...語句。如果上述語句的SELECT是范圍條件，InnoDB還會給源表加間隙鎖。所以這種SQL語句會阻塞對原表的并發(fā)更新，應盡量避免使用。

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

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

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

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

另外，在InnoDB中使用表鎖需要注意以下兩點：

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

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

7. 關于死鎖

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

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

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

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

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

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

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

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

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

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