数据库死锁分析(行锁、间隙锁)

线程1

线程2

......

update t1 set status =0 where rule=1

insert  t1 () values (),(),();

update t1 set status =0 where rule=2

insert  t1 () values (),(),();

......

-- 准备测试表

CREATE TABLE IF NOT EXISTS `test` (

  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT "主键",

  `col` int(11)  NULL COMMENT  "普通字段",

  `idx_col` int(11)  NULL COMMENT "索引字段",

  `uni_col` tinyint(4)  NULL COMMENT "唯一键字段",

  PRIMARY KEY (`id`),

  KEY `idx_1` (`idx_col`) USING BTREE,

  unique KEY `uni_idx_1` (`uni_col`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=0 ;

-- 插入测试数据

INSERT INTO `test` ( `col`,`idx_col`,`uni_col`) VALUES

(3,3,3),

(10,10,10),

(11,11,11),

(20,20,20),

(25,25,25),

(26,26,26),

(50,50,50)

;

-- 清空数据表

DELETE FROM test ;

DROP table test;

-- 查询测试表数据

SELECT * FROM test;

事务1

事务2

-- 开始事务1

BEGIN;

-- 开始事务2

BEGIN;

-- 更新test，使用索引字段

UPDATE test set col=99 WHERE idx_col=20;

-- 插入数据,因为有间隙锁，[11-25）这个区间全部被锁上了，插入被阻塞

INSERT INTO `test` ( `idx_col`) VALUES (11);

INSERT INTO `test` ( `idx_col`) VALUES (12);

INSERT INTO `test` ( `idx_col`) VALUES (20);

INSERT INTO `test` ( `idx_col`) VALUES (24);

在间隙之外的可以顺利插入

INSERT INTO `test` ( `idx_col`) VALUES (10);
INSERT INTO `test` ( `idx_col`) VALUES (25);
INSERT INTO `test` ( `idx_col`) VALUES (26);

事务1

事务2

-- 开始事务1

BEGIN;

-- 开始事务2

BEGIN;

-- 更新test，使用索引字段，锁间隙[11,25)

UPDATE test set col=99 WHERE idx_col=20;

-- 更新test，使用索引字段，锁间隙[20,26)

UPDATE test set col=99 WHERE idx_col=25;

-- 使用了事务2的间隙锁，所以阻塞
INSERT INTO `test` ( `idx_col`) VALUES (21);

-- 使用了事务1的间隙锁，阻塞，互相需要对方的锁，导致死锁
-- Deadlock found when trying to get lock; try restarting transaction

INSERT INTO `test` ( `idx_col`) VALUES (12);

-- 该事务被回滚，事务1提交成功。

事务1

事务2

-- 开始事务1

BEGIN;

-- 开始事务2

BEGIN;

-- 更新test，使用唯一键或主键无间隙锁

UPDATE test set col=99 WHERE uni_col=20;
或
UPDATE test set col=99 WHERE uni_col=20 AND col=20;

-- 更新test，使用唯一键或主键无间隙锁

UPDATE test set col=99 WHERE uni_col=25;

或
UPDATE test set col=99 WHERE uni_col=25 AND col=25;

-- 无间隙锁，顺利执行
INSERT INTO `test` ( `uni_col`) VALUES (21);

或
INSERT INTO `test` ( `uni_col`,`col`) VALUES (21,21);

-- 无间隙锁，顺利执行

INSERT INTO `test` ( `uni_col`) VALUES (12);

或
INSERT INTO `test` ( `uni_col`,`col`) VALUES (12,12);

事务1

事务2

-- 开始事务1

BEGIN;

-- 开始事务2

BEGIN;

-- 更新test

UPDATE test set col=99 WHERE  col=20;

-- 以下语句全部阻塞
UPDATE test set col=99 WHERE  col=25;

或
INSERT INTO `test` ( `uni_col`,`col`) VALUES (100,100);

线程1

线程2

......

xx = select id from t1 where rule=1
update t1 set status =0 where id in(xx)

insert  t1 () values (),(),();

xx = select id from t1 where rule=1
update t1 set status =0 where id in(xx)

insert  t1 () values (),(),();

......

id列是主键，RC或RR隔离级别

只有id=10记录上有行锁

id列是二级唯一索引，RC或RR隔离级别

id列是二级非唯一索引RC级别

id列是二级非唯一索引RR级别

在RR隔离级别下，为了防止幻读的发生，会使用Gap锁。

这里，你可以把Gap锁理解为，不允许在数据记录前面插入数据。首先，通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。

直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，

id上没有索引，RC级别

若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。

但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。

同时，优化也违背了2PL的约束（同时加锁同时放锁）

id上没有索引，RR隔离级别

聚簇索引上的所有记录，都被加上了X锁。其次，聚簇索引每条记录间的间隙(GAP)，也同时被加上了GAP锁。

MySQL是做了相关的优化的，就是所谓的semi-consistent read。semi-consistent read开启的情况下，对于不满足查询条件的记录，MySQL会提前放锁，同时也不会添加Gap锁。

实例：
delete from t1 where pubtime>1 and pubtime<20 and userid="hdc" and comment is not null

在RR隔离级别下，针对一个复杂的SQL，首先需要提取其where条件。

Index Key确定的范围，需要加上GAP锁；Index Filter过滤条件，视MySQL版本是否支持ICP，若支持ICP(index condition pushdown ,mysql 5.6)，则不满足Index Filter的记录，不加X锁，否则需要X锁；

Table Filter过滤条件，无论是否满足，都需要加X锁。