چرا ALTER در MySQL با LOCK=NONE هم منتظر می‌ماند؟

این پنل را در سه ترمینال جدا اجرا و آن‌ها را A، B و C نام‌گذاری کنید. مقصد هر سه باید همان سرور باشد. اجرای همهٔ SQLها در یک connection، انتظار بین نشست‌ها را نشان نمی‌دهد.

شناسهٔ هر اتصال را نگه دارید تا بعداً بدانید کدام‌یک قفل را نگه داشته و کدام منتظر است. نشست C باید همان دسترسی مشاهدهٔ ازقبل‌مجاز را داشته باشد.

mysql --defaults-extra-file='/home/academy/.mysql-lab.cnf' --database='academy_mdl_20261028' --init-command='SELECT CONNECTION_ID(),@@hostname,VERSION()'

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

تمرین را روی جدول برنامه اجرا نکنید و بی‌نظارت رها نکنید. در ادامه همین تراکنشِ متعلق به خودتان را پایان می‌دهید؛ نیازی به قطع connection دیگران نیست.

START TRANSACTION;
SELECT CONNECTION_ID(),count(*) FROM mdl_probe;

در نشست B فقط lock_wait_timeout همان نشست را تنظیم کنید. سپس ساخت ایندکس را با ALGORITHM=INPLACE و LOCK=NONE صریح بخواهید تا اگر این ترکیب برای عملیات مجاز نیست، خطا بدهد و پنهانی به روش مزاحم‌تری تبدیل نشود.

تا وقتی A تراکنش را باز نگه داشته، B باید برای گرفتن قفل فرادادهٔ جدول منتظر بماند و در پایان مهلت، خطا بگیرد. lock_wait_timeout را با innodb_lock_wait_timeout اشتباه نگیرید؛ دومی به انتظار قفل ردیف مربوط است.

SET SESSION lock_wait_timeout=30;
ALTER TABLE mdl_probe ADD INDEX idx_category(category),ALGORITHM=INPLACE,LOCK=NONE;

در نشست C و پیش از پایان مهلت B اجرا کنید. نمای sys شناسهٔ منتظر و مسدودکننده و مدت انتظار را می‌دهد؛ متن کامل کوئری‌ها و پیشنهاد kill را چاپ نمی‌کنیم. در نمای خام نیز قفل اعطاشده و درخواست pending را با شناسهٔ اتصال تطبیق می‌دهیم.

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

SELECT object_schema,object_name,waiting_pid,blocking_pid,waiting_query_secs FROM sys.schema_table_lock_waits WHERE object_schema='academy_mdl_20261028' AND object_name='mdl_probe';
SELECT t.PROCESSLIST_ID,m.LOCK_TYPE,m.LOCK_DURATION,m.LOCK_STATUS FROM performance_schema.metadata_locks m LEFT JOIN performance_schema.threads t ON t.THREAD_ID=m.OWNER_THREAD_ID WHERE m.OBJECT_SCHEMA='academy_mdl_20261028' AND m.OBJECT_NAME='mdl_probe';

ابتدا خطای پایان مهلت B را ثبت کنید. سپس در A تراکنش فقط‌خواندنی خودتان را ROLLBACK کنید. در رخداد واقعی، قطع‌کردن اتصال دیگری می‌تواند rollback بزرگ یا اختلال برنامه بسازد؛ باید مالک و پیامد آن معلوم باشد.

پنل مشاهدهٔ C را دوباره اجرا کنید و نبودن درخواست منتظر و قفل نگه‌داشته‌شدهٔ A را ببینید. مقدار autocommit به‌تنهایی پایان تراکنش را ثابت نمی‌کند؛ موفقیت ROLLBACK و وضعیت قفل‌ها را با هم بخوانید.

ROLLBACK;
SELECT CONNECTION_ID(),@@autocommit;

ابتدا بررسی کنید ایندکس پس از DDL ناموفق ساخته نشده باشد. اگر وجود دارد، همان ADD را دوباره نفرستید و نتیجه را بررسی کنید. پس از پایان A، یک بار عملیات را تکرار و تعریف جدول را بخوانید.

DDL در MySQL commit ضمنی دارد و با ROLLBACK بعدی برنمی‌گردد. موفقیت این جدول سه‌ردیفی دربارهٔ زمان، فضای دیسک یا اثر روی replica در یک جدول بزرگ چیزی را ثابت نمی‌کند.

SHOW INDEX FROM mdl_probe;
ALTER TABLE mdl_probe ADD INDEX idx_category(category),ALGORITHM=INPLACE,LOCK=NONE;
SHOW CREATE TABLE mdl_probe;