轉載自?MySQL中的讀鎖和寫鎖

?

在數據庫的鎖機制中介紹過，數據的鎖主要用來保證數據的一致性的，數據庫的鎖從鎖定的粒度上可以分為表級鎖、行級鎖和頁級鎖。在我的博客中重點介紹過MySQL數據庫的行級鎖。這篇文章主要來介紹一下MySQL數據庫中的表級鎖。

本文提到的讀鎖和寫鎖都是MySQL數據庫的MyISAM引擎支持的表鎖的。而對于行級鎖的共享讀鎖和互斥寫鎖請閱讀MySQL中的共享鎖與排他鎖。我習慣在描述表鎖的時候按照讀寫來區分，在表述行鎖的時候按照共享和互斥來區分。其實無論是表鎖還是行鎖。共享鎖指的就是讀鎖！互斥鎖、排他鎖、獨占鎖值得都是寫鎖。

重點知識回顧

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

• 表級鎖:開銷小，加鎖快;不會出現死鎖;鎖定粒度大，發生鎖沖突的概率最高，并發度最低。

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

• 頁面鎖:開銷和加鎖時間界于表鎖和行鎖之間;會出現死鎖;鎖定粒度界于表鎖和行鎖之間，并發度一般。

MyISAM表鎖

MyISAM 存儲引擎只支持表鎖，MySQL 的表級鎖有兩種模式:表共享讀鎖(Table Read Lock)和表獨占寫鎖(Table Write Lock)。

對于讀操作，可以增加讀鎖，一旦數據表被加上讀鎖，其他請求可以對該表再次增加讀鎖，但是不能增加寫鎖。（當一個請求在讀數據時，其他請求也可以讀，但是不能寫，因為一旦另外一個線程寫了數據，就會導致當前線程讀取到的數據不是最新的了。這就是不可重復讀現象）

對于寫操作，可以增加寫鎖，一旦數據表被加上寫鎖，其他請求無法在對該表增加讀鎖和寫鎖。（當一個請求在寫數據時，其他請求不能執行任何操作，因為在當前事務提交之前，其他的請求無法看到本次修改的內容。這有可能產生臟讀、不可重復讀和幻讀）

讀鎖和寫鎖都是阻塞鎖。

如果t1對數據表增加了寫鎖，這是t2請求對數據表增加寫鎖，這時候t2并不會直接返回，而是會一直處于阻塞狀態，直到t1釋放了對表的鎖，這時t2便有可能加鎖成功，獲取到結果。

表鎖的加鎖/解鎖方式

MyISAM 在執行查詢語句(SELECT)前,會自動給涉及的所有表加讀鎖,在執行更新操作 (UPDATE、DELETE、INSERT?等)前，會自動給涉及的表加寫鎖，這個過程并不需要用戶干預，因此，用戶一般不需要直接用LOCK TABLE命令給MyISAM表顯式加鎖。

如果用戶想要顯示的加鎖可以使用以下命令：

鎖定表：LOCK TABLES tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},…] 解鎖表：UNLOCK TABLES

在用?LOCK TABLES?給表顯式加表鎖時,必須同時取得所有涉及到表的鎖。

在執行?LOCK TABLES?后，只能訪問顯式加鎖的這些表，不能訪問未加鎖的表;

如果加的是讀鎖，那么只能執行查詢操作，而不能執行更新操作。

在自動加鎖的情況下也基本如此，MyISAM 總是一次獲得 SQL 語句所需要的全部鎖。這也正是 MyISAM 表不會出現死鎖(Deadlock Free)的原因。

對表test_table增加讀鎖：

LOCK TABLES test_table READ UNLOCK test_table

對表test_table增加寫鎖

LOCK TABLES test_table WRITE UNLOCK test_table

• 當使用 LOCK TABLES 時,不僅需要一次鎖定用到的所有表,而且,同一個表在 SQL 語句中出現多少次,就要通過與 SQL 語句中相同的別名鎖定多少次,否則也會出錯!

比如如下SQL語句：

select a.first_name,b.first_name, from actor a,actor b where a.first_name = b.first_name;

該Sql語句中，actor表以別名的方式出現了兩次，分別是a,b，這時如果要在該Sql執行之前加鎖就要使用以下Sql:

lock table actor as a read,actor as b read;

并發插入

上文 到過 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 表的讀鎖,同時另一個進程也請求同一表的寫鎖,MySQL 如何處理呢??

答案是寫進程先獲得鎖。

不僅如此,即使讀請求先到鎖等待隊列,寫請求后到,寫鎖也會插到讀鎖請求之前!這是因為 MySQL 認為寫請求一般比讀請求要重要。這也正是 MyISAM 表不太適合于有大量更新操作和查詢操作應用的原因,因為,大量的更新操作會造成查詢操作很難獲得讀鎖,從而可能永遠阻塞。這種情況有時可能會變得非常糟糕!

幸好我們可以通過 一些設置來調節 MyISAM 的調度行為。

通過指定啟動參數low-priority-updates,使MyISAM引擎默認給予讀請求以優先的權利。?

通過執行命令SET LOWPRIORITYUPDATES=1,使該連接發出的更新請求優先級降低。?

通過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性,降低該語句的優先級。

另外,MySQL也 供了一種折中的辦法來調節讀寫沖突,即給系統參數max_write_lock_count?設置一個合適的值,當一個表的讀鎖達到這個值后,MySQL就暫時將寫請求的優先級降低, 給讀進程一定獲得鎖的機會。

總結

數據庫中的鎖從鎖定的粒度上分可以分為行級鎖、頁級鎖和表級鎖。

MySQL的MyISAM引擎支持表級鎖。

表級鎖分為兩種：共享讀鎖、互斥寫鎖。這兩種鎖都是阻塞鎖。

可以在讀鎖上增加讀鎖，不能在讀鎖上增加寫鎖。在寫鎖上不能增加寫鎖。

默認情況下，MySql在執行查詢語句之前會加讀鎖，在執行更新語句之前會執行寫鎖。

如果想要顯示的加鎖/解鎖的花可以使用LOCK TABLES和UNLOCK來進行。

在使用LOCK TABLES之后，在解鎖之前，不能操作未加鎖的表。

在加鎖時，如果顯示的指明是要增加讀鎖，那么在解鎖之前，只能進行讀操作，不能執行寫操作。

如果一次Sql語句要操作的表以別名的方式多次出現，那么就要在加鎖時都指明要加鎖的表的別名。

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

由于讀鎖和寫鎖互斥，那么在調度過程中，默認情況下，MySql會本著寫鎖優先的原則。可以通過low-priority-updates來設置。

參考資料

《深入淺出MySQL》

?

總結

以上是生活随笔為你收集整理的MySQL中的读锁和写锁的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。