MySQL資料庫讀寫鎖、事務隔離級別示例詳解

MySQL資料庫讀寫鎖示例詳解、事務隔離級別示例詳解

鎖

性能分：樂觀（比如使用version欄位比對，無需等待）、悲觀（需要等待其他事務）

樂觀鎖，如它的名字那樣，總是認為別人不會去修改，只有在提交更新的時候去檢查數據的狀態。通常是給數據增加一個欄位來標識數據的版本。
悲觀鎖，正如它的名字那樣，資料庫總是認為別人會去修改它所要操作的數據，因此在資料庫處理過程中將數據加鎖。其實現依靠資料庫底層。

讀鎖(共享鎖)、寫鎖(排他鎖) 均屬於悲觀鎖
粒度分：行級鎖、表級鎖

鎖示例

對錶加讀鎖：lock table tableName read

所有session可以讀；但是當前session 更新插入報錯，其他session 更新插入等待。比如：在數據遷移時，加讀鎖，防止任何session的更新操作。

對錶加寫鎖：lock table tableName write

當前session可以查詢、更新；其他session阻塞讀、寫。

讀鎖示範

創建user表

CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 主鍵id, `name` varchar(20) DEFAULT COMMENT 姓名, `age` int(11) DEFAULT 0 COMMENT 年齡, `salary` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT 薪水, `version` int(11) NOT NULL DEFAULT 0 COMMENT 版本號, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=用戶表;

插入數據

insert into `user` (`id`, `name`, `age`, `salary`, `version`) values(1,風清揚,60,2000.00,0); insert into `user` (`id`, `name`, `age`, `salary`, `version`) values(2,朱元璋,80,3000.00,0); insert into `user` (`id`, `name`, `age`, `salary`, `version`) values(3,孫猴子,50,4000.00,0);

打開資料庫session1、session2

在2個會話中查詢到剛才插入的數據。

mysql> select * from user; +----+--------+------+---------+---------+ | id | name | age | salary | version | +----+--------+------+---------+---------+ | 1 | 風清揚 | 60 | 2000.00 | 0 | | 2 | 朱元璋 | 80 | 3000.00 | 0 | | 3 | 孫猴子 | 500 | 4000.00 | 0 | +----+--------+------+---------+---------+ 3 rows in set (0.00 sec)

第一步：在session1中對錶user進行加讀鎖：

session1：

mysql> lock table user read; Query OK, 0 rows affected (0.00 sec)

第二步：session2進行讀、寫操作：

mysql> insert into `user` ( `name`, `age`, `salary`, `version`) values(唐玄奘,40,8000.00,0);

發現session2的插入操作會一直等待，等待session1釋放鎖。

第三步：session中進行讀沒問題，在session1中進行更新操作會直接報錯，提示表存在讀鎖不能被更新：

mysql> insert into `user` ( `name`, `age`, `salary`, `version`) values(唐玄奘,40,8000.00,0); ERROR 1099 (HY000): Table user was locked with a READ lock and cant be updated

第四步：釋放session1中的讀鎖

mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)

此時會發現session2中等待的插入操作立馬執行完畢：

mysql> insert into `user` ( `name`, `age`, `salary`, `version`) values(唐玄奘,40,8000.00,0); Query OK, 1 row affected (6 min 38.15 sec)

mysql>

第五步：session1給表1加讀鎖，查詢表2時報錯

## 對錶user加讀鎖 mysql> lock table user read; Query OK, 0 rows affected (0.00 sec)

## 可以對錶user進行查詢
mysql> select * from user limit 1;
+----+--------+------+---------+---------+
| id | name | age | salary | version |
+----+--------+------+---------+---------+
| 1 | 風清揚 | 60 | 2000.00 | 0 |
+----+--------+------+---------+---------+
1 row in set (0.00 sec)

## 查詢表employee時報錯
mysql> select * from employee limit 1;
ERROR 1100 (HY000): Table employee was not locked with LOCK TABLES

讀鎖總結：對錶加讀鎖，不影響任何session的讀操作，都是共享的。但是當前session執行更新操作會失敗報錯，其他session更新則會阻塞等待。因為會阻塞等待，所以讀鎖是悲觀鎖。對錶加讀鎖後，session僅僅能對當前表進行操作，不能對其表操作

讀鎖的特點： - 持有表A讀鎖的session可以讀表A，但是不能更新表A，也不能對其他表進行任何查詢、更新操作 - 其他session可以讀取表A，但是不能更新表A

寫鎖示範

第一步：session1 對錶user加寫鎖

mysql> lock table user write; Query OK, 0 rows affected (0.00 sec)

第二部：session1 查詢、更新操作不影響

mysql> select * from user; +----+--------+------+---------+---------+ | id | name | age | salary | version | +----+--------+------+---------+---------+ | 1 | 風清揚 | 60 | 2000.00 | 0 | | 2 | 朱元璋 | 80 | 3000.00 | 0 | | 3 | 孫猴子 | 500 | 4000.00 | 0 | | 4 | 唐玄奘 | 40 | 8000.00 | 0 | +----+--------+------+---------+---------+ 4 rows in set (0.00 sec)

mysql> insert into `user` ( `name`, `age`, `salary`, `version`) values(趙孟頫,20,1000.00,0);
Query OK, 1 row affected (0.04 sec)

第三步： session2 查詢、更新操作都會阻塞等待

sesion1查詢：

mysql> select * from user;

session2更新：

mysql> insert into `user` ( `name`, `age`, `salary`, `version`) values(王羲之,40,3000.00,0);

第四步：釋放session1的寫鎖，session2的更新操作立馬完成

session1的寫鎖釋放：

mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)

session2更新完成：

mysql> insert into `user` ( `name`, `age`, `salary`, `version`) values(王羲之,40,3000.00,0); Query OK, 1 row affected (3 min 3.12 sec)

第五步：session1給表1加寫鎖，查詢表2時報錯

## 對錶user加寫鎖 mysql> lock table user read; Query OK, 0 rows affected (0.00 sec)

## 查詢表employee時報錯
mysql> select * from employee limit 1;
ERROR 1100 (HY000): Table employee was not locked with LOCK TABLES

寫鎖總結：對錶加寫鎖，不影響當前session的操作，但是會影響其他session的讀寫操作，其他session任何操作都會阻塞等待。因為會阻塞等待，所以寫鎖是悲觀鎖。對錶加寫鎖後，session僅僅能對當前表進行操作，不能對其表操作

寫鎖的特點：

session持有表A的寫鎖，則session可以對錶A進行查詢、更新操作
排他性，其他session不能對錶A進行查詢、更新操作

讀寫鎖注意事項

對錶A加鎖後，當前session僅僅能操作表A

mysql> LOCK TABLES A READ; mysql> SELECT COUNT(*) FROM A; +----------+ | COUNT(*) | +----------+ | 3 | +----------+ mysql> SELECT COUNT(*) FROM B; ERROR 1100 (HY000): Table B was not locked with LOCK TABLES

如果加鎖時取了別名，則操作時只能使用別名

mysql> LOCK TABLE t AS myalias READ; mysql> SELECT * FROM t; ERROR 1100: Table t was not locked with LOCK TABLES mysql> SELECT * FROM t AS myalias;

不能在一次查詢中多次引用加鎖的表

mysql> LOCK TABLE t WRITE, t AS t1 READ; mysql> INSERT INTO t SELECT * FROM t; ERROR 1100: Table t was not locked with LOCK TABLES mysql> INSERT INTO t SELECT * FROM t AS t1;

並髮帶來的問題

更新丟失

臟讀

事務A讀取到了事務B修改的數據，但是事務B還未提交，事務B失敗回滾，則事務A讀取的無效，不符合一致性

幻讀

事務A多次讀取，後面讀取讀到了其他事務的提交新增的數據，導致查詢結果前後不一，比如開始查詢結果是1條，後面查詢是2條。不符合隔離性

不可重複讀

事務A多次讀取，後面讀取讀到了其他事務的提交，導致查詢的數據已經變化。比如開始讀取id為1的姓名是xiaoming，後面再次讀取發現姓名是lilei。不符合隔離性。

事務

A：原子性，全部執行全部不執行
C：一致性，開始和完成時數據狀態一致
I：隔離性，事務之間隔離
D：持久性，事務完成是永久的

指定事務特徵的語法

## 官方語法，可以設置事務隔離級別與訪問模式；用於InnoDB表。 SET [GLOBAL | SESSION] TRANSACTION transaction_characteristic [, transaction_characteristic] ...

transaction_characteristic: {
ISOLATION LEVEL level
| READ WRITE # 讀寫模式
| READ ONLY # 只讀模式
}

level: {
REPEATABLE READ
| READ COMMITTED
| READ UNCOMMITTED
| SERIALIZABLE
}

## 示例--將當前session的事務隔離級別設置為可重複讀
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;

等效於：

mysql> set tx_isolation = repeatable-read;
Query OK, 0 rows affected (0.00 sec)

## 設置訪問模式
SET TRANSACTION READ WRITE;

可選項 GLOBAL or SESSION 是用來指定執行的語句的範圍為全局或者當前session。

MySQL 設置事務隔離級別從低到高分別為：

讀未提交 : READ UNCOMMITTED
讀已提交 : READ COMMITTED
可重複讀 : REPEATABLE READ 默認級別
串列化 : SERIALIZABLE

注意事項：不允許在事務中指定沒有標明SESSION 或者 GLOBAL 的事務設置：

# 開啟一個事務 mysql> START TRANSACTION; Query OK, 0 rows affected (0.02 sec)

# 在開啟的事務中設置事務特徵是不允許的
mysql> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ERROR 1568 (25001): Transaction characteristics cant be changed
while a transaction is in progress

命令行設置隔離級別，可以參考：https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_transaction_isolation

## 命令行設置事務隔離級別 - set tx_isolation = REPEATABLE-READ | READ-UNCOMMITTED | READ-COMMITTED |SERIALIZABLE

命令行演示，使用begin、commit演示多個session之間的事務

事務隔離級別示例

Mysql InnoDB 默認是可重複讀。

Mysql查看隔離級別命令

## 查看隔離級別 show variables like tx_isolation

讀未提交 READ UNCOMMITTED

SET TRANSACTION ISOLATION LEVEL READ UMCOMMITED;

事務A可以讀取事務B未提交的事務，會出現臟讀。

讀已提交 READ COMMITTED

讀提交都是讀取自身session的最新快照。當通過鎖讀時（SELECT with FOR UPDATE or FOR SHARE），InnoDB 只會鎖索引行記錄，並不是他們之間的範圍記錄，如此便會允許其他事務插入新的記錄。間隙鎖gap lock只會用在外鍵約束去檢查重複鍵。

因為間隙鎖不可用，所以可能出現幻讀，其他session可以在間隙中插入數據。

只有基於行級的二進位日誌支持 READ COMMITED 的隔離級別。如果使用 binlog_format = MIXED, 伺服器自動使用基於行級的日誌。

使用讀提交的隔離級別，會有額外影響：

對於UPDATE、DELETE語句，InnoDB僅僅持有更新或者刪除的行的鎖。MySQL在評估完WHERE條件之後，會釋放不匹配的行的鎖，大大減少了思索的可能性，但是還是有可能會發生死鎖的。對於UPDATE語句，如果一條記錄已經被鎖了，InnoDB會採取「半一致性」的讀方式，會返回最新的已經提交的版本記錄給MySQL，來匹配WHERE條件是否匹配。如果存在記錄匹配到了（必須會被UPDATE的），MySQL會再次讀取這些記錄並且這次 InnoDB 要麼鎖定它要麼等待它的鎖。

這裡看一個官方示例。

CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB; INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2); COMMIT;

在這個示例中，表時沒有索引的，所以查詢或者索引掃描時，會採用隱藏的集羣索引來鎖住記錄行。

Session A 開啟事務，並執行 UPDATE 操作：

# Session A START TRANSACTION; UPDATE t SET b = 5 WHERE b = 3; # 沒有用到索引會全表掃描，所有記錄都會鎖住

此時，session A 事務並未結束，在另一個 Session B 中執行 UPDATE操作：

# Session B UPDATE t SET b = 4 WHERE b = 2;

因為 InnoDB 執行每一個 UPDATE 操作，首先會為每一行記錄獲取可執行鎖，然後才決定是否去更新。如果 InnoDB 沒有更新記錄行，就會釋放鎖。否則， InnoDB 會持有鎖直到事務結束。這個示例影響事務如下：

當使用 REPEATABLE READ 級別時，第一個 UPDATE 獲取到了每一行的 x-lock，事務A 讀取並且沒有釋放它們：

x-lock(1,2); retain x-lock x-lock(2,3); update(2,3) to (2,5); retain x-lock x-lock(3,2); retain x-lock x-lock(4,3); update(4,3) to (4,5); retain x-lock x-lock(5,2); retain x-lock

此時事務B進行UPDATE，嘗試進行鎖，等待...

x-lock(1,2); block and wait for first UPDATE to commit or roll back

而如果是 READ COMMITED 級別，事務A獲得了鎖並且會釋放那些不匹配的行記錄：

x-lock(1,2); unlock(1,2) x-lock(2,3); update(2,3) to (2,5); retain x-lock x-lock(3,2); unlock(3,2) x-lock(4,3); update(4,3) to (4,5); retain x-lock x-lock(5,2); unlock(5,2)

事務B的UPDATE操作時，事務B會採取「半一致性」讀方式，會讀取最近已經提交的版本記錄，決定哪些是匹配的：

x-lock(1,2); update(1,2) to (1,4); retain x-lock x-lock(2,3); unlock(2,3) x-lock(3,2); update(3,2) to (3,4); retain x-lock x-lock(4,3); unlock(4,3) x-lock(5,2); update(5,2) to (5,4); retain x-lock

不管怎樣，如果where條件包含一個索引列，InnoDB 就會使用索引，只有索引列會考慮持有鎖。在下面的例子中，事務A的 UPDATE 操作持有每一個b=2的記錄的 x-lock 鎖。事務B會阻塞因為它也試圖獲得相同記錄的鎖。

CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB; INSERT INTO t VALUES (1,2,3),(2,2,4); COMMIT;

# Session A
START TRANSACTION;
UPDATE t SET b = 3 WHERE b = 2 AND c = 3;

# Session B 會阻塞
UPDATE t SET b = 4 WHERE b = 2 AND c = 4;

設置事務隔離級別為讀提交：

SET TRANSACTION ISOLATION LEVEL READ COMMITED;

事務B提交後，事務A纔可能讀取到事務B的數據滅有臟讀，但是可能幻讀、不可重複讀同一個事務中多次查詢結果可能是不一致的。示例：事務A先讀取id為1的姓名為xiaoming；事務B提交改為lilei；事務A再次讀取時讀到lilei。這樣事務A兩次讀取的數據不一致，出現了幻讀，不可重複讀。按業務來講，事務A應該第二次讀取到的是xiaoming，因為事務A可能在第一次讀到xiaoming時已經利用它實現了一些業務操作了。所以出現了不可重複讀。

可重複讀 REPEATABLE READ

set tx_isolation = repeatable-read; 或者 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Mysql InnoDB默認隔離級別。可以解決上述的案例問題。同一個事務中多次查詢結果是一致的。沒法解決幻讀。

示例：事務A讀取薪水是2000，事務B改為3000並提交事務，事務A再次讀取還是2000；事務A將薪水翻倍改為salary*2以為變成了4000，結果事務A查詢發現是6000了。如果使用的是set salary=4000，那就是4000(所以這是一種錯誤的方式)。事務A拿到了事務B的數據，產生了幻讀。

演示幻讀：事務A作更新後，再次查詢就出現了幻讀。

【select讀取的是快照版本，更新讀取的是真實數據。】

MVVC機制：多版本並發控制機制，使用快照版本數據達到可重複讀，但是快照版本是不可信的，無法解決幻讀問題。

可串列化 SERIALIZABLE

set tx_isolation = SERIALIZABLE;

效率低，可以解決臟讀、不可重複讀、幻讀；因為它將事務串列化了，沒有了並發事務。

Mysql間隙鎖

也叫Gap lock。

事務A使用範圍update時，假如表中只有id1-11的數據，mysl會對這些記錄比如update table where id > 10 and id <20；會對id為11--19的數據都加鎖，即便此時資料庫並沒有這些id為12--19的記錄；此時其他事務B來插入這樣的記錄insert id 為12 時，則事務B會等待，因為id12被事務A鎖了。

死鎖

事務A：select * from table where id = 1 for update;

事務B：select * from table where id = 2 for update;

事務A：select * from table where id = 2 for update;

事務B：select * from table where id = 1 for update;

查看死鎖日誌：

show engine innodb statusG

MySQL資料庫讀寫鎖、事務隔離級別示例詳解

MySQL資料庫讀寫鎖示例詳解、事務隔離級別示例詳解

鎖

鎖示例

對錶加讀鎖：lock table tableName read

對錶加寫鎖：lock table tableName write

讀鎖示範

創建user表

插入數據

打開資料庫session1、session2

第一步：在session1中對錶user進行加讀鎖：

第二步：session2進行讀、寫操作：

第三步：session中進行讀沒問題，在session1中進行更新操作會直接報錯，提示表存在讀鎖不能被更新：

第四步： 釋放session1中的讀鎖

第五步：session1給表1加讀鎖，查詢表2時報錯

寫鎖示範

第一步：session1 對錶user加寫鎖

第二部：session1 查詢、更新操作不影響

第三步： session2 查詢、更新操作都會阻塞等待

第四步： 釋放session1的寫鎖，session2的更新操作立馬完成

第五步：session1給表1加寫鎖，查詢表2時報錯

讀寫鎖注意事項

對錶A加鎖後，當前session僅僅能操作表A

如果加鎖時取了別名，則操作時只能使用別名

不能在一次查詢中多次引用加鎖的表

並髮帶來的問題

更新丟失

臟讀

幻讀

不可重複讀

事務

指定事務特徵的語法

事務隔離級別示例

讀未提交 READ UNCOMMITTED

讀已提交 READ COMMITTED

可重複讀 REPEATABLE READ

可串列化 SERIALIZABLE

Mysql間隙鎖

死鎖

mysql百萬級表在不停機的情況下增加一個欄位要怎麼處理的？

剛學習mysql就進行不下去了，能幫我看一下什麼問題嗎？

請問怎麼用php mysql 製作一張網頁？

mysql 導入 很大的 CSV ?

workbench 無法執行命令？

怎麼實現一個簡單的資料庫系統？

sql資料庫中什麼情況該加索引index？

條件：mysql中多個表的欄位完全相同 需求：在增/刪/改欄位時，同時修改這多個表？請問相關工具和辦法

為瞭解決OLTP，希望做分散式，支持分詞，應該如何選資料庫？PostgreSQL 與 MySQL ？

PHP不能直接調用mysql嗎？需要安裝gd庫才能調用？

怎樣實現資料庫裏的年齡可以隨著時間增加而增長（用MySQL）？

為什麼elasticsearch很適合日誌系統？在日誌方面相比與mysql的優勢是什麼？

SQL Server與Mysql，如何選擇？

MySQL 用於產品環境，應該如何選用版本？

mysql冗餘設計?

熱門新聞

週熱門

第四步：釋放session1中的讀鎖

第四步：釋放session1的寫鎖，session2的更新操作立馬完成

mysql 導入很大的 CSV ?

條件：mysql中多個表的欄位完全相同需求：在增/刪/改欄位時，同時修改這多個表？請問相關工具和辦法