携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第4天，点击查看活动详情
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction 问题
innoDB 中的锁
两大类：共享锁（S锁）和排它锁（X锁）
| X | S | |
|---|---|---|
| X | 不兼容 | 不兼容 |
| S | 不兼容 | 兼容 |
可以通过information_schema.INNODB_TRX, information_schema.INNODB_LOCKS, information.INNODB_LOCK_WAITS三张表来分析当前数据库中事务加锁的情况。
查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
- Select … for update 相当于加了一个X锁
- Select … lock in share mode 相当于加了一个S锁
插入意向锁（Insert Intention Locks）
插入意向锁是一个间隙锁，表示需要在该间隙插入记录。插入意向锁之间互相不排斥，例如在同一个间隙并发插入记录（这里假设只有主键索引一个唯一索引的情况）只要记录主键不冲突是可以并发插入成功的。但是插入意向锁与其余的间隙锁（互斥锁以及共享锁）是冲突的，这也是RR隔离级别下控制范围内不能新增记录的机制。dev.mysql.com/doc/refman/… 插入意向锁判断逻辑，可以看出插入意向锁是一个带LOCK_INSERT_INTENTION标记的间隙锁LOCK_GAP写锁LOCK_X.
/* If another transaction has an explicit lock request which locks
the gap, waiting or granted, on the successor, the insert has to wait.
An exception is the case where the lock by the another transaction
is a gap type lock which it placed to wait for its turn to insert. We
do not consider that kind of a lock conflicting with our insert. This
eliminates an unnecessary deadlock which resulted when 2 transactions
had to wait for their insert. Both had waiting gap type lock requests
on the successor, which produced an unnecessary deadlock. */
if (lock_rec_other_has_conflicting(
static_cast<enum lock_mode>(
LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION),
block, next_rec_heap_no, trx)) {
/* Note that we may get DB_SUCCESS also here! */
trx_mutex_enter(trx);
err = lock_rec_enqueue_waiting(
LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION,
block, next_rec_heap_no, index, thr);
trx_mutex_exit(trx);
} else {
err = DB_SUCCESS;
}
锁兼容性
其实mysql源码中关于各种锁的兼容性描述是比较清楚的，而且也比较权威，这里引用mysql 5.6版本关于锁兼容性的代码
if ((lock_is_on_supremum || (type_mode & LOCK_GAP))
&& !(type_mode & LOCK_INSERT_INTENTION)) {
/* Gap type locks without LOCK_INSERT_INTENTION flag
do not need to wait for anything. This is because
different users can have conflicting lock types
on gaps. */
return(FALSE);
}
if (!(type_mode & LOCK_INSERT_INTENTION)
&& lock_rec_get_gap(lock2)) {
/* Record lock (LOCK_ORDINARY or LOCK_REC_NOT_GAP
does not need to wait for a gap type lock */
return(FALSE);
}
if ((type_mode & LOCK_GAP)
&& lock_rec_get_rec_not_gap(lock2)) {
/* Lock on gap does not need to wait for
a LOCK_REC_NOT_GAP type lock */
return(FALSE);
}
if (lock_rec_get_insert_intention(lock2)) {
/* No lock request needs to wait for an insert
intention lock to be removed. This is ok since our
rules allow conflicting locks on gaps. This eliminates
a spurious deadlock caused by a next-key lock waiting
for an insert intention lock; when the insert
intention lock was granted, the insert deadlocked on
the waiting next-key lock.
Also, insert intention locks do not disturb each
other. */
return(FALSE);
}
- 不带插入意向锁标记的间隙锁（不包括next锁）不受任何锁阻挡
- 间隙锁（不包括next锁）只阻挡插入意向锁
- 行锁不阻挡间隙锁（不包括next锁）
- 插入意向锁不阻挡任何锁（由于第一个case已经对间隙锁做过特殊处理，与第一条结论不冲突）
case1
建一个表
create table t (a int, b int, PRIMARY KEY(a), Key(b))
-
其中a为主键索引，b为辅助索引，向表中插入几条记录
(1, 2), (2, 3), (3, 5), (4, 7)。开启一个事务并执行select * from t where b = 5 for update并不提交事务。此时主键索引a=3的记录会被加一个X锁，辅助索引b=5，所以b的区间(3, 5]（Next-Key Lock）会加一个X锁，注意b=5后面的开区间(5,7)也会加一个间隙锁，所以在事务不提交的情况下，执行insert into t select 5, 4会被阻塞，因为b=4在区间(3, 5]内，而执行insert into t select 6, 6;也会被阻塞，因为b=6在Next-Key Lock后面的间隙锁(5, 7)中。 -
Next-Key Lock主要是为了在READ COMMITED隔离级别下解决幻读的问题。从上面的例子来看，如果事务中不对数据行前后两个开区间加锁，那么会发生下面的问题：事务A开启事务并执行
select * from t where b > 5 for update不提交，返回 (4, 7)这行数据，此时另一个事务B执行insert into t select 5, 6并结束事务，再执行一次select * from t where b > 5 for update，此时会返回(5,6), (4, 7)这两行数据，这就造成了在同一个事务中，执行同一个select语句缺有不一样的结果
case2
建立表语句
CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`value` int(10) unsigned NOT NULL,
`idxvalue` int(10) unsigned NOT NULL,
`ukvalue` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_value` (`idxvalue`),
UNIQUE KEY `uk_value` (`ukvalue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
插入一条数据
insert into test (`value`, `idxvalue`, `ukvalue`) values (3, 6, 5);
发现事物2 报错：
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
左边事务1上有个锁(5,8],右边事务2， 数据插入尝试获取插入意向锁，导致阻塞，事务2在等待事务1释放插入意向锁，事务1 又插入了一条，需要获取插入意向锁，等待事务2释放意向锁，这样就形成了死锁。
