MySQL 5.7 SELECT ... LOCK IN SHARE MODE|FOR UPDATE语句的说明是什么

本篇文章为大家展示了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

InnDB事务等待行锁的时间长度，默认值是50秒
mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.01 sec)

会话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语句的说明是什么，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注亿速云行业资讯频道。