一、Mysql為什么要加鎖

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

| 事務(wù)A |事務(wù)B  |  
|--|--|  
| begin A |  |  
||begin B|  
|select salary form tb where id=1(查詢結(jié)果為1000)||  
||select salary form tb where id=1(查詢結(jié)果為1000)|  
|update tb set salary=1100 where id=1||  
||update tb set salary=1200 where id=1|  
|commit A||  
||commit B| 

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

二、鎖類型

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

行鎖：開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定粒度?。话l(fā)生鎖沖突的概率低，并發(fā)度高；

三、MyISAM存儲引擎：

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

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

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

四、InnoDB存儲引擎：

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

InnoDB存儲引擎標(biāo)準(zhǔn)的行級鎖：共享鎖(S Lock)：讀鎖，允許事務(wù)讀一行數(shù)據(jù)；　　　　排他鎖(X Lock)：寫鎖，允許事務(wù)刪除或更新一行數(shù)據(jù)； 　

## 事務(wù)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)  
## 事務(wù)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) 

InnoDB行鎖的實現(xiàn)方式： 　　

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

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

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

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

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) 

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

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

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

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

## 事務(wù)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)  
## 事務(wù)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) 

鎖算法：

五、死鎖

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

## 事務(wù)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)  
## 事務(wù)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 

避免死鎖的常用方法： 　　

1）在應(yīng)用中，如果不同的程序會并發(fā)存取多個表，應(yīng)該盡量約定以相同的順序來訪問表，這樣可以大大降低產(chǎn)生死鎖的機(jī)會。在上面的例子中，由于兩個session訪問表的順序不同，發(fā)生死鎖的機(jī)會就非常高，但是如果以相同的順序來訪問，死鎖就可以避免；

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

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

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

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

6）如果出現(xiàn)了死鎖，可以使用上面的檢查鎖信息的SQL命令來確定最后一個死鎖產(chǎn)生的原因。返回結(jié)果中國包括死鎖相關(guān)的事務(wù)的詳細(xì)信息，如引發(fā)死鎖的SQL語句，事務(wù)已經(jīng)獲得的鎖，正在等待什么鎖，以及被回滾的事務(wù)等。據(jù)此可以分析死鎖產(chǎn)生的原因和改進(jìn)措施。