誤用autocommit引起的業務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可以送出事務并改變值，可以與set autocommit=0成對出現。

阿裡雲rds feature

當出現如上的錯誤用法時，修改業務代碼需要一段時間。

針對這種case，阿裡雲rds版本提供了trx_idle_timeout參數，設定空閑事務停留時間。當超過設定的值（機關為秒）後，連接配接自動斷開。

比如設定為10，在示例的case裡面，sessino1在空閑10s後斷開連接配接，session2繼續執行，之後session3繼續執行。

該值預設為0。