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

本篇文章為大家展示了MySQL 5.7 SELECT ... LOCK IN SHARE MODE|FOR UPDATE語句的說明是什么，內(nèi)容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細(xì)介紹希望你能有所收獲。

在相同的事務(wù)中，如果你在查詢數(shù)據(jù)后，想插入或更新相關(guān)的數(shù)據(jù)，常規(guī)的SELECT語句無法提供足夠的保護(hù)。
其他事務(wù)可以更新或刪除你所查詢到的行。InnoDB引擎支持兩種讀鎖來提供額外的安全。

一、SELECT ... LOCK IN SHARE MODE在查詢到的行上設(shè)置共享鎖。其他回話可以讀取到這些行，但是不能修改這些行，直到提交事務(wù)后。
如果將LOCK IN SHARE MODE用在支持頁鎖或行鎖的存儲引擎上，查詢所覆蓋的行會被加上共享鎖，共享鎖允許其他事務(wù)讀，但是不允許其他事務(wù)更新或刪除這些行。

查看測試表中的數(shù)據(jù)，表中只有一個字段，且字段為主鍵
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，開啟一個事務(wù)，并對id范圍為10到30的數(shù)據(jù)增加共享鎖
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，可以執(zhí)行SELECT、INSERT操作，但是對會話1鎖定的行執(zhí)行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事務(wù)等待行鎖的時間長度，默認(rèn)值是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鎖定的行執(zhí)行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，提交事務(wù)
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用在支持頁鎖或行鎖的存儲引擎上，查詢所覆蓋的行會被加上寫鎖，直到當(dāng)前事務(wù)的結(jié)束。
其他回話可以讀取到這些行，但是不能修改這些行，直到提交事務(wù)后。

會話1，開啟一個事務(wù)，并對id范圍為10到30的數(shù)據(jù)增加寫鎖
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，可以執(zhí)行SELECT、INSERT操作，但是對會話1鎖定的行執(zhí)行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，提交事務(wù)
mysql> commit;
Query OK, 0 rows affected (0.06 sec)

會話2，之前阻塞的UPDATE操作會執(zhí)行成功
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)

上述內(nèi)容就是MySQL 5.7 SELECT ... LOCK IN SHARE MODE|FOR UPDATE語句的說明是什么，你們學(xué)到知識或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識儲備，歡迎關(guān)注億速云行業(yè)資訊頻道。