MySQL 事務的隔離級別及鎖操作的一點點演示

WindWant發表於2022-02-09
MySql

MySQL 版本：5.7

安裝環境：MAC OS

一、測試資料

測試資料庫：test；測試表：tt

CREATE TABLE `tt` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  KEY `name_idx` (`name`),
  KEY `id_idx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入測試資料：

insert into tt value(1, "a”);
insert into tt value(1, "b”);
insert into tt value(2, “b");

二、事務隔離級別設定

1、查詢當前事務隔離級別

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+

2、設定當前事務隔離級別

set global transaction_isolation = 'read-uncommitted';

設定隔離級別後，後續開啟的連線 Session 才會生效。

三、讀未提交（READ-UNCOMMITTED）

開啟兩個連線 Session：

Session 1 Session 2

開啟事務，更新 id 為 2 的記錄 name 為 “ss" ，保持事務未提交：

Query OK, 0 rows affected (0.00 sec)
mysql> select * from tt;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    1 | b    |
|    2 | b    |
+------+------+
3 rows in set (0.00 sec)
mysql> update tt set name = 'ss' where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
  
 

開啟事務，查詢 id 為 2 的記錄 name 值：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tt where id = 2;
+------+------+
| id   | name |
+------+------+
|    2 | ss   |
+------+———+ 

事務 2 可以查詢到事務 1 未提交的資料變更。對於事務 2 來說，這條資料是髒資料。

四、讀已提交（READ-COMMITTED）

解決 READ-UNCOMMITTED 隔離級別下產生的髒讀現象。

設定事務隔離級別：

mysql> set global transaction_isolation = 'read-committed';
Query OK, 0 rows affected (0.00 sec)

重新開啟測試 Session，查詢事務隔離級別：

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
 
Session 1 Session 2
開啟事務，更新 id 為 2 的記錄 name 為 “ssr”：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> update tt set name = 'ssr' where id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from tt where id = 2;
+------+------+
| id   | name |
+------+------+
|    2 | ssr  |
+------+------+
1 row in set (0.01 sec)
  
 
查詢資料，無法查詢到 事務 1 未提交的資料：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from tt where id = 2;
+------+------+
| id   | name |
+------+------+
|    2 | b    |
+------+------+
1 row in set (0.00 sec) 
提交事務：
mysql> commit ;
Query OK, 0 rows affected (0.01 sec)
  
 
查詢資料，得到的是事務 1 中已提交的資料變更：
mysql> select * from tt where id = 2;
+------+------+
| id   | name |
+------+------+
|    2 | ssr    |
+------+------+
1 row in set (0.00 sec)
 
對於事務 2 來說，在事務 1 提交前後，獲取到的資料是不一樣的，即不可重複讀問題。

五、可重複讀（REPEATABLE-READ）

解決 READ-COMMITTED 隔離級別下產生的不可重複讀現象。

Session 1中 設定事務隔離級別： 

mysql> set global transaction_isolation = 'repeatable-read';
Query OK, 0 rows affected (0.01 sec)

重新開啟事務，查詢隔離級別：

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

 

Session 1 Session 2

 

Session 2 開啟事務，查詢資料：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
Database changed
mysql> select * from tt where id = 2;
+------+------+
| id   | name |
+------+------+
|    2 | b    |
+------+------+
1 row in set (0.00 sec)

更新 id 為 2 的記錄 name 為 “ssrr”， 並提交事務：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update tt set name = 'ssrr' where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from tt where id = 2;
+------+------+
| id   | name |
+------+------+
|    2 | ssrr |
+------+------+
1 row in set (0.00 sec)  

 

 

Session 2 重新查詢資料：

mysql> select * from tt where id = 2;
+------+------+
| id   | name |
+------+------+
|    2 | b    |
+------+------+

當前資料未變。

但是問題是，事務 1 已經進行了資料變更，並且提交，事務 2 無法獲取所查記錄最新變更資訊。

為什麼事務 2 前後兩次相同查詢所得的資料是一樣的？

一致性讀（consistent read）查詢模式：基於【某一時刻】的【資料快照】提供讀查詢結果。無論查詢的資料是否被其它事務所改變。這個【某一時刻】在 repeatable-read 隔離級別下為事務中第一次執行查詢操作的時間點，read-committed 隔離級別下，資料快照會在每一次執行一致性讀操作時進行重置。

幻讀

如何避免：加X鎖

Next-key lock：Record lock + Gap lock

六、關於 Next-key lock 加鎖

調整表 tt 索引及資料：

mysql> show create table tt;
+-------+-------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                      |
+-------+-------------------------------------------------------+
| tt    | CREATE TABLE `tt` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from tt;
+-----+------+------+
| id  | name | age  |
+-----+------+------+
|  90 | aa   |   10 |
| 102 | bb   |   15 |
| 108 | cc   |   20 |
| 130 | dd   |   25 |
| 150 | ee   |   30 |
+-----+------+------+

1、等值條件

對於使用唯一性索引：加的鎖為 Record lock

Session 1 Session 2

開啟事務，查詢 id 為 108 記錄加 X lock：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from tt where id = 108 for update;
+-----+------+------+
| id  | name | age  |
+-----+------+------+
| 108 | cc   |   20 |
+-----+------+------+
1 row in set (0.01 sec)
  
 

開啟事務，記錄前後緊鄰 gap 插入記錄：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into tt value(106, 'bc', 16);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into tt value(110, 'cd', 22);
Query OK, 1 row affected (0.00 sec)
記錄均可成功插入

對於使用非唯一性索引：加的鎖為 Record lock + Gap lock 前後緊鄰 gap

：首先加鎖 (15, 20]，因為是非唯一索引，繼續向後查詢到第一個不滿足條件的元素 25 加 gap lock (20, 25)

Session 1 Session 2

開啟事務，查詢 age 為 20 記錄加 X lock： 

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from tt where age = 20 for update;
+-----+------+------+
| id  | name | age  |
+-----+------+------+
| 108 | cc   |   20 |
+-----+------+------+
1 row in set (0.00 sec)
  
 

開啟事務，記錄緊鄰前後 gap 插入記錄：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tt value(106, 'bc', 18);
 
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
 
mysql> insert into tt value(110, 'cd', 22);
 
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

操作均被 block。

緊鄰 gap 以外插入記錄：

mysql> insert into tt value(100, 'ab', 12);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into tt value(140, 'de', 27);
Query OK, 1 row affected (0.00 sec)

記錄均可成功插入

對於不使用索引的：加鎖為全部記錄及gap 

Session1
 Session2

開啟事務，查詢 name 為 ‘cc’ 記錄加 X lock：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from tt where name = 'cc' for update;
+-----+------+------+
| id  | name | age  |
+-----+------+------+
| 108 | cc   |   20 |
+-----+------+------+
  
 

開啟事務，各個間隙嘗試插入記錄：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into tt value(80, 'pa', 5);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into tt value(95, 'ab', 13);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into tt value(105, 'bc', 18);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into tt value(120, 'cd', 23);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into tt value(140, 'de', 28);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into tt value(160, 'en', 35);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

操作均被 block。

更新記錄：

mysql> update tt set age = 21 where name = 'cc';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update tt set age = 16 where name = 'bb';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

操作均被 block。

相關文章