记一次神奇的 MySQL 死锁

CREATE TABLE `test_lock_table_one`  (
     `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID自增',
     `custom_id` varchar(256)  NOT NULL COMMENT '自定义id',
     `name` varchar(256)  NOT NULL COMMENT '名称',
     `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '0：未删除 1：已删除',
     `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
     `modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
     PRIMARY KEY (`id`) USING BTREE,
     INDEX `idx_customId`(`custom_id`) USING BTREE COMMENT '用户ID'
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = '测试锁表' ROW_FORMAT = Dynamic;
CREATE TABLE `test_lock_table_two`  (
   `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID自增',
   `custom_id` varchar(32) NOT NULL COMMENT '自定义id',
   `name` varchar(32) NOT NULL COMMENT '名称',
   `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '0：未删除 1：已删除',
   `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
   `modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
   PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = '测试index对锁的影响' ROW_FORMAT = Dynamic;

@Slf4j
@Service
public class TestLockTableOneServiceImpl extends ServiceImpl<TestLockTableOneMapper, TestLockTableOne> implements ITestLockTableOneService {
    @Resource
    private DataSourceTransactionManager dataSourceTransactionManager;
    @Resource
    private TransactionDefinition transactionDefinition;
    @Resource
    private TestLockTableOneMapper lockTableOneMapper;
    
    @Resource
    private ITestLockTableTwoService testLockTableTwoService;
    @Override
    public boolean updateTableOne(String customId, String name) {
        boolean result = updateTableOneInternal(customId, name);
        return result;
    }
    // 这个方法在  lockTableOneMapper.updateTableOneInternal(customId, name);抛出异常后, 事务既不会回滚, 也不会提交
    // 代码本身就是有问题的, 不要纠结他写的好不好, 本文的重点不在这
    private boolean updateTableOneInternal(String customId, String name) {
        TransactionStatus transaction = dataSourceTransactionManager.getTransaction(transactionDefinition);
        try {
            testLockTableTwoService.queryIndexTable(customId);
            // 这里故意让数据库操作异常了, 
           lockTableOneMapper.updateTableOneInternal(customId, name);
           dataSourceTransactionManager.commit(transaction);
            
        } catch (BusinessException exception) {
            log.error("更新数据异常");
            
            // 这里只有在捕捉到是BusinessException是才会回滚  
            dataSourceTransactionManager.rollback(transaction);
        } catch (Exception ex) {
            log.error("更新数据异常: {}", ex.getMessage());
            throw  new BusinessException(400, "更新数据异常了");
        }
        return false;
    }
}
  // XML中的更新语句是这样的: 
    <update id="updateTableOneInternal">
        UPDATE test_lock_table_one
        set name = #{name}
        WHERE deleted = 0 AND custome_id = #{customId}
    </update>

@Service
public class TestLockTableTwoServiceImpl extends ServiceImpl<TestIndexLockTableMapper, TestLockTableTwo> implements ITestLockTableTwoService {
    @Resource
    private TestIndexLockTableMapper indexLockTableMapper;
    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean updateData(String customId, String name) {
        int count = indexLockTableMapper.updateData(customId, name);
        return count > 0;
    }
}
 // XML中的更新语句是这样的: 
    <update id="updateData">
        UPDATE test_lock_table_two
        set index_name = #{name}
        WHERE deleted = 0 AND custom_id = #{customId}
    </update

// 表示正在运行的线程
show processlist;
// 当前运行的所有事务
 SELECT * FROM information_schema.INNODB_TRX;
 //当前出现的锁
 SELECT * FROM information_schema.INNODB_LOCKs;
// 锁等待的对应关系
 SELECT * FROM information_schema.INNODB_LOCK_WAITS;

select now(),(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,b.id,b.user,b.host,b.db,d.SQL_TEXT
from information_schema.innodb_trx a 
inner join information_schema.PROCESSLIST b on a.TRX_MYSQL_THREAD_ID=b.id and b.command = 'Sleep'
inner join performance_schema.threads c ON b.id = c.PROCESSLIST_ID
inner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;