[用事實說明兩個凡是]一個由mysql事務隔離級别造成的問題分析

最近要做一個批跑服務, 基本邏輯就是定時掃描資料庫的記錄, 有滿足條件的就進行處理(一條記錄代表一個任務,以下任務與記錄含義相同). 要求支援多機部署批跑服務.

要實作多機部署, 隻要保證每個批跑服務執行個體每次隻擷取一條記錄, 處理完再擷取下一條即可. 其中最種要的是避免不同的執行個體擷取到同一條記錄,即所謂搶任務.

先看表結構設計:

以上是簡化的表結構,但足以說明本文試圖說明的問題.

要避免搶任務, oracle的做法, 直接

即可.

mysql的要啰嗦一點:

這兩個sql,第一個sql用于擷取符合條件的任務, 第二個sql使用者将任務鎖定. 在并發的場景下, 有可能不同的批跑執行個體的第一個sql會傳回相同的記錄, 但第二個sql隻有一個會更新成功, 通過判斷affected rows即可知道哪個鎖定成功. 鎖定成功的繼續處理本任務, 鎖定失敗的繼續處理其它任務.

管理背景送出了一個任務後, 兩個批跑執行個體恰好同時啟動, 進入搶任務環節. 結果發現異常, 其中一個執行個體成功搶到任務, 但另一個執行個體則挂死了:

搶到任務的執行個體:

沒有搶到任務的執行個體:

可以看到沒有搶到任務的執行個體進入了死循環.

按照我們之前的設計, 如果第二條sql鎖定任務的時候失敗了, 擷取下一個任務. 應當不會死循環. 死循環的原因是因為沒有搶到任務的執行個體, 在執行第一個sql的時候, 一直傳回了相同的記錄(id=11,實際上當時也隻有一條記錄)

請注意, 搶到任務的執行個體搶到任務後, 會把狀态更新并送出, 按說搶不到任務的執行個體會看到此狀态更新,并導緻第一條sql查不到資料,然後 正常退出.

而事實上搶不到任務的執行個體看不到此變化, 說明事務隔離級别(transaction isolation level)不是"read commited", 而是其它. 經确認, 級别是"repeatable-read"

"repeatable-read" 看到的資料是事務啟動時的樣子,是以看不到搶到任務的執行個體對任務狀态的修改. 進而導緻死循環.

請注意執行第一個sql查詢滿足條件的任務是在一個事務内進行的. 此事務實際上是業務的需要, 除了擷取到任務,還需要擷取其它資源,如果擷取不到其它 資源, 則rollback任務,以便下次處理.

oracle相應的事務隔離級别是"serializable isolation level", 如上描述的這個場景, 在oracle下的反應是搶不到任務的執行個體在試圖更新任務狀态的 時候,會傳回一個"ora-08177: cannot serialize access for this transaction"錯誤, 程式也可以正常退出. 詳見<> 第9章"overview of oracle database transaction isolation levels"

mysql在"repeatable-read"的事務隔離級别上的表現是不能讓人滿意的. 查詢到的資料是事務啟動時的樣子,但更新的時候看到的資料又是其它事務送出 後的結果,并且update也沒有錯誤提示.

而"serializable"更糟糕, 如果同時開了兩個session, 幹脆直接鎖表了, 誰了更新不了. 這就勢必造成另一個問題, 既然大家都更新不了,那就rollback事務, 重試呗. 但是重試也是很有可能大家再同時開了事務,又鎖死了, 一直死循環. 為了解決這種情況,可能的做法是, 各自等待一個随機時間再重試,讓随機打破這個僵局. 不知道是否有其它辦法,歡迎指教.

修改session的事務隔離級别

1.不斷查詢滿足條件的任務不要放到一個事務裡. 發現"affected rows"為0,更新不到資料時, 事務rollback,重新啟動事務. 即在循環裡不斷開啟事務而不是在事務裡不斷循環.

還有一個辦法是開事務然後select for update, 但是這種方法會導緻鎖表, 必須等待其它事務送出後才能傳回. 當初我進行設計的時候,是計劃使用select for update的方式的, 但是最終沒有使用, 現在回想, 可能是沒有開事務, 結果兩個執行個體都查詢到了相同的記錄, 是以被我否定了. 但是看我另一個文章 <>又似乎可能是由于鎖表而棄用了, 原因已經不可考了.

但從本個需求來說, 似乎使用select for update來讓把表鎖住會更簡單.

你以為搶到任務的執行個體就可以高枕無憂了嗎, 錯了! 等他高高興興處理完任務, 要把任務狀态置為成功時, 發現這個任務居然被沒有搶到任務的執行個體給鎖了, 自已隻能得到一個鎖逾時的錯誤

請期待下一個問題分析.

今天回來确認了一下, 實際上oracel的update task set status = 1 where status = 0 and rownum = 1 returning taskid 這個sql也會把表鎖住.

是以可以用@flygogo 在30樓提出的方法模拟oracle 的returning

而postgresql的update似乎沒有limit 1之類的限定隻更新一條的寫法?

同時oracle和postgresql的select for update 也都會鎖表.

差點被繞暈了. 其實本文所指出的mysql在"repeatable-read"事務隔離級别下的表現是奇怪的,不直覺的,這點值得注意. 明明select出來的資料是可更新, 而更新時候又沒有成功, 會讓人非常疑惑. 而為oracel在"serializable"級别下發現資料已經被更新了之後,抛出"ora-08177"的做法才更直覺更合适.