چرا خطای Lock wait timeout در MySQL رخ می‌دهد؟ | Lock wait timeout exceeded

خطای Lock wait timeout exceeded در MySQL وقتی ظاهر می‌شود که یک تراکنش برای به‌دست‌آوردن قفل موردنیازش بیش از حد مجاز منتظر بماند و در نهایت سرور با پیام معروف 1205 آن را رد کند. برخلاف بسیاری از خطاهای دیتابیس که نشانهٔ ورود دادهٔ نامعتبر هستند، این خطا دقیقاً نشان می‌دهد که دو یا چند تراکنش همزمان روی یک منبع قفل‌شده رقابت می‌کنند و یکی از آن‌ها در این رقابت می‌بازد. در سال‌هایی که روی فروشگاه‌های پربازدید وردپرسی و سیستم‌های تراکنشی کار کرده‌ام، این خطا معمولاً در ساعات اوج فروش سر می‌زند و ریشه‌اش بیشتر از آنکه در دیتابیس باشد، در لایهٔ اپلیکیشن پنهان است.

پیام دقیق این خطا در InnoDB به این شکل است:

ERROR 1205 (HY000): Lock wait timeout exceeded;
try restarting transaction

این خطا با خطای deadlock (کد 1213) اشتباه گرفته می‌شود، در حالی که تفاوت مهمی دارند. در deadlock، دو تراکنش یکدیگر را قفل می‌کنند و سیستم به‌طور خودکار یکی را می‌کشد. در Lock wait timeout، هیچ چرخه‌ای وجود ندارد؛ فقط یک تراکنش طولانی، منبعی را در اختیار دارد و بقیه در صف انتظار می‌مانند تا زمان انتظارشان تمام شود. همین تفاوت، مسیر دیباگ را کاملاً تغییر می‌دهد و باید به آن دقت کرد. مبانی تراکنش و مفهوم ایزولاسیون، در ویکی‌پدیای Database transaction به شکل دقیقی توضیح داده شده است و برای درک عمیق‌تر این خطا، مطالعهٔ آن توصیه می‌شود.

خطای Lock wait timeout دقیقاً چه می‌گوید؟

پیام کد 1205 در MySQL و MariaDB به‌طور دقیق می‌گوید که یک تراکنش نتوانسته در بازهٔ زمانی مشخصی قفل موردنیازش را به دست آورد. بازهٔ زمانی توسط متغیر سیستمی innodb_lock_wait_timeout تعیین می‌شود که مقدار پیش‌فرض آن ۵۰ ثانیه است. در این مدت، تراکنش در حالت انتظار می‌ماند و پس از پایان زمان، با همان پیام خطا رد می‌شود. بعد از این خطا، InnoDB به‌طور خودکار تراکنش را rollback می‌کند و تغییرات اعمال‌شده در آن تراکنش باطل می‌شوند.

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

تفاوت deadlock و Lock wait timeout را می‌توان این‌طور ساده کرد: در deadlock، دو تراکنش دست یکدیگر را گرفته‌اند؛ در Lock wait timeout، یکی از آن‌ها در صف منتظر مانده و دیگری بی‌خبر از این انتظار، مشغول کار خودش است.

این خطا در محیط‌های production وقتی ظاهر می‌شود که بار همزمان بالا باشد و چند تراکنش روی جداول مشترک کار می‌کنند. در سایت‌های فروشگاهی، دقیقاً لحظه‌ای که دو کاربر همزمان روی یک محصول مشغول خرید می‌شوند، احتمال بروز این خطا وجود دارد. اگر با مفهوم ایندکس و کارایی دیتابیس آشنا نیستید، راهنمای ایندکس‌گذاری در MySQL نقطهٔ شروع خوبی برای درک زیربنای این مباحث است.

چرا InnoDB این قفل‌ها را نگه می‌دارد؟

InnoDB برای حفظ یکپارچگی داده و پیاده‌سازی سطوح ایزولاسیون ACID، از دو نوع قفل استفاده می‌کند: قفل سطر (row lock) و قفل جدول (table lock). در اکثر عملیات، InnoDB تلاش می‌کند قفل‌ها را در سطح سطر نگه دارد تا همزمانی را بالا ببرد. ولی در برخی شرایط، مثل نبود ایندکس مناسب، مجبور می‌شود کل جدول یا بازهٔ وسیعی از سطرها را قفل کند و همین نقطه، منشأ بیشتر خطاهای Lock wait timeout است.

سه مکانیزم کلیدی در InnoDB وجود دارد که هر کدام در بروز این خطا نقش دارند:

Record lock: قفل روی یک سطر خاص که در UPDATE یا DELETE استفاده می‌شود.
Gap lock: قفل روی بازه‌ای بین دو مقدار ایندکس که برای جلوگیری از phantom read در سطح REPEATABLE READ استفاده می‌شود.
Next-key lock: ترکیبی از record lock و gap lock که در عمل، بیشترین حجم قفل را ایجاد می‌کند.

نکتهٔ مهم اینکه وجود ایندکس مناسب روی ستون‌های شرط WHERE، باعث می‌شود InnoDB از قفل در سطح سطر استفاده کند و احتمال خطا کاهش یابد. در مقابل، نبود ایندکس، باعث اسکن کامل جدول می‌شود و InnoDB ناچار است تعداد زیادی از سطرها را قفل کند. در یکی از پروژه‌های ووکامرس، کاهش زمان اجرای یک کوئری UPDATE با افزودن یک ایندکس، باعث شد خطای Lock wait timeout از روزی چند بار به صفر برسد. این تجربهٔ عملی، اهمیت ایندکس را در کنترل این خطا نشان می‌دهد. اگر می‌خواهید بدانید چرا ایندکس این‌قدر مهم است، راهنمای بهینه‌سازی کوئری‌های MySQL را ببینید.

عامل دوم، طولانی بودن تراکنش است. تراکنش‌های طولانی، قفل‌ها را مدت بیشتری در اختیار می‌گیرند و بقیه در صف می‌مانند. اگر تراکنش شما منتظر پاسخ یک API خارجی است یا داخل حلقه‌ای طولانی کار می‌کند، احتمال بروز این خطا به‌شدت بالا می‌رود. در پایتون و PHP، یکی از اشتباهات رایج این است که تراکنش را باز می‌کنید و بعد داخل آن، یک عملیات شبکه‌ای انجام می‌دهید. این الگو تقریباً همیشه به Lock wait timeout منتهی می‌شود. راهنمای تراکنش‌ها در MySQL این موضوع را با مثال‌های بیشتر توضیح داده است.

پرتکرارترین سناریوها در پروژه‌های واقعی

در تجربه‌ام، خطای Lock wait timeout در پنج سناریوی مشخص رخ می‌دهد. شناخت این سناریوها، مسیر تشخیص را از چند ساعت به چند دقیقه کاهش می‌دهد.

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

شایع‌ترین سناریو این است که یک تراکنش بدون commit یا rollback رها می‌شود. این حالت در کدهایی رخ می‌دهد که در مسیر خطا، به commit نمی‌رسند. مثال ساده در PHP:

$pdo->beginTransaction();
$pdo->exec("UPDATE orders SET status = 'processing' WHERE id = 5");
if ($someCondition) {
    exit();  // تراکنش رها می‌شود
}
$pdo->commit();

در این کد، اگر شرط برقرار باشد، تراکنش باز می‌ماند و قفل روی سطر سفارش ۵ باقی می‌ماند. هر تراکنش دیگری که بخواهد روی همان سطر کار کند، در صف می‌ماند و در نهایت با Lock wait timeout رد می‌شود. تشخیص این سناریو با کوئری SHOW ENGINE INNODB STATUS و بررسی بخش TRANSACTIONS به‌راحتی انجام می‌شود. هر تراکنش باز، شامل تعداد سطر قفل‌شده و زمان شروع است و می‌توان در چند ثانیه مقصر را پیدا کرد.

سناریو دوم: کوئری طولانی بدون ایندکس

کوئری‌هایی که به‌دلیل نبود ایندکس، روی تعداد زیادی سطر اجرا می‌شوند، قفل‌های بزرگی ایجاد می‌کنند. در نتیجه، تراکنش‌های دیگر که حتی روی سطرهای متفاوتی کار می‌کنند، نمی‌توانند پیش بروند. روش تشخیص: با EXPLAIN نگاه کنید آیا کوئری از type=ALL استفاده می‌کند یا از ref و range. اگر type برابر ALL بود، به‌احتمال زیاد مقصر همین کوئری است. برای تحلیل عمیق‌تر، راهنمای نوشتن کوئری‌های سریع‌تر SQL نکات عملی بسیاری دارد.

سناریو سوم: تراکنش‌های همزمان روی جداول مشترک

وقتی چند تراکنش همزمان، روی چند جدول مشترک کار می‌کنند، ترتیب قفل‌گیری اهمیت می‌یابد. اگر ترتیب قفل‌گیری در دو تراکنش متفاوت باشد، یکی از آن‌ها در انتظار می‌ماند. الگوی ساده: تراکنش A ابتدا جدول users را قفل می‌کند، سپس orders؛ تراکنش B ابتدا orders و سپس users. این دو تراکنش روی هم قفل می‌شوند و در شرایط فشار، یکی از آن‌ها به Lock wait timeout می‌خورد. راه‌حل: در تمام کد، ترتیب قفل‌گیری یکسانی داشته باشید. مثلاً همیشه اول users و بعد orders. این عادت کوچک، در پروژه‌های پرترافیک تفاوت بزرگی می‌سازد.

سناریو چهارم: قفل روی جدول‌های وابسته با Foreign Key

وقتی جدولی قید کلید خارجی دارد، درج یا به‌روزرسانی در جدول فرزند، نیازمند قفل مشترک روی ردیف والد است. اگر آن ردیف والد در تراکنش دیگری قفل شده باشد، این خطا رخ می‌دهد. اگر با قیود کلید خارجی آشنا نیستید، راهنمای رفع خطای Foreign key constraint fails در MySQL مفاهیم پایه را توضیح می‌دهد.

سناریو پنجم: قفل‌های اداری مانند LOCK TABLES

بعضی افزونه‌های وردپرسی، برای اطمینان از یکپارچگی داده در عملیات خاص، از LOCK TABLES استفاده می‌کنند. این قفل‌ها در سطح جدول هستند و هر تراکنش دیگری که به آن جدول نیاز دارد، در صف می‌ماند. اگر یک افزونه فراموش کند جدول را unlock کند یا در میانهٔ کار به خطا بخورد، جدول برای همه قفل می‌ماند. تشخیص این سناریو از خروجی SHOW PROCESSLIST به‌راحتی انجام می‌شود؛ چون کوئری‌های منتظر با وضعیت Waiting for table lock نمایش داده می‌شوند.

سناریو	نشانه در SHOW PROCESSLIST	راه‌حل سریع
تراکنش باز فراموش‌شده	SLEEP یا Sleep طولانی	kill کردن تراکنش و اصلاح کد
کوئری بدون ایندکس	Sending data طولانی	افزودن ایندکس مناسب
ترتیب قفل نامتوازن	Waiting for lock در چند session	یکسان‌سازی ترتیب قفل
Foreign key و والد قفل‌شده	Waiting for lock در درج فرزند	کوتاه‌کردن تراکنش والد

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

سطح ایزولاسیون و ارتباطش با این خطا

سطح ایزولاسیون (isolation level) که برای هر session تعیین می‌شود، یکی از عوامل پنهان در بروز Lock wait timeout است. InnoDB از چهار سطح ایزولاسیون پشتیبانی می‌کند:

READ UNCOMMITTED: کمترین قفل، بیشترین احتمال خواندن دادهٔ ناسازگار.
READ COMMITTED: قفل‌ها در سطح سطر، فقط روی سطرهای مشاهده‌شده.
REPEATABLE READ (پیش‌فرض): قفل‌های gap و next-key فعال هستند و احتمال Lock wait timeout بیشتر است.
SERIALIZABLE: بیشترین قفل، کمترین همزمانی.

در سطح REPEATABLE READ که پیش‌فرض MySQL است، درج یک سطر جدید در یک بازهٔ ایندکس، باعث گرفتن gap lock روی بازهٔ میان دو مقدار می‌شود. این قفل، حتی به سطرهای موجود اشاره نمی‌کند ولی ورود سطر جدید در همان بازه را مسدود می‌کند. همین باعث می‌شود در برخی سناریوها، خطای Lock wait timeout رخ دهد در حالی که دو تراکنش روی سطرهای متفاوتی کار می‌کنند. یکی از پروژه‌های ووکامرس که با این مشکل درگیر بود، با تغییر سطح ایزولاسیون به READ COMMITTED، خطای Lock wait timeout را تقریباً صفر کرد؛ چون gap lock در این سطح به‌طور پیش‌فرض غیرفعال است و تنها سطرهای واقعی قفل می‌شوند.

برای تغییر سطح ایزولاسیون در MySQL:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- یا در سطح سراسری
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

نکتهٔ مهم اینکه تغییر سطح ایزولاسیون، تعادل بین یکپارچگی و همزمانی را جابه‌جا می‌کند و باید آگاهانه انجام شود. برای فروشگاه‌های آنلاین که چندین تراکنش همزمان روی جداول مشابه کار می‌کنند، READ COMMITTED عملاً انتخاب پیش‌فرض معقولی است. ولی اگر پروژهٔ شما به داده‌های کاملاً همسان نیاز دارد، REPEATABLE READ منطقی‌تر است و در آن حالت باید قیود را از جنبهٔ معماری بهینه کنید.

برای مطالعهٔ عمیق‌تر درباره تنظیمات دیتابیس، راهنمای طراحی دیتابیس در MySQL بخش ایزولاسیون را با مثال‌های کاربردی باز کرده است.

روش گام‌به‌گام دیباگ یک قفل معیوب

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

گام اول: پیدا کردن تراکنش مسدودکننده

ابزار اصلی این مرحله، دستور زیر است که وضعیت لحظه‌ای موتور InnoDB را نشان می‌دهد:

SHOW ENGINE INNODB STATUS\G

خروجی این دستور چند بخش دارد. بخش TRANSACTIONS تمام تراکنش‌های فعال را با جزئیات نشان می‌دهد: شماره تراکنش، زمان شروع، تعداد سطرهای قفل‌شده و کوئری در حال اجرا. بخش LOCK WAITS، دقیقاً نشان می‌دهد کدام تراکنش در انتظار کدام قفل است. با دیدن این دو بخش، تقریباً همیشه می‌توان مقصر را پیدا کرد.

گام دوم: بررسی processlist

علاوه بر SHOW ENGINE INNODB STATUS، دستور زیر وضعیت همهٔ sessionهای فعال را نشان می‌دهد:

SHOW FULL PROCESSLIST;
SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM performance_schema.data_lock_waits;

جدول INNODB_TRX در MySQL 8 بسیار مفید است؛ چون همهٔ تراکنش‌های فعال را با جزئیات کامل نشان می‌دهد. جدول data_lock_waits نیز به‌طور مستقیم نشان می‌دهد کدام تراکنش منتظر کدام قفل است.

گام سوم: بستن تراکنش مسدودکننده

اگر تراکنش مسدودکننده را پیدا کردید و مطمئن شدید که متوقف‌کردن آن خطرناک نیست، با دستور زیر می‌توانید آن را kill کنید:

KILL ;

قبل از kill، حتماً به این نکته دقت کنید که این تراکنش ممکن است بخشی از یک تراکنش مالی باشد. اگر مطمئن نیستید، بهتر است اول با تیم کسب‌وکار مشورت کنید. برای بکاپ پیش از هر تغییر ساختاری، راهنمای پشتیبان‌گیری از MySQL روش‌های امن را توضیح داده است.

گام چهارم: بررسی لاگ خطا

سرور MySQL هر خطای 1205 را در error log ثبت می‌کند. با بررسی این لاگ، می‌توانید الگوهای زمانی را ببینید. مثلاً اگر خطا هر روز ساعت ۱۴ رخ می‌دهد، احتمالاً یک cron job یا یک فرآیند زمان‌بندی‌شده مقصر است. راهنمای بررسی لاگ‌های دیتابیس نکات دقیق‌تری برای فیلترکردن این لاگ‌ها دارد.

گام پنجم: تحلیل عمیق با performance_schema

در MySQL 8، جدول‌های performance_schema اطلاعات غنی‌تری فراهم می‌کنند. برای مثال، جدول events_statements_history تاریخچهٔ کوئری‌های اجراشده در هر session را نشان می‌دهد و می‌تواند به شناسایی کوئری مسبب کمک کند. اگر پروژه‌تان روی MySQL 5.7 است، این بخش محدودتر است ولی همان اطلاعات پایه در information_schema در دسترس است.

گام ششم: تست در staging

بعد از پیدا کردن مقصر، قبل از اعمال راه‌حل روی production، حتماً روی یک محیط staging تست کنید. این نکته به‌خصوص برای تغییر سطح ایزولاسیون یا افزودن ایندکس جدید حیاتی است؛ چون تأثیر این تغییرات در پروژه‌های پربار می‌تواند غیرشهودی باشد.

در وردپرس و ووکامرس چطور ظاهر می‌شود؟

وردپرس هستهٔ خودش به‌طور پیش‌فرض از تراکنش‌های طولانی استفاده نمی‌کند، ولی این موضوع در اکوسیستم افزونه‌ها کاملاً برعکس است. ووکامرس، به‌عنوان پرکاربردترین افزونهٔ فروشگاهی وردپرس، در فرآیندهای پرداخت، مدیریت موجودی، و ثبت سفارش از تراکنش استفاده می‌کند. به همین دلیل، خطای Lock wait timeout در فروشگاه‌های پربازدید ووکامرسی بیشتر از سایت‌های محتوایی دیده می‌شود.

سه موقعیت رایج در ووکامرس که این خطا را می‌بینید. اول، هنگام به‌روزرسانی موجودی محصولات، وقتی چند کاربر همزمان روی یک محصول خرید می‌کنند. دوم، هنگام اجرای cron jobهایی که سفارش‌های قدیمی را پاک می‌کنند و با تراکنش‌های کاربران همزمان می‌شوند. سوم، هنگام گزارش‌گیری سنگین از جداول wp_woocommerce_order_items و wp_woocommerce_order_itemmeta در پنل مدیریت. اگر پروژهٔ ووکامرس شما این خطا را زیاد نشان می‌دهد، راهنمای بهینه‌سازی دیتابیس ووکامرس نکات عملی بسیاری دارد.

در وردپرس، ابزار $wpdb برای اجرای تراکنش، متدهای مستقیمی ندارد ولی می‌توان با $wpdb->query('START TRANSACTION') و سپس commit یا rollback دستی کار کرد. اگر افزونه‌ای این کار را بدون محافظت از خطا انجام دهد، احتمال تراکنش باز و رها بالا می‌رود. یکی از عادات بد در افزونه‌های تازه‌کار، استفاده از تراکنش در نقاطی است که نیاز واقعی وجود ندارد. توصیهٔ من این است که قبل از هر تراکنش، دقیقاً مشخص کنید چه چیزی باید اتمیک باشد؛ و اگر نبود، تراکنش را حذف کنید.

برای بررسی عمیق‌تر تنظیمات وردپرس و چگونگی کار با دیتابیس در آن، راهنمای بهینه‌سازی کوئری‌های وردپرس با کد را ببینید. همچنین اگر با خطای کندی سایت روبه‌رو هستید، راهنمای تأثیر دیتابیس بر سرعت سایت تحلیل کاملی از این موضوع ارائه می‌دهد.

در ووکامرس، هر ثانیه‌ای که یک تراکنش باز می‌ماند، یک مشتری بالقوه در صف خرید انتظار می‌کشد؛ هزینهٔ این انتظار، به‌طور مستقیم در نرخ تبدیل فروشگاه دیده می‌شود.

پیشگیری: عادت‌هایی که این خطا را کاهش می‌دهند

پیشگیری از Lock wait timeout، بیش از هر چیز به عادت‌های کدنویسی و طراحی دیتابیس برمی‌گردد. پنج عادت زیر، در پروژه‌های واقعی بیشترین اثر را داشته‌اند.

عادت اول: تراکنش‌های کوتاه بنویسید

هرچه تراکنش کوتاه‌تر باشد، قفل‌ها کمتر در اختیار می‌مانند. عملیات شبکه‌ای، خواندن فایل، فراخوانی API و انتظار برای ورودی کاربر را هرگز داخل تراکنش انجام ندهید. اگر لازم است چند عملیات مختلف انجام دهید، آن‌ها را در چند تراکنش کوچک بشکنید. این تغییر ساده، در بسیاری از پروژه‌ها خطای Lock wait timeout را ریشه‌کن کرده است.

عادت دوم: ایندکس مناسب روی ستون‌های شرط

هر کوئری UPDATE یا DELETE که در شرط WHERE خود ستونی دارد، باید روی آن ستون ایندکس داشته باشد. بدون ایندکس، InnoDB برای یافتن سطرها مجبور است کل جدول را اسکن کند و در نتیجه، تعداد زیادی سطر را قفل می‌کند. این نکته را به‌عنوان یک قانون کلی در نظر بگیرید: قبل از هر کوئری UPDATE در production، با EXPLAIN چک کنید که از ایندکس استفاده می‌کند. اصول دقیق ایندکس‌گذاری را در راهنمای ایندکس‌گذاری در MySQL آورده‌ام.

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

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

عادت چهارم: کاهش سطح ایزولاسیون در جایی که مجاز است

برای فروشگاه‌های آنلاین و سایت‌های پربازدید که به داده‌های تقریباً همسان نیاز دارند، سطح READ COMMITTED اکثر اوقات کافی است و حجم قفل‌ها را به‌شدت کاهش می‌دهد. این تغییر، در کنار مزایای عملکردی، احتمال Lock wait timeout را هم پایین می‌آورد. البته برای جداول مالی که باید کاملاً همسان باشند، سطح بالاتر لازم است. این تصمیم را با تیم کسب‌وکار بگیرید نه صرفاً بر اساس بار سرور.

عادت پنجم: پایش دوره‌ای قفل‌ها

در پروژه‌های production، پایش هفتگی قفل‌ها با کوئری‌های ساده اطلاعات کافی می‌دهد تا پیش از تبدیل شدن به مشکل، روند را تشخیص دهید. حتی یک اسکریپت کوچک که هر پنج دقیقه SELECT COUNT(*) FROM information_schema.INNODB_TRX را اجرا کند، الگوی رشد تراکنش‌ها را نشان می‌دهد. بکاپ منظم دیتابیس، مکمل این پایش است و روش‌های آن در راهنمای پشتیبان‌گیری از MySQL توضیح داده شده است.

عادت ششم: مدیریت صحیح خطا در لایهٔ اپلیکیشن

اگر تراکنشی با خطای 1205 رد شد، اپلیکیشن باید بتواند آن را به‌طور خودکار retry کند. یک الگوی رایج این است که تراکنش را در یک حلقه با حداکثر سه تلاش و backoff نمایی اجرا کنید. این کار، فشار روی دیتابیس را کاهش می‌دهد و تجربهٔ کاربر را بهبود می‌بخشد. در PHP و پایتون، این الگو به‌راحتی پیاده‌سازی می‌شود ولی نکتهٔ مهم این است که retry فقط برای خطاهای موقت مثل 1205 و 1213 معنا دارد، نه برای خطاهای منطقی مانند Foreign key constraint fails.

پرسش‌های پرتکرار درباره Lock wait timeout

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

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

افزایش این مقدار، خطا را به تأخیر می‌اندازد ولی مشکل ریشه‌ای را حل نمی‌کند. اگر تراکنش مسدودکننده تا ابد باز باشد، حتی با مقدار ۳۰۰ ثانیه هم مشکل باقی می‌ماند. راه‌حل درست، شناسایی تراکنش مسدودکننده و اصلاح کد یا ساختار است. افزایش مقدار به‌عنوان راه‌حل موقت، در مواقع بحرانی قابل قبول است ولی باید با برنامهٔ اصلاح همراه باشد.

تفاوت این خطا با deadlock چیست؟

در deadlock، دو تراکنش به‌صورت چرخه‌ای یکدیگر را قفل می‌کنند و MySQL یکی را به‌عنوان قربانی انتخاب و rollback می‌کند. در Lock wait timeout، هیچ چرخه‌ای وجود ندارد و فقط یک تراکنش طولانی مدت زیادی قفل را در اختیار دارد. مسیر دیباگ این دو متفاوت است. برای بررسی deadlock، راهنمای رفع خطای Deadlock در MySQL را ببینید.

آیا Lock wait timeout روی داده‌ها اثر مخرب دارد؟

خودِ خطا اثری روی داده‌ها ندارد، چون تراکنش به‌طور خودکار rollback می‌شود. ولی اگر اپلیکیشن، این خطا را به‌درستی مدیریت نکند و تراکنش را دوباره اجرا کند بدون اینکه شرط‌های قبلی را بررسی کند، ممکن است باعث داده‌های تکراری یا اعمال چندبارهٔ یک عملیات شود. الگوی درست، استفاده از idempotent operations و بررسی پیش از اجرای مجدد است.

آیا این خطا می‌تواند در نتیجهٔ باگ سرور باشد؟

در نسخه‌های خاصی از MySQL و MariaDB، باگ‌های شناخته‌شده‌ای وجود داشته که باعث بروز خطاهای Lock wait غیرمنتظره می‌شده است. اگر مطمئن هستید که کد و داده بدون مشکل هستند ولی خطا همچنان ادامه دارد، changelog نسخهٔ سرور را بررسی کنید و در صورت نیاز به نسخهٔ پایدارتر ارتقا دهید.

چه مقدار برای innodb_lock_wait_timeout مناسب است؟

پیش‌فرض ۵۰ ثانیه برای اکثر پروژه‌ها بیش از حد است. در فروشگاه‌های پربازدید، مقادیر ۵ تا ۱۵ ثانیه منطقی‌تر است چون سریع‌تر خطا را نشان می‌دهد و اجازه نمی‌دهد تراکنش طولانی برای همیشه در صف بماند. مقدار دقیق به ماهیت پروژه بستگی دارد. برای سیستم‌های بانکی که هر تراکنش مالی مهم است، مقدار بالاتر منطقی است؛ برای فروشگاه‌های معمولی، مقدار پایین‌تر مناسب‌تر است.

آیا LOCK TABLES راه‌حل این خطا است؟

خیر. LOCK TABLES خودش از عوامل بروز این خطا است، نه راه‌حل آن. هر بار که جدولی را قفل می‌کنید، همهٔ تراکنش‌های دیگر روی همان جدول در صف می‌مانند. در موارد بسیار نادر که نیاز به قفل انحصاری دارید، از LOCK TABLES استفاده کنید ولی به‌جای آن معمولاً استفاده از تراکنش با سطح ایزولاسیون مناسب، راه‌حل بهتری است. اگر با خطای پرشدن جدول قفل مواجه شدید، راهنمای رفع خطای Too many connections در MySQL ممکن است کمک کند.

آیا این خطا در MySQL 5.7 و 8 تفاوت دارد؟

بله، تفاوت‌های ظریفی وجود دارد. در MySQL 8، جدول‌های information_schema و performance_schema غنی‌تر شده‌اند و اطلاعات دقیق‌تری برای دیباگ فراهم می‌کنند. همچنین برخی رفتارهای قفل‌گیری در نسخه‌های مختلف بهینه‌تر شده است. اگر پروژه‌ای را از 5.7 به 8 مهاجرت می‌دهید، حتماً روی staging تست کنید و تغییرات رفتار قفل را با دقت بررسی کنید.

ابزارها و تکنیک‌های حرفه‌ای پایش قفل

در پروژه‌های جدی، پایش دستی کافی نیست. چند ابزار و تکنیک وجود دارد که سرعت بررسی قفل‌ها را چند برابر می‌کند و در تیم‌های بالغ به یک عادت تبدیل شده است.

پایش با اطلاعات information_schema و performance_schema

دو جدول کلیدی برای پایش قفل‌ها در MySQL 8:

SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

این دو جدول، نمای لحظه‌ای قفل‌ها و انتظارها را نشان می‌دهند. برخلاف SHOW ENGINE INNODB STATUS که خروجی متنی دارد، این جدول‌ها به‌راحتی قابل فیلتر و اتصال به سایر جدول‌ها هستند. توصیهٔ من این است که این دو کوئری را در چک‌لیست پایش هفتگی قرار دهید.

پایش با ابزارهای گرافیکی

ابزارهایی مثل Percona Monitoring and Management (PMM)، MySQL Workbench و DBeaver، نمای گرافیکی از قفل‌ها و تراکنش‌ها ارائه می‌دهند. PMM در تیم‌های بزرگ محبوب است چون امکان مقایسهٔ تاریخی و هشدار خودکار را دارد. اگر پروژه‌تان پیچیده است، سرمایه‌گذاری روی یکی از این ابزارها سرعت تشخیص را چند برابر می‌کند.

پایش با اسکریپت‌های سبک

برای پروژه‌های کوچک، یک اسکریپت bash یا Python که هر پنج دقیقه کوئری‌های پایش را اجرا کند و در صورت عبور از آستانه، هشدار بدهد، کافی است. مثلاً اگر تعداد تراکنش‌های فعال از ۵۰ گذشت، ایمیل یا پیام در Slack بفرستد. این کار ساده ولی مؤثر است. چارچوب این نوع پایش در راهنمای بررسی لاگ‌های دیتابیس به تفصیل آمده است.

لاگ slow query و ارتباطش با قفل‌ها

ابزار slow query log که برای پیدا کردن کوئری‌های کند ساخته شده، به‌طور غیرمستقیم به یافتن مقصران Lock wait timeout هم کمک می‌کند. اگر یک کوئری در slow query log مکرراً ظاهر شود و همزمان خطاهای قفل هم در error log دیده شود، احتمالاً همان کوئری مقصر اصلی است. ترکیب این دو لاگ، تصویر کامل‌تری از وضعیت می‌دهد.

تست بارگذاری هدفمند

یکی از تکنیک‌های حرفه‌ای، اجرای تست بار روی محیط staging با الگوهایی است که احتمال قفل را بالا می‌برند. مثلاً اجرای چند تراکنش همزمان روی یک محصول خاص، می‌تواند رفتار قفل را در شرایط فشار نشان دهد. ابزارهایی مثل sysbench و mysqlslap برای این کار مناسب‌اند. با این تست‌ها می‌توان پیش از production، گلوگاه‌های قفل را پیدا و اصلاح کرد.

پشت صحنه InnoDB: نگاهی مهندسی به مکانیزم قفل

برای توسعه‌دهندگانی که در سطح معماری کار می‌کنند، درک رفتار InnoDB در سطح پیاده‌سازی، تفاوت‌های ظریفی را آشکار می‌کند که در پروژه‌های پرترافیک حیاتی می‌شوند.

InnoDB از یک ساختار داده به‌نام lock table استفاده می‌کند که در حافظهٔ اشتراکی سرور قرار دارد و قفل‌ها را با الگوریتم درهم‌ساز (hash) روی شناسهٔ سطر مدیریت می‌کند. هر قفل، شامل شماره تراکنش، نوع قفل و اطلاعات سطر است. زمانی که یک تراکنش به قفل درخواستی می‌رسد که در اختیار دیگری است، به صف انتظار اضافه می‌شود. مدت انتظار در صف، توسط innodb_lock_wait_timeout محدود می‌شود. بعد از پایان زمان، تراکنش از صف خارج و با کد 1205 رد می‌شود.

نکتهٔ ظریف در سطح پیاده‌سازی این است که InnoDB برای تشخیص زمان انتظار، از یک watch dog thread استفاده می‌کند که به‌طور دوره‌ای صف‌های انتظار را بررسی می‌کند. فاصلهٔ این بررسی، به نسخهٔ سرور بستگی دارد و در نسخه‌های جدید، بهینه‌تر شده است. به همین دلیل، دقیق بودن مقدار timeout در همهٔ نسخه‌ها یکسان نیست و همیشه مقداری خطا وجود دارد.

نکتهٔ دیگر اینکه قفل‌های InnoDB، مستقل از سطح ایزولاسیون هستند و در همهٔ سطوح پشتیبانی می‌شوند. ولی نوع قفل‌های گرفته‌شده، بسته به سطح ایزولاسیون متفاوت است. مثلاً در READ COMMITTED، قفل‌های gap به‌طور پیش‌فرض غیرفعال هستند و به همین دلیل، درج سطرهای جدید در بازه‌های موجود، محدودیتی ایجاد نمی‌کند. این تفاوت، یکی از دلایل محبوبیت READ COMMITTED در فروشگاه‌های آنلاین است.

در سطح کارایی، هر قفل در InnoDB نیازمند دسترسی به lock table و انجام چند عملیات اتمی است. در پروژه‌های با حجم بالای تراکنش، همین عملیات کوچک، اگر روی تعداد بالایی از سطرها تکرار شود، می‌تواند به گلوگاه تبدیل شود. به همین دلیل، یکی از اصول بهینه‌سازی در InnoDB این است که تعداد سطرهای قفل‌شده در هر تراکنش را کمینه نگه داریم. این هدف، دقیقاً با آنچه در مورد ایندکس‌گذاری و ترتیب قفل‌گیری گفته شد همسو است.

یک نکتهٔ آکادمیک که در کار روزمره هم به کار می‌آید: در نظریهٔ همزمانی دیتابیس، دو مفهوم lock و latch از هم متمایز هستند. lock در سطح منطقی و برای تراکنش‌ها است و latch در سطح فیزیکی و برای ساختارهای داده استفاده می‌شود. خطای Lock wait timeout به lock مربوط است، نه latch. در تشخیص دقیق، این تفاوت اهمیت دارد چون latch conflicts معمولاً در لاگ‌های متفاوتی ثبت می‌شوند.

در معماری‌های multi-tenant که یک دیتابیس، دادهٔ چند مشتری را نگه می‌دارد، احتمال بروز این خطا بیشتر می‌شود چون تراکنش‌های چند مشتری ممکن است روی جداول مشترک کار کنند. راهکار عملی، شاردینگ (sharding) در سطح دیتابیس است که بار تراکنش‌ها را تقسیم می‌کند. اگر پروژهٔ شما به این سطح از پیچیدگی رسیده، طراحی معماری وب راهنمای کلی خوبی برای دیدن تصویر کلان است.

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

خط پایان و توصیه‌های آخر

خطای Lock wait timeout exceeded، بیش از آنکه نشانهٔ مشکل دیتابیس باشد، نشانهٔ ناهماهنگی در لایهٔ اپلیکیشن است. این جمله را عمداً تکرار می‌کنم؛ چون در تجربه‌ام دیدم که تیم‌های تازه‌کار، همیشه سراغ تنظیمات سرور می‌روند در حالی که مقصر اصلی، کد خودشان است. یکی از پروژه‌های فروشگاهی که این خطا را روزانه چند بار می‌دید، بعد از حذف یک فراخوانی API از داخل تراکنش، برای همیشه از این خطا خلاص شد؛ بدون هیچ تغییر در تنظیمات سرور.

سه توصیهٔ پایانی من به تیم‌های فنی این است. اول، تراکنش‌ها را کوتاه نگه دارید و هر عملیات خارج از دیتابیس را بیرون از تراکنش انجام دهید. دوم، با EXPLAIN هر کوئری UPDATE و DELETE را قبل از production بررسی کنید و مطمئن شوید از ایندکس مناسب استفاده می‌کند. سوم، پایش قفل‌ها را به یک عادت دوره‌ای تبدیل کنید. اگر این سه را رعایت کنید، خطای Lock wait timeout از یک مانع آزاردهنده به یک سیگنال نادر تبدیل می‌شود که با کمی دقت، همیشه سریع ریشه‌یابی می‌شود.

اگر در پروژه‌ای با یک مورد نادر از این خطا روبه‌رو شده‌اید که در هیچ‌کدام از سناریوهای این مقاله جا نمی‌گیرد، تجربه‌تان را در دیدگاه بنویسید؛ به‌خصوص اگر خروجی SHOW ENGINE INNODB STATUS یا کوئری مقصر را ذکر کنید، می‌توانیم با هم به ریشه برسیم. همچنین اگر ترفند یا ابزار خاصی دارید که در پروژه‌های خودتان برای پایش قفل‌ها استفاده می‌کنید، همان را به اشتراک بگذارید؛ برای خواننده بعدی که با همین خطا درگیر است، تجربهٔ شما ارزشمندتر از هر مستند رسمی است. 🔐