死锁分析延续

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

事务 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的专长，作为开发也得去深究，真难

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

查看事务自动提交选项

show session variables like 'autocommit';
show global variables like 'autocommit’;

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

查看事务隔离级别

1	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输出，只需启用innodb_status_output_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 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,还有一个主键索引锁

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

使用二级索引删除操作

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

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

使用二级索引查询

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

使用主键更新操作

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操作，快照读都不会加锁，实验结果也与理论一致