Deadlock: تشخیص، خواندن SHOW ENGINE INNODB STATUS و پیشگیری

دو تراکنش، هر کدام منتظر دیگری

Deadlock وقتی رخ می‌دهد که تراکنش A قفلی دارد که B می‌خواهد و B قفلی دارد که A می‌خواهد. هیچ‌کدام نمی‌توانند ادامه دهند. InnoDB این چرخه را فوراً تشخیص می‌دهد، یکی از تراکنش‌ها (معمولاً آن که تغییرات کمتری داشته) را ROLLBACK می‌کند و به برنامه خطای زیر را می‌دهد:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

بازسازی یک deadlock

-- نشست A                                      -- نشست B
START TRANSACTION;                              START TRANSACTION;
UPDATE products SET stock = stock - 1
WHERE id = 7;                                   UPDATE products SET stock = stock - 1
                                                WHERE id = 9;
UPDATE products SET stock = stock - 1
WHERE id = 9;       -- منتظر B می‌ماند
                                                UPDATE products SET stock = stock - 1
                                                WHERE id = 7;   -- ERROR 1213 (قربانی)

این دقیقاً چیزی است که در سبد خرید رخ می‌دهد: مشتری اول فرش 7 و بعد 9 را می‌خرد و مشتری دوم هم‌زمان 9 و بعد 7 را.

خواندن گزارش

SHOW ENGINE INNODB STATUS\G
-- ------------------------
-- LATEST DETECTED DEADLOCK
-- ------------------------
-- *** (1) TRANSACTION: ... UPDATE products SET stock = stock - 1 WHERE id = 9
-- *** (1) HOLDS THE LOCK(S): RECORD LOCKS ... index PRIMARY of table `carpet_shop`.`products`
-- *** (1) WAITING FOR THIS LOCK TO BE GRANTED: ... lock_mode X locks rec but not gap
-- *** (2) TRANSACTION: ... UPDATE products SET stock = stock - 1 WHERE id = 7
-- *** WE ROLL BACK TRANSACTION (2)

از گزارش سه چیز بیرون بکشید: کدام دو کوئری، روی کدام ایندکس (PRIMARY یا یک ایندکس ثانویه) و چه نوع قفلی (locks rec but not gap یعنی record lock، locks gap before rec یعنی gap lock). این گزارش فقط آخرین deadlock را نگه می‌دارد؛ برای ثبت همه در لاگ خطا:

SET PERSIST innodb_print_all_deadlocks = ON;

پیشگیری

1. ترتیب ثابت: ردیف‌ها را همیشه به یک ترتیب قفل کنید؛ مثلاً اقلام سبد را قبل از UPDATE بر اساس product_id مرتب کنید.
2. تراکنش کوتاه: فراخوانی درگاه پرداخت، ارسال پیامک یا هر کار شبکه‌ای را بیرون از تراکنش انجام دهید.
3. ایندکس مناسب: تا UPDATE و DELETE ردیف‌های کمتری را پیمایش و قفل کنند.
4. READ COMMITTED برای کاهش gap lockها، اگر منطق برنامه اجازه می‌دهد.
5. تلاش دوباره: deadlock در سیستم پرترافیک کاملاً از بین نمی‌رود؛ برنامه باید خطای 1213 را بگیرد و کل تراکنش را یکی دو بار تکرار کند.

Lock wait timeout

اگر چرخه‌ای در کار نباشد و فقط یک تراکنش قفل را طولانی نگه داشته باشد، منتظرها پس از innodb_lock_wait_timeout (پیش‌فرض ۵۰ ثانیه) خطای ERROR 1205: Lock wait timeout exceeded می‌گیرند. برای وب، ۵۰ ثانیه خیلی زیاد است؛ عدد کمتر (مثلاً ۱۰) در نشست برنامه منطقی‌تر است.

نکته‌هایی که کمتر کسی می‌داند

• پس از خطای 1205، به‌طور پیش‌فرض فقط همان دستور برگردانده می‌شود، نه کل تراکنش (innodb_rollback_on_timeout = OFF)؛ اگر برنامه ادامه دهد و COMMIT کند، نیمی از کار ذخیره می‌شود. پس از 1205 همیشه صریحاً ROLLBACK کنید.
• برخلاف 1205، پس از 1213 کل تراکنش برگردانده شده است؛ تکرار فقط آخرین دستور اشتباه است و باید از START TRANSACTION دوباره شروع کرد.
• روی سرورهای با هم‌زمانی بسیار بالا، خود تشخیص deadlock گران می‌شود؛ innodb_deadlock_detect = OFF آن را خاموش می‌کند و به lock wait timeout تکیه می‌کند — فقط با timeout کوتاه و دانستن عواقب.
• Deadlock می‌تواند فقط با یک ردیف و دو INSERT هم رخ دهد (به‌خاطر قفل اشتراکی روی کلید تکراری و سپس درخواست انحصاری)؛ اگر گزارش دو INSERT نشان داد، به کلیدهای UNIQUE مشکوک شوید.
• کلید خارجی هم قفل می‌گیرد: درج در order_items یک قفل اشتراکی روی ردیف والد در orders می‌گذارد و می‌تواند با UPDATE هم‌زمان سفارش در چرخه‌ی deadlock بیفتد.