mysql autocommit=0引起的業(yè)務(wù)hang住問(wèn)題分析

這篇文章主要講解了“mysql autocommit=0引起的業(yè)務(wù)hang住問(wèn)題分析”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“mysql autocommit=0引起的業(yè)務(wù)hang住問(wèn)題分析”吧！

背景

有用戶報(bào)告一個(gè)普通的select 語(yǔ)句被hang住了，執(zhí)行超時(shí)。查明之后發(fā)現(xiàn)是autocommit使用不當(dāng)導(dǎo)致。
這里將case簡(jiǎn)化，說(shuō)明復(fù)現(xiàn)步驟及原因。

復(fù)現(xiàn)

session1 建表并插入數(shù)據(jù)：

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;

這個(gè)執(zhí)行流程的目的很直觀，建表、插入數(shù)據(jù)、查詢結(jié)果。貌似沒(méi)有問(wèn)題。

維持session1不斷，新建一個(gè)連接session2，執(zhí)行 create table if not exists t(id int primary key, c int);
此時(shí)該語(yǔ)句處于等待狀態(tài).

再新建一個(gè)連接session3, 執(zhí)行select count(*) from t; 該語(yǔ)句處于等待狀態(tài).

于是從業(yè)務(wù)上看就是一個(gè)select 語(yǔ)句被hang住。

原因分析

MySQL Tips:  如果服務(wù)中某些語(yǔ)句無(wú)法執(zhí)行完成，追查問(wèn)題時(shí)第一步要先保留現(xiàn)場(chǎng)，pstack <pid of mysqld> > tmplog之一個(gè)常用的方法。

這兩個(gè)等待線程的棧如：
#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.

簡(jiǎn)單說(shuō)明下什么是MDL。試想，如果一個(gè)語(yǔ)句在執(zhí)行一個(gè)表上的查詢過(guò)程中，表結(jié)構(gòu)被改了，或者表被drop，這樣會(huì)得到一個(gè)錯(cuò)誤的結(jié)果。因此在一個(gè)事務(wù)持續(xù)期間，就需要對(duì)訪問(wèn)的表結(jié)構(gòu)作保護(hù)。這個(gè)就是meta data lock (MDL).
很容易理解的，對(duì)表數(shù)據(jù)作增刪改查，需要對(duì)MDL加讀鎖，修改表結(jié)構(gòu)、刪除表等操作則加寫鎖。

MySQL Tips: MDL是5.5才加入的機(jī)制，5.1版本下本文的case不會(huì)復(fù)現(xiàn)。 MySQL Tips: 事務(wù)中MDL申請(qǐng)時(shí)機(jī)是在首次使用時(shí)，釋放時(shí)機(jī)是在事務(wù)結(jié)束后。

也就是說(shuō)文章開(kāi)頭的這個(gè)case，原因是session2等待在加寫鎖過(guò)程。而session3雖然只是加讀鎖，但與session2沖突，也需要等待。

session1的事務(wù)

也就是說(shuō)session1還持有表t的MDL讀鎖。但我們的事務(wù)明明已經(jīng)提交(commit)了。這里就涉及到一個(gè)常見(jiàn)的誤解。以前有看過(guò)文章說(shuō)，可以用set autocommit=0開(kāi)啟一個(gè)事務(wù)。其實(shí)這個(gè)描述不準(zhǔn)確.

MySQL Tips: set autocommit=0是將本線程設(shè)置為非自動(dòng)提交模式。在每個(gè)事務(wù)結(jié)束后，下個(gè)語(yǔ)句開(kāi)始時(shí)自動(dòng)新建一個(gè)事務(wù)。

這就意味著，session1最后的那個(gè)select count(*)操作，實(shí)際上之前隱含了一個(gè)begin操作。由于該事務(wù)沒(méi)有提交，因此session1持有表t的MDL讀鎖。

因此對(duì)于業(yè)務(wù)方的建議就是，及時(shí)提交這些讀事務(wù)，或斷開(kāi)連接。

MySQL Tips: 連接斷開(kāi)時(shí)，MySQL會(huì)自動(dòng)回滾當(dāng)前未提交的事務(wù)。 由于本case里面session1的最后一個(gè)事務(wù)只是一個(gè)select語(yǔ)句，因此回滾不影響業(yè)務(wù)。

感謝各位的閱讀，以上就是“mysql autocommit=0引起的業(yè)務(wù)hang住問(wèn)題分析”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)mysql autocommit=0引起的業(yè)務(wù)hang住問(wèn)題分析這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！