mysql两个事务锁等待是怎么形成的？

Question

-- version mysql 5.7.20

-- 数据准备【数据库隔离级别为默然RR级别】
CREATE TABLE `employee` (
  `id` int(11) NOT NULL COMMENT 'id',
  `deptNo` int(11) NOT NULL COMMENT '部门编号',
  `name` varchar(200) DEFAULT NULL COMMENT '姓名',
  PRIMARY KEY (`deptNo`,`id`),
  UNIQUE KEY `employee_id_uindex` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表';

INSERT INTO employee (id,deptNo,name) value (1,1000,'小明');



-- 事务1
START TRANSACTION ;
DELETE FROM employee WHERE deptNo = 1001;
INSERT INTO employee (id,deptNo,name) value (2,1001,'张三');
ROLLBACK ;

-- 事务2
START TRANSACTION ;
DELETE FROM employee WHERE deptNo = 1002;
INSERT INTO employee (id,deptNo,name) value (3,1002,'李四');
ROLLBACK ;


-- 问题：执行事务1，然后执行事务2，此时事务2锁等待，请大神给详细解答一下，跪谢！！！

Answer 1

根据你提供的代码，事务1和事务2都涉及对employee表进行删除和插入操作。在默认的隔离级别下（即Repeatable Read），这意味着两个事务在执行期间都将锁定employee表。不过，在事务1中删除了deptNo为1001的记录，这可能会引发锁等待问题。假设事务2要删除deptNo为1002的记录，它需要获取一个锁，但由于事务1已经锁定了整个表，事务2必须等待事务1释放锁。因此，事务2将进入锁等待状态。只有当事务1释放了锁并提交了事务后，事务2才能继续执行。

要解决这个问题，可以将隔离级别改为Read Commited，这将减少锁的粒度并减少锁等待的可能性。另一种方法是使用NOLOCK和READUNCOMMITTED提示在事务中执行SELECT查询，以避免对表进行锁定。虽然这些提示可以减少锁等待时间，但它们也可能导致数据不一致的问题。因此，在使用它们之前请务必认真考虑。