原文：https://my.oschina.net/lujianing/blog/887923
一慧域、前言
上周遇到一個(gè)因insert而引發(fā)的死鎖問題愧杯，其成因比較令人費(fèi)解慕蔚。
于是想要了解一下insert加鎖機(jī)制继薛，但是發(fā)現(xiàn)網(wǎng)上介紹的文章比較少且零散威鹿，挖掘過程比較忙亂匾灶。
本以為只需要系統(tǒng)學(xué)習(xí)一個(gè)較完全的邏輯吆豹，但是實(shí)際牽扯很多innodb鎖相關(guān)知識(shí)及加鎖方式鳍刷。我好像并沒有那么大的能耐占遥，把各種場景的加鎖過程一一列舉并加之分析；亦沒有太多的精力驗(yàn)證網(wǎng)上的言論的準(zhǔn)確性输瓜。
只好根據(jù)現(xiàn)在了解的內(nèi)容瓦胎，參考官方文檔芬萍，說說自己當(dāng)前的理解。
本文僅供參考搔啊，如有誤導(dǎo)柬祠，概不負(fù)責(zé)。
二负芋、現(xiàn)場狀態(tài)
不同的mysql版本漫蛔，不同的參數(shù)設(shè)置，都可能對加鎖過程有影響旧蛾。
分析加鎖機(jī)制還是應(yīng)當(dāng)盡可能多地列舉一下關(guān)鍵參數(shù)莽龟，例如：當(dāng)前mysql版本、事務(wù)隔離級別等蚜点。
如下轧房，僅僅只列出個(gè)別比較重要的參數(shù)拌阴。
1.數(shù)據(jù)庫版本
|
1
2
3
4
5
6
|
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.27 |
+-----------+
|
2. 數(shù)據(jù)庫引擎
|
1
2
3
4
5
6
7
8
|
mysql> show variables like '%engine%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| storage_engine | InnoDB |
+----------------------------+--------+
|
注：InnoDB支持事務(wù)绍绘，Myisam不支持事務(wù)；InnoDB支持行鎖和表鎖迟赃；Myisam不支持行鎖陪拘。
3. 事務(wù)隔離級別
|
1
2
3
4
5
6
|
mysql> select @@global.tx_isolation, @@session.tx_isolation, @@tx_isolation;
+-----------------------+------------------------+-----------------+
| @@global.tx_isolation | @@session.tx_isolation | @@tx_isolation |
+-----------------------+------------------------+-----------------+
| REPEATABLE-READ | REPEATABLE-READ | REPEATABLE-READ |
+-----------------------+------------------------+-----------------+
|
注：幾種事務(wù)隔離級別：READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE
4. 查看gap鎖開啟狀態(tài)
|
1
2
3
4
5
6
|
mysql> show variables like 'innodb_locks_unsafe_for_binlog';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF |
+--------------------------------+-------+
|
innodb_locks_unsafe_for_binlog：默認(rèn)值為0，即啟用gap lock纤壁。
最主要的作用就是控制innodb是否對gap加鎖左刽。
但是，這一設(shè)置變更并不影響外鍵和唯一索引（含主鍵）對gap進(jìn)行加鎖的需要酌媒。
開啟innodb_locks_unsafe_for_binlog的REPEATABLE-READ事務(wù)隔離級別欠痴，很大程度上已經(jīng)蛻變成了READ-COMMITTED。
參見官方文檔[1]：
By default, the value of innodb_locks_unsafe_for_binlog is 0 (disabled), which means that gap locking is enabled: InnoDB uses next-key locks for searches and index scans. To enable the variable, set it to 1. This causes gap locking to be disabled: InnoDB uses only index-record locks for searches and index scans.
Enabling innodb_locks_unsafe_for_binlog does not disable the use of gap locking for foreign-key constraint checking or duplicate-key checking.
The effect of enabling innodb_locks_unsafe_for_binlog is similar to but not identical to setting the transaction isolation level to READ COMMITTED.
5. 查看自增鎖模式
|
1
2
3
4
5
6
|
mysql> show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1 |
+--------------------------+-------+
|
innodb_autoinc_lock_mode有3種配置模式：0秒咨、1喇辽、2，分別對應(yīng)”傳統(tǒng)模式”, “連續(xù)模式”, “交錯(cuò)模式”雨席。[2]
傳統(tǒng)模式：涉及auto-increment列的插入語句加的表級AUTO-INC鎖菩咨，只有插入執(zhí)行結(jié)束后才會(huì)釋放鎖。這是一種兼容MySQL 5.1之前版本的策略陡厘。
連續(xù)模式：可以事先確定插入行數(shù)的語句(包括單行和多行插入)抽米，分配連續(xù)的確定的auto-increment值；對于插入行數(shù)不確定的插入語句糙置，仍加表鎖云茸。這種模式下，事務(wù)回滾谤饭，auto-increment值不會(huì)回滾标捺，換句話說胖笛，自增列內(nèi)容會(huì)不連續(xù)。
交錯(cuò)模式：同一時(shí)刻多條SQL語句產(chǎn)生交錯(cuò)的auto-increment值宜岛。
由于insert語句常常涉及自增列的加鎖過程长踊，會(huì)涉及到AUTO-INC Locks加鎖過程。
為了分步了解insert加鎖過程萍倡，本文暫不討論任何涉及自增列的加鎖邏輯身弊。
這一參數(shù)設(shè)置相關(guān)內(nèi)容可能會(huì)出現(xiàn)在我的下一篇文章里。
n. etc
相關(guān)的參數(shù)配置越詳情越好列敲。
三阱佛、InnoDB鎖類型[3]
1. 基本鎖
基本鎖：共享鎖(Shared Locks：S鎖)與排他鎖(Exclusive Locks：X鎖)
mysql允許拿到S鎖的事務(wù)讀一行，允許拿到X鎖的事務(wù)更新或刪除一行戴而。
加了S鎖的記錄凑术，允許其他事務(wù)再加S鎖，不允許其他事務(wù)再加X鎖所意；
加了X鎖的記錄淮逊，不允許其他事務(wù)再加S鎖或者X鎖。
mysql對外提供加這兩種鎖的語法如下：
加S鎖：select…lock in share mode
加X鎖：select…for update
2. 意向鎖(Intention Locks)
InnoDB為了支持多粒度(表鎖與行鎖)的鎖并存扶踊，引入意向鎖泄鹏。
意向鎖是表級鎖，可分為意向共享鎖(IS鎖)和意向排他鎖(IX鎖)秧耗。
InnoDB supports multiple granularity locking which permits coexistence of row-level locks and locks on entire tables. To make locking at multiple granularity levels practical, additional types of locks called intention locks are used. Intention locks are table-level locks in InnoDB that indicate which type of lock (shared or exclusive) a transaction will require later for a row in that table. There are two types of intention locks used in InnoDB (assume that transaction T has requested a lock of the indicated type on table t):
Intention shared (IS): Transaction T intends to set S locks on individual rows in table t.
Intention exclusive (IX): Transaction T intends to set X locks on those rows.
事務(wù)在請求S鎖和X鎖前备籽，需要先獲得對應(yīng)的IS、IX鎖分井。
Before a transaction can acquire an S lock on a row in table t, it must first acquire an IS or stronger lock on t. Before a transaction can acquire an X lock on a row, it must first acquire an IX lock on t.
意向鎖產(chǎn)生的主要目的是為了處理行鎖和表鎖之間的沖突车猬，用于表明“某個(gè)事務(wù)正在某一行上持有了鎖，或者準(zhǔn)備去持有鎖”尺锚。
The main purpose of IX and IS locks is to show that someone is locking a row, or going to lock a row in the table.
共享鎖珠闰、排他鎖與意向鎖的兼容矩陣如下：
| | X | IX | S | IS |
| X | 沖突 | 沖突 | 沖突 | 沖突 |
| IX | 沖突 | 兼容 | 沖突 | 兼容 |
| S | 沖突 | 沖突 | 兼容 | 兼容 |
| IS | 沖突 | 兼容 | 兼容 | 兼容 |
思考
從官方文檔字面意思上看意向鎖是表級鎖，但是大牛不認(rèn)為“Intention lock 是表級鎖”[4]?
另外缩麸，由于意向鎖主要用于解決行鎖與表鎖間沖突問題铸磅，鑒于平時(shí)表級操作特別少，在分析加鎖過程是否可以不用過多考慮意向鎖的問題?
3. 行鎖
記錄鎖(Record Locks)
記錄鎖, 僅僅鎖住索引記錄的一行杭朱。
單條索引記錄上加鎖阅仔，record lock鎖住的永遠(yuǎn)是索引，而非記錄本身弧械，即使該表上沒有任何索引八酒，那么innodb會(huì)在后臺(tái)創(chuàng)建一個(gè)隱藏的聚集主鍵索引，那么鎖住的就是這個(gè)隱藏的聚集主鍵索引刃唐。所以說當(dāng)一條sql沒有走任何索引時(shí)羞迷，那么將會(huì)在每一條聚集索引后面加X鎖界轩，這個(gè)類似于表鎖，但原理上和表鎖應(yīng)該是完全不同的衔瓮。
參見官方文檔[5]：
If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.
間隙鎖(Gap Locks)
區(qū)間鎖, 僅僅鎖住一個(gè)索引區(qū)間(開區(qū)間)浊猾。
在索引記錄之間的間隙中加鎖，或者是在某一條索引記錄之前或者之后加鎖热鞍，并不包括該索引記錄本身葫慎。
next-key鎖(Next-Key Locks)
record lock + gap lock, 左開右閉區(qū)間。
A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.
By default, InnoDB operates in REPEATABLE READ transaction isolation level and with the innodb_locks_unsafe_for_binlog system variable disabled. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows薇宠。
默認(rèn)情況下偷办，innodb使用next-key locks來鎖定記錄。
但當(dāng)查詢的索引含有唯一屬性的時(shí)候澄港，Next-Key Lock 會(huì)進(jìn)行優(yōu)化椒涯，將其降級為Record Lock，即僅鎖住索引本身回梧，不是范圍废岂。
插入意向鎖(Insert Intention Locks)
Gap Lock中存在一種插入意向鎖（Insert Intention Lock），在insert操作時(shí)產(chǎn)生漂辐。在多事務(wù)同時(shí)寫入不同數(shù)據(jù)至同一索引間隙的時(shí)候泪喊，并不需要等待其他事務(wù)完成，不會(huì)發(fā)生鎖等待髓涯。
假設(shè)有一個(gè)記錄索引包含鍵值4和7，不同的事務(wù)分別插入5和6哈扮，每個(gè)事務(wù)都會(huì)產(chǎn)生一個(gè)加在4-7之間的插入意向鎖纬纪，獲取在插入行上的排它鎖，但是不會(huì)被互相鎖住滑肉，因?yàn)閿?shù)據(jù)行并不沖突包各。
An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.
注：插入意向鎖并非意向鎖，而是一種特殊的間隙鎖靶庙。
4. 行鎖的兼容矩陣[6]
| | Gap | Insert Intention | Record | Next-Key |
| Gap | 兼容 | 兼容 | 兼容 | 兼容 |
| Insert Intention | 沖突 | 兼容 | 兼容 | 沖突 |
| Record | 兼容 | 兼容 | 沖突 | 沖突 |
| Next-Key | 兼容 | 兼容 | 沖突 | 沖突 |
表注：橫向是已經(jīng)持有的鎖问畅，縱向是正在請求的鎖。
由于S鎖和S鎖是完全兼容的六荒，因此在判別兼容性時(shí)只考慮持有的鎖與請求的鎖是這三種組合情形：X护姆、S和S、X和X掏击、X卵皂。
另外，需要提醒注意的是進(jìn)行兼容判斷也只是針對于加鎖涉及的行有交集的情形砚亭。
分析兼容矩陣可以得出如下幾個(gè)結(jié)論：
- INSERT操作之間不會(huì)有沖突灯变。
- GAP,Next-Key會(huì)阻止Insert殴玛。
- GAP和Record,Next-Key不會(huì)沖突
- Record和Record、Next-Key之間相互沖突添祸。
- 已有的Insert鎖不阻止任何準(zhǔn)備加的鎖滚粟。
5. 自增鎖(AUTO-INC Locks)
AUTO-INC鎖是一種特殊的表級鎖，發(fā)生涉及AUTO_INCREMENT列的事務(wù)性插入操作時(shí)產(chǎn)生刃泌。
官方解釋如下[5]：
An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.
四坦刀、insert加鎖過程
官方文檔[7]對于insert加鎖的描述如下：
INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.
Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.
If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock.
簡單的insert會(huì)在insert的行對應(yīng)的索引記錄上加一個(gè)排它鎖，這是一個(gè)record lock蔬咬，并沒有g(shù)ap鲤遥，所以并不會(huì)阻塞其他session在gap間隙里插入記錄。
不過在insert操作之前林艘，還會(huì)加一種鎖盖奈，官方文檔稱它為insertion intention gap lock，也就是意向的gap鎖狐援。這個(gè)意向gap鎖的作用就是預(yù)示著當(dāng)多事務(wù)并發(fā)插入相同的gap空隙時(shí)钢坦，只要插入的記錄不是gap間隙中的相同位置，則無需等待其他session就可完成啥酱，這樣就使得insert操作無須加真正的gap lock爹凹。
假設(shè)有一個(gè)記錄索引包含鍵值4和7，不同的事務(wù)分別插入5和6镶殷，每個(gè)事務(wù)都會(huì)產(chǎn)生一個(gè)加在4-7之間的插入意向鎖禾酱，獲取在插入行上的排它鎖，但是不會(huì)被互相鎖住绘趋，因?yàn)閿?shù)據(jù)行并不沖突颤陶。
假設(shè)發(fā)生了一個(gè)唯一鍵沖突錯(cuò)誤，那么將會(huì)在重復(fù)的索引記錄上加讀鎖陷遮。當(dāng)有多個(gè)session同時(shí)插入相同的行記錄時(shí)滓走，如果另外一個(gè)session已經(jīng)獲得該行的排它鎖，那么將會(huì)導(dǎo)致死鎖帽馋。
思考：Insert Intention Locks作用
Insert Intention Locks的引入搅方，我理解是為了提高數(shù)據(jù)插入的并發(fā)能力。
如果沒有Insert Intention Locks的話绽族，可能就需要使用Gap Locks來代替姨涡。
五、insert死鎖場景分析
接下來项秉，帶大家看幾個(gè)與insert相關(guān)的死鎖場景绣溜。
1. duplicate key error引發(fā)的死鎖
這個(gè)場景主要發(fā)生在兩個(gè)以上的事務(wù)同時(shí)進(jìn)行唯一鍵值相同的記錄插入操作。
表結(jié)構(gòu)
|
1
2
3
4
5
6
7
8
9
|
CREATE TABLE aa (
id int(10) unsigned NOT NULL COMMENT '主鍵',
name varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
age int(11) NOT NULL DEFAULT '0' COMMENT '年齡',
stage int(11) NOT NULL DEFAULT '0' COMMENT '關(guān)卡數(shù)',
PRIMARY KEY (id),
UNIQUE KEY udx_name (name),
KEY idx_stage (stage)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
表數(shù)據(jù)
|
1
2
3
4
5
6
7
8
9
10
|
mysql> select * from aa;
+----+------+-----+-------+
| id | name | age | stage |
+----+------+-----+-------+
| 1 | yst | 11 | 8 |
| 2 | dxj | 7 | 4 |
| 3 | lb | 13 | 7 |
| 4 | zsq | 5 | 7 |
| 5 | lxr | 13 | 4 |
+----+------+-----+-------+
|
事務(wù)執(zhí)行時(shí)序表
| T1(36727) | T2(36728) | T3(36729) |
| begin; | begin; | begin; |
| insert into aa values(6, ‘test’, 12, 3); | | |
| | insert into aa values(6, ‘test’, 12, 3); | |
| | | insert into aa values(6, ‘test’, 12, 3); |
| rollback; | | |
| | | ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
| | Query OK, 1 row affected (13.10 sec) | |
如果T1未rollback娄蔼，而是commit的話怖喻，T2和T3會(huì)報(bào)唯一鍵沖突：ERROR 1062 (23000): Duplicate entry ‘6’ for key ‘PRIMARY’
事務(wù)鎖占用情況
T1 rollback前底哗，各事務(wù)鎖占用情況：
|
1
2
3
4
5
6
7
8
|
mysql> select * from information_schema.innodb_locks;
+--------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+--------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| 36729:24:3:7 | 36729 | S | RECORD | test.aa | PRIMARY | 24 | 3 | 7 | 6 |
| 36727:24:3:7 | 36727 | X | RECORD | test.aa | PRIMARY | 24 | 3 | 7 | 6 |
| 36728:24:3:7 | 36728 | S | RECORD | test.aa | PRIMARY | 24 | 3 | 7 | 6 |
+--------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
|
注：mysql有自己的一套規(guī)則來決定T2與T3哪個(gè)進(jìn)行回滾，本文不做討論锚沸。
死鎖日志
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
LATEST DETECTED DEADLOCK
2016-07-21 19:34:23 700000a3f000
*** (1) TRANSACTION:
TRANSACTION 36728, ACTIVE 199 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 13, OS thread handle 0x700000b0b000, query id 590 localhost root update
insert into aa values(6, 'test', 12, 3)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 3 n bits 80 index PRIMARY of table test.aa trx id 36728 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 36729, ACTIVE 196 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 14, OS thread handle 0x700000a3f000, query id 591 localhost root update
insert into aa values(6, 'test', 12, 3)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 24 page no 3 n bits 80 index PRIMARY of table test.aa trx id 36729 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 3 n bits 80 index PRIMARY of table test.aa trx id 36729 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (2)
|
死鎖成因
事務(wù)T1成功插入記錄跋选，并獲得索引id=6上的排他記錄鎖(LOCK_X | LOCK_REC_NOT_GAP)。
緊接著事務(wù)T2哗蜈、T3也開始插入記錄前标，請求排他插入意向鎖(LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION)；但由于發(fā)生重復(fù)唯一鍵沖突距潘，各自請求的排他記錄鎖(LOCK_X | LOCK_REC_NOT_GAP)轉(zhuǎn)成共享記錄鎖(LOCK_S | LOCK_REC_NOT_GAP)炼列。
T1回滾釋放索引id=6上的排他記錄鎖(LOCK_X | LOCK_REC_NOT_GAP)，T2和T3都要請求索引id=6上的排他記錄鎖(LOCK_X | LOCK_REC_NOT_GAP)音比。
由于X鎖與S鎖互斥俭尖，T2和T3都等待對方釋放S鎖。
于是洞翩，死鎖便產(chǎn)生了稽犁。
如果此場景下，只有兩個(gè)事務(wù)T1與T2或者T1與T3骚亿，則不會(huì)引發(fā)如上死鎖情況產(chǎn)生已亥。
思考
為什么發(fā)現(xiàn)重復(fù)主鍵沖突的時(shí)候，要將事務(wù)請求的X鎖轉(zhuǎn)成S鎖来屠？
(比較牽強(qiáng)的)個(gè)人理解虑椎，跟插入意向鎖類型，也是為了提高插入的并發(fā)效率的妖。插入前請求插入意向鎖的作用绣檬？
個(gè)人認(rèn)為，通過兼容矩陣來分析嫂粟，Insert Intention Locks是為了減少插入時(shí)的鎖沖突。
2. GAP與Insert Intention沖突引發(fā)的死鎖
表結(jié)構(gòu)
|
1
2
3
4
5
6
|
CREATE TABLE t (
a int(11) NOT NULL,
b int(11) DEFAULT NULL,
PRIMARY KEY (a),
KEY idx_b (b)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
表數(shù)據(jù)
|
1
2
3
4
5
6
7
8
9
|
mysql> select * from t;
+----+------+
| a | b |
+----+------+
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 11 | 22 |
+----+------+
|
事務(wù)執(zhí)行時(shí)序表
| T1(36831) | T2(36832) |
| begin; | begin; |
| select * from t where b = 6 for update; | |
| | select * from t where b = 8 for update; |
| insert into t values (4,5); | |
| | insert into t values (4,5); |
| | ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
| Query OK, 1 row affected (5.45 sec) | |
事務(wù)鎖占用情況
T2 insert前墨缘，各事務(wù)鎖占用情況：
|
1
2
3
4
5
6
7
|
mysql> select * from information_schema.innodb_locks;
+--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| 36831:25:4:5 | 36831 | X,GAP | RECORD | test.t | idx_b | 25 | 4 | 5 | 22, 11 |
| 36832:25:4:5 | 36832 | X,GAP | RECORD | test.t | idx_b | 25 | 4 | 5 | 22, 11 |
+--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
|
死鎖日志
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
LATEST DETECTED DEADLOCK
2016-07-28 12:28:34 700000a3f000
*** (1) TRANSACTION:
TRANSACTION 36831, ACTIVE 17 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 38, OS thread handle 0x700000b0b000, query id 953 localhost root update
insert into t values (4,5)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 25 page no 4 n bits 72 index idx_b of table test.t trx id 36831 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000016; asc ;;
1: len 4; hex 8000000b; asc ;;
*** (2) TRANSACTION:
TRANSACTION 36832, ACTIVE 13 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 360, 2 row lock(s)
MySQL thread id 39, OS thread handle 0x700000a3f000, query id 954 localhost root update
insert into t values (4,5)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 25 page no 4 n bits 72 index idx_b of table test.t trx id 36832 lock_mode X locks gap before rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000016; asc ;;
1: len 4; hex 8000000b; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 25 page no 3 n bits 72 index PRIMARY of table test.t trx id 36832 lock mode S locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000004; asc ;;
1: len 6; hex 000000008fdf; asc ;;
2: len 7; hex 8d000001d00110; asc ;;
3: len 4; hex 80000005; asc ;;
*** WE ROLL BACK TRANSACTION (2)
|
死鎖成因
事務(wù)T1執(zhí)行查詢語句星虹，在索引b=6上加排他Next-key鎖(LOCK_X | LOCK_ORDINARY)，會(huì)鎖住idx_b索引范圍(4, 22)镊讼。
事務(wù)T2執(zhí)行查詢語句宽涌，在索引b=8上加排他Next-key鎖(LOCK_X | LOCK_ORDINARY)，會(huì)鎖住idx_b索引范圍(4, 22)蝶棋。由于請求的GAP與已持有的GAP是兼容的卸亮，因此，事務(wù)T2在idx_b索引范圍(4, 22)也能加鎖成功玩裙。
事務(wù)T1執(zhí)行插入語句兼贸，會(huì)先加排他Insert Intention鎖段直。由于請求的Insert Intention鎖與已有的GAP鎖不兼容，則事務(wù)T1等待T2釋放GAP鎖溶诞。
事務(wù)T2執(zhí)行插入語句鸯檬，也會(huì)等待T1釋放GAP鎖。
于是螺垢，死鎖便產(chǎn)生了喧务。
注：LOCK_ORDINARY擁有LOCK_GAP一部分特性。
思考：Insert Intention鎖在加哪級索引上枉圃？
這個(gè)排他鎖加在PK上功茴，還是二級索引上？
六孽亲、課后思考
無主鍵的加鎖過程
無PK時(shí)坎穿，會(huì)創(chuàng)建一個(gè)隱式聚簇索引。加鎖在這個(gè)隱式聚簇索引會(huì)有什么不同墨林？復(fù)合索引加鎖過程
多條件(where condition)加鎖過程
隱式鎖與顯式鎖赁酝，隱式鎖什么情況下會(huì)轉(zhuǎn)換成顯式鎖
如果插入意向鎖不阻止任何鎖，這個(gè)鎖還有必要存在嗎旭等？
目前看到的作用是酌呆，通過加鎖的方式來喚醒等待線程。
但這并不意味著搔耕，被喚醒后可以直接做插入操作了隙袁。需要再次判斷是否有鎖沖突。
七弃榨、補(bǔ)充知識(shí)
1. 查看事務(wù)隔離級別
SELECT @@global.tx_isolation;
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;
2. 設(shè)置隔離級別
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
例如：set session transaction isolation level read uncommitted;
3. 查看auto_increment機(jī)制模式
show variables like ‘innodb_autoinc_lock_mode’;
4. 查看表狀態(tài)
show table status like ‘plan_branch’\G;
show table status from test like ‘plan_branch’\G;
5. 查看SQL性能
show profiles
show profile for query 1;
6. 查看當(dāng)前最新事務(wù)ID
每開啟一個(gè)新事務(wù)菩收，記錄當(dāng)前最新事務(wù)的id，可用于后續(xù)死鎖分析鲸睛。
show engine innodb status\G;
7. 查看事務(wù)鎖等待狀態(tài)情況
select *from information_schema.innodb_locks;
select *from information_schema.innodb_lock_waits;
select * from information_schema.innodb_trx;
8. 查看innodb狀態(tài)(包含最近的死鎖日志)
show engine innodb status;
