一,关于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量最小的事务
本文完