在相同的事務中，如果你在查詢資料後，想插入或更新相關的資料，常規的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語句說明

相關文章