概述MySQL那幾把鎖

05-22

概述MySQL那幾把鎖

來自專欄功夫運維

資料庫鎖定機制簡單來說，就是資料庫為了保證數據的一致性，而使各種共享資源在被並發訪問變得有序所設計的一種規則。MySQL資料庫由於其自身架構的特點，存在多種數據存儲引擎，每種存儲引擎所針對的應用場景特點都不太一樣，為了滿足各自特定應用場景的需求，每種存儲引擎的鎖定機制都是為各自所面對的特定場景而優化設計，所以各存儲引擎的鎖定機制也有較大區別。MySQL各存儲引擎使用了三種類型（級別）的鎖定機制：表級鎖定，行級鎖定和頁級鎖定。

1.表級鎖定（table-level）

表級別的鎖定是MySQL各存儲引擎中最大顆粒度的鎖定機制。該鎖定機制最大的特點是實現邏輯非常簡單，帶來的系統負面影響最小。所以獲取鎖和釋放鎖的速度很快。由於表級鎖一次會將整個表鎖定，所以可以很好的避免困擾我們的死鎖問題。鎖定顆粒度大所帶來最大的負面影響就是出現鎖定資源爭用的概率也會最高，致使並發大打折扣。使用表級鎖定的主要是MyISAM，MEMORY，CSV等一些非事務性存儲引擎。

表級鎖定分為兩類，讀鎖與寫鎖。讀鎖是預期將對數據表進行讀取操作，鎖定期間保證表不能被修改。寫鎖是預期會對數據表更新操作，鎖定期間保證表不能被其他線程更新或讀取。

讀鎖：

用法： LOCK TABLE table_name [ AS alias_name ] READ

指定數據表，LOCK類型為READ即可，AS別名是可選參數，如果指定別名，使用時也要指定別名才可

申請讀鎖前提：當前沒有線程對該數據表使用寫鎖，否則申請會阻塞。

操作限制：其他線程可以對鎖定表使用讀鎖；其他線程不可以對鎖定表使用寫鎖

寫操作

讀操作

使用讀鎖線程

否（報錯）

能

不使用讀鎖線程

否（阻塞）

能

對於使用讀鎖的MySQL線程，由於讀鎖不允許任何線程對鎖定表進行修改，在釋放鎖資源前，該線程對錶操作只能進行讀操作，寫操作時會提示非法操作。而對於其他沒使用鎖的MySQL線程，對鎖定表進行讀操作可以正常進行，但是進行寫操作時，線程會等待讀鎖的釋放，當鎖定表的所有讀鎖都釋放時，線程才會響應寫操作。

寫鎖：

用法： LOCK TABLE table_name [AS alias_name] [ LOW_PRIORITY ] WRITE

別名用法與讀鎖一樣，寫鎖增加了指定優先順序的功能，加入LOW_PRIORITY可以指定寫鎖為低優先順序。

申請寫鎖前提： 當沒有線程對該數據表使用寫鎖與讀鎖，否則申請回阻塞。

操作限制：其他MySQL線程不可以對鎖表使用寫鎖、讀鎖

寫操作

讀操作

使用寫鎖線程

能

不使用寫鎖線程

否（阻塞）

能（阻塞）

對於使用寫鎖的MySQL線程，其可以對鎖定表進行讀寫操作。但是對於其他線程，對指定表讀寫操作都是非法的，需要等待直到寫鎖釋放。

鎖分配優先順序：

對於鎖分配的優先順序，是： LOW_PRIORITY WRITE < READ < WRITE

當多個線程申請鎖，會優先分配給WRITE鎖，不存在WRITE鎖時，才分配READ鎖，LOW_PRIORITY WRITE需要等到WRITE鎖與READ都釋放後，才有機會分配到資源。

對於相同優先順序的鎖申請，分配原則為誰先申請，誰先分配。

注意事項：

不能操作（查詢或更新）沒有被鎖定的表。

例如當只申請table1的讀鎖，SQL語句中包含對table2的操作是非法的。例如：

mysql>LOCK TABLE test_table READ;
Query OK, 0 rows affected (0.00 sec)
mysql>SELECT * FROM test_table;
ERROR 1100 (HY000): Table test_table was not locked with LOCK TABLES

查詢表級鎖爭用情況

MySQL內部有兩組專門的狀態變數記錄系統內部鎖資源爭用情況：

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

2.行級鎖定（row-level）

行級鎖定最大的特點就是鎖定對象的顆粒度很小，也是目前各大資料庫管理軟體所實現的鎖定顆粒度最小的。由於鎖定顆粒度很小，所以發生鎖定資源爭用的概率也最小，能夠給予應用程序儘可能大的並發處理能力而提高一些需要高並發應用系統的整體性能。

雖然能夠在並發處理能力上面有較大的優勢，但是行級鎖定也因此帶來了不少弊端。由於鎖定資源的顆粒度很小，所以每次獲取鎖和釋放鎖需要做的事情也更多，帶來的消耗自然也就更大了。此外，行級鎖定也最容易發生死鎖。使用行級鎖定的主要是InnoDB存儲引擎。

INNODB的行級鎖有共享鎖（S LOCK）和排他鎖（X LOCK）兩種。共享鎖允許事物讀一行記錄，不允許任何線程對該行記錄進行修改。排他鎖允許當前事物刪除或更新一行記錄，其他線程不能操作該記錄。

共享鎖：

用法：SELECT ... LOCK IN SHARE MODE;

MySQL會對查詢結果集中每行都添加共享鎖。

鎖申請前提：當前沒有線程對該結果集中的任何行使用排他鎖，否則申請會阻塞。

操作限制：使用共享鎖線程與不使用共享鎖線程對鎖定記錄操作限制表

使用共享鎖線程可對其鎖定記錄進行讀取，其他線程同樣也可對鎖定記錄進行讀取操作，並且這兩個線程讀取的數據都屬於同一個版本。

對於寫入操作，使用共享鎖的線程需要分情況討論，當只有當前線程對指定記錄使用共享鎖時，線程是可對該記錄進行寫入操作（包括更新與刪除），這是由於在寫入操作前，線程向該記錄申請了排他鎖，然後才進行寫入操作；當其他線程也對該記錄使用共享鎖時，則不可進行寫入操作，系統會有報錯提示。不對鎖定記錄使用共享鎖的線程，當然是不可進行寫入操作了，寫入操作會阻塞。

使用共享鎖進程可再次對鎖定記錄申請共享鎖，系統並不報錯，但是操作本身並沒有太大意義。其他線程同樣也可以對鎖定記錄申請共享鎖。

使用共享鎖進程可對其鎖定記錄申請排他鎖；而其他進程是不可以對鎖定記錄申請排他鎖，申請會阻塞。

排他鎖：

用法： SELECT ... FOR UPDATE;

MySQL會對查詢結果集中每行都添加排他鎖，在事物操作中，任何對記錄的更新與刪除操作會自動加上排他鎖

鎖申請前提：當前沒有線程對該結果集中的任何行使用排他鎖或共享鎖，否則申請會阻塞。

操作限制：

使用排他鎖線程與不使用排他鎖線程對鎖定記錄操作限制表

線程

讀取操作

寫入操作

共享鎖申請

排他鎖申請

使用排他鎖

可讀（新版本）

可寫

可申請

不使用排他鎖

可讀（舊版本）

不可寫(阻塞)

不可申請(阻塞)

使用排他鎖線程可以對其鎖定記錄進行讀取，讀取的內容為當前事物的最新版本；而對於不使用排他鎖的線程，同樣是可以進行讀取操作，這種特性是一致性非鎖定讀。即對於同一條記錄，資料庫記錄多個版本，在事物內的更新操作會反映到新版本中，而舊版本會提供給其他線程進行讀取操作。

使用排他鎖線程可對其鎖定記錄進行寫入操作；對於不使用排他鎖的線程，對鎖定記錄的寫操作是不允許的，請求會阻塞。

使用排他鎖進程可對其鎖定記錄申請共享鎖，但是申請共享鎖之後，線程並不會釋放原先的排他鎖，因此該記錄對外表現出排他鎖的性質；其他線程是不可對已鎖定記錄申請共享鎖，請求會阻塞。

使用排他鎖進程可對其鎖定記錄申請排他鎖（實際上並沒有任何意義）；而其他進程是不可對鎖定記錄申請排他鎖，申請會阻塞。

3.頁級鎖定（page-level）

頁級鎖定是MySQL中比較獨特的一種鎖定級別，在其他資料庫管理軟體中也並不是太常見。頁級鎖定的特點是鎖定顆粒度介於行級鎖定與表級鎖之間，所以獲取鎖定所需要的資源開銷，以及所能提供的並發處理能力也同樣是介於上面二者之間。另外，頁級鎖定和行級鎖定一樣，會發生死鎖。

在資料庫實現資源鎖定的過程中，隨著鎖定資源顆粒度的減小，鎖定相同數據量的數據所需要消耗的內存數量是越來越多的，實現演算法也會越來越複雜。不過，隨著鎖定資源顆粒度的減小，應用程序的訪問請求遇到鎖等待的可能性也會隨之降低，系統整體並發度也隨之提升。