قفل‌ها در InnoDB: record، gap، next-key و metadata lock | توسعه هوشمند فرش ایرانیان

وقتی دو نفر یک چیز را می‌خواهند

MVCC خواندن را بدون قفل حل می‌کند، اما دو نوشتن هم‌زمان روی یک ردیف باید پشت هم بایستند. InnoDB قفل را در سطح ردیف (در واقع روی رکوردهای ایندکس) می‌گیرد، نه کل جدول؛ یعنی دو کاربر می‌توانند هم‌زمان دو سفارش مختلف را ویرایش کنند.

انواع قفل

قفل	چه چیزی را قفل می‌کند	چه زمانی
Shared (S)	ردیف؛ دیگران هم می‌توانند S بگیرند اما نمی‌توانند بنویسند	`SELECT ... FOR SHARE`
Exclusive (X)	ردیف؛ هیچ قفل دیگری مجاز نیست	UPDATE، DELETE، `SELECT ... FOR UPDATE`
Record lock	یک رکورد ایندکس	جست‌وجوی برابری روی ایندکس یکتا
Gap lock	فاصله‌ی بین دو رکورد ایندکس؛ جلوی درج را می‌گیرد	REPEATABLE READ، اسکن بازه‌ای
Next-key lock	رکورد + فاصله‌ی قبل از آن	حالت پیش‌فرض قفل در RR
Metadata lock (MDL)	تعریف جدول	هر دستوری که از جدول استفاده می‌کند؛ ALTER قفل انحصاری می‌خواهد

Gap lock دلیلی است که REPEATABLE READ در قفل‌گیری هم phantom ندارد: اگر تراکنشی «همه‌ی سفارش‌های مشتری 42» را با FOR UPDATE قفل کند، کسی نمی‌تواند سفارش جدیدی برای مشتری 42 وسط کار درج کند.

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

-- ستون status ایندکس ندارد:
START TRANSACTION;
UPDATE orders SET status = 'cancelled' WHERE status = 'draft' AND created_at < '2024-01-01';
-- InnoDB برای پیدا کردن ردیف‌ها کل جدول را اسکن می‌کند و روی
-- همه‌ی رکوردهای اسکن‌شده قفل می‌گذارد؛ عملاً کل جدول تا COMMIT قفل است.

قفل روی رکوردهایی گذاشته می‌شود که پیمایش می‌شوند، نه فقط رکوردهایی که تغییر می‌کنند. ایندکس مناسب در UPDATE و DELETE فقط برای سرعت نیست؛ دامنه‌ی قفل را هم کوچک می‌کند.

چه کسی چه کسی را قفل کرده؟

SELECT waiting_pid, waiting_query, blocking_pid, blocking_query, wait_age
FROM sys.innodb_lock_waits;

SELECT object_name, index_name, lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks;

-- در صورت لزوم، اتصال مزاحم را ببندید
KILL 1234;

دام Metadata Lock

سناریوی کلاسیک قطعی سایت: یک تراکنش طولانی (یا یک SELECT سنگین گزارش) روی جدول orders در حال اجراست. شما ALTER TABLE orders ADD COLUMN ... می‌زنید. ALTER منتظر قفل انحصاری metadata می‌ماند، و از این لحظه همه‌ی کوئری‌های بعدی روی orders، حتی SELECTهای ساده، پشت ALTER صف می‌کشند. در SHOW PROCESSLIST وضعیت Waiting for table metadata lock می‌بینید.

SET SESSION lock_wait_timeout = 5;   -- ALTER بعد از ۵ ثانیه تسلیم شود، نه اینکه سایت را بخواباند
ALTER TABLE orders ADD COLUMN note VARCHAR(200) NULL, ALGORITHM=INSTANT;

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

lock_wait_timeout (برای metadata lock) با innodb_lock_wait_timeout (برای قفل ردیف) فرق دارد؛ پیش‌فرض اولی یک سال است! برای مایگریشن‌ها همیشه کمش کنید.
در READ COMMITTED، InnoDB قفل ردیف‌هایی را که در شرط WHERE صدق نکردند بلافاصله پس از بررسی آزاد می‌کند؛ در RR تا پایان تراکنش نگه می‌دارد.
درج ردیف با کلید تکراری در UNIQUE یک قفل اشتراکی روی رکورد موجود می‌گیرد؛ چند INSERT هم‌زمان با کلید یکسان منشأ رایج deadlockهای عجیب است.
ALGORITHM=INSTANT (افزودن ستون از 8.0.12 و در هر جایگاهی از 8.0.29) تغییر را فقط در متادیتا اعمال می‌کند؛ اگر ممکن نباشد خطا می‌دهد، به‌جای اینکه بی‌صدا جدول را بازسازی کند.
برای ALTER روی جدول‌های خیلی بزرگ و پرترافیک، ابزارهای gh-ost و pt-online-schema-change جدول سایه می‌سازند و بدون قفل طولانی جابه‌جا می‌کنند.

برای ذخیره‌ی پیشرفت و شرکت در آزمون، وارد شوید — رایگان است.