利用mysql唯一索引做分布式锁，多线程同时执行时会报死锁问题，怎么样保证顺序执行下来呢

Question

问题背景

目前公司项目是用的Mysql的唯一索引来做分布式锁的，但是现在发现，当多个请求同时过来时，会报死锁导致很多请求报错，现在想让这些请求一个一个等待获取锁，不再报死锁。

问题相关代码

写了一个Demo

//这里是模拟多个并发请求
    @Test
    public void test() {
        for (int i = 0; i < 100; i++) {
            lockService.doWithLock();
        }
        try {
            Thread.sleep(2000 * 100);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
    }
  
    // 这里是模拟业务代码执行
    @Transactional
    @Async
    public void doWithLock() {
        BusiLock lock = new BusiLock();
        lock.setName("lock");
        log.info("获取锁");
        lockMapper.insert(lock);
        // 模拟业务代码执行
        try {
            Thread.sleep(2000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }

        log.info("释放锁");
        lockMapper.deleteById(lock.getId());
    }

表结构是这样的

CREATE TABLE `busi_lock` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `lock_UN` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=202 DEFAULT CHARSET=utf8

运行结果及报错内容

报错截图

死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-02-17 16:28:15 7f1f182b9700
*** (1) TRANSACTION:
TRANSACTION 898094, ACTIVE 2 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 148, OS thread handle 0x7f1f180eb700, query id 14978 172.18.0.1 root update
INSERT INTO busi_lock  ( name )  VALUES  ( 'lock' )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 113 page no 4 n bits 72 index `lock_UN` of table `demo`.`busi_lock` trx id 898094 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 898089, ACTIVE 2 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 152, OS thread handle 0x7f1f182b9700, query id 14969 172.18.0.1 root update
INSERT INTO busi_lock  ( name )  VALUES  ( 'lock' )
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 113 page no 4 n bits 72 index `lock_UN` of table `demo`.`busi_lock` trx id 898089 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 113 page no 4 n bits 72 index `lock_UN` of table `demo`.`busi_lock` trx id 898089 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

我的解答思路和尝试过的方法

之前删除的时候也是根据唯一索引删除的，后来改成根据主键删除，依然无果。
现在能想到的就是用Redis做分布式锁了，但又要引新的组件。

我想要达到的结果

想不报死锁，能够顺序执行下来。

Answer 1

看样子是意向锁的原因，t_898094和t_898089都执行插入操作，所以他们都要加排他锁，但是这时间应该有一个解锁删除操作在执行，拿着排他锁，所以这两个插入事务都会先将排他锁转为共享锁，等删除操作完成，因为记录已经不存在了，所以t_898094和t_898089都不会走冲突的流程，都可以继续执行，就需要将自己的共享锁转为排他锁，但是要转为排他锁t_898094就要等t_898089释放共享锁， 同样的t_898089也在等t_898094释放共享锁，就导致了死锁的发生。
解决方案的话，我觉得可以变新增为更新，我插入一条数据，

// 加锁的操作变为
 update busi_lock set lock_UN = 'LOCK' where  lock_UN  = 'UNLOCK'
// 解锁的操作变为
 update busi_lock set lock_UN = 'UNLOCK' where  lock_UN  = 'LOCK'

然后通过sql的影响行数来判定是是否加锁成功，如果影响行数是1，那说明操作成功，如果影响行数是0，那说明操作失败。

另外，我想对于你的建表语句提一个小意见，关于lock_UN ，他只是一个标识，所以一个int或者char(1)类型就可以，即使建立为varchar,也没有必要建成varchar(100), 不可否认的是，varchar的类型在磁盘中确实是按照实际空间来占用的，但是这并不意味着varchar类型就可以随意的设置长度，因为涉及到文件排序或者临时表这类需要在内存中做的事情时，mysql并不知道varchar字段真正的长度是多少，所以会悲观的按照最大长度分配内存，这就会资源浪费和影响性能，虽然你列出的场景中这个表不会涉及到这个，但是依然需要注意这个事情。