死锁分析延续

码农戏码

发布于 2021-03-23 16:30:58

6180

文章被收录于专栏：DDDDDD

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

【使用电脑阅读更佳】

事务 1	事务 2
-	begin
begin	delete id=1;(1)id加锁、(2)A加锁
delete id=2;(3)id加锁、(4)A加锁	-

| update ? where userid=? and tenantid=? and no=?;
| 如果先走no索引，已经持有B锁，再去持D锁时等待 update ? where userid=? and tenantid=? and no=?; | - 先走no索引，持B锁时等待；deadlock |-

update ? where userid=? and tenantid=? and no=?;如果先走（tenantid,userid）则是日志一

先走no，则是日志二

索引交叉，则是日志三

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

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

查看事务自动提交选项

show session variables like 'autocommit';show global variables like 'autocommit’;
set session autocommit=0;set global autocommit=0;

查看事务隔离级别

select @@tx_isolation

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

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输出，只需启用innodbstatusoutput_locks

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

=====================================2020-06-30 15:44:46 7fdc8a76e700 INNODB MONITOR OUTPUT=====================================
----------------------------END OF INNODB MONITOR OUTPUT============================

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

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

delete from invoice_collection_info where id=1275244823997059072
TABLE LOCK table `assist`.`invoice_collection_info` trx id 1636893 lock mode IXRECORD 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,还有一个主键索引锁

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

使用二级索引删除操作

delete FROM invoice_item WHERE ( collection_id = 1275244823997059072 );
TABLE LOCK table `assist`.`invoice_item` trx id 1636964 lock mode IXRECORD 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 gapRecord 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 gapRecord lock, heap no 28 PHYSICAL RECORD: n_fields 23; compact format; info bits 0

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

使用二级索引查询

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 IXRECORD 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二级索引上加锁，再在主键索引加锁

使用主键更新操作

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 IXRECORD 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中的二级索引锁显示出来了