MySQL意向锁和死锁攻略

意向锁

MySQL中有两种意向锁：意向共享锁（IS）和意向排它锁（IX）。当一个事务请求一张表的排它锁或者共享锁时，MySQL会先判断表是否已经被其它事务加了锁。若没有加锁，则直接获取锁；若被加锁，则会判断待加的锁类型。若是要请求共享锁，则会在表上加意向共享锁（IS）；若是要请求排它锁，则会在表上加意向排它锁（IX）。意向锁只是一个标记而已，并不真正锁住了什么，其目的在于表示一个事务准备加一个类型的锁。

例如，事务A要获取一张表的共享锁（读锁），而该表当前未被锁住。那么，MySQL会在该表上加意向共享锁（IS）。之后，事务B也要获取该表的共享锁，MySQL会发现该表已经有了意向共享锁（IS），那么MySQL就知道有其它事务在该表上存在对该表的读操作，并不需要等待其它事务释放锁。

从意向锁的功能可以看出，意向锁是针对表级别的锁，而不是针对行级别的锁。

死锁

假设有两个事务T1和T2，T1在等待T2释放的锁，而同时T2也在等待T1释放的锁，那么这两个事务就产生了死锁。此时，系统一般会强制回滚其中一个事务，解除死锁的情况。

如下面所示的示例：

执行以下SQL：

SET SESSION autocommit=0; --关闭自动提交
BEGIN; --开启一个事务
SELECT * FROM table1 WHERE id = 10 FOR UPDATE; --对表进行排他锁

上述SQL语句会对表table1中id等于10的一行进行排它锁（FOR UPDATE）。此时，该行的锁定时间会一直持续到当前事务提交或回滚。

现在开启另外一个事务，并尝试获取该行的排它锁：

SET SESSION autocommit=0; --关闭自动提交
BEGIN; --开启一个事务
SELECT * FROM table1 WHERE id = 10 FOR UPDATE; --获取排它锁（FOR UPDATE）

此时，Notice如下提示：

MySQL on localhost (5.7.33)  Warning (1205): Lock wait timeout exceeded; try restarting transaction

它表示这个事务超时（由锁等待超时参数选项决定）了以后，还是没有获得需要的锁，MySQL就会强制回滚该事务。

在该示例中，如果两个事务先后执行，则T1成功获取该行的锁，而T2则始终在等待该锁的释放，最后会超时回滚，解除死锁的情况。

示例

接下来，我们来看一下具体的示例操作。

在该示例中，我们创建一个students表，然后模拟两个事务之间的死锁情况。

首先，我们创建一个students表：

CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

接着，我们往students表中插入一些记录：

INSERT INTO `students` (`name`, `age`) VALUES
  ('Tom', 18),
  ('Jack', 20),
  ('Lucy', 22),
  ('Mike', 24),
  ('David', 20),
  ('John', 22);

现在，我们让两个事务分别对students表进行操作。

第一个事务（T1）

在第一个事务中，我们首先查询students表中所有年龄为20岁的学生信息：

SET SESSION autocommit=0;
BEGIN;
SELECT * FROM students WHERE age = 20 FOR UPDATE;

上述SQL对students表中所有age为20的行进行了排它锁，该锁将一直存在于当前事务中。

第二个事务（T2）

在第二个事务中，我们对students表中年龄为22的行进行更新操作：

SET SESSION autocommit=0;
BEGIN;
UPDATE students SET age = age + 1 WHERE age = 22;

上述SQL需要在students表中获取一行排他锁（FOR UPDATE），但由于第一个事务已经持有了students表的排它锁，因此该事务将等待第一个事务的锁释放，直到超时后回滚。

在上述示例中，我们展示了MySQL意向锁和死锁的操作过程。需要注意的是，在实际应用中，我们通常需要注意编写高效的SQL语句，避免出现死锁等问题，同时也可以根据实际需求对MySQL锁相关的配置进行适当调整，以提高系统的性能和稳定性。