mysql死锁日志分析
背景
在给业务方用otter做数据迁移时，发现数据库经常出现死锁问题，数据迁移Load阶段的同步性能比较低下，每隔几批就会出现load阶段执行时间超过3s。
同步日志如下：
1 | Jul 9 13:57:28 otter2 otter-node-prd[235942]: 2021-07-09 13:57:28.867 [pipelineId = 1,taskName = SelectWorker] WARN com.alibaba.otter.node.etl.select.SelectTask - cost processId:502379 Select耗时:10 数量:1000 |
Mysql死锁
MySQL的死锁指的是两个事务互相等待的场景，这种循环等待理论上不会有尽头。
比如事务A持有行1的锁，事务B持有行2的锁，
然后事务A试图获取行2的锁，事务B试图获取行1的锁，
这样事务A要等待事务B释放行2的锁，事务B要等待事务A释放行1的锁，
两个事务互相等待，谁也提交不了。
这种情况下MySQL会选择中断并回滚其中一个事务，使得另一个事务可以提交。MySQL会记录死锁的日志。
mysql死锁日志如下：
1 | ===================================== |
事物一
1 | (1) TRANSACTION: |
其中：
TRANSACTION 12190992019：是此事务的id
ACTIVE 0 sec：活跃时间0秒
inserting：事务当前正在插入数据
mysql tables in use 1, locked 1：表示此事务修改了一个表，锁了一行数据
MySQL thread id 10966617：mysql线程id
query id 44636074063：查询id
10.1.1.38 migrate_chd update：数据库ip、账号、更新语句
**insert into chd.credit_user_task_log_9999_q4…**：sql语句
1 | (1) WAITING FOR THIS LOCK TO BE GRANTED: |
RECORD LOCKS：表示持有的是行级锁
index uniq_userId_indexId_taskId：表示锁的是唯一索引
table chd.credit_user_task_log_9999_q4：表示锁的具体是哪个表
trx id 12190992019：事务id，和上面的TRANSACTION相同
lock_mode X locks gap before rec insert intention，锁模式：意向锁
事物二
1 | (2) TRANSACTION: |
其中：
TRANSACTION 12190992013：是此事务的id
ACTIVE 0 sec：活跃时间0秒
inserting：事务当前正在插入数据
mysql tables in use 1, locked 1：表示此事务修改了一个表，锁了一行数据
MySQL thread id 10966625：mysql线程id
query id 44636074031：查询id
10.1.1.38 migrate_chd update：数据库ip、账号、更新语句
**insert into chd.credit_user_task_log_9999_q4…**：sql语句
1 | (2) HOLDS THE LOCK(S): |
RECORD LOCKS：表示持有的是行级锁
index uniq_userId_indexId_taskId：表示锁的是唯一索引
table chd.credit_user_task_log_9999_q4：表示锁的具体是哪个表
trx id 12190992013：事务id，和上面的TRANSACTION相同
lock_mode X locks gap before rec insert intention，锁模式：意向锁
死锁处理结果
1 | WE ROLL BACK TRANSACTION (1) |
表示MySQL最终决定回滚事务1。
另外，日志里还记录的当前SESSION和事务列表，也就是这段：
1 | TRANSACTIONS |
锁的类型
行锁（Record Locks）
行锁是作用在索引上的
间隙锁（Gap Locks）
间隙锁是锁住一个区间的锁。
这个区间是一个开区间，范围是从某个存在的值向左直到比他小的第一个存在的值，所以间隙锁包含的内容就是在查询范围内，而又不存在的数据区间。
比如有id分别是1,10,20，要修改id<15的数据，那么生成的间隙锁有以下这些：(-∞,1)，(1,10)，(10,20)，此时若有其他事务想要插入id=11的数据，则需要等待。
间隙锁是不互斥的。
作用是防止其他事务在区间内添加记录，而本事务可以在区间内添加记录，从而防止幻读。
在可重复读这种隔离级别下会启用间隙锁，而在读未提交和读已提交两种隔离级别下，即使使用select … in share mode或select … for update，也不会有间隙锁，无法防止幻读。
加锁规则有以下特性，我们会在后面的案例中逐一解释：
- 1.加锁的基本单位是（next-key lock）,他是前开后闭原则
- 2.插叙过程中访问的对象会增加锁
- 3.索引上的等值查询–给唯一索引加锁的时候，next-key lock升级为行锁
- 4.索引上的等值查询–向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁
- 5.唯一索引上的范围查询会访问到不满足条件的第一个值为止
临键锁（Next-key Locks）
临键锁=间隙锁+行锁，于是临键锁的区域是一个左开右闭的区间。
隔离级别是可重复读时，select … in share mode或select … for update会使用临键锁，防止幻读。普通select语句是快照读，不能防止幻读。
共享锁/排他锁（Shared and Exclusive Locks）
共享锁和排它锁都是行锁。共享锁用于事务并发读取，比如select … in share mode。排它锁用于事务并发更新或删除。比如select … for update
意向共享锁/意向排他锁（Intention Shared and Exclusive Locks）
意向共享锁和意向排他锁都是表级锁。
官方文档中说，事务获得共享锁前要先获得意向共享锁，获得排它锁前要先获得意向排它锁。
意向排它锁互相之间是兼容的。
插入意向锁（Insert Intention Locks）
插入意向锁锁的是一个点，是一种特殊的间隙锁，用于并发插入。
插入意向锁和间隙锁互斥。插入意向锁互相不互斥。
自增锁（Auto-inc Locks）
自增锁用于事务中插入自增字段。5.1版本前是表锁，5.1及以后版本是互斥轻量锁。
自增所相关的变量有：
auto_increment_offset，初始值
auto_increment_increment，每次增加的数量
innodb_autoinc_lock_mode，自增锁模式
其中：
innodb_autoinc_lock_mode=0，传统方式，每次都产生表锁。此为5.1版本前的默认配置。
innodb_autoinc_lock_mode=1，连续方式。产生轻量锁，申请到自增锁就将锁释放，simple insert会获得批量的锁，保证连续插入。此为5.2版本后的默认配置。
innodb_autoinc_lock_mode=2，交错锁定方式。不锁表，并发速度最快。但最终产生的序列号和执行的先后顺序可能不一致，也可能断裂。
解决方案
Otter在数据迁移Load阶段采用了batch并行批量插入方式，由于每批插入数据是50条，在加上业务表有一个唯一索引，由于全量数据的主键id都是间隔的，最终导致全量同步数据发生间隙锁问题，导致插入性能低下。
目前解决方式只能通过增大batchsize来降低锁冲突问题，提高插入性能。