记录select for update mysql 死锁问题

java 同时被 2 个专栏收录
38 篇文章 0 订阅
7 篇文章 0 订阅

背景

项目要求某表单个列自增,但是where条件,并不是这个列所有的值都符合自增

想用select for update mysql 锁表后,查询最大值自增,后发现有死锁问题,先记录一下。

 tag_id自增

自增sql where条件

原版sql如下

INSERT INTO  表一(
            column1, 
            column2, 
            column3, 
            column4,
            age
        )SELECT
            column1, 
            column2, 
            column3, 
            column4,
            (select IFNULL((select max(age) from 表名,0)+1);
INSERT INTO  place_tag(
            `tagkey_id`, 
            `tagkey_name`, 
             `tag_id`, 
            `tag`,
            `status`,
            `tag_type`,
            `type_name`
        )SELECT
           `tagkey_id`, 
            `tagkey_name`, 
             ((SELECT IFNULL((SELECT max(tag_id) FROM place_tag,0)+1, 
            `tag`,
            `status`,
            `tag_type`,
            `type_name`
            ) WHERE `tagkey_id` = '119001' AND STATUS != 3;

实例

以下代码在可重复读隔离级别下执行,表中原来只有1~10的rank。假如A、B两个事务同时试图插入rank为21和22的rank;

@Transactional
    public void createBannerDeadlock(Banner banner) {
        banner.recordCreate();
        List<Banner> rankList = cscCenterBannerDAO.selectForUpdate(banner.getRank());
        // 无记录,A占据间隙锁rank(10,+), B占据间隙锁 (10,+),gap锁之间不互斥
        if (!rankList.isEmpty()) {
            throw new RuntimeException("记录已存在");
        }
        try {
            logger.info("睡5秒钟");
            Thread.sleep(MILLIS);
        } catch (InterruptedException e) {
            throw new RuntimeException("中断");
        }
        logger.info("开始插入: {}", System.currentTimeMillis());
        cscCenterBannerDAO.insert(banner); // 都申请插入意向锁, 都需要等待别人的间隙锁释放;死锁
    }

1.如果rank上没索引:
两个事务第一个for update就会互相阻塞(锁全表),串行执行,没有死锁,两个插入都成功;
2.如果rank上有索引(普通索引或者唯一索引):
两个事务第一个for update不会互相阻塞(锁区间10~正无穷),并行执行,有死锁,到最后真正插入的时候,两者都需要插入意向锁,然后都等待对方的gap锁释放,死锁;等待很久以后,一个成功一个失败(一个事务被回滚);

死锁异常:

Deadlock found when trying to get lock; try restarting transaction;

原理

两个原因导致了有索引的时候反而会死锁:
1.select for update的间隙锁之间不互斥,两个事务都能获得gap锁;
2.插入意向锁需要等待gap锁释放;

间隙锁相关: http://xiaoyue26.github.io/2018/05/26/2018-05/MVCC%E4%B8%8E%E9%97%B4%E9%9A%99%E9%94%81-mysql%E6%8B%BE%E9%81%97/
意向锁相关: http://xiaoyue26.github.io/2018/12/24/2018-12/mysql%E6%84%8F%E5%90%91%E9%94%81/

select for update的结果

即使是同样的语句,也有可能有不同的加锁结果。
1.如果有唯一索引,命中了唯一记录: 行锁,互斥锁;
2.如果有唯一索引,没命中: gap锁,另一个事务也可以获得这个gap锁,但是不能插入数据;(后续有死锁可能)
3.如果有普通索引,命中了记录: 行锁+gap锁;(后续有死锁可能)
4.如果有普通索引,没有命中记录: gap锁,和情况2相同;(后续有死锁可能)
5.如果没有索引,直接锁全表,互斥,直接阻塞别的事务。(mysql的行锁是依赖索引的,这一点和oracle锁在数据块上不同;
因此如果没有索引或者没有用上索引,mysql就只能加表锁了。)

可见如果where条件没有命中记录的时候,如果有索引,反而可能有死锁风险。
除了select for update,其他进行当前读的语句(如delete)也可以获得gap锁,因此也有同样的烦恼。

那么都有哪些语句能获得gap锁呢?

参考

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-gap-locks

gap锁

首先当然要可重复读和串行读才有gap锁。
然后根据上面的参考资料,gap锁只有一个目标:
就是防止别的事务在这个区间插入数据。

因此不同事务可以获得同一个区间的gap锁,因为这与上述目标并不冲突。两个事务可以获得这个区间的gap-S锁,gap-X锁,都可以。这一点和意向锁有点类似,只不过意向锁是加在表上的,gap锁是加在区间上的。

gap-S锁和gap-X锁没有区别。(因此select in share mode和select for update获得的gap锁本质是一样的,想要无锁读,就直接用select进行快照读。)

select in share mode/select for update/update/delete这4种语句是可能获取gap锁的。因此这4种当前读如果没有命中记录,而且又用到了索引,就会给死锁埋下风险。

当可以通过select for update的where条件筛出记录时,上面的代码是不会有deadlock问题的。然而当select for update中的where条件无法筛选出记录时,这时在有多个线程执行上面的acquire方法时是可能会出现死锁的。

2.1 一个简单的复现场景

下面通过一个比较简单的例子复现一下这个场景
首先给表里初始化3条数据。

insert into number select 'bbb',2;
insert into number select 'hhh',8;
insert into number select 'yyy',25;

接着按照如下的时序进行操作:

session 1session 2
begin;
begin;
select * from number where prefix='ddd' for update;
select * from number where prefix='fff' for update
insert into number select 'ddd',1
锁等待中insert into number select 'fff',1
锁等待解除死锁,session 2的事务被回滚

2.2 分析下这个死锁

通过查看show engine innodb status的信息,我们慢慢地观察每一步的情况:

2.2.1 session1做了select for update

------------
TRANSACTIONS
------------
Trx id counter 238435
Purge done for trx's n:o < 238430 undo n:o < 0 state: running but idle
History list length 13
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281479459589696, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281479459588792, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 238434, ACTIVE 3 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 160, OS thread handle 123145573965824, query id 69153 localhost root
TABLE LOCK table test.number trx id 238434 lock mode IX
RECORD LOCKS space id 1506 page no 3 n bits 80 index uk_prefix of table test.number trx id 238434 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 3; hex 686868; asc hhh;;
1: len 6; hex 00000003a350; asc P;;
2: len 7; hex d2000001ff0110; asc ;;
3: len 8; hex 8000000000000008; asc ;;

事务238434拿到了hhh前的gap锁,也就是('bbb', 'hhh')的gap锁。

2.2.2 session2做了select for update

------------
TRANSACTIONS
------------
Trx id counter 238436
Purge done for trx's n:o < 238430 undo n:o < 0 state: running but idle
History list length 13
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281479459589696, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 238435, ACTIVE 3 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 161, OS thread handle 123145573408768, query id 69155 localhost root
TABLE LOCK table test.number trx id 238435 lock mode IX
RECORD LOCKS space id 1506 page no 3 n bits 80 index uk_prefix of table test.number trx id 238435 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 3; hex 686868; asc hhh;;
1: len 6; hex 00000003a350; asc P;;
2: len 7; hex d2000001ff0110; asc ;;
3: len 8; hex 8000000000000008; asc ;;
---TRANSACTION 238434, ACTIVE 30 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 160, OS thread handle 123145573965824, query id 69153 localhost root
TABLE LOCK table test.number trx id 238434 lock mode IX
RECORD LOCKS space id 1506 page no 3 n bits 80 index uk_prefix of table test.number trx id 238434 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 3; hex 686868; asc hhh;;
1: len 6; hex 00000003a350; asc P;;
2: len 7; hex d2000001ff0110; asc ;;
3: len 8; hex 8000000000000008; asc ;;

事务238435也拿到了hhh前的gap锁。

截自InnoDB的lock_rec_has_to_wait方法实现,可以看到的LOCK_GAP类型的锁只要不带有插入意向标识,不必等待其它锁(表锁除外)

2.2.3 session1尝试insert

------------
TRANSACTIONS
------------
Trx id counter 238436
Purge done for trx's n:o < 238430 undo n:o < 0 state: running but idle
History list length 13
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281479459589696, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 238435, ACTIVE 28 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 161, OS thread handle 123145573408768, query id 69155 localhost root
TABLE LOCK table test.number trx id 238435 lock mode IX
RECORD LOCKS space id 1506 page no 3 n bits 80 index uk_prefix of table test.number trx id 238435 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 3; hex 686868; asc hhh;;
1: len 6; hex 00000003a350; asc P;;
2: len 7; hex d2000001ff0110; asc ;;
3: len 8; hex 8000000000000008; asc ;;
---TRANSACTION 238434, ACTIVE 55 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 160, OS thread handle 123145573965824, query id 69157 localhost root executing
insert into number select 'ddd',1
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1506 page no 3 n bits 80 index uk_prefix of table test.number trx id 238434 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 3; hex 686868; asc hhh;;
1: len 6; hex 00000003a350; asc P;;
2: len 7; hex d2000001ff0110; asc ;;
3: len 8; hex 8000000000000008; asc ;;


TABLE LOCK table test.number trx id 238434 lock mode IX
RECORD LOCKS space id 1506 page no 3 n bits 80 index uk_prefix of table test.number trx id 238434 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 3; hex 686868; asc hhh;;
1: len 6; hex 00000003a350; asc P;;
2: len 7; hex d2000001ff0110; asc ;;
3: len 8; hex 8000000000000008; asc ;;
RECORD LOCKS space id 1506 page no 3 n bits 80 index uk_prefix of table test.number trx id 238434 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 3; hex 686868; asc hhh;;
1: len 6; hex 00000003a350; asc P;;
2: len 7; hex d2000001ff0110; asc ;;
3: len 8; hex 8000000000000008; asc ;;

可以看到,这时候事务238434在尝试插入'ddd',1时,由于发现其他事务(238435)已经有这个区间的gap锁,因此innodb给事务238434上了插入意向锁,锁的模式为LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION,等待事务238435释放掉gap锁。

截取自InnoDB的lock_rec_insert_check_and_lock方法实现

2.2.4 session2尝试insert

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-12-21 22:50:40 0x70001028a000
*** (1) TRANSACTION:
TRANSACTION 238434, ACTIVE 81 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 160, OS thread handle 123145573965824, query id 69157 localhost root executing
insert into number select 'ddd',1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1506 page no 3 n bits 80 index uk_prefix of table test.number trx id 238434 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 3; hex 686868; asc hhh;;
1: len 6; hex 00000003a350; asc P;;
2: len 7; hex d2000001ff0110; asc ;;
3: len 8; hex 8000000000000008; asc ;;
*** (2) TRANSACTION:
TRANSACTION 238435, ACTIVE 54 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 161, OS thread handle 123145573408768, query id 69159 localhost root executing
insert into number select 'fff',1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1506 page no 3 n bits 80 index uk_prefix of table test.number trx id 238435 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 3; hex 686868; asc hhh;;
1: len 6; hex 00000003a350; asc P;;
2: len 7; hex d2000001ff0110; asc ;;
3: len 8; hex 8000000000000008; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1506 page no 3 n bits 80 index uk_prefix of table test.number trx id 238435 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 3; hex 686868; asc hhh;;
1: len 6; hex 00000003a350; asc P;;
2: len 7; hex d2000001ff0110; asc ;;
3: len 8; hex 8000000000000008; asc ;;
*** WE ROLL BACK TRANSACTION (2)


TRANSACTIONS

Trx id counter 238436
Purge done for trx's n:o < 238430 undo n:o < 0 state: running but idle
History list length 13
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281479459589696, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281479459588792, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 238434, ACTIVE 84 sec
3 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 160, OS thread handle 123145573965824, query id 69157 localhost root
TABLE LOCK table test.number trx id 238434 lock mode IX
RECORD LOCKS space id 1506 page no 3 n bits 80 index uk_prefix of table test.number trx id 238434 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 3; hex 686868; asc hhh;;
1: len 6; hex 00000003a350; asc P;;
2: len 7; hex d2000001ff0110; asc ;;
3: len 8; hex 8000000000000008; asc ;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 3; hex 646464; asc ddd;;
1: len 6; hex 00000003a362; asc b;;
2: len 7; hex de000001e60110; asc ;;
3: len 8; hex 8000000000000001; asc ;;
RECORD LOCKS space id 1506 page no 3 n bits 80 index uk_prefix of table test.number trx id 238434 lock_mode X locks gap before rec insert intention
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 3; hex 686868; asc hhh;;
1: len 6; hex 00000003a350; asc P;;
2: len 7; hex d2000001ff0110; asc ;;
3: len 8; hex 8000000000000008; asc ;;

到了这里,我们可以从死锁信息中看出,由于事务238435在插入时也发现了事务238434的gap锁,同样加上了插入意向锁,等待事务238434释放掉gap锁。因此出现死锁的情况。

  • 0
    点赞
  • 2
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值