MySQL之InnoDB下的鎖問題

更新時(shí)間：2023年08月10日 15:18:28 作者：Redick01

這篇文章主要介紹了MySQL之InnoDB下的鎖問題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

背景知識(shí)

InnoDB相比較MyISAM一是支持事務(wù)，二是支持了行級(jí)鎖，提到InnoDB鎖問題就不得不提到事務(wù)，所以在這之前先了解下事務(wù)的一些知識(shí)

事務(wù)及其ACID屬性

事務(wù)是由一組SQL語句組成的邏輯處理單元，事務(wù)具有以下4個(gè)屬性，通常簡(jiǎn)稱為事務(wù)的ACID屬性。

原子性（Atomicity）：事務(wù)開始后所有操作，要么全部做完，要么全部不做，不可能停滯在中間環(huán)節(jié)。事務(wù)執(zhí)行過程中出錯(cuò)，會(huì)回滾到事務(wù)開始前的狀態(tài)，所有的操作就像沒有發(fā)生一樣。也就是說事務(wù)是一個(gè)不可分割的整體，就像化學(xué)中學(xué)過的原子，是物質(zhì)構(gòu)成的基本單位。
一致性（Consistency）：事務(wù)開始前和結(jié)束后，數(shù)據(jù)庫的完整性約束沒有被破壞。比如A向B轉(zhuǎn)賬，不可能A扣了錢，B卻沒收到。
隔離性（Isolation）：同一時(shí)間，只允許一個(gè)事務(wù)請(qǐng)求同一數(shù)據(jù)，不同的事務(wù)之間彼此沒有任何干擾。比如A正在從一張銀行卡中取錢，在A取錢的過程結(jié)束前，B不能向這張卡轉(zhuǎn)賬。
持久性（Durability）：事務(wù)完成后，事務(wù)對(duì)數(shù)據(jù)庫的所有更新將被保存到數(shù)據(jù)庫，不能回滾。

并發(fā)事務(wù)處理帶來的問題

相對(duì)于串行處理來說，并發(fā)事務(wù)處理能大大增加數(shù)據(jù)庫資源的利用率，提高數(shù)據(jù)庫系統(tǒng)的事務(wù)吞吐量，從而可以支持更多的用戶。但是并發(fā)事務(wù)處理也會(huì)帶來以下問題。

臟讀：事務(wù)A讀取了事務(wù)B更新的數(shù)據(jù)，然后B回滾操作，那么A讀取到的數(shù)據(jù)是臟數(shù)據(jù)
不可重復(fù)讀：事務(wù) A 多次讀取同一數(shù)據(jù)，事務(wù) B 在事務(wù)A多次讀取的過程中，對(duì)數(shù)據(jù)作了更新并提交，導(dǎo)致事務(wù)A多次讀取同一數(shù)據(jù)時(shí)，結(jié)果不一致。
幻讀：系統(tǒng)管理員A將數(shù)據(jù)庫中所有學(xué)生的成績(jī)從具體分?jǐn)?shù)改為ABCDE等級(jí)，但是系統(tǒng)管理員B就在這個(gè)時(shí)候插入了一條具體分?jǐn)?shù)的記錄，當(dāng)系統(tǒng)管理員A改結(jié)束后發(fā)現(xiàn)還有一條記錄沒有改過來，就好像發(fā)生了幻覺一樣，這就叫幻讀。

小結(jié)：不可重復(fù)讀的和幻讀很容易混淆，不可重復(fù)讀側(cè)重于修改，幻讀側(cè)重于新增或刪除。解決不可重復(fù)讀的問題只需鎖住滿足條件的行，解決幻讀需要鎖表

事務(wù)隔離級(jí)別

MySQL數(shù)據(jù)庫實(shí)現(xiàn)事務(wù)隔離的方式，基本上可分為兩種。

一種是在讀取數(shù)據(jù)前，對(duì)其加鎖，阻止其他事務(wù)對(duì)數(shù)據(jù)進(jìn)行修改
不加鎖，通過一定機(jī)制生成一個(gè)數(shù)據(jù)請(qǐng)求時(shí)間點(diǎn)的一致性數(shù)據(jù)快照，并用這個(gè)快照來提供一定級(jí)別（語句級(jí)或事務(wù)級(jí)）的一致性讀取。從用戶的角度來看，好像是數(shù)據(jù)庫可以提供同意數(shù)據(jù)的多個(gè)版本，因此，這種技術(shù)叫做 數(shù)據(jù)多版本并發(fā)控制 簡(jiǎn)稱MVCC。

四種事務(wù)隔離級(jí)別：

READ UNCOMMITTED（讀未提交）：事務(wù)A和B操作同一數(shù)據(jù)，事務(wù)A能夠讀到事務(wù)B未提交的數(shù)據(jù)，會(huì)產(chǎn)生幻讀，不可重復(fù)讀，臟讀
READ COMMITTED（讀已提交）：事務(wù)A和B操作同一數(shù)據(jù)，事務(wù)A能夠讀到事務(wù)B更新的數(shù)據(jù)，會(huì)產(chǎn)生幻讀和不可重復(fù)度
REPEATABLE READ（可重復(fù)讀）：事務(wù)A和事務(wù)B操作同一數(shù)據(jù)，事務(wù)A不能讀到事務(wù)B已經(jīng)插入的數(shù)據(jù)，會(huì)產(chǎn)生幻讀
SERIALIZABLE（串行化）：所有事務(wù)都必須保證串行執(zhí)行，不會(huì)產(chǎn)生臟讀，幻讀，不可重復(fù)度

獲取InnoDB行鎖爭(zhēng)用情況

可以通過檢查InnoDB_row_lock狀態(tài)變量來分析系統(tǒng)傷的行鎖的爭(zhēng)奪情況：

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.04 sec)

如果鎖爭(zhēng)用比較嚴(yán)重，Innodb_row_lock_waits和Innodb_row_lock_time_avg的值比較高，可以通過查詢information_schema數(shù)據(jù)庫中相關(guān)的表來查看鎖情況，或者通過設(shè)置InnodDB Monitors來進(jìn)一步觀察發(fā)生鎖沖突的表、數(shù)據(jù)行等，并分析其原因。

（1）通過查詢information_schema數(shù)據(jù)庫中的innodb_locks表了解鎖的等待情況：

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from innodb_locks;
Empty set, 1 warning (0.01 sec)
mysql>

（2）通過設(shè)置InnoDB Monitors觀察鎖沖突情況：

mysql> create table innodb_monitor(a INT) ENGINE=INNODB;
Query OK, 0 rows affected (0.05 sec)

然后通過下面語句來進(jìn)行查看：

mysql> show engine innodb status;
| Type   | Name | Status                                                                                                                                                                                                                                                                                           
| InnoDB |      |
...
------------
TRANSACTIONS
------------
Trx id counter 6076
Purge done for trx's n:o < 6071 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421657844005624, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421657844004704, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421657844006544, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
...
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1, Main thread ID=140182422546176, state: sleeping
Number of rows inserted 55250, updated 1240, deleted 376, read 22512
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.01 sec)

監(jiān)視器可以通過下列語句來停止：

mysql&gt; drop table innodb_monitor;Query OK, 0 rows affected (0.02 sec)

設(shè)置監(jiān)視器后，在show innodb status的顯示內(nèi)容中，會(huì)有詳細(xì)的當(dāng)前鎖等待的信息，包括表名、鎖類型、鎖定記錄的情況等，便于進(jìn)一步分析和定位問題。

InnoDB的行鎖模式及加鎖方法

InnoDB實(shí)現(xiàn)了兩種類型的行鎖

共享所（S）：允許一個(gè)事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。
排他鎖（X）：允許獲得排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)取得相同數(shù)據(jù)集的共享讀鎖和排他寫鎖。

另外，為了允許行鎖和表鎖共存，實(shí)現(xiàn)多粒度鎖機(jī)制，InnoDB還有兩種內(nèi)部使用的意向鎖。

意向共享鎖（IS）：事務(wù)打算給數(shù)據(jù)行加共享鎖，事務(wù)在給一個(gè)數(shù)據(jù)行加共享鎖前必須先獲得該表的IS鎖。
意向排他鎖（IX）：事務(wù)打算給數(shù)據(jù)行加行排他鎖，事務(wù)在給一個(gè)數(shù)據(jù)行排他鎖前必須先獲得該表的IX鎖。

InnoDB行鎖模式兼容性列表：

	X	IX	S	IS
X	沖突	沖突	沖突	沖突
IX	沖突	兼容	沖突	兼容
S	沖突	沖突	兼容	兼容
IS	沖突	兼容	兼容	兼容

如果一個(gè)事務(wù)請(qǐng)求的鎖模式與當(dāng)前的鎖兼容，InnoDB就將請(qǐng)求的鎖收于授予該事務(wù)，否則該事務(wù)就要等待鎖的釋放。

意向鎖是InnoDB自動(dòng)加的，對(duì)于update，delete和insert語句，InnoDB是會(huì)自動(dòng)給涉及數(shù)據(jù)集加排他鎖（X）；對(duì)于普通SELECT語句，InnoDB不會(huì)加任何鎖；事務(wù)可以通過以下語句顯示給記錄集加共享鎖或排他鎖。

共享鎖（S）：select * from table_name where … lock in share mode;
排他鎖（X）：select * from table_name where … for update;

用lock in share mode獲得共享鎖，主要用在需要數(shù)據(jù)依存關(guān)系時(shí)來確認(rèn)某行記錄是否存在，并確保沒有人對(duì)這個(gè)記錄進(jìn)行update或delete。但是如果當(dāng)前事務(wù)也需要對(duì)該記錄進(jìn)行更新操作，則很有可能造成死鎖。

注：session1和session2是兩個(gè)連接到MySQL的客戶端，使用的數(shù)據(jù)庫是從mysql官網(wǎng)下載的，下載地址：http://downloads.mysql.com/docs/sakila-db.zip

下面是使用 lock in share mode加共享鎖的例子

session1:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from actor where actor_id=178;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      178 | LISA       | MONROE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)

session2:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from actor where actor_id=178;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      178 | LISA       | MONROE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)

session1對(duì)actor_id=178的記錄加share mode的共享鎖，session2也對(duì)actor_id=178加share mode的共享鎖，此時(shí)session1和session2能夠加共享鎖，如下：

session1
mysql> select * from actor where actor_id=178 lock in share mode;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      178 | LISA       | MONROE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)
session2
mysql> select * from actor where actor_id=178 lock in share mode;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      178 | LISA       | MONROE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)

緊接著session1對(duì)178記錄進(jìn)行update，此時(shí)session1會(huì)等待鎖，與此同時(shí)session2也對(duì)178記錄更新，此時(shí)session2發(fā)生死鎖，退出；

session1
mysql> update actor set last_name='monore t' where actor_id=178;
...等待
session2
mysql> mysql> update actor set last_name='monore t' where actor_id=178;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql> update actor set last_name='monore t' where actor_id=178' at line 1

session2提交事務(wù)，session1獲取到鎖，update成功。

session2
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
session1
Query OK, 1 row affected (49.29 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from actor where actor_id=178;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      178 | LISA       | monore t  | 2021-09-02 12:47:50 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)

下面是使用for update加排他鎖的例子

session1對(duì)actor_id=178的行記錄使用for update加排他鎖，此時(shí)session2再次對(duì)178加排他鎖是不會(huì)獲取到鎖的，會(huì)等待。

session1
mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from actor where actor_id=178 for update;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      178 | LISA       | monore t  | 2021-09-02 12:47:50 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)
session2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from actor where actor_id=178 for update;
...等待

session1提交事務(wù)，session2獲取到鎖。

session1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
session2
mysql> select * from actor where actor_id=178 for update;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      178 | LISA       | monore t  | 2021-09-02 12:47:50 |
+----------+------------+-----------+---------------------+
1 row in set (4.84 sec)

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

InnoDB行鎖是通過給索引上的索引項(xiàng)加鎖來實(shí)現(xiàn)的，如果沒有索引，InnoDB將通過隱藏的聚集索引Row_id來對(duì)記錄加鎖，InnoDB行鎖分為3種情形。

Record Lock：對(duì)索引項(xiàng)加鎖。
Gap lock：對(duì)索引項(xiàng)之間的“間隙”、第一條記錄前的“間隙”或最后一條記錄后的“間隙”加鎖。
Next-key lock：前兩種的組合，對(duì)記錄及其前面的間隙加鎖

InnoDB這種行鎖實(shí)現(xiàn)特點(diǎn)意味著：如果不通過索引條件檢索數(shù)據(jù)，那么InnoDB將對(duì)表中的所有記錄加鎖，實(shí)際效果跟鎖表一樣！在實(shí)際應(yīng)用中，要特別注意InnoDB行鎖這一特性，否則可能導(dǎo)致大量的鎖沖突，從而影響并發(fā)性能。

在不通過索引條件查詢時(shí)，InnoDB會(huì)鎖定表中的所有記錄

如下，payment表的amount字段沒有索引

session1加鎖查詢amount=8.99的數(shù)據(jù)，然后session2在加鎖查詢amount=3.99的數(shù)據(jù)，此時(shí)session2就會(huì)等待鎖，session1 commit后session2獲取到鎖查詢到數(shù)據(jù)。看起來session1只給amount=8.99的行加了鎖，但是卻出現(xiàn)了鎖等待，原因就是在沒有索引的情況下InnoDB對(duì)所有的記錄加鎖。

session1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select payment_id, customer_id, staff_id, amount from payment where amount=8.99 for update;
+------------+-------------+----------+--------+
| payment_id | customer_id | staff_id | amount |
+------------+-------------+----------+--------+
|         62 |           3 |        1 |   8.99 |
|         81 |           3 |        1 |   8.99 |
|         83 |           3 |        2 |   8.99 |
...
+------------+-------------+----------+--------+
session2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select payment_id, customer_id, staff_id, amount from payment where amount=3.99 for update;
...等待

下面我們看一下通過索引條件加鎖時(shí)的情況，例如session1加鎖查詢payment_id=62，session2加鎖查詢payment_id=81；此時(shí)使用了索引，加鎖就只加在符合索引條件的記錄上了，并沒有出現(xiàn)等待鎖情況。

session1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select payment_id, customer_id, staff_id, amount from payment where payment_id=62 for update;
+------------+-------------+----------+--------+
| payment_id | customer_id | staff_id | amount |
+------------+-------------+----------+--------+
|         62 |           3 |        1 |   8.99 |
+------------+-------------+----------+--------+
1 row in set (0.00 sec)
session2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select payment_id, customer_id, staff_id, amount from payment where payment_id=81 for update;
+------------+-------------+----------+--------+
| payment_id | customer_id | staff_id | amount |
+------------+-------------+----------+--------+
|         81 |           3 |        1 |   8.99 |
+------------+-------------+----------+--------+
1 row in set (0.00 sec)

由于MySQL的行鎖是對(duì)索引加的鎖

所以雖然訪問了不同的記錄，但是如果使用相同的索引鍵會(huì)出現(xiàn)沖突的

比如payment表staff_id有索引，amount沒有索引，session1加鎖查詢staff_id=1 and amount=8.99的記錄，session2加鎖查詢staff_id=1 and amount=3.99的記錄，session2就會(huì)等待獲取鎖，雖然訪問的是不同的行，因?yàn)殒i是加在索引上，所以會(huì)產(chǎn)生鎖沖突。session1 commit后session2獲取鎖成功。

session1
mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)
mysql> select payment_id, customer_id, staff_id, amount from payment where staff_id=1 and amount=8.99 for update;
+------------+-------------+----------+--------+
| payment_id | customer_id | staff_id | amount |
+------------+-------------+----------+--------+
|         62 |           3 |        1 |   8.99 |
|         81 |           3 |        1 |   8.99 |
|        122 |           5 |        1 |   8.99 |
|        188 |           7 |        1 |   8.99 |
...
+------------+-------------+----------+--------+
session2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select payment_id, customer_id, staff_id, amount from payment where staff_id=1 and amount=3.99 for update;
...等待

當(dāng)表有多個(gè)索引的時(shí)候

不同事務(wù)可以使用不同的索引鎖定不同的行，不論是使用主鍵索引、唯一索引或普通索引，InnoDB都會(huì)使用行鎖來對(duì)數(shù)據(jù)加鎖

payment表的customer_id和staff_id是索引，session1加鎖查詢customer_id=3，session2加鎖查詢staff_id=1的行，customer_id=3的數(shù)據(jù)中包含staff_id=1的數(shù)據(jù)，session2會(huì)等待鎖，因?yàn)閟ession1鎖了所有customer_id=3的行，包含staff_id=1的，所以session2或等待獲取鎖。

session1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select payment_id, customer_id, staff_id, amount from payment where customer_id=3 for update;
+------------+-------------+----------+--------+
| payment_id | customer_id | staff_id | amount |
+------------+-------------+----------+--------+
|         60 |           3 |        1 |   1.99 |
|         61 |           3 |        1 |   2.99 |
|         62 |           3 |        1 |   8.99 |
|         63 |           3 |        1 |   6.99 |
|         64 |           3 |        2 |   6.99 |
|         65 |           3 |        1 |   2.99 |
|         66 |           3 |        1 |   4.99 |
|         67 |           3 |        1 |   4.99 |
|         68 |           3 |        1 |   5.99 |
|         69 |           3 |        2 |  10.99 |
|         70 |           3 |        2 |   7.99 |
|         71 |           3 |        2 |   6.99 |
|         72 |           3 |        1 |   4.99 |
|         73 |           3 |        2 |   4.99 |
|         74 |           3 |        1 |   2.99 |
|         75 |           3 |        1 |   1.99 |
|         76 |           3 |        2 |   3.99 |
|         77 |           3 |        1 |   2.99 |
|         78 |           3 |        2 |   4.99 |
|         79 |           3 |        2 |   5.99 |
|         80 |           3 |        2 |   4.99 |
|         81 |           3 |        1 |   8.99 |
|         82 |           3 |        2 |   2.99 |
|         83 |           3 |        2 |   8.99 |
|         84 |           3 |        2 |   0.99 |
|         85 |           3 |        1 |   2.99 |
+------------+-------------+----------+--------+
26 rows in set (0.00 sec)
session2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select payment_id, customer_id, staff_id, amount from payment where staff_id=1 for update;
...等待

注：即便在條件中使用了索引字段，但是否使用索引來檢索數(shù)據(jù)是有MySQL通過判斷不同執(zhí)行計(jì)劃的代價(jià)來決定的，如果MySQL認(rèn)為全表掃描效率更高，比如對(duì)一些很小的表就不會(huì)使用索引，這種情況InnoDB會(huì)對(duì)所有的行加鎖，因此在分析鎖沖突時(shí)別忘了分析sql執(zhí)行計(jì)劃。

Next-Key鎖

當(dāng)用范圍條件而不是相等條件檢索數(shù)據(jù)，并請(qǐng)求共享鎖或排他鎖時(shí)，InnoDB會(huì)給符合條件的數(shù)據(jù)行的索引項(xiàng)加鎖，對(duì)于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做“間隙（GAP）”，InnoDB會(huì)對(duì)這個(gè)“間隙”加鎖，這種鎖機(jī)制就是所謂的Next-Key鎖。

舉個(gè)例子，加鎖查詢payment_id>16048的數(shù)據(jù)，16049的記錄會(huì)加鎖，大于16049的記錄（不存在）的“間隙”也會(huì)加鎖。

session1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select payment_id, customer_id, staff_id, amount from payment where payment_id>16048 for update;
+------------+-------------+----------+--------+
| payment_id | customer_id | staff_id | amount |
+------------+-------------+----------+--------+
|      16049 |         599 |        2 |   2.99 |
+------------+-------------+----------+--------+
1 row in set (0.00 sec)
session2
mysql> insert into payment (payment_id, customer_id, staff_id, amount) value (16050, 3, 2, 1.99);
... 等待

還要特別說的是，InnoDB除了通過范圍條件加鎖時(shí)使用Next-Key鎖外，如果使用相等條件請(qǐng)求一個(gè)不存在的記錄加鎖，InnoDB也會(huì)使用Next-Key。例如session1加鎖請(qǐng)求payment_id=16051的記錄（該記錄不存在），session2 插入payment_id=16051的記錄就會(huì)等待鎖。

session1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select payment_id, customer_id, staff_id, amount from payment where payment_id=16051 for update;
Empty set (0.01 sec)
session2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into payment (payment_id, customer_id, staff_id, amount) value (16051, 3, 2, 1.99);
...等待

什么時(shí)候用表鎖

事務(wù)需要更新大部分或全部數(shù)據(jù)，表比較大，如果使用行鎖，不僅這個(gè)事務(wù)執(zhí)行效率低，而且可能造成其他事務(wù)長(zhǎng)時(shí)間鎖等待和鎖沖突，這種情況下可以考慮使用表鎖來提高事務(wù)的執(zhí)行效率。
事務(wù)涉及到多個(gè)表，比較復(fù)雜，很可能引起死鎖，造成大量事務(wù)回滾。這種情況也可以考慮一次性鎖定事務(wù)涉及的表，從而避免死鎖，減少數(shù)據(jù)庫因事務(wù)回滾帶來的開銷。

當(dāng)然這兩種情況事務(wù)不能太多，否則，就應(yīng)該考慮使用MyISAM表了。

死鎖

死鎖示例，session1加鎖查詢payment表payment_id=15866記錄，session2加鎖查詢actor表actor_id=200記錄，之后session1加鎖查詢actor表actor_id=200，此時(shí)因?yàn)閟ession2持有鎖所以session1等待鎖，然后session2加鎖查詢payment表payment_id=15866記錄，這時(shí)InnoDB檢測(cè)到了死鎖，session2退出，session1查詢到actor_id=200的記錄。

session1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from payment where payment_id=15866 for update;
+------------+-------------+----------+-----------+--------+---------------------+---------------------+
| payment_id | customer_id | staff_id | rental_id | amount | payment_date        | last_update         |
+------------+-------------+----------+-----------+--------+---------------------+---------------------+
|      15866 |         592 |        1 |     11410 |   8.99 | 2005-08-02 19:29:01 | 2006-02-15 22:23:29 |
+------------+-------------+----------+-----------+--------+---------------------+---------------------+
1 row in set (0.00 sec)
session2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from actor where actor_id=200 for update;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      200 | THORA      | TEMPLE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.01 sec)
session1
mysql> select * from actor where actor_id=200 for update;
... 等待鎖
session2，InnoDB檢測(cè)到死鎖，退出事務(wù)
mysql> select * from payment where payment_id=15866 for update;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
session2退出事務(wù)，session1獲取到鎖
mysql> select * from actor where actor_id=200 for update;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      200 | THORA      | TEMPLE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (6.53 sec)

通過過例子可以看出，InnoDB一般能自動(dòng)檢測(cè)到死鎖，并且使一個(gè)事務(wù)釋放鎖并回退，另一個(gè)事務(wù)獲取到鎖后可以繼續(xù)正常執(zhí)行，但是涉及外部鎖以及表鎖的情況下，InnoDB并不能完全自動(dòng)檢測(cè)到死鎖，這就需要設(shè)置鎖等待的超時(shí)時(shí)間innodb_lock_wait_timeout來解決。

這個(gè)參數(shù)并不是只用來結(jié)局死鎖問題，在并發(fā)比較高的情況下如果大量事務(wù)因無法立即獲得所需的鎖而掛起，會(huì)占用大量計(jì)算機(jī)資源，造成嚴(yán)重的性能問題，甚至拖垮數(shù)據(jù)庫。通過設(shè)置合適的鎖等待超市閾值可以避免這種情況發(fā)生。

避免死鎖的常用方法

在應(yīng)用中，如果不同的程序會(huì)并發(fā)存取多個(gè)表，應(yīng)盡量約定以相同的順序來訪問表，這樣可以大大降低產(chǎn)生死鎖的機(jī)會(huì)。
在程序以批量的方式處理數(shù)據(jù)的時(shí)候，如果實(shí)現(xiàn)對(duì)數(shù)據(jù)排序，保證每個(gè)線程按照固定的順序來處理記錄，也可以大大降低出現(xiàn)死鎖的可能。
在事務(wù)中，如果要更新記錄，應(yīng)該直接申請(qǐng)足夠級(jí)別的鎖，即排他鎖，不應(yīng)該先申請(qǐng)共享鎖再在更新時(shí)申請(qǐng)排他鎖，防止其他事務(wù)在更新時(shí)獲取到共享鎖，造成鎖沖突甚至是死鎖。
在REPEATABLE-READ隔離級(jí)別下，如果兩個(gè)線程同時(shí)對(duì)相同記錄用select … for update加排他鎖，在沒有符合記錄的情況下，兩個(gè)線程都會(huì)加鎖成功。程序發(fā)現(xiàn)記錄不存在，就試圖插入一條新紀(jì)錄，如果兩個(gè)線程都這么做，就會(huì)出現(xiàn)死鎖。
當(dāng)隔離級(jí)別為READ COMMITED時(shí)，如果兩個(gè)線程都先執(zhí)行select … for update，判斷是否存在符合條件記錄，如果沒有，就插入記錄。此時(shí)，只有一個(gè)線程能插入成功，另一個(gè)線程會(huì)出現(xiàn)鎖等待，當(dāng)?shù)谝粋€(gè)線程提交后，第二個(gè)線程因主鍵重復(fù)出錯(cuò)，雖然出錯(cuò)了，卻會(huì)獲得一個(gè)排他鎖！這時(shí)如果第3個(gè)線程又來申請(qǐng)排他鎖，也會(huì)出現(xiàn)死鎖。

總結(jié)

InnoDB的行鎖是基于索引實(shí)現(xiàn)的，如果不通過索引加鎖訪問數(shù)據(jù)，InnoDB會(huì)對(duì)所有數(shù)據(jù)加鎖。
在不同隔離級(jí)別下，InnoDB的鎖機(jī)制和一致性讀取策略不通。
介紹了Next-Key鎖機(jī)制，以及InnoDB使用Next-Key鎖的原因。
精心設(shè)計(jì)索引，并盡量使用索引訪問數(shù)據(jù)，使加鎖的粒度更小，從而減少鎖沖突的機(jī)會(huì)。
選擇合理的事務(wù)，小事務(wù)發(fā)生死鎖的概率小。
盡量使用相等的條件訪問數(shù)據(jù)，避免Next-Key鎖對(duì)并發(fā)插入的影響。
避免死鎖的一些常用方法
對(duì)于一些特定的事務(wù)，可以利用表鎖來提高處理速度
不要申請(qǐng)超過實(shí)際需要的鎖級(jí)別；除非必須，查詢時(shí)不要顯示加鎖。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: