歡迎光臨
每天分享高質量文章

史上最全MySQL鎖機制

本文主要記錄學習MyISAM 和 InnoDB 這兩個儲存引擎。

 

為什麼要學習鎖機制


 

鎖是計算機協調多個行程或執行緒併發訪問某一資源的機制。

 

因為資料也是一種供許多用戶共享的資源,如何保證資料併發訪問的一致性、有效性是所有資料庫必須解決的一個問題,鎖衝突也是影響資料庫併發訪問性能的一個重要因素,所以進一步學習MySQL,就需要去瞭解它的鎖機制。

 


MySQL鎖概述:

 

相對其他資料庫而言,MySQL
的鎖機制比較簡單,其最顯著的特點是不同的儲存引擎支持不同的鎖機制。比如,MyISAM和MEMORY儲存引擎採用的是表級鎖(table-level
locking);BDB儲存引擎採用的是頁面鎖(page-level
locking),但也支持表級鎖;InnoDB儲存引擎既支持行級鎖(row-level
locking),也支持表級鎖,但預設情況下是採用行級鎖。
MySQL這3種鎖的特性可大致歸納如下。

 

開銷、加鎖速度、死鎖、粒度、併發性能 

①:表級鎖:開銷小,加鎖快;不會出現死鎖;鎖定粒度大,發生鎖衝突的概率最高,併發度最低。

②:行級鎖:開銷大,加鎖慢;會出現死鎖;鎖定粒度最小,發生鎖衝突的概率最低,併發度也最高。

③:頁面鎖:開銷和加鎖時間界於表鎖和行鎖之間;會出現死鎖;鎖定粒度界於表鎖和行鎖之間,併發度一般。

從上述特點可見,很難籠統地說哪種鎖更好,只能就具體應用的特點來說哪種鎖更合適!僅從鎖的角度來說:表級鎖更適合於以查詢為主,只有少量按索引條件更新資料的應用,如Web應用;而行級鎖則更適合於有大量按索引條件併發更新少量不同資料,同時又有併發查詢的應用,如一些在線事務處理(OLTP)系統。由於BDB已經被InnoDB取代,即將成為歷史(所以現在基本都在使用InnoDB儲存引擎)。

 


MyISAN儲存引擎


 

MyISAM 儲存引擎只支持表鎖,這也是 MySQL 開始幾個版本中唯一支持的鎖型別。

MySQL表級鎖

 

查詢表鎖爭用情況

mysql> show status like 'table%';+----------------------------+-------+| Variable_name              | Value |+----------------------------+-------+| Table_locks_immediate      | 4     || Table_locks_waited         | 0     || Table_open_cache_hits      | 4     || Table_open_cache_misses    | 8     || Table_open_cache_overflows | 0     |+----------------------------+-------+5 rows in set (0.00 sec)

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


 

MySQL的表級鎖的兩種樣式

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

 

MySQL中的表鎖兼容性:

請求鎖樣式
矩陣結果表示是否兼容
當前鎖樣式
None 讀鎖 寫鎖
讀鎖
寫鎖

 

也就是說,在MyISAM讀樣式下,不會阻塞其它用戶的同一表讀操作,但是會阻塞寫操作;而在寫樣式下,會同時阻塞其它用戶同一表的讀寫操作。

 

測試MyISAM的寫鎖樣式


 

新建一個user表,引擎是MyISAM:

mysql> desc user;+---------+-------------+------+-----+---------+----------------+| Field   | Type        | Null | Key | Default | Extra          |+---------+-------------+------+-----+---------+----------------+| id      | int(11)     | NO   | PRI | NULL    | auto_increment || name    | varchar(20) | YES  |     | NULL    |                || age     | int(3)      | YES  |     | NULL    |                || address | varchar(60) | YES  |     | NULL    |                |+---------+-------------+------+-----+---------+----------------+4 rows in set (0.01 sec)

session A session B
獲得user表的鎖鎖定
mysql> lock table user write;
Query OK, 0 rows affected (0.00 sec)
mysql>select * from user;
Empty set (0.00 sec)
mysql> insert into user(id, name, age, address) values(1, ‘test’, 18, ‘test address’);
Query OK,1 row affected (0.02 sec)
mysql> select * from user\G
被阻塞了,一直卡住在這,沒有傳回結果
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
等待
mysql> select * from user\G
**********
name: test
age: 18
address: test address
1 row in set (5 min 29.61 sec)

 

可以看出,通過lock table user write將user表鎖住後,其它用戶進行對該表操作時,都會被阻塞。


測試MyISAM讀鎖

 

在用LOCK TABLES給表顯式加表鎖時,必須同時取得所有涉及到表的鎖,並且MySQL不支持鎖升級。也就是說,在執行LOCK
TABLES後,只能訪問顯式加鎖的這些表,不能訪問未加鎖的表;同時,如果加的是讀鎖,那麼只能執行查詢操作,而不能執行更新操作。其實,在自動加鎖的情況下也基本如此,MyISAM總是一次獲得SQL陳述句所需要的全部鎖。這也正是MyISAM表不會出現死鎖(Deadlock
Free)的原因。

 

session A session B
獲得user表的讀鎖定
mysql> lock table user read;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id = 1 \G
中從查詢速度中可以看出,sessionB並沒有被阻塞
1 row in set (0.00 sec)
由於沒有獲取order表的讀鎖定,所以不能查詢order表
mysql> select * from order;
ERROR 1100 (HY000): Table ‘order’ was not locked with LOCK TABLES
但是session B可以訪問oder表,不阻塞
mysql> select * from order;
Empty set (0.00 sec)
獲得讀鎖定時,不能進行寫操作
mysql> update user set name = ‘wahaha’ where id = 1;
ERROR 1099 (HY000): Table ‘user’ was locked with a READ lock and can’t be updated
其它session進行更新操作時,會被阻塞
mysql> update user set name = ‘wahaha’ where id = 1;
等待ing
釋放鎖
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
等待
mysql> update user set name = ‘wahaha’ where id = 1;
Query OK, 1 row affected (1 min 6.43 sec)

 

 

MyISAM支持併發插入


 

MyISAM表的讀和寫是串行的,但這是就總體而言的。在一定條件下,MyISAM表也支持查詢和插入操作的併發進行。MyISAM儲存引擎有一個系統變數concurrent_insert,專門用以控制其併發插入的行為,其值分別可以為0、1或2。

  • 當concurrent_insert設置為0時,不允許併發插入。
  • 當concurrent_insert設置為1時,如果MyISAM表中沒有空洞(即表的中間沒有被刪除的行),MyISAM允許在一個行程讀表的同時,另一個行程從表尾插入記錄。這也是MySQL的預設設置。
  • 當concurrent_insert設置為2時,無論MyISAM表中有沒有空洞,都允許在表尾併發插入記錄。

 

MyISAM的鎖調度


 

MyISAM儲存引擎的讀鎖和寫鎖是互斥的,讀寫操作是串行的。
但它認為寫鎖的優先級比讀鎖高,所以即使讀請求先到鎖等待佇列,寫請求後到,寫鎖也會插到讀鎖請求之前!
這也正是MyISAM表不太適合於有大量更新操作和查詢操作應用的原因,因為,大量的更新操作會造成查詢操作很難獲得讀鎖,從而可能永遠阻塞。
可以通過一些設置來調節MyISAM的調度行為。

 

  • 通過指定啟動引數low-priority-updates,使MyISAM引擎預設給予讀請求以優先的權利。
  • 通過執行命令SET LOW_PRIORITY_UPDATES=1,使該連接發出的更新請求優先級降低。
  • 通過指定INSERT、UPDATE、DELETE陳述句的LOW_PRIORITY屬性,降低該陳述句的優先級。

 

雖然上面3種方法都是要麼更新優先,要麼查詢優先的方法,但還是可以用其來解決查詢相對重要的應用(如用戶登錄系統)中,讀鎖等待嚴重的問題。
另外,MySQL也提供了一種折中的辦法來調節讀寫衝突,即給系統引數max_write_lock_count設置一個合適的值,當一個表的讀鎖達到這個值後,MySQL就暫時將寫請求的優先級降低,給讀行程一定獲得鎖的機會。
上面已經討論了寫優先調度機制帶來的問題和解決辦法。

 

這裡還要強調一點:一些需要長時間運行的查詢操作,也會使寫行程“餓死”!因此,應用中應儘量避免出現長時間運行的查詢操作,不要總想用一條SELECT陳述句來解決問題,因為這種看似巧妙的SQL陳述句,往往比較複雜,執行時間較長,在可能的情況下可以通過使用中間表等措施對SQL陳述句做一定的“分解”,使每一步查詢都能在較短時間完成,從而減少鎖衝突。如果複雜查詢不可避免,應儘量安排在資料庫空閑時段執行,比如一些定期統計可以安排在夜間執行。

 

InnoDB


 

InnoDB與MyISAM的最大不同有兩點:一是支持事務(TRANSACTION);二是採用了行級鎖。行級鎖與表級鎖本來就有許多不同之處,另外,事務的引入也帶來了一些新問題。

 

事務概念

學習Spring的時候,一般通過註解@Transitional就能啟動spring的事務管理,在MySQL中也同樣支持事務的四個原則ACID:

 

  • **A(Atomicity)原子性:**事務是一個原子操作單元,其對資料的修改,要麼全都執行,要麼全都不執行。
  • **C(Consistent)一致性:**在事務開始和完成時,資料都必須保持一致狀態。這意味著所有相關的資料規則都必須應用於事務的修改,以保持資料的完整性;事務結束時,所有的內部資料結構(如B樹索引或雙向鏈表)也都必須是正確的。
  • **I(Isolation)隔離性:**資料庫系統提供一定的隔離機制,保證事務在不受外部併發操作影響的“獨立”環境執行。這意味著事務處理過程中的中間狀態對外部是不可見的,反之亦然。
  • **D(Durable)持久性:**事務完成之後,它對於資料的修改是永久性的,即使出現系統故障也能夠保持。

併發事務處理帶來的問題

 

相對於串行處理來說,併發事務處理能大大增加資料庫資源的利用率,提高資料庫系統的事務吞吐量,從而可以支持更多的用戶。但併發事務處理也會帶來一些問題,主要包括以下幾種情況。

 

  • 更新丟失(Last update):A和B同時對一行資料進行處理,A修改後進行儲存,然後B修改後進行儲存,這樣A的更新被改寫了,相當於發生丟失更新的問題。所以可以在A事務未結束前,B不能訪問該記錄,這樣就能避免更新丟失的問題。
  • 臟讀(Dirty Reads):A事務在對一條記錄做修改,但還未提交,這條記錄處於不一致的狀態;這時,B事務也來讀同一條記錄,這時如果沒有加控制,B讀了未修改前的資料,並根據該資料進行進一步處理,就會產生未提交的資料依賴關係。這種現象叫做“臟讀”
  • 不可重覆讀(Non-Repeatable Reads):B事務在讀取某些資料後的某個時間,再次讀取以前讀過的資料,卻發現其讀出的資料已經發生了改變(被更新或者刪除了,例如A事務修改了)。這種現象叫做“不可重覆讀”。
  • 幻讀(Phantom Reads):A事務按照相同查詢條件,重新讀取之前檢索過得內容,卻發現其它事務插入或修改其查詢條件的新資料,這種現象就叫”幻讀“。

 

事務的隔離級別

資料庫的事務隔離越嚴格,併發副作用越小,但付出的代價也就越大,因為事務隔離實質上就是使事務在一定程度上
“串行化”進行,這顯然與“併發”是矛盾的。同時,不同的應用對讀一致性和事務隔離程度的要求也是不同的,比如許多應用對“不可重覆讀”和“幻讀”並不敏感,可能更關心資料併發訪問的能力。

4種隔離級別比較

讀資料一致性及允許的併發副作用
隔離級別
讀資料一致性 臟讀 不可重覆讀 幻讀
未提交讀(Read uncommitted) 最低級別,只能保證不讀取
物理上損害的資料
已提交讀(Read committed) 陳述句級
可重覆讀(Repeatable read) 事務級
可序列化(Serializable) 最高級別,事務級

獲取InnoDB行鎖爭用情況

檢查InnoDB_row_lock狀態變數來分析:

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     |
+-------------------------------+-------+
5 rows in set (0.00 sec)
複製代碼

如果InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比較高,表示鎖爭用情況比較嚴重。


InnoDB的行鎖樣式以及加鎖方法

InnoDB實現了一下兩種型別的行鎖:

  • 共享鎖(S):允許一個事務去多一行,阻止其它事務獲得相同資料集的排他鎖。
  • 排他鎖(X): 允許獲得排他鎖的事務更新資料,阻止其它事務獲得相同資料集的共享鎖和排他寫鎖。

另外,為了允許行鎖和表鎖共存,實現多粒度鎖機制,InnoDB還有兩種內部使用的意向鎖(Intention Locks),這兩種意向鎖都是表鎖。(感覺與MyISAM的表鎖機制類似)

  • 意向共享鎖(IS):事務打算給資料行加行共享鎖,事務在給一個資料行加共享鎖前必須先取得該表的IS鎖。
  • 意向排他鎖(IX):事務打算給資料行加行排他鎖,事務在給一個資料行加排他鎖前必須先取得該表的IX鎖。

InnoDB行鎖樣式兼容性串列:

請求鎖樣式
矩陣結果表示是否兼容
當前鎖樣式
X IX S IS
X 衝突 衝突 衝突 衝突
IX 衝突 兼容 衝突 兼容
S 衝突 衝突 兼容 兼容
IS 衝突 兼容 兼容 兼容

 

如果一個事務請求的鎖樣式與當前的鎖兼容,InnoDB就將請求的鎖授予該事務;反之,如果兩者不兼容,該事務就要等待鎖釋放。
意向鎖是InnoDB自動加的;對於UPDATE、DELETE和INSERT陳述句,InnoDB會自動給設計資料集加排他鎖(X);對於普通的SELECT陳述句,InnoDB不會加鎖。
可以通過以下陳述句顯示給記錄集加共享鎖或排他鎖:

 

  • 共享鎖(S):SELECT * FROM TABLE_NAME WHERE … LOCK IN SHARE MODE.
  • 排他鎖(X):SELECT * FROM TABLE_NAME WHERE … FOR UPDATE.

 

用SELECT … IN SHARE
MODE獲得共享鎖,主要用在需要資料依存關係時來確認某行記錄是否存在,並確保沒有人對這個記錄進行UPDATE或者DELETE操作。但是如果當前事務也需要對該記錄進行更新操作,則很有可能造成死鎖,對於鎖定行記錄後需要進行更新操作的應用,應該使用SELECT…
FOR UPDATE方式獲得排他鎖。

 

所以在使用共享鎖樣式下,查詢完資料後不要進行更新操作,不然又可能會造成死鎖;要更新資料,應該使用排他鎖樣式。


 

InnoDB行鎖實現方式


 

InnoDB行鎖是通過給索引上的索引項加鎖來實現的,這一點MySQL與Oracle不同,後者是通過在資料塊中對相應資料行加鎖來實現的。InnoDB這種行鎖實現特點意味著:只有通過索引條件檢索資料,InnoDB才使用行級鎖,否則,InnoDB將使用表鎖!(這個問題遇到過,由於沒加索引,行鎖變表鎖

 

  • 在不通過索引條件查詢的時候,InnoDB確實使用的是表鎖,而不是行鎖。
  • 由於MySQL的行鎖是針對索引加的鎖,不是針對記錄加的鎖,所以雖然是訪問不同行的記錄,但是如果是使用相同的索引鍵,是會出現鎖衝突的。
  • 當表有多個索引的時候,不同的事務可以使用不同的索引鎖定不同的行,另外,不論是使用主鍵索引、唯一索引或普通索引,InnoDB都會使用行鎖來對資料加鎖。
  • 即便在條件中使用了索引欄位,但是否使用索引來檢索資料是由MySQL通過判斷不同執行計劃的代價來決定的,如果MySQL認為全表掃描效率更高,比如對一些很小的表,它就不會使用索引,這種情況下InnoDB將使用表鎖,而不是行鎖。

 

可以通過explain執行計劃查看是否真正使用了索引。


 

間隙鎖(Next-key鎖)


 

當我們用範圍條件而不是相等條件檢索資料,並請求共享或排他鎖時,InnoDB會給符合條件的已有資料記錄的索引項加鎖;對於鍵值在條件範圍內但並不存在的記錄,叫做“間隙(GAP)”,InnoDB也會對這個“間隙”加鎖,這種鎖機制就是所謂的間隙鎖(Next-Key鎖)。

 

舉個例子:假如emp表中只有101條記錄,其id的值從1~101,下麵的sql:
select * from emp where id > 100 for update;
是範圍條件查詢,InnoDB不僅會對符合條件的id值為101的記錄加鎖,也會對id大於101(並不存在的值)的“間隙”加鎖。

 

結論:

 

很顯然,在使用範圍條件檢索並鎖定記錄時,InnoDB這種加鎖機制會阻塞符合條件範圍內鍵值的併發插入,這往往會造成嚴重的鎖等待。因此,在實際應用開發中,尤其是併發插入比較多的應用,我們要儘量優化業務邏輯,儘量使用相等條件來訪問更新資料,避免使用範圍條件。

 


關於死鎖(DeadLock)


 

上面知識點說過,MyISAM表鎖是deadlock free的,這是因為MyISAM總是一次獲得所需的全部鎖,要麼全部滿足,要麼等待,因此不會出現死鎖。但在InnoDB中,除單個SQL組成的事務外,鎖是逐步或得的,所以InnoDB發生死鎖是可能的。

 

舉個例子:

session A session B
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from table_1 where where id=1 for update;

做一些其他處理…
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from table_2 where id=1 for update;
select * from table_2 where id =1 for update;
因session_2已取得排他鎖,等待
做一些其他處理…
mysql> select * from table_1 where where id=1 for update;
死鎖

 

也就是我們死鎖產生的條件,互相持有資源不釋放,還有環形等待。

 

發生死鎖後,InnoDB一般都能自動檢測到,並使一個事務釋放鎖並回退,另一個事務獲得鎖,繼續完成事務。但在涉及外部鎖,或涉及表鎖的情況下,InnoDB並不能完全自動檢測到死鎖,這需要通過設置鎖等待超時引數 innodb_lock_wait_timeout來解決。需要說明的是,這個引數並不是只用來解決死鎖問題,在併發訪問比較高的情況下,如果大量事務因無法立即獲得所需的鎖而掛起,會占用大量計算機資源,造成嚴重性能問題,甚至拖跨資料庫。我們通過設置合適的鎖等待超時閾值,可以避免這種情況發生。

 

免死鎖的方法

  1. 在應用中,如果不同的程式會併發存取多個表,應儘量約定以相同的順序來訪問表,這樣可以大大降低產生死鎖的機會。在下麵的例子中,由於兩個session訪問兩個表的順序不同,發生死鎖的機會就非常高!但如果以相同的順序來訪問,死鎖就可以避免。
  2. 在程式以批量方式處理資料的時候,如果事先對資料排序,保證每個執行緒按固定的順序來處理記錄,也可以大大降低出現死鎖的可能。
  3. 在事務中,如果要更新記錄,應該直接申請足夠級別的鎖,即排他鎖,而不應先申請共享鎖,更新時再申請排他鎖,因為當用戶申請排他鎖時,其他事務可能又已經獲得了相同記錄的共享鎖,從而造成鎖衝突,甚至死鎖。
  4. 在REPEATABLE-READ隔離級別下,如果兩個執行緒同時對相同條件記錄用SELECT…FOR
    UPDATE加排他鎖,在沒有符合該條件記錄情況下,兩個執行緒都會加鎖成功。程式發現記錄尚不存在,就試圖插入一條新記錄,如果兩個執行緒都這麼做,就會出現死鎖。這種情況下,將隔離級別改成READ COMMITTED,就可避免問題。
  5. 當隔離級別為READ COMMITTED時,如果兩個執行緒都先執行SELECT…FOR
    UPDATE,判斷是否存在符合條件的記錄,如果沒有,就插入記錄。此時,只有一個執行緒能插入成功,另一個執行緒會出現鎖等待,當第1個執行緒提交後,第2個執行緒會因主鍵重出錯,但雖然這個執行緒出錯了,卻會獲得一個排他鎖!這時如果有第3個執行緒又來申請排他鎖,也會出現死鎖。
  • 對於這種情況,可以直接做插入操作,然後再捕獲主鍵重異常,或者在遇到主鍵重錯誤時,總是執行ROLLBACK釋放獲得的排他鎖

 

小結


 

這是一篇學習文章,關於MySQL的鎖機制又多了幾分瞭解,以後在寫SQL和排查問題時候,儘量避免死鎖和更快定位問題所在。

 

出處:https://juejin.im/post/5ce8eee45188253114078f2a

編輯:尹文敏

赞(0)

分享創造快樂