Mysql

在 MySQL/InnoDB 中為事務設置時間限制

  • November 10, 2021

這源於這個相關的問題,我想知道如何在一個簡單的情況下強制兩個事務按順序發生(兩者都只在一行上執行)。我得到了一個答案——SELECT ... FOR UPDATE用作兩個事務的第一行——但這會導致一個問題:**如果第一個事務從未送出或回滾,那麼第二個事務將被無限期地阻塞。**該innodb_lock_wait_timeout變數設置嘗試進行第二次事務的客戶端將被告知“抱歉,再試一次”的秒數……但據我所知,他們會再次嘗試直到下一次伺服器重新啟動。所以:

  1. ROLLBACK如果交易永遠持續下去,肯定有一種方法可以強制執行嗎?我必須求助於使用守護程序來終止此類事務嗎?如果是,那麼這樣的守護程序會是什麼樣子?
  2. 如果連接被事務殺死wait_timeoutinteractive_timeout在事務中途終止,事務是否回滾?有沒有辦法從控制台測試這個?

Clarificationinnodb_lock_wait_timeout設置事務在放棄之前等待鎖釋放的秒數;我想要的是一種強制釋放鎖的方法。

更新 1:這是一個簡單的範例,說明為什麼innodb_lock_wait_timeout不足以確保第二個事務不被第一個阻塞:

START TRANSACTION;
SELECT SLEEP(55);
COMMIT;

在預設設置下innodb_lock_wait_timeout = 50,此事務在 55 秒後無錯誤地完成。如果您UPDATE在該行之前添加一個SLEEP,然後從另一個嘗試到SELECT ... FOR UPDATE同一行的客戶端啟動第二個事務,則超時的是第二個事務,而不是睡著的那個。

我正在尋找的是一種方法來強制結束這筆交易的平靜睡眠。

更新 2:為了回應 hobodave 對上述範例的真實性的擔憂,這裡有一個替代方案:DBA 連接到實時伺服器並執行

START TRANSACTION
SELECT ... FOR UPDATE

其中第二行鎖定了應用程序經常寫入的行。然後 DBA 被打斷並走​​開,忘記結束事務。應用程序停止執行,直到該行被解鎖。我想盡量減少由於這個錯誤導致應用程序卡住的時間。

這個文章有一半以上似乎是關於如何在 ServerFault 上提問。我認為這個問題很有道理,也很簡單:你如何自動回滾停滯的事務?

如果您願意終止整個連接,一種解決方案是設置 wait_timeout/interactive_timeout。請參閱https://stackoverflow.com/questions/9936699/mysql-rollback-on-transaction-with-lost-disconnected-connection

由於您的問題是在 ServerFault 上提出的,因此假設您正在尋求 MySQL 問題的 MySQL 解決方案是合乎邏輯的,特別是在系統管理員和/或 DBA 具有專業知識的知識領域。因此,以下部分解決您的問題:

如果第一個事務從未送出或回滾,則第二個事務將被無限期阻塞

不,不會的。我想你不明白innodb_lock_wait_timeout。它完全符合您的需要。

如手冊中所述,它將返回錯誤:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
  • 根據定義,這不是無限期的。如果您的應用程序反復重新連接和阻塞,那麼您的應用程序是“無限期阻塞”,而不是事務。第二筆交易非常明確地阻塞了innodb_lock_wait_timeout幾秒鐘。

預設情況下,事務不會回滾。您的應用程式碼有責任決定如何處理此錯誤,無論是重試還是回滾。

如果要自動回滾,手冊中也有說明:

目前事務沒有回滾。(要使整個事務回滾,請使用該--innodb_rollback_on_timeout選項啟動伺服器。


回复:您的眾多更新和評論

首先,您在評論中表示您“打算”說您想要一種方法來超時第一個無限期阻塞的事務。這在您最初的問題中並不明顯,並且與“如果第一個事務從未送出或回滾,那麼第二個事務將被無限期地阻止”相衝突。

儘管如此,我也可以回答這個問題。MySQL 協議沒有“查詢超時”。這意味著您不能使第一個被阻止的事務超時。您必須等到它完成,或者終止會話。當會話被終止時,伺服器將自動回滾事務。

唯一的另一種選擇是使用或編寫一個利用非阻塞 I/O 的 mysql 庫,這將允許您的應用程序在 N 秒後終止進行查詢的執行緒/分叉。此類庫的實現和使用超出了 ServerFault 的範圍。這是StackOverflow的適當問題。

其次,您在評論中陳述了以下內容:

實際上,我的問題更關心的是客戶端應用程序在事務過程中掛起(例如,陷入無限循環)的情況,而不是事務在 MySQL 結束時需要很長時間的情況。

這在您最初的問題中一點也不明顯,現在仍然不是。這只有在您在評論中分享了這個相當重要的花絮後才能辨別出來。

如果這實際上是您要解決的問題,那麼恐怕您在錯誤的論壇上問過它。您描述了一個應用程序級程式問題,它需要一個程式解決方案,MySQL 無法提供,並且超出了這個社區的範圍。您的最新答案回答了“如何防止 Ruby 程序無限循環?”的問題。這個問題對這個社區來說是題外話,應該在StackOverflow上提問。

引用自:https://serverfault.com/questions/241823