怎樣進行MySQL中的事務和鎖簡單測試

180次閱讀

共計 2662 個字符，預計需要花費 7 分鐘才能閱讀完成。

本篇文章為大家展示了怎樣進行 MySQL 中的事務和鎖簡單測試，內(nèi)容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。

一直以來，對于 MySQL 中的事務和鎖的內(nèi)容是淺嘗輒止，沒有花時間了解過，在一次看同事排查的故障中有個問題引起了我的興趣，雖然過去了很久，但是現(xiàn)在簡單總結(jié)一下還是有一些收獲。
首先我們初始化數(shù)據(jù)，事務的隔離級別還是 MySQL 默認的 RR，存儲引擎為 InnoDB
create table test(id int,name varchar(30));
insert into test values(1, aa
開啟一個會話，開啟事務。
會話 1：
[test] start transaction;

這個時候我們查看 show processlist 的信息是不會看到更為具體的 SQL 等的信息。

我們在另外一個會話中查看事務相關的一個表,Innodb_trx, 其實它對應的存儲引擎是 MEMORY
[information_schema] select *from innodb_trx\G

然后在會話 1 執(zhí)行一條語句。
select * from test where id=1 for update;
再次查看事務表的信息，我們對比前后兩次的結(jié)果變化，發(fā)現(xiàn)唯一的不同是 trx_lock_structs 的地方，由 0 變?yōu)榱?2

對于這個字段的含義，可以參考官方文檔的介紹。
https://dev.mysql.com/doc/refman/5.6/en/innodb-trx-table.html

對于字段 TRX_LOCK_STRUCTS 的官方解釋如下：
The number of locks reserved by the transaction.

會話 2：
這個時候在會話 2 中執(zhí)行語句會發(fā)生阻塞，因為存在相應的鎖等待。
select * from test where id=1 for update;
等待一段時間，會話 2 就會提示超時。
[test] select * from test where id=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
這個地方和一個參數(shù)是有關聯(lián)的，innodb_lock_wait_timeout 它會控制阻塞等待的時長。
[test] show variables like %innodb_lock_wait_timeout%
| Variable_name | Value |
| innodb_lock_wait_timeout | 120 |
對于事務相關的信息查看，在 MySQL 中有三個比較經(jīng)典的數(shù)據(jù)字典，innodb_lock_waits,innodb_trx,innodb_trx，三者可以結(jié)合起來，就能夠查到相對比較完整的阻塞信息和事務的情況，官方提供的一個 SQL 如下：

我們簡稱為 check_trx.sql，在這個場景中我們運行 check_trx.sql 會發(fā)現(xiàn)線程 3573 在等待，阻塞它的正是線程 3574

這個時候有一個地方需要注意，那就是通過 show engine innodb status 得到的結(jié)果中，標紅的部分可以看出鎖是表級鎖。這個還是和表的結(jié)構(gòu)有一定的關系。
我們可以換一個方式來測試完善，比如測試一下死鎖。

測試死鎖
首先給表 test 添加一條記錄
insert into test values(2, bb
為了杜絕表級鎖，對表 test 添加主鍵, 如果采用下面的方式添加主鍵，竟然不可以，看來 Oracle 用慣了，很多思維方式要復制過來，SQL 語法還是有不少地方需要注意。
[test] alter table test modify id primary key;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server vline 1。。。
可以使用下面的方式來添加主鍵。
[test] ALTER TABLE test ADD UNIQUE INDEX (id), ADD PRIMARY KEY (id);
Query OK, 2 rows affected (0.25 sec)
Records: 2 Duplicates: 0 Warnings: 0
接下來來復現(xiàn)一下死鎖的情況。

會話 1：
開啟事務，更新 id= 1 的那行數(shù)據(jù)。
start transaction;
[test] select * from test where id=1 for update;
+—-+——+
| id | name |
+—-+——+
| 1 | aa |
+—-+——+
1 row in set (0.00 sec)
這個時候查看 innodb_trx 的信息，只有 1 條記錄。

會話 2：
開啟事務，更新 id= 2 的那行數(shù)據(jù)。
start transaction;
select * from test where id=2 for update;
(root:localhost:Sat Oct 8 18:15:10 2016)[test] select * from test where id=2 for update;
+—-+——+
| id | name |
+—-+——+
| 2 | bb |
+—-+——+
1 row in set (0.00 sec)
這個時候兩者是不存在阻塞的情況，因為彼此都是影響獨立的行。
source check_trx.sql
Empty set (0.00 sec)
查看事務表，里面就是 2 條記錄了。

會話 1：
在會話 1 中修改 id= 2 的數(shù)據(jù)行。
select * from test where id=2 for update;
查看事務表，會有一條阻塞的信息。

會話 2：
在會話 2 中修改 id= 1 的數(shù)據(jù)行，這個時候會發(fā)現(xiàn)存在死鎖，而 MySQL 會毫不猶豫的清理掉阻塞的那個會話。這個過程是自動完成的。
[test] select * from test where id=1 for update;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
查看阻塞的信息，就會發(fā)現(xiàn)已經(jīng)被清理掉了。
[(none)] source check_trx.sql
Empty set (0.00 sec)
查看事務表，會發(fā)現(xiàn)只有 1 條記錄了。

總體感覺 MySQL 的數(shù)據(jù)字典還是比較少，不過使用起來還是比較清晰。