Mysql死锁排查记录

    //外层Async调用
    @Transactional(rollbackFor = Exception.class)
    @Override
    public void saveOrUpdate(Long customerId, Long visitCustomerId) {
        deleteVisit(customerId, visitCustomerId);
        customerVisitDao.insertVisit(customerId, visitCustomerId);
    }

CREATE TABLE `customer_visit` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `customer_id` bigint(20) NOT NULL DEFAULT '-1' COMMENT '访问用户',
  `visit_customer_id` bigint(20) NOT NULL DEFAULT '-1' COMMENT '被访问用户',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_customer_id_visit_customer_id` (`customer_id`,`visit_customer_id`),
  KEY `index_customer_id` (`customer_id`),
  KEY `index_visit_customer_id` (`visit_customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='访客记录';

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-04-08 22:58:23 7f8d2fbff700
//事务1 编号694322167 活跃0.005s 事务状态:根据索引读取数据
*** (1) TRANSACTION: 

TRANSACTION 694322167, ACTIVE 0.005 sec starting index read
//事务1使用一个表 表锁1
mysql tables in use 1, locked 1
//等待锁链表的长度为2 当前事务持有的行记录锁/gap(间隙锁)锁1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
LOCK BLOCKING MySQL thread id: 5828505 block 5835430
MySQL thread id 5835430, OS thread handle 0x7f8d6afff700, query id 4891428548 192.168.0.47 web_user updating
//事务1正在等待锁的sql
DELETE FROM customer_visit
        where
        customer_id = 735***68
        and
        visit_customer_id =624***90
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
//事务1 正在等待表customer_visit上索引uniq_customer_id_visit_customer_id的X锁(排他锁)-记录锁 
RECORD LOCKS space id 47 page no 25368 n bits 616 index `uniq_customer_id_visit_customer_id` of table `huayan`.`customer_visit` trx id 694322167 lock_mode X locks rec but not gap waiting
Record lock, heap no 502 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex 800000000462ed68; asc      b h;;
 1: len 8; hex 8000000003b97a96; asc       z ;;
 2: len 8; hex 8000000000eb5fb1; asc       _ ;;
 

//事务2 编号694322166 活跃0.005s 事务状态:插入数据
*** (2) TRANSACTION:
TRANSACTION 694322166, ACTIVE 0.005 sec inserting
//事务2使用一个表 表锁1
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
MySQL thread id 5828505, OS thread handle 0x7f8d2fbff700, query id 4891428551 192.168.0.48 web_user update
insert customer_visit(customer_id,visit_customer_id)
      values (735***68,624***90)

//事务2 持有表customer_visit上索引uniq_customer_id_visit_customer_id的X锁 记录锁
//由于是RC隔离模式下的基于唯一索引的等值查询，会申请一个记录锁
//此处是事务2通过delete fromcustomer_visit where customer_id = 735***68 and visit_customer_id =624***90申请的锁
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 47 page no 25368 n bits 616 index `uniq_customer_id_visit_customer_id` of table `huayan`.`customer_visit` trx id 694322166 lock_mode X locks rec but not gap
Record lock, heap no 502 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex 800000000462ed68; asc      b h;;
 1: len 8; hex 8000000003b97a96; asc       z ;;
 2: len 8; hex 8000000000eb5fb1; asc       _ ;;
 
//事务2 正在申请S锁(共享锁) 
//此处是事务2通过insert customer_visit(customer_id,visit_customer_id) values (735***68,624***90) 申请的
//insert语句在普通情况下是会申请排他锁，也就是X锁，但是这里出现了S锁。这是因为a字段是一个唯一索引，
//所以insert语句会在插入前进行一次duplicate key的检查，为了使这次检查成功，需要申请S锁防止其他事务对a字段进行修改。
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 47 page no 25368 n bits 616 index `uniq_customer_id_visit_customer_id` of table `huayan`.`customer_visit` trx id 694322166 lock mode S waiting
Record lock, heap no 502 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex 800000000462ed68; asc      b h;;
 1: len 8; hex 8000000003b97a96; asc       z ;;
 2: len 8; hex 8000000000eb5fb1; asc       _ ;;