MySQL Deadlock Investigation
前陣子遇到個 MySQL deadlock issue.
起源是想寫腳本啟動多執行緒再打 HTTP API 向 DB 塞資料, 往死裡塞, 然後, 然後它就往死結(劫)裡去了 …
誒…死結, 不就是我佔著茅坑, 你拿著廁所紙, 誰都不讓誰嗎 ? 應該很快便能找出哪個資源是關鍵角色吧. 然而事情總是不像臭宅想的那樣簡單
首先這個 HTTP API 要做些什麼
- 建立一張發票 (Invoice), 裡頭有基本資訊, 例如開立發票的辦公室(Office), 開立時間, 開立人, 發票對象, 金額等…
- 成功建立發票之後, 會再幫它填上一個”唯一”的發票號碼 (兩種情境, 各 office 之中唯一或是所有 office 中是唯一)
Pseudocode 如下,
@transaction.atomic()
def create_invoice(data, ...):
sz = SOME_SERIALIZER(data=data)
sz.is_valid()
invoice = sz.save() invoice.uni_no = get_unique_no(invoice.office_id)
invoice.save(update_fields=['uni_no']) return Response()def get_unique_no(office_id):
office = Office.objects.select_for_update.get(pk=office_id)
uni_no = office.invoice_no
office.invoice_no += 1
office.save(update_fields=['invoice_no'])
return uni_no
從這樣的虛擬碼, 大概能對 Schema 產生點概念 …
mysql> desc office;
+------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| invoice_no | int(11) | NO | | 1 | |
+------------+---------+------+-----+---------+----------------+mysql> desc invoice;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| office_id | int(11) | NO | MUL | NULL | |
| uni_no | int(11) | YES | | NULL | |
+-----------+---------+------+-----+---------+----------------+* invoice table 的 office_id 欄位是一個參考 office.id 的外鍵.
接著來看看 Deadlock 是如何發生的 ? 請看下面動畫
當兩個 request 同時完成了 invoice 的建立後, 其中一個 request 透過 select_for_update 準備獲取 office 並增加 office.invoice_no 的數值時卻被 block 住; 另一個 request 隨後也呼叫了 select_for_update, 此時 mysql 偵測到 deadlock 發生, 於是將其中一個 request transaction 進行 rollback, 因此沒被 rollback 的 transaction 得以順利進行後續動作.
深入分析
來, 跟著一起唸咒語
mysql> SHOW ENGINE INNODB STATUS\G其實也沒那麼神奇, 就像財哥所說的
顯…示In…n…o…D…B…引…擎狀…態…
注意到這個區塊 LATEST DETECTED DEADLOCK, 裡面有死結命案的所有加害與被害者們 (Transaction 9297521 與 Transaction 9297522), 並且連當時的 query statement 也給你惹, 甚至還告訴你每個 Transaction 當下 “持有” 或 “欲獲取” 哪些 lock resource.
如何解讀
- Txn (1) 準備執行 select_for_update 時, 等待獲取 office table 上 target row 的 exclusive(X) lock, 實際上是針對 primary key index record 的一個鎖.
- Txn (2) 目前擁有 office table 上 target row 的 shared(S) lock.
- Txn(2) 在準備執行 select_for_update 時, 也在等待獲取 target row 的 exclusive (X) lock.
- MySQL 此時偵測到 Deadlock, 選擇 rollback Txn(2), 此時 (S) lock 被釋放, Txn(1) 獲得了 (X) lock 而繼續執行.
- 延伸閱讀, InnoDB locking
這裡讓人不解的是, 為何 office 的 target row 被上了一個 (S) lock? 如果只是單純的 select-statement, 是不會對 record 上鎖的, 那有可能是早一步對 invoice table 的 insert 操作觸發的嗎 !? RTFM is the cure.
If a FOREIGN KEY constraint is defined on a table, any insert, update, or delete that requires the constraint condition to be checked sets shared record-level locks on the records that it looks at to check the constraint. — Locks Set by Different SQL Statements in InnoDB
謎題揭曉, 因為在 invoice 的 CUD 操作過程中必須對其外鍵 (office_id ) 參考上鎖, 進而確保 constraint condition.
來吶，你試試啊
simple reproduce steps.
CREATE DATABASE dbdeadlock;
USE dbdeadlock;CREATE TABLE user(
id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
name VARCHAR(32) NOT NULL
);insert into user(id, name) values(1, 'Mr. Hard');CREATE TABLE user_messages(
id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
user_id INT(10) unsigned NOT NULL,
message VARCHAR(500) NOT NULL,CONSTRAINT user_messages_user_id_foreign FOREIGN KEY (user_id) REFERENCES user (id)
);
USE dbdeadlock;
START TRANSACTION;
insert into `user_messages` (`user_id`, `message`) values (1, 'starting txn1...');
在另一個 console 裡
USE dbdeadlock;
START TRANSACTION;insert into `user_messages` (`user_id`, `message`) values (1, 'starting txn2...');update `user` set `name` = 'Mr. Harder' where `id` = 1;
然後唸上咒語, happy debugging!
可能解法 (針對 Invoice/Office 使用情境)
- 提早對外鍵參照表上鎖, 也就是在建立 Invoice 之前就先對 Office 上鎖. 但這樣會降低 API 的並行性 (Concurrency)
- 將 Invoice 對 Office 的外鍵約束移除. 然而這對於使用 RDBMS 來說便成為一個矛盾, 正反理由可以參照 SO-What’s wrong with foreign keys. 這不是我們的選項.
- 把對 Office table 上儲存”唯一發票號碼”的 invoice_no 欄位放到另一張表上,e.g. OfficeUniNumberTable. 如此, 對 Invoice 的 CUD 雖然會將 Office 的 row record 上 (S) lock, 但後續對 OfficeUniNumberTable 的 select_for_update 也不會造成死結.
