目錄
一，關于MySQL的死鎖
二，人造一個死鎖的場景
三，檢視最近一次死鎖的日志
四，死鎖日志的内容
1，事務1資訊
2，事務1持有的鎖
3，事務1正在等待的鎖
4，事務2資訊
5，事務2正在持有的鎖
6，事務2正在等待的鎖
7，死鎖處理結果
五，關于mysql的八種鎖
1，行鎖（Record Locks）
2，間隙鎖（Gap Locks）
3，臨鍵鎖（Next-key Locks）
4，共享鎖/排他鎖（Shared and Exclusive Locks）
5，意向共享鎖/意向排他鎖（Intention Shared and Exclusive Locks）
6，插入意向鎖（Insert Intention Locks）
7，自增鎖（Auto-inc Locks）
六，關于死鎖的解鎖
一，關于MySQL的死鎖
MySQL的死鎖指的是兩個事務互相等待的場景，這種循環等待理論上不會有盡頭。
比如事務A持有行1的鎖，事務B持有行2的鎖，
然後事務A試圖擷取行2的鎖，事務B試圖擷取行1的鎖，
這樣事務A要等待事務B釋放行2的鎖，事務B要等待事務A釋放行1的鎖，
兩個事務互相等待，誰也送出不了。
這種情況下MySQL會選擇中斷并復原其中一個事務，使得另一個事務可以送出。
MySQL會記錄死鎖的日志。
二，人造一個死鎖的場景
建立一個表，添加兩條資料：
建立兩個事務，事務執行的sql分别是：
事務A：
set autocommit=0;
update medicine_control set current_count=1 where id='1';
update medicine_control set current_count=1 where id='2';
COMMIT;
事務B：
set autocommit=0;
update medicine_control set current_count=2 where id='2';
update medicine_control set current_count=2 where id='1';
COMMIT;
可見，事務A先改id=1的資料再改id=2的資料，事務B相反，先改id=2的資料再改id=1的資料。
兩個事務sql的執行順序如下：
| 步驟 | 事務A | 事務A |
| 1 | set autocommit=0; | |
| 2 | update medicine_control set current_count=1 where id='1'; | |
| 3 | set autocommit=0; | |
| 4 | update medicine_control set current_count=2 where id='2'; | |
| 5 | update medicine_control set current_count=1 where id='2'; | |
| 6 | update medicine_control set current_count=2 where id='1'; |
對每一步的說明：
1，事務A開始事務。
2，事務A修改id=1的資料，持有了該行的鎖。
3，事務B開始事務。
4，事務B修改id=2的資料，持有了該行的鎖。
5，事務A試圖修改id=2的資料，此行的鎖被事務B持有，于是事務A等待事務B釋放鎖。
事務B送出或復原都能釋放鎖。
6，事務B試圖修改id=1的資料，此行的鎖被事務A持有，于是事務B等待事務A釋放鎖。
事務A送出或復原都能釋放鎖。當執行到這一步時，MySQL會立即檢測到死鎖，并且中斷并復原其中一個事務。此次復原的是事務B，執行SQL的傳回資訊是這樣的：
[SQL]update medicine_control set current_count=2 where id='1';
[Err] 1213 - Deadlock found when trying to get lock; try restarting transaction
三，檢視最近一次死鎖的日志
執行sql指令：
SHOW ENGINE INNODB STATUS;
執行結果如下：
其中的status字段裡包含了最近一次死鎖的日志。
四，死鎖日志的内容
上面制造的死鎖，其死鎖日志的内容是這樣的：
=====================================
2020-09-15 14:46:28 0x7f732fcff700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 37 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 609 srv_active, 0 srv_shutdown, 23969851 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 100
OS WAIT ARRAY INFO: signal count 98
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 29, rounds 870, OS waits 25
RW-sx spins 1, rounds 30, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 30.00 RW-excl, 30.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-09-15 14:46:15 0x7f7350cf3700
*** (1) TRANSACTION:
TRANSACTION 10298, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 7623, OS thread handle 140132789073664, query id 6006191 127.0.0.1 root updating
update medicine_control set current_count=1 where id='2'
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 55 page no 4 n bits 88 index PRIMARY of table `jeecg-boot`.`medicine_control` trx id 10298 lock_mode X locks rec but not gap
Record lock, heap no 21 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
0: len 1; hex 31; asc 1;;
1: len 6; hex 00000000283a; asc (:;;
2: len 7; hex 020000012510db; asc % ;;
3: len 6; hex e5a5b6e5a5b6; asc ;;
4: len 12; hex e79b98e5b0bce8a5bfe69e97; asc ;;
5: len 4; hex 80000001; asc ;;
6: len 4; hex 80000005; asc ;;
7: len 4; hex 80000000; asc ;;
8: len 5; hex 6a65656367; asc jeecg;;
9: len 5; hex 99a60eadf7; asc ;;
10: len 3; hex 6a6f62; asc job;;
11: len 5; hex 99a75e0780; asc ^ ;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 55 page no 4 n bits 88 index PRIMARY of table `jeecg-boot`.`medicine_control` trx id 10298 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
0: len 1; hex 32; asc 2;;
1: len 6; hex 00000000283b; asc (;;;
2: len 7; hex 01000002012bd8; asc + ;;
3: len 6; hex e788b7e788b7; asc ;;
4: len 6; hex e69f90e69f90; asc ;;
5: len 4; hex 80000002; asc ;;
6: len 4; hex 80000002; asc ;;
7: len 4; hex 80000000; asc ;;
8: len 5; hex 6c6979616e; asc liyan;;
9: len 5; hex 99a67b3730; asc {70;;
10: len 3; hex 6a6f62; asc job;;
11: len 5; hex 99a75e0780; asc ^ ;;
*** (2) TRANSACTION:
TRANSACTION 10299, ACTIVE 7 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 7625, OS thread handle 140133576603392, query id 6006195 127.0.0.1 root updating
update medicine_control set current_count=2 where id='1'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 55 page no 4 n bits 88 index PRIMARY of table `jeecg-boot`.`medicine_control` trx id 10299 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
0: len 1; hex 32; asc 2;;
1: len 6; hex 00000000283b; asc (;;;
2: len 7; hex 01000002012bd8; asc + ;;
3: len 6; hex e788b7e788b7; asc ;;
4: len 6; hex e69f90e69f90; asc ;;
5: len 4; hex 80000002; asc ;;
6: len 4; hex 80000002; asc ;;
7: len 4; hex 80000000; asc ;;
8: len 5; hex 6c6979616e; asc liyan;;
9: len 5; hex 99a67b3730; asc {70;;
10: len 3; hex 6a6f62; asc job;;
11: len 5; hex 99a75e0780; asc ^ ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 55 page no 4 n bits 88 index PRIMARY of table `jeecg-boot`.`medicine_control` trx id 10299 lock_mode X locks rec but not gap waiting
Record lock, heap no 21 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
0: len 1; hex 31; asc 1;;
1: len 6; hex 00000000283a; asc (:;;
2: len 7; hex 020000012510db; asc % ;;
3: len 6; hex e5a5b6e5a5b6; asc ;;
4: len 12; hex e79b98e5b0bce8a5bfe69e97; asc ;;
5: len 4; hex 80000001; asc ;;
6: len 4; hex 80000005; asc ;;
7: len 4; hex 80000000; asc ;;
8: len 5; hex 6a65656367; asc jeecg;;
9: len 5; hex 99a60eadf7; asc ;;
10: len 3; hex 6a6f62; asc job;;
11: len 5; hex 99a75e0780; asc ^ ;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 10301
Purge done for trx's n:o < 10301 undo n:o < 0 state: running but idle
History list length 61
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421608706154464, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706153592, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706152720, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706151848, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706150976, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706150104, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706148360, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706147488, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706146616, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706145744, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706144872, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706144000, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 10298, ACTIVE 24 sec
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
MySQL thread id 7623, OS thread handle 140132789073664, query id 6006198 127.0.0.1 root
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
2048 OS file reads, 24777 OS file writes, 11472 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.59 writes/s, 0.54 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 5 buffer(s)
0.00 hash searches/s, 0.27 non-hash searches/s
---
LOG
---
Log sequence number 2246453180
Log buffer assigned up to 2246453180
Log buffer completed up to 2246453180
Log written up to 2246453180
Log flushed up to 2246453180
Added dirty pages up to 2246453180
Pages flushed up to 2246453180
Last checkpoint at 2246453180
9242 log i/o's done, 0.14 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456
Dictionary memory allocated 835752
Buffer pool size 8192
Free buffers 6046
Database pages 2131
Old database pages 788
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1923, created 208, written 13739
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2131, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=920, Main thread ID=140133220153088 , state=sleeping
Number of rows inserted 416, updated 2599, deleted 440, read 821958
0.00 inserts/s, 0.08 updates/s, 0.00 deletes/s, 0.11 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
其中：
=====================================
2020-09-15 14:46:28 0x7f732fcff700 INNODB MONITOR OUTPUT
=====================================
這段記錄的是查詢死鎖日志的時間
------------------------
LATEST DETECTED DEADLOCK
------------------------
這段後面記錄的就是此次死鎖的資訊，分為幾部分
1，事務1資訊
也就是這一部分：
*** (1) TRANSACTION:
TRANSACTION 10298, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 7623, OS thread handle 140132789073664, query id 6006191 127.0.0.1 root updating
update medicine_control set current_count=1 where id='2'
其中：
TRANSACTION 10298，是此事務的id。
ACTIVE 11 sec，活躍時間11秒。
starting index read，事務目前正在根據索引讀取資料。
starting index read這個描述還有其他情況：
- fetching rows 表示事務狀态在row_search_for_mysql中被設定，表示正在查找記錄。
- updating or deleting 表示事務已經真正進入了Update/delete的函數邏輯（row_update_for_mysql）
- thread declared inside InnoDB 說明事務已經進入innodb層。通常而言 不在innodb層的事務大部分是會被復原的。
mysql tables in use 1, locked 1，表示此事務修改了一個表，鎖了一行資料。
MySQL thread id 7623，這是線程id
query id 6006191，這是查詢id
127.0.0.1 root updating，資料庫ip位址，賬号，更新語句。
update medicine_control set current_count=1 where id='2'，這是正在執行的sql。
2，事務1持有的鎖
也就是這段：
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 55 page no 4 n bits 88 index PRIMARY of table `jeecg-boot`.`medicine_control` trx id 10298 lock_mode X locks rec but not gap
Record lock, heap no 21 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
0: len 1; hex 31; asc 1;;
1: len 6; hex 00000000283a; asc (:;;
2: len 7; hex 020000012510db; asc % ;;
3: len 6; hex e5a5b6e5a5b6; asc ;;
4: len 12; hex e79b98e5b0bce8a5bfe69e97; asc ;;
5: len 4; hex 80000001; asc ;;
6: len 4; hex 80000005; asc ;;
7: len 4; hex 80000000; asc ;;
8: len 5; hex 6a65656367; asc jeecg;;
9: len 5; hex 99a60eadf7; asc ;;
10: len 3; hex 6a6f62; asc job;;
11: len 5; hex 99a75e0780; asc ^ ;;
其中：
RECORD LOCKS，表示持有的是行級鎖。
index PRIMARY，表示鎖的是主鍵索引。
table `jeecg-boot`.`medicine_control`，表示鎖的具體是哪個表。
trx id 10298，事務id，和上面的TRANSACTION相同。
lock_mode X locks，鎖模式：排它鎖。（X：排他鎖，S：共享鎖）
but not gap，非間隙鎖
後面的0至11，代表鎖的具體哪一行，0至11指的是表的第1至第12個字段，0開頭的這行表示id列，可見鎖的是id=1的那一行，可知這裡的事務1就是上面的事務A。
3，事務1正在等待的鎖
也就是這段：
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 55 page no 4 n bits 88 index PRIMARY of table `jeecg-boot`.`medicine_control` trx id 10298 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
0: len 1; hex 32; asc 2;;
1: len 6; hex 00000000283b; asc (;;;
2: len 7; hex 01000002012bd8; asc + ;;
3: len 6; hex e788b7e788b7; asc ;;
4: len 6; hex e69f90e69f90; asc ;;
5: len 4; hex 80000002; asc ;;
6: len 4; hex 80000002; asc ;;
7: len 4; hex 80000000; asc ;;
8: len 5; hex 6c6979616e; asc liyan;;
9: len 5; hex 99a67b3730; asc {70;;
10: len 3; hex 6a6f62; asc job;;
11: len 5; hex 99a75e0780; asc ^ ;;
其中：
index PRIMARY，表示等待的是主鍵的鎖。
table `jeecg-boot`.`medicine_control`，表示等待的表。
trx id 10298，目前事務1的id。注意這裡不是持有目标鎖的事務的id，而是目前事務id。
lock_mode X locks，表示目标鎖是排它鎖。
but not gap，表示非間隙鎖。
waiting，表示目前事務正在等待。
後面的0至11，表示等待的行，可見等待的是id=2的行的鎖。
4，事務2資訊
也就是這一段：
*** (2) TRANSACTION:
TRANSACTION 10299, ACTIVE 7 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 7625, OS thread handle 140133576603392, query id 6006195 127.0.0.1 root updating
update medicine_control set current_count=2 where id='1'
格式和事務1資訊相同。
TRANSACTION 10299，表示事務id是10299。
update medicine_control set current_count=2 where id='1'，表示事務2正在執行的sql。
5，事務2正在持有的鎖
也就是這段：
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 55 page no 4 n bits 88 index PRIMARY of table `jeecg-boot`.`medicine_control` trx id 10299 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
0: len 1; hex 32; asc 2;;
1: len 6; hex 00000000283b; asc (;;;
2: len 7; hex 01000002012bd8; asc + ;;
3: len 6; hex e788b7e788b7; asc ;;
4: len 6; hex e69f90e69f90; asc ;;
5: len 4; hex 80000002; asc ;;
6: len 4; hex 80000002; asc ;;
7: len 4; hex 80000000; asc ;;
8: len 5; hex 6c6979616e; asc liyan;;
9: len 5; hex 99a67b3730; asc {70;;
10: len 3; hex 6a6f62; asc job;;
11: len 5; hex 99a75e0780; asc ^ ;;
可見事務2持有id=2的行鎖，也就是說這裡的事務2就是上面的事務B。
6，事務2正在等待的鎖
也就是這段：
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 55 page no 4 n bits 88 index PRIMARY of table `jeecg-boot`.`medicine_control` trx id 10299 lock_mode X locks rec but not gap waiting
Record lock, heap no 21 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
0: len 1; hex 31; asc 1;;
1: len 6; hex 00000000283a; asc (:;;
2: len 7; hex 020000012510db; asc % ;;
3: len 6; hex e5a5b6e5a5b6; asc ;;
4: len 12; hex e79b98e5b0bce8a5bfe69e97; asc ;;
5: len 4; hex 80000001; asc ;;
6: len 4; hex 80000005; asc ;;
7: len 4; hex 80000000; asc ;;
8: len 5; hex 6a65656367; asc jeecg;;
9: len 5; hex 99a60eadf7; asc ;;
10: len 3; hex 6a6f62; asc job;;
11: len 5; hex 99a75e0780; asc ^ ;;
可見事務2正在等待id=1的行鎖。
7，死鎖處理結果
也就是這段：
*** WE ROLL BACK TRANSACTION (2)
表示MySQL最終決定復原事務2，也就是上面的事務B，這和上面事務B傳回的死鎖資訊是一緻的。
另外，日志裡還記錄的目前SESSION和事務清單，也就是這段：
------------
TRANSACTIONS
------------
Trx id counter 10301
Purge done for trx's n:o < 10301 undo n:o < 0 state: running but idle
History list length 61
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421608706154464, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706153592, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706152720, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706151848, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706150976, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706150104, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706148360, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706147488, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706146616, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706145744, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706144872, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706144000, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 10298, ACTIVE 24 sec
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
MySQL thread id 7623, OS thread handle 140132789073664, query id 6006198 127.0.0.1 root
可見多數的SESSION下的事務都沒開始，注意最後的這段：
--- TRANSACTION 10298, ACTIVE 24 sec
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
表示id為10298的事務（也就是事務1）還沒送出。
五，關于mysql的八種鎖
1，行鎖（Record Locks）
行鎖是作用在索引上的。
2，間隙鎖（Gap Locks）
間隙鎖是鎖住一個區間的鎖。
這個區間是一個開區間，範圍是從某個存在的值向左直到比他小的第一個存在的值，是以間隙鎖包含的内容就是在查詢範圍内，而又不存在的資料區間。
比如有id分别是1,10,20，要修改id<15的資料，那麼生成的間隙鎖有以下這些：(-∞,1)，(1,10)，(10,20)，此時若有其他事務想要插入id=11的資料，則需要等待。
間隙鎖是不互斥的。
作用是防止其他事務在區間内添加記錄，而本事務可以在區間内添加記錄，進而防止幻讀。
在可重複讀這種隔離級别下會啟用間隙鎖，而在讀未送出和讀已送出兩種隔離級别下，即使使用select ... in share mode或select ... for update，也不會有間隙鎖，無法防止幻讀。
3，臨鍵鎖（Next-key Locks）
臨鍵鎖=間隙鎖+行鎖，于是臨鍵鎖的區域是一個左開右閉的區間。
隔離級别是可重複讀時，select ... in share mode或select ... for update會使用臨鍵鎖，防止幻讀。普通select語句是快照讀，不能防止幻讀。
4，共享鎖/排他鎖（Shared and Exclusive Locks）
共享鎖和排它鎖都是行鎖。共享鎖用于事務并發讀取，比如select ... in share mode。排它鎖用于事務并發更新或删除。比如select ... for update
5，意向共享鎖/意向排他鎖（Intention Shared and Exclusive Locks）
意向共享鎖和意向排他鎖都是表級鎖。
官方文檔中說，事務獲得共享鎖前要先獲得意向共享鎖，獲得排它鎖前要先獲得意向排它鎖。
意向排它鎖互相之間是相容的。
6，插入意向鎖（Insert Intention Locks）
插入意向鎖鎖的是一個點，是一種特殊的間隙鎖，用于并發插入。
插入意向鎖和間隙鎖互斥。插入意向鎖互相不互斥。
7，自增鎖（Auto-inc Locks）
自增鎖用于事務中插入自增字段。5.1版本前是表鎖，5.1及以後版本是互斥輕量鎖。
自增所相關的變量有：
auto_increment_offset，初始值
auto_increment_increment，每次增加的數量
innodb_autoinc_lock_mode，自增鎖模式
其中：
innodb_autoinc_lock_mode=0，傳統方式，每次都産生表鎖。此為5.1版本前的預設配置。
innodb_autoinc_lock_mode=1，連續方式。産生輕量鎖，申請到自增鎖就将鎖釋放，simple insert會獲得批量的鎖，保證連續插入。此為5.2版本後的預設配置。
innodb_autoinc_lock_mode=2，交錯鎖定方式。不鎖表，并發速度最快。但最終産生的序列号和執行的先後順序可能不一緻，也可能斷裂。
六，關于死鎖的解鎖
InnoDB存儲引擎會選擇復原undo量最小的事務
本文完