詳解Mysql的鎖機制

一、Mysql為什麼要加鎖

鎖機制用於管理對共享資源的併發訪問，是對數據庫的一種保護機制，也是數據庫在事務操作中保證事務數據一致性和完整性的一種機制。當有多個用戶併發的去存取數據時，在數據庫中就可能會產生多個事務同時去操作一行數據的情況，如果我們不對此類併發操作不加以控制的話，就可能會讀取和存儲不正確的數據，最終破壞了數據的一致性；下面請看一種典型的併發更新數據所產生的數據丟失更新問題：

<code>| 事務A |事務B  |
|--|--|
| begin A |  |
||begin B|
|select salary form tb where id=1(查詢結果為1000)||
||select salary form tb where id=1(查詢結果為1000)|
|update tb set salary=1100 where id=1||
||update tb set salary=1200 where id=1|
|commit A||
||commit B|/<code>

異常結果：表中salary字段id為1員工的工資更新為了1200，但是實際上針對該員工的工資進行了兩次的修改操作，由於事務B在事務A之後提交，所以首先提交的事務A的更新操作被丟失了，所以我們就需要鎖機制來保證這種情況不會發生，保證事務中數據的一致性。

二、鎖類型

表鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突概率高，併發度最低；

行鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度小；發生鎖衝突的概率低，併發度高；

三、MyISAM存儲引擎：

表級鎖的鎖模式： 　　　　表級鎖有兩種模式：表共享讀鎖（Table Read Lock）和表獨佔寫鎖（Table Write Lock）；對於MyISAM表的讀操作，不會阻塞其他用戶對同一個表的讀請求，但是會阻塞對同一個表的寫請求；對MyISAM表的寫操作，則會阻塞其他用戶對同一個表的讀和寫操作；MyISAM表的讀操作與寫操作之間，以及寫操作與寫操作之間時串行的。

併發插入（Concurrent Inserts）：　　　　MyISAM表的讀和寫是串行的，但這是就總體而言的，在一定的條件下，MyISAM表也可以支持查詢和插入操作的併發進行；MyISAM存儲引擎有一個系統變量concurrent_insert，專門用以控制其併發插入的行為，其值分為可以為0、1/2。當concurrent_insert設置為0時，則不允許併發插入；當concurrent_insert設置為1時，如果MyISAM表中沒有空洞（即表的中間沒有被刪除的行），MyISAM允許在一個進程讀表的同時，另一個進程從表尾插入記錄，這也是MySQL的默認設置；當concurrent_insert設置為2時，無論MyISAM表中有沒有空洞，都允許在表尾併發插入記錄。可以利用MyISAM存儲引擎此併發插入特性，來解決應用中對同一個表查詢和插入的鎖爭用。例如：將concurrent_insert變量的值設為2，總是允許併發插入操作，同時通過定期在系統空閒時段執行OPTIMIZE TABLE語句來整理空間碎片，回收因刪除記錄而產生的中間空洞。

MyISAM引擎的鎖調度： 　　　　MyISAM存儲引擎的讀鎖和寫鎖是互斥的，讀寫操作時串行的。一個進程請求某個MyISAM表的讀鎖，同時另一個進程也請求同一個表的寫鎖，寫的優先級比讀的優先級更高，所以寫進程會先獲得鎖，即使讀請求先到鎖的等待隊列中，寫請求後到鎖的等待隊列中，寫鎖頁回插入到讀鎖請求之前執行；我們可以通過一些設置來調節MyISAM的調度行為，通過指定啟動參數low-priority-updates，使MyISAM引擎默認給予讀請求以優先的權利；通過執行命令SET LOW_PRIORITY_UPDATE=1，使該連接發出的更新請求優先級降低；通過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性，降低該語句的優先級。另外，MySQL也提供了一種折中的辦法來調節讀寫衝突，即給系統參數max_write_lock_count設置一個合適的值，當一個表的讀鎖達到這個值後，MySQL就暫時將寫請求的優先級降低，給讀進程一定的獲得鎖的機會。

四、InnoDB存儲引擎：

　　樂觀鎖與悲觀鎖是兩種併發控制的思想，可用於解決丟失更新的問題：　　　　樂觀鎖會"樂觀的"假定大概率不會發生併發更新衝突，訪問、處理數據過程中不加鎖，只在更新數據時再根據版本號或時間戳判斷是否有衝突，有則處理，無則提交事務；悲觀鎖會"悲觀的"假定大概率會發生併發更新衝突，訪問、處理數據前就加排他鎖，在整個數據處理過程中鎖定數據，事務提交或回滾後才釋放鎖；

InnoDB存儲引擎標準的行級鎖：　　　　共享鎖(S Lock)：讀鎖，允許事務讀一行數據；　　　　排他鎖(X Lock)：寫鎖，允許事務刪除或更新一行數據； 　　　　 　

<code>## 事務1
MariaDB [test]> show variables  like "autocommit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [test]> begin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> update tb1  set   name="aaa"  where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test]> commit; 

Query OK, 0 rows affected (0.00 sec)



## 事務2：
MariaDB [test]> show variables like "autocommit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [test]> begin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> update tb1  set  name="haha"  where id=1;
Query OK, 1 row affected (12.89 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test]> rollback;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select *   from     tb1 where id=1;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
+----+------+
1 row in set (0.00 sec)
/<code>

InnoDB行鎖的實現方式： 　　

InnoDB行鎖是通過給索引上的索引項加鎖的，InnoDB這種行鎖實現特點意味著：只有通過索引條件檢索數據，InnoDB才使用行級鎖，否則InnoDB將使用表鎖。在不通過索引條件查詢的時候，InnoDB確實是使用表鎖而不是行鎖；由於MySQL的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，是會出現鎖衝突的；當表有多個索引的時候，不同的事務可以使用不同的索引鎖定不同的行，另外，無論是使用主鍵索引、唯一索引或普通索引，InnoDB都會使用行鎖來對數據加鎖；即便是在條件中使用了索引字段，但是否使用索引來檢索數據是由MySQL通過判斷不同執行計劃的代價來決定的，如果MySQL認為全表掃描效率更高，比如對一些很小的表，它就不會使用索引，這種情況下InnoDB將使用表鎖而不是行鎖。

意向鎖： 　　　　意向共享鎖(IS Lock)：事務想要獲得一張表中某幾行的共享鎖； 　　　　意向排他鎖(IX Lock)：事務想要獲得一張表中某幾行的排它鎖； 　　　　 查看InnoDB存儲引擎的鎖信息：

<code>MariaDB [test]> show  engine innodb status\\G;/<code>

<code>MariaDB [test]> show full processlist;/<code>

<code>MariaDB [test]> select * from information_schema.innodb_trx\\G;
*************************** 1. row ***************************
                    trx_id: 1266629
                 trx_state: RUNNING
               trx_started: 2020-01-08 16:24:50
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 36696
                 trx_query: select * from information_schema.innodb_trx
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 376
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
1 row in set (0.00 sec)/<code>

<code>MariaDB [test]> select * from information_schema.innodb_locks\\G;
Empty set (0.00 sec)/<code>

<code>MariaDB [test]> select * from information_schema.innodb_lock_waits\\G;
Empty set (0.00 sec)/<code>

一致性的非鎖定讀(Consistent Nonlocking Read): 　　是指InnoDB存儲引擎通過很多個版本控制(multi versioning)的方式來讀取當前執行時間數據庫中的行的數據。如果讀取的行正在執行DELETE或UPDATE操作，這時讀取操作不會因此去等待行上的鎖的釋放；相反，InnoDB存儲引擎會去讀取行的一個快照數據，快照數據是指該行的之前的版本的數據，該實現是通過undo段來完成的。而undo用來事務中國回滾數據，因此快照數據本身是沒有額外的開銷。此外，讀取快照數據是不需要上鎖的，因為沒有事務需要對歷史數據進行修改操作。然而在不同的事務隔離級別下，對於快照數據，非一致性讀總是讀取被鎖定行的最新一份快照數據，而在REPEATABLE READ事務隔離級別下，對於快照數據，非一致性讀總是讀取事務開始時的行數據版本。

一致性的鎖定讀： 　　顯示地對數據庫讀取操作進行加鎖以保證數據邏輯的一致性；　　SELECT ... FOR UPDATE：對讀取的行記錄加一個X鎖，其他事務不能對已鎖定的行加任何的鎖；　　SELECT ... LOCK IN SHARE MODE：對讀取的行記錄加一個S鎖，其他事務可以向被鎖定的行加S鎖，但是如果加X鎖，則會被阻塞；

<code>## 事務1
MariaDB [test]> begin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select  * from   tb1 where id=1 for update;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
+----+------+
1 row in set (0.00 sec)

MariaDB [test]> rollback;
Query OK, 0 rows affected (0.00 sec)



## 事務2
MariaDB [test]> begin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select * from tb1 where id=1 lock in share mode;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
+----+------+
1 row in set (11.55 sec)

MariaDB [test]> rollback;
Query OK, 0 rows affected (0.00 sec)
/<code>

鎖算法：

五、死鎖

死鎖是指兩個或兩個以上的事務在執行過程中，因爭奪資源而造成的一種互相等待的現象；MyISAM表鎖是Deadlock Free的，這時因為MyISAM總是一次獲得所需的全部鎖，要麼全部滿足，要麼等待，因此不會出現死鎖。但在InnoDB中，除單個SQL組成的事務外，鎖是逐步獲得的，這就決定了在InnoDB中發生死鎖是可能的。發生死鎖後，InnoDB一般都能自動檢測到，並使一個事務釋放鎖並回退，另外一個事務獲得鎖，繼續完成事務。但在涉及外部鎖，或涉及表鎖的情況下，InnoDB並不能完全自動檢測到死鎖，這需要通過設置鎖等待超時參數innodb_lock_wait_timeout來解決，需要說明的是，這個參數並不是用來解決死鎖問題，在併發訪問比較高的情況下，如果大量事務因無法立即獲得所需的鎖而掛起，會佔用大量計算機資源，造成嚴重性能問題，甚至拖垮數據庫。我們通過設置合適的鎖等待超時閾值，可以避免這種情況的發生。

<code>## 事務1
MariaDB [test]> begin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> update tb1  set   name="jyy"  where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test]> update tb1  set   name="xixi"  where id=2;
Query OK, 1 row affected (8.25 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test]> commit;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select * from    tb1  where id in(1,2);
+----+------+
| id | name |
+----+------+
|  1 | jyy  |
|  2 | xixi |
+----+------+
2 rows in set (0.00 sec)


## 事務2
MariaDB [test]> begin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> update tb1  set  name="haha"  where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test]> update tb1  set  name="heihei"  where id=1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction/<code>

避免死鎖的常用方法： 　　

1）在應用中，如果不同的程序會併發存取多個表，應該儘量約定以相同的順序來訪問表，這樣可以大大降低產生死鎖的機會。在上面的例子中，由於兩個session訪問表的順序不同，發生死鎖的機會就非常高，但是如果以相同的順序來訪問，死鎖就可以避免；　　

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

3）在事務中，如果要更新記錄，應該直接申請足夠級別的鎖，即排他鎖，而不應該先申請共享鎖，從而造成鎖衝突，甚至死鎖；　　

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

5）當隔離級別為READ COMMITTED時，如果兩個線程都先執行SELECT...FOR UPDATE，判斷是否存在符合條件的記錄，如果沒有，就插入記錄。此時，只有一個線程能插入成功，另外一個線程就會出現鎖等待，當第一個線程提交後，第二個線程會因為主鍵衝突出錯，但雖然這個線程出錯了，卻會獲得一個排他鎖，這時如果有第三個線程又來申請排它鎖，也會出現死鎖。

6）如果出現了死鎖，可以使用上面的檢查鎖信息的SQL命令來確定最後一個死鎖產生的原因。返回結果中國包括死鎖相關的事務的詳細信息，如引發死鎖的SQL語句，事務已經獲得的鎖，正在等待什麼鎖，以及被回滾的事務等。據此可以分析死鎖產生的原因和改進措施。

查看更多...

閱讀更多 運維小筆記 的文章

關鍵字: 詳解 跳槽那些事兒 數據庫