MySQL中的共享锁(lock in share mode)和排它锁(for update)

原创已于 2022-05-09 23:22:13 修改 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

于 2022-05-09 22:28:15 首次发布

MySQL 专栏收录该内容

57 篇文章

订阅专栏

本文详细介绍了MySQL中的共享锁（lock in share mode）和排它锁（for update）。共享锁允许读取但阻止修改，而排它锁不仅阻止修改，还阻止其他事务加锁。在实际操作中，会话1对数据加锁后，会话2可以快照读但无法加锁或修改。总结中指出，共享锁适用于涉及多表一致性的情况，排它锁适用于单表操作的一致性需求。

数据库中新建一张表user：

CREATE TABLE `test_user` (
  `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` VARCHAR(2) NOT NULL COMMENT '姓名',
  `age` INT(11) NOT NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

往user表中插入两行数据：

INSERT INTO `test_user` (`id`,`name`,`age`) VALUES (1,'张三',23);
INSERT INTO `test_user` (`id`,`name`,`age`) VALUES (2,'李四',24);

特别说明：共享锁和排他锁均阻塞不了快照读

1.共享锁(select ... lock in share mode)

一个事务对表中符合条件的数据行加了共享锁，这样的话其他事务可以读取（快照读）这些数据，也可以继续添加共享锁，但是无法修改这些数据直到你这个加锁的事务执行完成（否则会出现锁等待超时）。

会话1使用begin开启一个事务，对test_user表中的数据加了共享锁：

此时会话2使用begin也开启一个事务，对test_user表中的数据进行快照读，可以看到快照读时是可以读取到test_user表中的数据：

接着会话2再对test_user表中的数据加共享锁，如下图所示，可以看到还是读取到表中的数据：

接着会话1继续对test_user表进行更新操作，可以看到会话1此时会阻塞：

这时会话2也对test_user表进行更新操作(注意执行更新操作的时间不要超过会话1的锁等待超时检测时间，即不要超过innodb_lock_wait_timeout设置的值)，由于检测到了死锁，事务2会发生回滚：

这时原本阻塞的会话1会立马执行完成

2.排它锁(for update)

一个事务对表中符合条件的数据行加了排它锁，其他事务可以进行快照读，但是无法在这些记录上添加任何的排它锁或共享锁。即for update并不会阻塞其他事务的快照读取操作，除了select ...lock in share mode和select ... for update这种显示加锁的查询操作。

会话1使用BEGIN开启一个事务，对test_user表中的数据加了排它锁：

会话2使用BEGIN开启一个事务，对test_user表中的数据进行快照读，可以读取到数据：

会话2再对test_user表中的数据加共享锁，50秒后会出现锁等待超时：

会话2再对test_user表中的数据加排它锁，50秒后也会出现现锁等待超时：

接着会话2再次对test_user表中的数据加排它锁，出现锁等待：

然后此时会话1对开启的事务进行提交操作，可以看到上面会话2中阻塞的排它锁查询已经将查询结果查询出来了

3.使用总结

lock in share mode适用于两张表存在业务关系时的一致性要求，for update适用于操作同一张表时的一致性要求。

例如主表(t_parent)与子表(into t_child)间具有关联关系，可能在往子表插入数据的过程中对应的主表数据被删除掉，那么这个时候该怎么办了？

解决方法：

1.业务方法上添加事务注解@Transactional

2.对主表的记录加共享锁，即select * from t_parent lock where id=1 in share mode;

3.然后再往子表中插入记录：insert into t_child (...,parent_id) values (...,1);