死锁分析延续

根据上一篇【死锁分析】,又重新梳理了一下,画图表示更形象一些

事务 1 事务 2
- begin
begin delete id=1;(1)id加锁、(2)A加锁
delete id=2;(3)id加锁、(4)A加锁 -
- update ? where user_id=? and tenant_id=? and no=?;
- 如果先走no索引,已经持有B锁,再去持D锁时等待
update ? where user_id=? and tenant_id=? and no=?; -
先走no索引,持B锁时等待;deadlock -

update ? where user_id=? and tenant_id=? and no=?;如果先走(tenant_id,user_id) 则是日志一

先走no,则是日志二

索引交叉,则是日志三


最近借着死锁事项,又温习了部分数据库的理论知识,后面有时间再整理。现在的程序员真难,得上知天文,下知地理;这应该是DBA的专长,作为开发也得去深究,真难

在追查死锁的过程中,对照理论有一些实践,总结一下:

查看事务自动提交选项

1
2
3
4
5
show session variables like 'autocommit';
show global variables like 'autocommit’;

set session autocommit=0;
set global autocommit=0;

查看事务隔离级别

1
select @@tx_isolation

查看锁信息,想查看一条sql使用了什么锁,需要打开锁监听

1
2
3
4
5
6
7
8
9
show session variables like '%output%';

#开启InnoDB锁监控
set global innodb_status_output_locks=on;

set global innodb_status_output=on;
#上面的语句可定期写入到标准错误输出(stderr,即error log,大概每15s写一次),你也可以使用 SHOW ENGINE INNODB STATUS 语句直接在客户端获取innodb信息

show session variables like '%output%';

要启用InnoDB锁定监视器的 SHOW ENGINE INNODB STATUS输出,只需启用innodb_status_output_locks

打开监控开关后,在mysql日志文件中会出现

1
2
3
4
5
6
7
=====================================
2020-06-30 15:44:46 7fdc8a76e700 INNODB MONITOR OUTPUT
=====================================

----------------------------
END OF INNODB MONITOR OUTPUT
============================

打开锁监控,可以确定一下常用语句的锁信息

执行完一条SQL,使用SHOW ENGINE INNODB STATUS打印出锁信息

1
2
3
4
delete from invoice_collection_info where id=1275244823997059072

TABLE LOCK table `assist`.`invoice_collection_info` trx id 1636893 lock mode IX
RECORD LOCKS space id 18491 page no 211 n bits 104 index `PRIMARY` of table `assist`.`invoice_collection_info` trx id 1636893 lock_mode X locks rec but not gap

delete语句,根据主键删除操作;可以明显看出上了表锁IX model,还有一个主键索引锁

其实如果有二级索引,还会有二级索引锁,但那是隐式锁,所以没有显示出来,后面会有试验让隐式锁显示化


使用二级索引删除操作

1
2
3
4
5
6
7
8
9
10
delete FROM invoice_item WHERE ( collection_id = 1275244823997059072 );

TABLE LOCK table `assist`.`invoice_item` trx id 1636964 lock mode IX
RECORD LOCKS space id 18493 page no 4 n bits 784 index `idx_collection_id` of table `assist`.`invoice_item` trx id 1636964 lock_mode X locks rec but not gap
Record lock, heap no 563 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 91b2946930001000; asc i0 ;;
1: len 8; hex 91b2ae5d61401000; asc ]a@ ;;

RECORD LOCKS space id 18493 page no 12 n bits 96 index `PRIMARY` of table `assist`.`invoice_item` trx id 1636964 lock_mode X locks rec but not gap
Record lock, heap no 28 PHYSICAL RECORD: n_fields 23; compact format; info bits 0

先在二级索引上加锁,再在对应的主键索引上加锁


使用二级索引查询

1
2
3
4
5
6
select * from invoice_collection_info where invoice_uiq_flag = '031001900104-62079412' for update

TABLE LOCK table `assist`.`invoice_collection_info` trx id 1636947 lock mode IX
RECORD LOCKS space id 18491 page no 642 n bits 344 index `idx_uniflag` of table `assist`.`invoice_collection_info` trx id 1636947 lock_mode X locks rec but not gap

RECORD LOCKS space id 18491 page no 496 n bits 96 index `PRIMARY` of table `assist`.`invoice_collection_info` trx id 1636947 lock_mode X locks rec but not gap

从日志中看出,先在invoice_uiq_flg二级索引上加锁,再在主键索引加锁


使用主键更新操作

1
2
3
4
update invoice_collection_info set invoice_uiq_flag = '031200190010-62079412' WHERE (  id = 1275244823997059072 );

TABLE LOCK table `assist`.`invoice_collection_info` trx id 1636958 lock mode IX
RECORD LOCKS space id 18491 page no 741 n bits 88 index `PRIMARY` of table `assist`.`invoice_collection_info` trx id 1636958 lock_mode X locks rec but not gap

其实不仅会在主键加X,还会在二级索引上也加X,但没有显示出来;这儿跟delete一样,其实也是个隐式锁


模拟测试了一下,两条sql,一条根据二级索引删除操作、另一条使用主键更新记录模拟并发死锁,把update中的二级索引锁显示出来了


上面的操作都是在RC级别下进行的,对于select操作,快照读都不会加锁,实验结果也与理论一致

朱兴生 wechat
最新文章尽在微信公众号『码农戏码』