autocommit=0引起的業務hang住 ---轉自丁奇部落格

背景

有使用者報告一個普通的select 語句被hang住了，執行超時。查明之後發現是autocommit使用不當導致。
這裡將case簡化，說明覆現步驟及原因。 

復現

session1 建表並插入資料：

create table if not exists t(id int primary key, c int);
set autocommit=0;
insert into t values(1,1);
insert into t values(2,2);
insert into t values(3,3);
commit;
select count(*) from t;

這個執行流程的目的很直觀，建表、插入資料、查詢結果。貌似沒有問題。

維持session1不斷，新建一個連線session2，執行 create table if not exists t(id int primary key, c int);
此時該語句處於等待狀態.

再新建一個連線session3, 執行select count(*) from t; 該語句處於等待狀態.

於是從業務上看就是一個select 語句被hang住。 

原因分析

MySQL Tips:  如果服務中某些語句無法執行完成，追查問題時第一步要先保留現場，pstack <pid of mysqld> > tmplog之一個常用的方法。

這兩個等待執行緒的棧如：
#0  0x000000310ce0b7bb in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#1  0x000000000063ba46 in MDL_wait::timed_wait(THD*, timespec*, bool, char const*) ()
#2  0x000000000063e095 in MDL_context::acquire_lock(MDL_request*, unsigned long) ()

可以看到，堵在MDL_wait.

簡單說明下什麼是MDL。試想，如果一個語句在執行一個表上的查詢過程中，表結構被改了，或者表被drop，這樣會得到一個錯誤的結果。因此在一個事務持續期間，就需要對訪問的表結構作保護。這個就是meta data lock (MDL).
很容易理解的，對錶資料作增刪改查，需要對MDL加讀鎖，修改表結構、刪除表等操作則加寫鎖。

MySQL Tips: MDL是5.5才加入的機制，5.1版本下本文的case不會復現。 MySQL Tips: 事務中MDL申請時機是在首次使用時，釋放時機是在事務結束後。

也就是說文章開頭的這個case，原因是session2等待在加寫鎖過程。而session3雖然只是加讀鎖，但與session2衝突，也需要等待。 

session1的事務

也就是說session1還持有表t的MDL讀鎖。但我們的事務明明已經提交(commit)了。這裡就涉及到一個常見的誤解。以前有看過文章說，可以用set autocommit=0開啟一個事務。其實這個描述不準確.

MySQL Tips: set autocommit=0是將本執行緒設定為非自動提交模式。在每個事務結束後，下個語句開始時自動新建一個事務。

這就意味著，session1最後的那個select count(*)操作，實際上之前隱含了一個begin操作。由於該事務沒有提交，因此session1持有表t的MDL讀鎖。

因此對於業務方的建議就是，及時提交這些讀事務，或斷開連線。

MySQL Tips: 連線斷開時，MySQL會自動回滾當前未提交的事務。 由於本case裡面session1的最後一個事務只是一個select語句，因此回滾不影響業務。 

小結

1) 顯式的啟動事務的方法是begin或start transaction; 提交事務的方法是commit;
2) set autocommit=0的好處是在頻繁開啟事務的場景下，減少一次begin的互動。
3) 注意set autocommit=0修改了執行緒變數，會影響本執行緒存活期間的事務行為。
4) set autocommit=1可以提交事務並改變值。