MySQL單表模擬鎖的幾個場景

  在MySQL中對於併發，鎖問題總是會有很多值得討論的地方，但是通常來說，要模擬這些鎖或者一些鎖的問題需要花點功夫，比如建立多個表，建立大量的資料，然後像除錯鐘錶的秒針一樣，讓問題剛好復現在哪個時間點上。如果換一個角度，單表來模擬這類而是可以嗎，其實是可行的。

   今天簡單透過單表的測試模擬死鎖，事務中的隱式提交（其實可以理解是個bug),間隙鎖。

初始化資料

首先的準備工作就是初始化資料，我們建立一個表test,事務隔離級別為預設的RR。

建表語句：

create table test(

id int not null ,

name int ,

primary key(id),

unique key(name)

) engine=innodb;

事務隔離級別：

mysql> show variables like '%isolation%';

+---------------+-----------------+

| Variable_name | Value           |

+---------------+-----------------+

| tx_isolation  | REPEATABLE-READ |

+---------------+-----------------+

1 row in set (0.00 sec)

除此之外就是開啟兩個視窗，我們簡稱為會話1，會話2.

模擬死鎖

我們開始先模擬一下死鎖問題。

會話1：

我們開啟一個事務，插入一行記錄，資料就選做今天的日期吧。

mysql>begin；

mysql> insert into test values(2017,827);

Query OK, 1 row affected (0.01 sec)

會話2；

mysql> insert into test values(2016,827);

這個時候會話2會阻塞，這個時候有一種特殊的情況，那就是阻塞超時，如果超時，會自動停止。

會話1：

mysql> insert into test values(2018,826);

Query OK, 1 row affected (0.00 sec)

可見會話1中的DML操作依舊是可以的。

會話2：

mysql> insert into test values(2016,827);

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

如果看會話2的情況，就會發現產生了死鎖。

如果要嘗試事務隔離級別RC,其實表現的效果是一樣的。

仔細看看這個操作的過程就會發現，還是蠻“奇怪”的，資料之間彼此沒有直接的依賴關聯，怎麼會產生死鎖，這個裡面有銀式鎖升級，還有間隙鎖的一些東西，留給大家思考吧。

模擬意料之外的事務自動提交

為了基於上面的測試資料，讓兩條資料成功插入，我們在會話2中結束事務。

mysql>commit;

然後開始做意料之外的事務自動提交測試，這一次我們在同一個會話中測試即可。問題的背景是如果我們顯式宣告事務，在同一會話中做了DML操作，沒有提交，如果再開啟一個事務，之前的事務會自動提交。

會話1：

這是基於場景1的測試之後的資料情況。

mysql> select *from test;

+------+------+

| id   | name |

+------+------+

| 2018 |  826 |

| 2017 |  827 |

+------+------+

2 rows in set (0.00 sec)

我們顯式宣告一個事務。

mysql> begin;

Query OK, 0 rows affected (0.02 sec)

然後插入一條記錄，重新給一個日期。

mysql> insert into test values(2019,825);

Query OK, 1 row affected (0.00 sec)

這個時候沒有提交，我們在當前會話中重新再開啟一個事務。

mysql > begin;

mysql > insert into test values(2015,830);

這個時候如果在會話2中檢視，其實會發現，事務已經幫你提交了。

mysql> select *from test;

+------+------+

| id   | name |

+------+------+

| 2018 |  826 |

| 2017 |  827 |

+------+------+

2 rows in set (0.00 sec)

在會話1我們繼續回滾事務，會發現於事無補。

mysql> rollback;

Query OK, 0 rows affected (0.01 sec)

這個時候資料已經自動提交了一部分。

mysql> select *from test;

+------+------+

| id   | name |

+------+------+

| 2018 |  826 |

| 2017 |  827 |

+------+------+

2 rows in set (0.00 sec)

間隙鎖測試

上面的測試場景其實還是多多少少都有些關聯，其中第一個場景和間隙鎖也有關係，我就簡單用單表模擬一下間隙鎖。

首先還是保證事務隔離級別是RR,因為間隙鎖是RR隔離級別特供，RC中就沒有間隙鎖這樣的定製，在併發場景中還是有不小的影響。我們來看看效果。

會話1：

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

指定資料範圍，然後顯示宣告。

mysql> select id from test where id <2019 lock in share mode;

+------+

| id   |

+------+

| 2018 |

| 2017 |

+------+

2 rows in set (0.00 sec)

會話2：

會話2中也開啟一個事務，插入一條記錄。結果就被阻塞了。

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(2016,829);

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

直到事務超時才作罷。