چرا خطای Deadlock در MySQL رخ می‌دهد و چگونه آن را ریشه‌ای برطرف کنیم؟

مقدمه

اولین بار که با خطای «Deadlock found when trying to get lock» مواجه شدم، روی یک فروشگاه ووکامرسی بود که روزانه حدود هزار سفارش ثبت می‌کرد. خطا هر چند ساعت یک بار در لاگ ظاهر می‌شد و یک سفارش تصادفی شکست می‌خورد. مشتری فکر می‌کرد مشکل از کد است؛ اما بعد از چند روز بررسی دقیق لاگ InnoDB، متوجه شدم ریشه مشکل در ترتیبی است که دو تراکنش هم‌زمان، قفل‌ها را درخواست می‌کنند. این خطا در تجربه من از همه خطاهای MySQL فریبنده‌تر است، چون معمولاً بی‌صدا رخ می‌دهد و فقط بخش کوچکی از تراکنش‌ها را قربانی می‌کند.

در این مقاله می‌خواهم دقیقاً بگویم deadlock از کجا می‌آید، چطور می‌توان آن را خواند و تحلیل کرد، و چه الگوهای کدنویسی و تنظیماتی می‌توانند احتمال بروزش را به حداقل برسانند. تمرکز من روی رفتار واقعی InnoDB است، نه روی توصیه‌های کلیشه‌ای.

خطای Deadlock دقیقاً چیست؟

Deadlock یا «قفل‌شدگی متقابل» حالتی است که در آن دو تراکنش یا بیشتر، هرکدام منتظر آزاد شدن منبعی هستند که تراکنش دیگر در اختیار دارد. نتیجه این است که هیچ‌کدام نمی‌توانند پیش بروند و سیستم در بن‌بست کامل قرار می‌گیرد. در MySQL، موتور InnoDB به‌صورت خودکار یکی از تراکنش‌ها را به‌عنوان «قربانی» انتخاب می‌کند و آن را با یک Rollback برمی‌گرداند تا بن‌بست شکسته شود.

پیام خطا معمولاً به این شکل است:

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

این خطا با خطای Lock wait timeout exceeded تفاوت بنیادین دارد. در آن خطا، یک تراکنش پس از مدتی انتظار، به‌خاطر تنظیم innodb_lock_wait_timeout با شکست مواجه می‌شود؛ اما در deadlock، سیستم فوراً تشخیص می‌دهد که یک چرخه انتظار وجود دارد و به‌سرعت یکی از تراکنش‌ها را قربانی می‌کند تا بقیه بتوانند ادامه دهند.

نکته‌ای که در سال‌ها تجربه یاد گرفته‌ام این است: deadlock اغلب نشانه یک باگ منطقی نیست؛ نشانه یک الگوی ناسازگار در ترتیب دسترسی به منابع است. یعنی حتی کد کاملاً درست، اگر دو تراکنش هم‌زمان قفل‌ها را در ترتیب معکوس درخواست کنند، می‌تواند به deadlock منجر شود.

Deadlock، خطای منطق تراکنش‌های شماست، نه خطای MySQL؛ موتور فقط قربانی را انتخاب می‌کند تا سیستم نفس بکشد.

چهار شرط لازم برای بروز Deadlock

برای بروز deadlock، همزمانی چهار شرط لازم است. اگر هر یک از این شرط‌ها را بتوانید از بین ببرید، deadlock هرگز اتفاق نمی‌افتد. این چهار شرط در ادبیات Deadlock به‌عنوان شرایط Coffman شناخته می‌شوند:

۱. Mutual Exclusion (انحصار متقابل)

هر منبع (مثلاً یک ردیف جدول یا یک ایندکس) در یک لحظه فقط در اختیار یک تراکنش است. اگر دو تراکنش بتوانند هم‌زمان به یک ردیف دسترسی داشته باشند، اصلاً قفلی وجود ندارد که deadlock بخواهد رخ دهد. این شرط تا زمانی که روی جدول‌های InnoDB کار می‌کنید، حذف‌شدنی نیست.

۲. Hold and Wait (نگه‌داشتن و انتظار)

یک تراکنش، حداقل یک قفل را نگه داشته و هم‌زمان منتظر قفلی دیگر است. این شرط بسیار رایج است؛ در اکثر deadlock‌ها، یک تراکنش روی ردیف اول قفل دارد و منتظر ردیف دوم است. برای شکستن این شرط، می‌توان از تراکنش‌های کوچک‌تر استفاده کرد یا از دستور SELECT ... FOR UPDATE NOWAIT بهره گرفت.

۳. No Preemption (عدم پیش‌دستی)

قفل را نمی‌توان از یک تراکنش به‌زور گرفت. InnoDB فقط می‌تواند تراکنش را به‌عنوان قربانی Rollback کند، اما نمی‌تواند تنها قفل را آزاد کند. این شرط را هم نمی‌توانید تغییر دهید.

۴. Circular Wait (انتظار چرخه‌ای)

زنجیره‌ای از تراکنش‌ها وجود دارد که هر کدام منتظر منبعی است که تراکنش بعدی در اختیار دارد. اگر این چرخه نباشد، deadlock رخ نمی‌دهد. مهم‌ترین اهرمی که شما در دست دارید، همین شرط چهارم است: اگر همه تراکنش‌ها قفل‌ها را در یک ترتیب یکسان درخواست کنند، چرخه شکل نمی‌گیرد.

در تجربه من، بیش از ۸۰٪ deadlock‌هایی که دیده‌ام، ریشه در شکستن شرط دوم و چهارم داشته‌اند: یا تراکنش‌ها بیش از حد بزرگ بوده‌اند (Hold and Wait طولانی)، یا ترتیب دسترسی به ردیف‌ها ناسازگار بوده (Circular Wait). تمرکز روی همین دو شرط، بیشترین بازدهی را در پیشگیری دارد.

نشانه‌ها و علائم هشداردهنده

Deadlock همیشه به‌طور ناگهانی خودش را نشان نمی‌دهد. قبل از اینکه به یک مشکل جدی تبدیل شود، معمولاً نشانه‌هایی وجود دارد:

خطاهای گاه‌به‌گاه در لاگ MySQL: پیام‌هایی مثل «Deadlock found when trying to get lock» که گاهی با فاصله چند ساعت یا چند روز ظاهر می‌شوند.
Rollback‌های ناموفق در اپلیکیشن: اگر برنامه شما تراکنش‌ها را مدیریت می‌کند، ممکن است گاهی خطای Rollback failed ببیند.
کندی ناگهانی در ساعات پیک: وقتی ترافیک زیاد است، احتمال بروز deadlock بیشتر می‌شود؛ چرا که تراکنش‌های هم‌زمان بیشتری وجود دارند.
افزایش نرخ Timeout در APIها: بعضی از درخواست‌ها به‌خاطر انتظار طولانی برای قفل، با timeout مواجه می‌شوند.
ظاهر شدن خطا در صفحات خاص: اگر deadlock روی یک جدول خاص (مثل wp_options) متمرکز باشد، فقط صفحاتی که به آن جدول دسترسی دارند خطا می‌دهند.

تشخیص دقیق: خواندن لاگ InnoDB

خبر خوب این است که InnoDB به‌صورت پیش‌فرض، هر deadlock را در لاگ ثبت می‌کند. این لاگ دقیقاً به شما می‌گوید کدام تراکنش‌ها، کدام کوئری‌ها و کدام ردیف‌ها درگیر بوده‌اند. اولین کاری که در مواجهه با deadlock می‌کنم، اجرای این دستور است:

SHOW ENGINE INNODB STATUS\G

در خروجی این دستور، به بخش LATEST DETECTED DEADLOCK مراجعه کنید. این بخش معمولاً به این شکل است:

------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 42, OS thread handle 0x7f, query id 100 localhost root updating
UPDATE wp_options SET option_value = 'x' WHERE option_name = 'y'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 100 page no 5 n bits 72 index PRIMARY of table `db`.`wp_options`

*** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
UPDATE wp_options SET option_value = 'a' WHERE option_name = 'b'
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 100 page no 4 n bits 80 index PRIMARY of table `db`.`wp_options`

این خروجی در واقع یک داستان کامل از deadlock است. سه چیز مهم را به شما می‌گوید:

چه تراکنش‌هایی درگیر بودند: در این مثال، TRANSACTION 12345 و 12346.
چه کوئری‌هایی اجرا می‌شدند: دو دستور UPDATE که هر کدام روی یک ردیف از wp_options کار می‌کردند.
کدام قفل‌ها درگیر بودند: هر تراکنش، قفل روی یک صفحه را نگه داشته و منتظر صفحه دیگری است.

اگر به SSH دسترسی ندارید، می‌توانید این اطلاعات را از لاگ MySQL هم بخوانید. مسیر لاگ معمولاً در /var/log/mysql/error.log است یا در پنل هاست قابل دسترسی است.

ابزار mysqladmin و اطلاعات زنده

برای مانیتورینگ زنده می‌توانید از دستور زیر استفاده کنید:

mysqladmin -u root -p extended-status | grep -i deadlock

این دستور تعداد کل deadlock‌هایی که از زمان راه‌اندازی سرور رخ داده را نشان می‌دهد. اگر عدد در حال افزایش سریع است، باید فوراً به سراغ تحلیل لاگ بروید.

راه‌حل‌های عملی و گام‌به‌گام

وقتی deadlock تشخیص داده شد، راه‌حل‌ها را باید به سه سطح تفکیک کرد: راه‌حل فوری، راه‌حل کوتاه‌مدت و راه‌حل ساختاری.

راه‌حل فوری: Retry خودکار

ساده‌ترین راه‌حل که InnoDB خودش پیشنهاد می‌دهد، Retry کردن تراکنش است. اگر در کد اپلیکیشن، خطای ERROR 1213 را دریافت کردید، به‌جای نمایش خطا به کاربر، تراکنش را مجدداً اجرا کنید:

function executeWithRetry($callback, $maxRetries = 3) {
    for ($i = 0; $i < $maxRetries; $i++) {
        try {
            return $callback();
        } catch (PDOException $e) {
            if ($e->errorInfo[1] === 1213 && $i < $maxRetries - 1) {
                usleep(100000); // ۱۰۰ میلی‌ثانیه
                continue;
            }
            throw $e;
        }
    }
}

این الگو در پروژه‌های واقعی، بیش از ۹۰٪ deadlock‌های تصادفی را می‌پوشاند. اما فراموش نکنید که این یک مسکّن است؛ نه درمان.

راه‌حل کوتاه‌مدت: کوچک کردن تراکنش‌ها

یکی از رایج‌ترین دلایل deadlock در ووکامرس، تراکنش‌های طولانی است. اگر یک تراکنش، ۵۰ کوئری را در بر بگیرد، احتمال اینکه در میانه راه یک تراکنش دیگر قفل مورد نیازش را گرفته باشد، بسیار بیشتر می‌شود. راه‌حل: تراکنش را به قطعات کوچکتر بشکنید. به‌جای یک تراکنش که همه سفارش‌های یک کاربر را به‌روزرسانی می‌کند، ده تراکنش کوچک با قفل‌های کوتاه‌مدت اجرا کنید.

راه‌حل ساختاری: ترتیب ثابت قفل‌ها

مهم‌ترین راه‌حل ساختاری، تضمین یک ترتیب ثابت برای درخواست قفل‌هاست. اگر همه تراکنش‌ها، ردیف‌ها را به ترتیب id قفل کنند، هرگز چرخه شکل نمی‌گیرد. برای مثال:

-- ترتیب اشتباه (احتمال deadlock):
UPDATE accounts SET balance = balance - 100 WHERE id = 5;
UPDATE accounts SET balance = balance + 100 WHERE id = 3;

-- ترتیب درست (بدون deadlock):
UPDATE accounts SET balance = balance - 100 WHERE id = 3;
UPDATE accounts SET balance = balance + 100 WHERE id = 5;

در سیستم‌هایی که دو تراکنش هم‌زمان، ممکن است روی دو ردیف کار کنند، این تکنیک تقریباً همیشه جواب می‌دهد.

راه‌حل تنظیماتی: کاهش innodb_lock_wait_timeout

پارامتر innodb_lock_wait_timeout به‌طور پیش‌فرض ۵۰ ثانیه است. کاهش آن به ۱۰ ثانیه، باعث می‌شود تراکنش‌های گیرکرده زودتر شکست بخورند و منابع سریع‌تر آزاد شوند. هرچند این تنظیم، خودِ deadlock را حل نمی‌کند، اما از تبدیل شدن یک قفل‌شدگی کوچک به یک بحران گسترده جلوگیری می‌کند:

SET GLOBAL innodb_lock_wait_timeout = 10;

استراتژی‌های پیشگیری

پیشگیری از deadlock، برخلاف حل آن، نیاز به نگاه معماری دارد. در تجربه‌ام، رعایت هفت اصل زیر، احتمال بروز deadlock را در پروژه‌های واقعی به کمتر از ۵٪ کاهش داده:

۱. تراکنش‌های کوچک

هرچه تراکنش کوتاه‌تر باشد، تعداد قفل‌هایی که نگه می‌دارد کمتر است و احتمال برخورد با تراکنش‌های دیگر پایین‌تر می‌آید. قاعده من: هیچ تراکنشی نباید بیش از ۱۰ کوئری داشته باشد.

۲. ترتیب ثابت ردیف‌ها

اگر روی چند ردیف کار می‌کنید، همیشه آن‌ها را بر اساس PRIMARY KEY مرتب کنید. این کار در اکثر اپلیکیشن‌ها خودکار است، اما در ORMها گاهی نادیده گرفته می‌شود.

۳. استفاده از NOWAIT یا SKIP LOCKED

در MySQL 8.0، دو گزینه عالی برای قفل‌ها اضافه شده: NOWAIT که اگر قفل آزاد نبود، فوراً خطا می‌دهد (بدون انتظار)، و SKIP LOCKED که ردیف‌های قفل‌شده را نادیده می‌گیرد. این دو گزینه، به‌ویژه برای صف‌های کار (Job Queues) بسیار مفیدند:

SELECT * FROM jobs 
WHERE status = 'pending' 
ORDER BY id 
LIMIT 10 
FOR UPDATE SKIP LOCKED;

۴. کاهش Isolation Level

سطح ایزولاسیون پیش‌فرض InnoDB یعنی REPEATABLE READ، قفل‌های بیشتری نسبت به READ COMMITTED می‌گیرد. در بسیاری از اپلیکیشن‌های وب، استفاده از READ COMMITTED هم منطقی است و هم احتمال deadlock را کاهش می‌دهد:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

۵. ایندکس‌گذاری درست

یکی از مهم‌ترین عوامل پنهان deadlock، نداشتن ایندکس مناسب است. اگر کوئری شما به‌جای استفاده از ایندکس، مجبور به اسکن کل جدول باشد، InnoDB مجبور می‌شود ردیف‌های بیشتری را قفل کند. این موضوع در پروژه‌های ووکامرسی که روی جدول wp_postmeta عملیات سنگین انجام می‌دهند، بسیار رایج است. راهنمای ایندکس‌گذاری اصولی را در مقاله ایندکس‌گذاری در دیتابیس به تفصیل نوشته‌ام.

۶. جداسازی جدول‌های پرمعامله

اگر جدولی مثل wp_options مدام درگیر deadlock است، بهتر است داده‌های آن را به چند جدول جداگانه تفکیک کنید. این کار نیازمند تغییر ساختار افزونه است، اما کاهش قابل‌توجهی در برخورد قفل‌ها ایجاد می‌کند.

۷. مانیتورینگ فعال

به‌جای انتظار برای گزارش کاربر، خودتان خطا را شکار کنید. یک اسکریپت کوچک که هر دقیقه لاگ deadlock را بررسی می‌کند و در صورت یافتن مورد جدید، به شما هشدار می‌دهد، در پروژه‌های حساس ضروری است.

هرچه تراکنش کوچک‌تر، قفل‌ها کوتاه‌تر و ترتیب درخواست‌ها یکسان‌تر باشد، deadlock از یک خطر به یک احتمال نادر تبدیل می‌شود.

Isolation Level و نقش آن در Deadlock

سطح ایزولاسیون تراکنش (Transaction Isolation Level) یکی از مهم‌ترین پارامترهای MySQL است که تأثیر مستقیم روی تعداد قفل‌ها و در نتیجه احتمال deadlock دارد. چهار سطح استاندارد وجود دارد:

سطح ایزولاسیون	ویژگی	احتمال Deadlock
READ UNCOMMITTED	کمترین قفل	بسیار پایین
READ COMMITTED	قفل‌های کوتاه‌مدت	پایین
REPEATABLE READ	پیش‌فرض InnoDB	متوسط تا بالا
SERIALIZABLE	بیشترین قفل	بسیار بالا

در تجربه‌ام، تغییر از REPEATABLE READ به READ COMMITTED در پروژه‌های ووکامرسی که با deadlock مکرر دست‌وپنجه نرم می‌کردند، در بیش از ۷۰٪ موارد کافی بوده است. دلیلش ساده است: در READ COMMITTED، قفل‌های Gap باز نمی‌شوند؛ در نتیجه دامنه قفل کوچک‌تر است و تعداد چرخه‌های انتظار کمتر.

اما یک هشدار جدی: تغییر سطح ایزولاسیون، رفتار تراکنش‌ها را تغییر می‌دهد. اگر برنامه شما به «خواندن پایدار» در طول یک تراکنش وابسته است، نباید این تغییر را انجام دهید. برای مطالعه بیشتر درباره تفاوت موتورها و سطوح ایزولاسیون، مقاله تفاوت InnoDB و MyISAM را ببینید.

الگوهای کدنویسی مقاوم در برابر Deadlock

در طول سال‌ها کار روی پروژه‌های مختلف، چند الگوی طراحی را شناسایی کرده‌ام که در کاهش deadlock بسیار مؤثر بوده‌اند:

الگوی ۱: قفل Pessimistic با ترتیب مشخص

BEGIN;
SELECT * FROM accounts WHERE id IN (3, 5) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 3;
UPDATE accounts SET balance = balance + 100 WHERE id = 5;
COMMIT;

نکته کلیدی در این الگو، ORDER BY id است که تضمین می‌کند همه تراکنش‌ها ردیف‌ها را به یک ترتیب قفل می‌کنند.

الگوی ۲: صف کار با SKIP LOCKED

برای سیستم‌های صف (مثل پردازش سفارش‌ها یا ایمیل‌ها)، الگوی زیر بسیار مقاوم است:

BEGIN;
SELECT * FROM jobs 
WHERE status = 'pending' 
ORDER BY created_at 
LIMIT 5 
FOR UPDATE SKIP LOCKED;
-- پردازش کارها
UPDATE jobs SET status = 'processing' WHERE id IN (...);
COMMIT;

الگوی ۳: Optimistic Locking

در سیستم‌هایی که احتمال برخورد کم است، به‌جای قفل ردیف، یک نسخه (Version) اضافه کنید:

UPDATE products 
SET price = 100, version = version + 1 
WHERE id = 42 AND version = 5;

-- اگر affected_rows صفر بود، یعنی یک تراکنش دیگر تغییر داده
-- باید تراکنش را Retry کنید

الگوی ۴: کاهش طول تراکنش با خارج کردن منطق

یکی از بدترین اشتباهاتی که در تراکنش‌ها می‌بینم، قرار دادن منطق کند در وسط تراکنش است. مثلاً ارسال ایمیل، فراخوانی API خارجی یا تولید فایل. هر یک از این کارها باید قبل یا بعد از تراکنش انجام شود، نه در میانه آن:

// اشتباه: ایمیل داخل تراکنش
BEGIN;
INSERT INTO orders ...;
send_email($customer); // کند!
COMMIT;

// درست: ایمیل بیرون از تراکنش
BEGIN;
INSERT INTO orders ...;
COMMIT;
send_email($customer);

پرسش‌های پرتکرار درباره Deadlock در MySQL

آیا Deadlock باعث از دست رفتن داده می‌شود؟

خیر. InnoDB با انتخاب یک تراکنش به‌عنوان قربانی و Rollback کردن آن، تضمین می‌کند که هیچ تغییری نیمه‌کاره باقی نمی‌ماند. اما اگر اپلیکیشن شما آن Retry را درست پیاده‌سازی نکند، ممکن است کاربر نهایی، پیام خطا ببیند.

آیا Deadlock فقط در سیستم‌های پرترافیک رخ می‌دهد؟

نه لزوماً. حتی در سایت‌های کم‌ترافیک هم اگر دو تراکنش هم‌زمان روی یک جدول با ترتیب ناسازگار کار کنند، می‌تواند رخ دهد. نکته کلیدی، همزمانی است نه حجم ترافیک.

آیا InnoDB همیشه قربانی را به‌صورت تصادفی انتخاب می‌کند؟

در عمل، InnoDB تراکنشی را که کمترین هزینه Rollback را دارد (مثلاً تراکنشی که هنوز ردیف‌های کمی را تغییر داده) قربانی می‌کند. اما این قاعده تضمین‌شده نیست و گاهی تراکنش‌های کوچک هم قربانی می‌شوند.

آیا REPAIR TABLE روی جدول‌های InnoDB هم Deadlock را حل می‌کند؟

خیر. REPAIR TABLE برای جدول‌های MyISAM طراحی شده و روی InnoDB کاربرد ندارد. اگر با deadlock مواجهید، باید به سراغ تحلیل تراکنش‌ها بروید — همان‌طور که در بخش «تشخیص دقیق» توضیح دادم.

آیا می‌توانم Deadlock را کاملاً حذف کنم؟

در تئوری بله (با رعایت دقیق چهار شرط Coffman)، اما در عمل، در سیستم‌های پیچیده، همیشه احتمال کم آن باقی می‌ماند. هدف واقع‌بینانه این است که deadlock را به یک رخداد نادر تبدیل کنید که به‌سادگی با Retry جبران می‌شود.

آیا Deadlock در MySQL 8 تفاوت کرده است؟

در MySQL 8، الگوریتم تشخیص deadlock سریع‌تر شده و برخی پارامترهای تنظیمی (مثل innodb_deadlock_detect) اضافه شده‌اند. همچنین قابلیت NOWAIT و SKIP LOCKED که در نسخه‌های قبل نبود، به‌شدت به کاهش deadlock کمک می‌کند.

چطور بفهمم کدام افزونه وردپرس باعث Deadlock است؟

از خروجی SHOW ENGINE INNODB STATUS نام جدول را استخراج کنید. سپس از پوشه افزونه‌های وردپرس، به دنبال افزونه‌ای بگردید که به آن جدول دسترسی دارد. روش عمومی‌تر، غیرفعال‌سازی افزونه‌ها یکی‌یکی و مانیتور کردن نرخ deadlock است.

کالبدشکافی فنی: درون موتور InnoDB چه می‌گذرد؟

برای درک عمیق deadlock، باید بدانید InnoDB چطور قفل‌ها را مدیریت می‌کند. InnoDB از یک گراف جهت‌دار به‌نام Wait-for Graph استفاده می‌کند. هر گره در این گراف، یک تراکنش است و هر یال، یک انتظار را نشان می‌دهد: «تراکنش A منتظر قفل تراکنش B است». هر بار که یک تراکنش جدید، درخواست قفلی می‌کند، InnoDB این گراف را به‌روزرسانی می‌کند و به‌دنبال چرخه می‌گردد.

اگر چرخه‌ای پیدا شود، InnoDB:

تمام تراکنش‌های درگیر در چرخه را شناسایی می‌کند.
بر اساس معیار «کمترین هزینه Rollback»، یکی را قربانی انتخاب می‌کند.
تراکنش قربانی را Rollback می‌کند و لاگ deadlock را می‌نویسد.
قفل‌های قربانی را آزاد می‌کند و به سایر تراکنش‌ها اجازه ادامه می‌دهد.

این فرآیند در چند میلی‌ثانیه انجام می‌شود. به همین دلیل، deadlock در InnoDB به‌طور خودکار حل می‌شود — چیزی که در برخی DBMSها (مثل MySQL با MyISAM یا SQL Server قدیمی) نیاز به دخالت دستی داشت.

نکته‌ای که خیلی‌ها نمی‌دانند: InnoDB از دو نوع قفل استفاده می‌کند — قفل رکورد (Record Lock) و قفل شکاف (Gap Lock). قفل‌های Gap به‌طور پیش‌فرض در REPEATABLE READ فعال هستند و می‌توانند deadlock‌های پنهانی ایجاد کنند که در سطح ردیف قابل مشاهده نیستند. به همین دلیل در سیستم‌هایی که روی REPEATABLE READ هستند، کاهش به READ COMMITTED می‌تواند تفاوت چشمگیری در کاهش deadlock داشته باشد.

برای مطالعه بیشتر درباره ساختار داخلی موتورهای ذخیره‌سازی، مقاله موتور ذخیره‌سازی MySQL چیست را توصیه می‌کنم. اگر هم روی بهینه‌سازی کلی دیتابیس تمرکز دارید، راهنمای بهینه‌سازی کوئری‌های MySQL نقطه شروع خوبی است.

مانیتورینگ و ابزارهای تخصصی

برای پروژه‌های جدی، مانیتورینگ مداوم ضروری است. ابزارهای زیر را در تجربه‌ام مفید یافته‌ام:

۱. Percona Toolkit

مجموعه‌ای از ابزارهای خط فرمانی که برای تحلیل عمیق‌تر تراکنش‌ها طراحی شده‌اند. ابزار pt-deadlock-logger به‌طور خودکار لاگ deadlock را پارس می‌کند و در یک جدول اختصاصی ذخیره می‌کند. این ابزار برای تحلیل روند deadlock در طول زمان بی‌نظیر است.

۲. MySQL Enterprise Monitor

نسخه تجاری Oracle که داشبورد گرافیکی از deadlock‌ها ارائه می‌دهد. برای پروژه‌های سازمانی مناسب است، اما هزینه لایسنس دارد.

۳. افزونه Query Monitor در وردپرس

در پروژه‌های وردپرسی، این افزونه رایگان به شما نشان می‌دهد کدام کوئری‌ها کندتر از حد معمول هستند و چه افزونه‌ای آن‌ها را اجرا می‌کند. اگر deadlock روی یک جدول خاص متمرکز است، Query Monitor سریع‌ترین راه برای پیدا کردن مقصر است.

۴. ابزارهای هاست اشتراکی

بسیاری از هاست‌های حرفه‌ای (مثل سی‌پنل با پلاگین MySQL Monitor) قابلیت مشاهده تعداد deadlock در ۲۴ ساعت گذشته را دارند. اگر روی هاست اشتراکی هستید، این گزینه سریع‌ترین راه برای دیدن الگوهاست.

برای مطالعه جامع‌تر درباره تأثیر دیتابیس بر عملکرد کل سایت، مقاله تأثیر دیتابیس بر سرعت سایت را توصیه می‌کنم.

مطالعه موردی: رفع Deadlock در یک فروشگاه ووکامرسی

یک فروشگاه ووکامرسی با حدود ۸۰۰۰ محصول و ۵۰۰۰ مشتری فعال داشت و روزانه ۶۰۰ تا ۱۰۰۰ سفارش ثبت می‌کرد. مدیر سایت شکایت داشت که در ساعات اوج فروش، حدود ۲٪ سفارش‌ها با خطا مواجه می‌شوند.

علائم:

خطای «Deadlock found when trying to get lock» در لاگ
جدول درگیر، در بیش از ۹۰٪ موارد، wp_wc_order_stats بود
خطا فقط در ساعات ۲۰ تا ۲۳ رخ می‌داد

تشخیص:

با اجرای SHOW ENGINE INNODB STATUS، دو الگوی متفاوت را در لاگ دیدم:

بیشتر deadlock‌ها بین دو کوئری UPDATE روی wp_wc_order_stats رخ می‌داد
ترتیب دسترسی دو تراکنش متفاوت بود (یکی از order_id پایین به بالا، دیگری برعکس)

درمان:

ابتدا Isolation Level را از REPEATABLE READ به READ COMMITTED تغییر دادم
سپس در گزارش‌گیری‌های افزونه، ترتیب درخواست‌ها را اصلاح کردیم
در نهایت، پارامتر innodb_lock_wait_timeout را از ۵۰ به ۱۵ ثانیه کاهش دادیم

نتیجه:

در یک هفته پس از اعمال این تغییرات، نرخ deadlock به کمتر از ۰.۱٪ رسید. یک ماه بعد، تقریباً صفر شد. کل ماجرا، بدون یک خط کد جدید در افزونه‌های ووکامرس، فقط با سه تنظیم و یک اصلاح کوچک در ترتیب کوئری‌ها حل شد.

درس‌آموخته:

در بسیاری از موارد، deadlock با تحلیل درست و تنظیمات دقیق حل می‌شود و نیازی به بازنویسی معماری نیست. اما نکته کلیدی، تحلیل درست است — نه تست‌وخطا. اگر لاگ را درست بخوانید، معمولاً پاسخ دقیقاً همان‌جا نوشته شده است.

برای مطالعه موردی مشابه روی بهینه‌سازی دیتابیس ووکامرس، مقاله بهینه‌سازی دیتابیس ووکامرس را ببینید.

نتیجه‌گیری: تفکر تراکنشی، نه تعمیر موقت

خطای Deadlock در MySQL، برخلاف آنچه در نگاه اول به نظر می‌رسد، یک خطای تصادفی نیست؛ نتیجه مستقیم یک چرخه منطقی در درخواست قفل‌هاست. موتور InnoDB در این میان، فقط نقش داور بازی را دارد: یکی از تراکنش‌ها را قربانی می‌کند تا بقیه بتوانند ادامه دهند. بنابراین، حل ریشه‌ای این مشکل، نیازمند نگاه تراکنشی به کد است، نه نگاه تک‌کوئری.

از تجربه‌ام، چهار اصل عملی بیشترین بازدهی را داشته‌اند: اول، تراکنش‌ها را تا حد امکان کوچک نگه دارید. دوم، ترتیب دسترسی به منابع را در همه تراکنش‌ها یکسان کنید. سوم، اگر روی MySQL 8 هستید، از SKIP LOCKED و NOWAIT برای صف‌های کار استفاده کنید. چهارم، مانیتورینگ فعال داشته باشید؛ deadlock‌هایی که بی‌صدا رخ می‌دهند، در بلندمدت به بزرگ‌ترین دردسر تبدیل می‌شوند.

در نهایت، اگر چه ممکن است همیشه نتوان deadlock را به صفر رساند، می‌توان آن را به یک رخداد نادر تبدیل کرد که با یک Retry ساده در لایه اپلیکیشن جبران می‌شود. این هدف واقع‌بینانه‌ای است که در صدها پروژه واقعی جواب داده است.

اگر روی پروژه‌ای با deadlock مکرر دست‌وپنجه نرم کرده‌اید و روشی متفاوت برای حل پیدا کرده‌اید — به‌خصوص اگر با تراکنش‌های پیچیده در ووکامرس یا سیستم‌های پرداخت کار کرده‌اید — خوشحال می‌شوم تجربه‌تان را بشنوم. بگویید در آن پروژه، کدام یک از چهار اصل بالا مؤثرتر بود و کدام یک را نادیده گرفته بودید؛ همین گفت‌وگو به خواننده بعدی کمک می‌کند سریع‌تر به جواب برسد.