همه مقالات تخصصی

رفع Deadlock وLock Wait در MySQL InnoDB

راهنمای عملی تشخیص Deadlock وLock Wait Timeout در MySQL InnoDB با SHOW ENGINE، Performance Schema، ترتیب قفل‌ها، ایندکس مناسب و Retry امن تراکنش.

رفع Deadlock وLock Wait در MySQL InnoDB

Deadlock زمانی رخ می‌دهد که دو تراکنش منابع را در ترتیب مخالف قفل کرده‌اند و هیچ‌کدام نمی‌تواند ادامه دهد. InnoDB یکی را قربانی و Rollback می‌کند تا سیستم آزاد شود. Lock Wait Timeout متفاوت است: تراکنش مدت زیادی منتظر قفلی می‌ماند. هدف حذف همه قفل‌ها نیست؛ قفل برای صحت داده لازم است. باید زمان و دامنه قفل را کم و ترتیب دسترسی را قابل پیش‌بینی کنید.

آخرین Deadlock را از InnoDB بخوانید

بلافاصله پس از رخداد، بخش LATEST DETECTED DEADLOCK را ثبت کنید. Queryها، Transaction ID، نوع Lock و Index درگیر را یادداشت کنید. برای تاریخچه مداوم، ثبت Deadlock در Error Log را فعال کنید.

SHOW ENGINE INNODB STATUS\G
SET GLOBAL innodb_print_all_deadlocks = ON;
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

منتظرها و مسدودکننده‌ها را در Performance Schema پیدا کنید

در نسخه‌های جدید MySQL، جداول data_locks و data_lock_waits ارتباط Waiter و Blocker را نشان می‌دهند. ابتدا تراکنش و Query مسدودکننده را پیدا کنید؛ کشتن فوری Session بدون فهم علت ممکن است داده نیمه‌کاره یا تکرار رخداد بسازد.

SELECT * FROM performance_schema.data_lock_waits\G
SELECT * FROM performance_schema.data_locks\G
SELECT trx_id, trx_started, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

تراکنش‌ها را کوتاه نگه دارید

فراخوانی API، پردازش فایل، دریافت ورودی کاربر یا Sleep را داخل Transaction انجام ندهید. ابتدا داده لازم را آماده کنید، سپس Transaction را باز، تغییر را اعمال و سریع Commit کنید. تراکنش باز در پنل مدیریتی یا Job شکست‌خورده می‌تواند دقیقه‌ها قفل نگه دارد.

START TRANSACTION;
SELECT id FROM orders WHERE id = 42 FOR UPDATE;
UPDATE orders SET status = 'paid' WHERE id = 42;
COMMIT;

ترتیب قفل‌گیری را در همه مسیرها یکسان کنید

اگر یک مسیر ابتدا orders و سپس inventory را قفل می‌کند، مسیر دیگر نباید ترتیب معکوس داشته باشد. برای چند Row، IDها را مرتب و همیشه در همان ترتیب Lock کنید. این کار چرخه Wait را می‌شکند.

ایندکس مناسب دامنه قفل را کوچک می‌کند

UPDATE یا SELECT FOR UPDATE بدون ایندکس مناسب ممکن است تعداد زیادی Record و Gap را اسکن و قفل کند. با EXPLAIN بررسی کنید Predicate از ایندکس انتخابی استفاده می‌کند. ایندکس اضافی نیز هزینه Write دارد؛ دقیق و بر مبنای Query واقعی طراحی کنید.

EXPLAIN SELECT id FROM jobs
WHERE queue = 'email' AND status = 'ready'
ORDER BY id LIMIT 1 FOR UPDATE;

CREATE INDEX idx_jobs_queue_status_id
ON jobs(queue, status, id);

Retry را محدود و امن پیاده‌سازی کنید

Deadlock یک خطای قابل انتظار در سامانه همزمان است. Transaction قربانی باید با Backoff کوتاه و تعداد محدود Retry شود. عملیات باید Idempotent باشد؛ برای پرداخت یا ارسال پیام از کلید یکتا استفاده کنید تا Retry اثر را دو بار ایجاد نکند.

Isolation Level را با احتیاط انتخاب کنید

در REPEATABLE READ، Next-Key و Gap Lockها برای جلوگیری از Phantom استفاده می‌شوند. READ COMMITTED می‌تواند برخی Gap Lockها را کم کند، اما رفتار خواندن را تغییر می‌دهد. تغییر سراسری بدون تست بار و منطق برنامه خطرناک است.

چک‌لیست قفل InnoDB

  • خروجی SHOW ENGINE INNODB STATUS را ذخیره کنید.
  • Waiter و Blocker را در Performance Schema پیدا کنید.
  • تراکنش‌ها را کوتاه و بدون I/O خارجی نگه دارید.
  • ترتیب قفل‌گیری را در همه کدها یکسان کنید.
  • Predicate و ایندکس Queryهای قفل‌کننده را بررسی کنید.
  • Retry محدود، Backoff و Idempotency اضافه کنید.

سؤال‌های رایج

آیا افزایش innodb_lock_wait_timeout مشکل را حل می‌کند؟

معمولاً نه؛ فقط مدت انتظار را بیشتر می‌کند. باید Blocker، تراکنش طولانی یا ایندکس نامناسب اصلاح شود.

آیا Deadlock نشانه خرابی InnoDB است؟

خیر. در همزمانی بالا ممکن است طبیعی باشد؛ نرخ زیاد آن نشانه طراحی نامناسب ترتیب قفل یا Transaction است.

آیا باید همیشه Session مسدودکننده را Kill کنم؟

فقط در حادثه و پس از بررسی اثر. راه‌حل پایدار اصلاح کد، Query و زمان Transaction است.

راهنماهای مرتبط