问题初现
在某天下午,突然系统报警,抛出个异常:
仔细一看好像是事务回滚异常,写着的是因为死锁回滚,原来是个死锁问题,由于我对Mysql锁还是有一定 了解的,于是开始主动排查这个问题。首先在数据库中查找Innodb Status,在Innodb Status中会记录上一次死锁的信息,输入下面命令:
SHOW ENGINE INNODB STATUS
死锁信息如下,sql信息进行了简单处理:
------------------------LATEST DETECTED DEADLOCK------------------------2019-02-22 15:10:56 0x7eec2f468700*** (1) TRANSACTION:TRANSACTION 2660206487, ACTIVE 0 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)MySQL thread id 31261312, OS thread handle 139554322093824, query id 11624975750 10.23.134.92 erp_crm__6f73 updating/*id:3637ba36*/UPDATE tenant_config SET open_card_point = 0 where tenant_id = 123*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 1322 page no 534 n bits 960 index uidx_tenant of table `erp_crm_member_plan`.`tenant_config` trx id 2660206487 lock_mode X locks rec but not gap waiting*** (2) TRANSACTION:TRANSACTION 2660206486, ACTIVE 0 sec starting index readmysql tables in use 1, locked 13 lock struct(s), heap size 1136, 2 row lock(s)MySQL thread id 31261311, OS thread handle 139552870532864, query id 11624975758 10.23.134.92 erp_crm__6f73 updating/*id:3637ba36*/UPDATE tenant_config SET open_card_point = 0 where tenant_id = 123*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 1322 page no 534 n bits 960 index uidx_tenant of table `erp_crm_member_plan`.`tenant_config` trx id 2660206486 lock mode S*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 1322 page no 534 n bits 960 index uidx_tenant of table `erp_crm_member_plan`.`tenant_config` trx id 2660206486 lock_mode X locks rec but not gap waiting*** WE ROLL BACK TRANSACTION (1)------------
给大家简单的分析解释一下这段死锁日志,事务1执行Update语句的时候需要获取uidx_tenant这个索引再where条件上的X锁(行锁),事务2执行同样的Update语句,也在uidx_tenant上面想要获取X锁(行锁),然后就出现了死锁,回滚了事务1。当时我就很懵逼,回想了一下死锁产生的必要条件:
- 互斥。
- 请求与保持条件。
- 不剥夺条件。
- 循环等待。 从日志上来看事务1和事务2都是取争夺同一行的行锁,和以往的互相循环争夺锁有点不同,怎么看都无法满足循环等待条件。经过同事提醒,既然从死锁日志中不能进行排查,那么就只能从业务代码和业务日志从排查。这段代码的逻辑如下:
public int saveTenantConfig(PoiContext poiContext, TenantConfigDO tenantConfig) { try { return tenantConfigMapper.saveTenantConfig(poiContext.getTenantId(), poiContext.getPoiId(), tenantConfig); } catch (DuplicateKeyException e) { LOGGER.warn("[saveTenantConfig] 主键冲突,更新该记录。context:{}, config:{}