MySQL 5.7 SELECT … LOCK IN SHARE MODE

104次阅读

共计 3294 个字符，预计需要花费 9 分钟才能阅读完成。

本篇文章为大家展示了 MySQL 5.7 SELECT … LOCK IN SHARE MODE|FOR UPDATE 语句的说明是什么，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。

在相同的事务中，如果你在查询数据后，想插入或更新相关的数据，常规的 SELECT 语句无法提供足够的保护。
其他事务可以更新或删除你所查询到的行。InnoDB 引擎支持两种读锁来提供额外的安全。

一、SELECT … LOCK IN SHARE MODE 在查询到的行上设置共享锁。其他回话可以读取到这些行，但是不能修改这些行，直到提交事务后。
如果将 LOCK IN SHARE MODE 用在支持页锁或行锁的存储引擎上，查询所覆盖的行会被加上共享锁，共享锁允许其他事务读，但是不允许其他事务更新或删除这些行。

查看测试表中的数据，表中只有一个字段，且字段为主键
mysql desc t5;
+——-+———————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+———————+——+—–+———+——-+
| id | tinyint(3) unsigned | NO | PRI | NULL | |
+——-+———————+——+—–+———+——-+
1 row in set (0.01 sec)
mysql show create table t5;
+——-+———————————————————————————————————————-+
| Table | Create Table |
+——-+———————————————————————————————————————-+
| t5 | CREATE TABLE `t5` (
`id` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+——-+———————————————————————————————————————-+
1 row in set (0.00 sec)
mysql select * from t5;
+—-+
| id |
+—-+
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
+—-+
5 rows in set (0.00 sec)

会话 1，开启一个事务，并对 id 范围为 10 到 30 的数据增加共享锁
mysql start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql select * from t5 where id = 10 and id =30 lock in share mode;
+—-+
| id |
+—-+
| 10 |
| 20 |
| 30 |
+—-+
3 rows in set (0.00 sec)

会话 2，可以执行 SELECT、INSERT 操作，但是对会话 1 锁定的行执行 UPDATE 操作会阻塞，直到超过 InnoDB 锁的等待时间，会话 2 会自动回滚
mysql start transaction;
Query OK, 0 rows affected (0.00 sec)
+—-+
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
| 60 |
+—-+
6 rows in set (0.00 sec)
mysql insert into t5 values(60);
Query OK, 1 row affected (0.04 sec)
mysql update t5 set id=100 where id=10;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

会话 2，对会话 1 锁定的行执行 DELETE 操作会阻塞

mysql start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql delete from t5 where id=30;
Query OK, 1 row affected (15.31 sec)

会话 1，提交事务
mysql commit;
Query OK, 0 rows affected (0.06 sec)

会话 2，之前阻塞的 DELETE 操作会生效
mysql delete from t5 where id=30;
Query OK, 1 row affected (15.31 sec)

mysql rollback;

Query OK, 0 rows affected (0.02 sec)

二、如果将 FOR UPDATE 用在支持页锁或行锁的存储引擎上，查询所覆盖的行会被加上写锁，直到当前事务的结束。
其他回话可以读取到这些行，但是不能修改这些行，直到提交事务后。

会话 1，开启一个事务，并对 id 范围为 10 到 30 的数据增加写锁
mysql start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql select * from t5 where id = 10 and id =30 for update;
+—-+
| id |
+—-+
| 10 |
| 20 |
| 30 |
+—-+
3 rows in set (0.00 sec)

会话 2，可以执行 SELECT、INSERT 操作，但是对会话 1 锁定的行执行 UPDATE 操作会阻塞，直到超过 InnoDB 锁的等待时间，会话 2 会自动回滚
mysql start transaction;
Query OK, 0 rows affected (0.00 sec)
+—-+
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
| 60 |
+—-+
6 rows in set (0.00 sec)
mysql insert into t5 values(70);
Query OK, 1 row affected (0.04 sec)

mysql update t5 set id=100 where id=10;

会话 1，提交事务
mysql commit;
Query OK, 0 rows affected (0.06 sec)

会话 2，之前阻塞的 UPDATE 操作会执行成功
mysql update t5 set id=100 where id=10;
Query OK, 1 row affected (15.38 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql rollback;
Query OK, 0 rows affected (0.04 sec)

上述内容就是 MySQL 5.7 SELECT … LOCK IN SHARE MODE|FOR UPDATE 语句的说明是什么，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注丸趣 TV 行业资讯频道。

正文完