萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mysql教程 >> MySQL中的事務和鎖簡單用法測試

MySQL中的事務和鎖簡單用法測試

MySQL中的事務和鎖是非常的簡單了對於MySQL中的事務和鎖你了解多少呢,我今天一起來看看關於MySQL中的事務和鎖的一個介紹.

一直以來,對於MySQL中的事務和鎖的內容是淺嘗辄止,沒有花時間了解過,在一次看同事排查的故障中有個問題引起了我的興趣,雖然過去了很久,但是現在簡單總結一下還是有一些收獲。
首先我們初始化數據,事務的隔離級別還是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執行一條語句。
select * from test where id=1 for update;
再次查看事務表的信息,我們對比前後兩次的結果變化,發現唯一的不同是trx_lock_structs的地方,由0變為了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中執行語句會發生阻塞,因為存在相應的鎖等待。
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
這個地方和一個參數是有關聯的,innodb_lock_wait_timeout它會控制阻塞等待的時長。
[test]>show variables like '%innodb_lock_wait_timeout%';
| Variable_name            | Value |
| innodb_lock_wait_timeout | 120  |
對於事務相關的信息查看,在MySQL中有三個比較經典的數據字典,innodb_lock_waits,innodb_trx,innodb_trx,三者可以結合起來,就能夠查到相對比較完整的阻塞信息和事務的情況,官方提供的一個SQL如下:

我們簡稱為check_trx.sql,在這個場景中我們運行check_trx.sql會發現線程3573在等待,阻塞它的正是線程3574




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

測試死鎖
首先給表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
接下來來復現一下死鎖的情況。

1
開啟事務,更新id=1的那行數據。
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的那行數據。
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的數據行。
select * from test where id=2 for update;
查看事務表,會有一條阻塞的信息。


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

總體感覺MySQL的數據字典還是比較少,不過使用起來還是比較清晰。

死鎖原理與分析

本文前面的部分,基本上已經涵蓋了MySQL/InnoDB所有的加鎖規則。深入理解MySQL如何加鎖,有兩個比較重要的作用:

 
  • 可以根據MySQL的加鎖規則,寫出不會發生死鎖的SQL;  
  • 可以根據MySQL的加鎖規則,定位出線上產生死鎖的原因;
下面,來看看兩個死鎖的例子 (一個是兩個Session的兩條SQL產生死鎖;另一個是兩個Session的一條SQL,產生死鎖): 上面的兩個死鎖用例。第一個非常好理解,也是最常見的死鎖,每個事務執行兩條SQL,分別持有了一把鎖,然後加另一把鎖,產生死鎖。   第二個用例,雖然每個Session都只有一條語句,仍舊會產生死鎖。要分析這個死鎖,首先必須用到本文前面提到的MySQL加鎖的規則。針對Session 1,從name索引出發,讀到的[hdc, 1],[hdc, 6]均滿足條件,不僅會加name索引上的記錄X鎖,而且會加聚簇索引上的記錄X鎖,加鎖順序為先[1,hdc,100],後[6,hdc,10]。而Session 2,從pubtime索引出發,[10,6],[100,1]均滿足過濾條件,同樣也會加聚簇索引上的記錄X鎖,加鎖順序為[6,hdc,10],後[1,hdc,100]。發現沒有,跟Session 1的加鎖順序正好相反,如果兩個Session恰好都持有了第一把鎖,請求加第二把鎖,死鎖就發生了。   結論:死鎖的發生與否,並不在於事務中有多少條SQL語句,死鎖的關鍵在於:兩個(或以上)的Session加鎖的順序不一致。而使用本文上面提到的,分析MySQL每條SQL語句的加鎖規則,分析出每條語句的加鎖順序,然後檢查多個並發SQL間是否存在以相反的順序加鎖的情況,就可以分析出各種潛在的死鎖情況,也可以分析出線上死鎖發生的原因。
copyright © 萬盛學電腦網 all rights reserved