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


【AD】美国洛杉矶/香港/日本VPS推荐,回程电信CN2 GIA线路,延迟低、稳定性高、免费备份_搬瓦工

【AD】炭云:36元/年/1GB内存/20GB SSD空间/500GB流量/5Gbps端口/KVM/香港/国际线路LUMEN