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

رفع انتظار Metadata Lock در MySQL؛ چرا ALTER TABLE متوقف می‌شود؟

شناسایی تراکنش مسدودکننده تغییر ساختار جدول در MySQL، تشخیص Metadata Lock از قفل ردیف و رفع انتظار با کمترین اختلال.

رفع انتظار Metadata Lock در MySQL؛ چرا ALTER TABLE متوقف می‌شود؟

گاهی ALTER TABLE ظاهراً متوقف می‌شود، درحالی‌که مصرف CPU پایین است. علت می‌تواند تراکنشی باشد که جدول را خوانده و بعد بیکار مانده؛ نه یک Query سنگین. MySQL برای حفظ سازگاری ساختار اشیا از Metadata Lock استفاده می‌کند و تراکنش تمام‌نشده می‌تواند تغییر Schema را متوقف کند. این راهنما برای MySQL 8.0 و 8.4 است؛ رفتار MariaDB یا نسخه‌های قدیمی را جداگانه بررسی کنید.

۱. قفل متادیتا با قفل ردیف متفاوت است

قفل ردیف از رکوردهای در حال تغییر محافظت می‌کند؛ Metadata Lock از تعریف جدول و سازگاری دسترسی هم‌زمان. تراکنش صریحی که فقط SELECT اجرا کرده، ممکن است تا پایان تراکنش قفل متادیتای جدول را نگه دارد. بنابراین Session در وضعیت Sleep هم می‌تواند DDL را مسدود کند. خالی‌بودن فیلد Query فعلی یا CPU پایین به معنی پایان تراکنش نیست.

درخواست قفل انحصاری DDL که در صف مانده، می‌تواند به انتظار عملیات بعدی روی همان جدول نیز منجر شود. اگر هنگام مهاجرت ساختار، زمان پاسخ سایت بالا می‌رود، چند ALTER دیگر اجرا نکنید. ابتدا زنجیره انتظار را پیدا کنید. Online DDL یا Instant DDL هم به معنای حذف کامل نیاز به Metadata Lock نیست.

۲. Session منتظر و مسدودکننده را پیدا کنید

SELECT VERSION();
SHOW FULL PROCESSLIST;
SELECT object_schema, object_name,
       waiting_pid, waiting_query_secs, waiting_query,
       blocking_pid, blocking_account, blocking_lock_type
FROM sys.schema_table_lock_waits
ORDER BY waiting_query_secs DESC;

View مربوط به sys شناسه Processlist درخواست منتظر و مسدودکننده را نشان می‌دهد. یک درخواست ممکن است چند مسدودکننده داشته باشد؛ تمام ردیف‌ها را نگه دارید. شناسه‌ها را با کاربر، میزبان، فرایند انتشار و سن تراکنش تطبیق دهید. برای مشاهده اطلاعات از حساب تشخیصی مجاز استفاده کنید. خالی‌بودن خروجی ممکن است ناشی از سطح دسترسی یا Instrumentation باشد، نه نبودن قفل.

۳. تراکنش و قفل‌های واقعی را بررسی کنید

SELECT trx_mysql_thread_id, trx_started, trx_state, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

SELECT ml.OBJECT_SCHEMA, ml.OBJECT_NAME,
       ml.LOCK_TYPE, ml.LOCK_DURATION, ml.LOCK_STATUS,
       th.PROCESSLIST_ID, th.PROCESSLIST_USER, th.PROCESSLIST_INFO
FROM performance_schema.metadata_locks AS ml
LEFT JOIN performance_schema.threads AS th
       ON th.THREAD_ID = ml.OWNER_THREAD_ID
WHERE ml.OBJECT_TYPE = 'TABLE'
  AND ml.OBJECT_SCHEMA = 'appdb'
  AND ml.OBJECT_NAME = 'orders';

appdb و orders را با نام واقعی Schema و جدول جایگزین کنید. LOCK_STATUS برابر GRANTED یعنی قفل گرفته شده و PENDING یعنی درخواست هنوز قفل را نگرفته است. اتصال جدول threads شناسه Processlist را می‌دهد؛ THREAD_ID داخلی را با شناسه قابل استفاده در KILL اشتباه نگیرید. هر قفل GRANTED لزوماً مسدودکننده موردنظر نیست؛ نوع قفل و رابطه گزارش‌شده در sys را مقایسه کنید.

اگر اطلاعات قفل ثبت نمی‌شود، Instrument با نام wait/lock/metadata/sql/mdl را در performance_schema.setup_instruments بررسی کنید و تغییر آن را با مدیر دیتابیس هماهنگ کنید. همه ثبت‌های Performance Schema را کورکورانه فعال نکنید. نداشتن مجوز PROCESS ممکن است مشاهده تراکنش حساب دیگر را محدود کند؛ به‌جای حدس‌زدن، از نشست تشخیصی مجاز کمک بگیرید.

۴. با کمترین اختلال انتظار را رفع کنید

  • اگر DDL ضروری نیست و انتظار آن صف ایجاد کرده، ابتدا از طریق مالک یا ابزار Deploy، مهاجرت منتظر را لغو کنید.
  • از مالک تراکنش مسدودکننده بخواهید آن را آگاهانه تمام کند: COMMIT برای نگهداری تغییرات صحیح و ROLLBACK برای کنارگذاشتن تغییرات. این دستورها باید در همان Session مالک اجرا شوند.
  • اجرای KILL QUERY روی نشست بیکار الزاماً تراکنش را تمام نمی‌کند؛ لغو یک دستور با بستن تراکنش یکسان نیست.
  • KILL CONNECTION را فقط پس از تأیید شناسه فعلی، مجوز، اثر تجاری و برنامه بازیابی انجام دهید. قطع اتصال می‌تواند کار ثبت‌نشده را Rollback کند و Rollback نیز زمان ببرد.

۵. انتظار مهاجرت را در همان Session محدود کنید

-- In the migration connection, before the reviewed DDL:
SET SESSION lock_wait_timeout = 10;

این نمونه زمان انتظار گرفتن Metadata Lock در همان نشست را محدود می‌کند؛ تضمین نمی‌کند کل عملیات ALTER در ده ثانیه تمام شود. عدد را با سیاست Deploy و Timeout کلاینت تطبیق دهید. innodb_lock_wait_timeout مربوط به قفل ردیف InnoDB است و جای سیاست انتظار قفل متادیتا را نمی‌گیرد. هنگام حادثه، بدون شناخت اثر روی همه برنامه‌ها مقدار Global را تغییر ندهید.

۶. فقط در محیط آزمایش بازتولید کنید

-- Lab only: disposable database and table
CREATE DATABASE mdl_lab;
CREATE TABLE mdl_lab.demo (id INT PRIMARY KEY);
INSERT INTO mdl_lab.demo VALUES (1);

-- Session A
START TRANSACTION;
SELECT * FROM mdl_lab.demo;
-- Leave this transaction open briefly.

-- Session B, separate connection
SET SESSION lock_wait_timeout = 5;
ALTER TABLE mdl_lab.demo ADD COLUMN note VARCHAR(50);

-- Session A, after observing the wait
ROLLBACK;

دو نشست نمونه را دستی در دیتابیس آزمایشی اجرا کنید. ممکن است ALTER پیش از پایان تراکنش A با Timeout شکست بخورد؛ در این حالت فقط پس از آزادشدن تراکنش دوباره امتحان کنید. پیش از Retry با SHOW CREATE TABLE mdl_lab.demo ساختار واقعی را ببینید. اگر ستون قبلاً ایجاد شده باشد، افزودن مجدد آن خطا می‌دهد. برای نمایش این رفتار از جدول عملیاتی سفارش‌ها استفاده نکنید.

۷. بازیابی را تأیید و از تکرار جلوگیری کنید

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

Restart ممکن است کلاینت‌ها را قطع و شواهد اولیه را پنهان کند؛ اولین قدم عیب‌یابی نیست. اگر مشکل قفل ردیف یا Deadlock است، راهنمای مستقل آن را در آرشیو مقالات بخوانید.

منابع رسمی

MySQL metadata locking · sys.schema_table_lock_waits · Performance Schema metadata_locks