一、Mysql為什么要加鎖

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

| 事務(wù)A |事務(wù)B  |  
|--|--|  
| begin A |  |  
||begin B|  
|select salary form tb where id=1(查詢(xún)結(jié)果為1000)||  
||select salary form tb where id=1(查詢(xún)結(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，但是實(shí)際上針對(duì)該員工的工資進(jìn)行了兩次的修改操作，由于事務(wù)B在事務(wù)A之后提交，所以首先提交的事務(wù)A的更新操作被丟失了，所以我們就需要鎖機(jī)制來(lái)保證這種情況不會(huì)發(fā)生，保證事務(wù)中數(shù)據(jù)的一致性。

二、鎖類(lèi)型

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

行鎖：開(kāi)銷(xiāo)大，加鎖慢；會(huì)出現(xiàn)死鎖；鎖定粒度小；發(fā)生鎖沖突的概率低，并發(fā)度高；

三、MyISAM存儲(chǔ)引擎：

表級(jí)鎖的鎖模式： 表級(jí)鎖有兩種模式：表共享讀鎖（Table Read Lock）和表獨(dú)占寫(xiě)鎖（Table Write Lock）；對(duì)于MyISAM表的讀操作，不會(huì)阻塞其他用戶(hù)對(duì)同一個(gè)表的讀請(qǐng)求，但是會(huì)阻塞對(duì)同一個(gè)表的寫(xiě)請(qǐng)求；對(duì)MyISAM表的寫(xiě)操作，則會(huì)阻塞其他用戶(hù)對(duì)同一個(gè)表的讀和寫(xiě)操作；MyISAM表的讀操作與寫(xiě)操作之間，以及寫(xiě)操作與寫(xiě)操作之間時(shí)串行的。

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

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

四、InnoDB存儲(chǔ)引擎：

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

InnoDB存儲(chǔ)引擎標(biāo)準(zhǔn)的行級(jí)鎖：共享鎖(S Lock)：讀鎖，允許事務(wù)讀一行數(shù)據(jù)；　　　　排他鎖(X Lock)：寫(xiě)鎖，允許事務(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行鎖的實(shí)現(xiàn)方式： 　　

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

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

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存儲(chǔ)引擎通過(guò)很多個(gè)版本控制(multi versioning)的方式來(lái)讀取當(dāng)前執(zhí)行時(shí)間數(shù)據(jù)庫(kù)中的行的數(shù)據(jù)。如果讀取的行正在執(zhí)行DELETE或UPDATE操作，這時(shí)讀取操作不會(huì)因此去等待行上的鎖的釋放；相反，InnoDB存儲(chǔ)引擎會(huì)去讀取行的一個(gè)快照數(shù)據(jù)，快照數(shù)據(jù)是指該行的之前的版本的數(shù)據(jù)，該實(shí)現(xiàn)是通過(guò)undo段來(lái)完成的。而undo用來(lái)事務(wù)中國(guó)回滾數(shù)據(jù)，因此快照數(shù)據(jù)本身是沒(méi)有額外的開(kāi)銷(xiāo)。此外，讀取快照數(shù)據(jù)是不需要上鎖的，因?yàn)闆](méi)有事務(wù)需要對(duì)歷史數(shù)據(jù)進(jìn)行修改操作。然而在不同的事務(wù)隔離級(jí)別下，對(duì)于快照數(shù)據(jù)，非一致性讀總是讀取被鎖定行的最新一份快照數(shù)據(jù)，而在REPEATABLE READ事務(wù)隔離級(jí)別下，對(duì)于快照數(shù)據(jù)，非一致性讀總是讀取事務(wù)開(kāi)始時(shí)的行數(shù)據(jù)版本。

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

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

鎖算法：

五、死鎖

死鎖是指兩個(gè)或兩個(gè)以上的事務(wù)在執(zhí)行過(guò)程中，因爭(zhēng)奪資源而造成的一種互相等待的現(xiàn)象；MyISAM表鎖是Deadlock Free的，這時(shí)因?yàn)镸yISAM總是一次獲得所需的全部鎖，要么全部滿(mǎn)足，要么等待，因此不會(huì)出現(xiàn)死鎖。但在InnoDB中，除單個(gè)SQL組成的事務(wù)外，鎖是逐步獲得的，這就決定了在InnoDB中發(fā)生死鎖是可能的。發(fā)生死鎖后，InnoDB一般都能自動(dòng)檢測(cè)到，并使一個(gè)事務(wù)釋放鎖并回退，另外一個(gè)事務(wù)獲得鎖，繼續(xù)完成事務(wù)。但在涉及外部鎖，或涉及表鎖的情況下，InnoDB并不能完全自動(dòng)檢測(cè)到死鎖，這需要通過(guò)設(shè)置鎖等待超時(shí)參數(shù)innodb_lock_wait_timeout來(lái)解決，需要說(shuō)明的是，這個(gè)參數(shù)并不是用來(lái)解決死鎖問(wèn)題，在并發(fā)訪(fǎng)問(wèn)比較高的情況下，如果大量事務(wù)因無(wú)法立即獲得所需的鎖而掛起，會(huì)占用大量計(jì)算機(jī)資源，造成嚴(yán)重性能問(wèn)題，甚至拖垮數(shù)據(jù)庫(kù)。我們通過(guò)設(shè)置合適的鎖等待超時(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)用中，如果不同的程序會(huì)并發(fā)存取多個(gè)表，應(yīng)該盡量約定以相同的順序來(lái)訪(fǎng)問(wèn)表，這樣可以大大降低產(chǎn)生死鎖的機(jī)會(huì)。在上面的例子中，由于兩個(gè)session訪(fǎng)問(wèn)表的順序不同，發(fā)生死鎖的機(jī)會(huì)就非常高，但是如果以相同的順序來(lái)訪(fǎng)問(wèn)，死鎖就可以避免；

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

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

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

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

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