MySQL 5.7 SELECT ... LOCK IN SHARE MODE|FOR UPDATE语句说明

迷南。 2024-04-18 00:18 56阅读 0赞

在相同的事务中，如果你在查询数据 后 ，想插入或更新相关的数据，常规的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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2123438/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2123438/