‏یه کانکشن باز چطور کل دیتابیس رو زمین می‌زنه

این پست با یه سوال مصاحبه شروع می‌شه که Sam Lambert سال‌ها پیش تو GitHub می‌پرسید: پیج شدی، همهٔ کوئری‌ها fail می‌شن، بگو چرا. جواب یه تغییر اسکیمای MySQL بود که قفل انحصاری روی یه جدول می‌خواست، ولی یه سشن دیگه قبلاً اون جدول رو دست زده بود و هیچ‌وقت COMMIT نکرده بود. اون تراکنش باز، جلوی تغییر اسکیما رو گرفت و بعد هر کوئری جدید هم پشت اون صف کشید. نویسنده می‌گه این ماجرا مخصوص MySQL نیست و روی Postgres هم با یه کاربر معمولی اپلیکیشن بازتولید می‌شه:

-- Connection A: کاربر معمولی اپلیکیشن
BEGIN;
SELECT * FROM orders WHERE id = 123;
-- بعدش اپ استثنا می‌ده و COMMIT هیچ‌وقت نمی‌رسه
-- Connection B: مهاجرت، پشت قفل گیر می‌کنه
ALTER TABLE orders ADD COLUMN foo integer;

SELECT تموم شده، ولی Postgres کانکشن رو نگه می‌داره چون منتظر COMMIT یا ROLLBACK هست که هیچ‌وقت نمی‌رسه. SELECT فقط قفل خواندن (ACCESS SHARE) می‌خواست، ولی ALTER TABLE قفل ACCESS EXCLUSIVE می‌خواد که همه‌چیز رو می‌بنده، پس منتظر می‌مونه. Postgres هم اجازه نمی‌ده کوئری‌های بعدی از صف بزنن جلو، پس هر کوئری جدید روی orders هم گیر می‌کنه. یعنی یه استثنای هندل‌نشده می‌تونه بشه داون‌تایم. پیشگیریش هم اینه که به idle_in_transaction_session_timeout مقدار بدی، چون پیش‌فرضش غیرفعاله.

بخش دوم پست معرفی امکانات جدید PlanetScale برای همین دردسره. تا حالا برای دیدن وضعیت کانکشن‌ها باید یه کوئری رو تو ترمینال لوپ می‌کردی، و بدترین حالت این بود که دیتابیس از بس کانکشن باز داره جا برای خودت نمی‌ذاره که وصل شی و مقصر رو بکشی. به گفتهٔ نویسنده تب Connections از یه کانکشن ادمین رزروشده استفاده می‌کنه، پس حتی وقتی کانکشن‌ها تموم شدن هم لیست پروسه‌ها رو نشون می‌ده، با ستون‌هایی مثل Process ID و State و Duration و مهم‌تر از همه ستونی که می‌گه هر کانکشن چند کوئری رو معطل نگه داشته.

کشتن پروسه سه گزینه داره، از کم‌خطر به پرخطر: Cancel query فقط دستور در حال اجرا رو متوقف می‌کنه و کانکشن باز می‌مونه، Terminate transaction تراکنش باز رو رول‌بک می‌کنه و کانکشن رو می‌بنده، و Terminate connection کل backend رو می‌ندازه. تو همون مثال داون‌تایم، چون SELECT قبلاً تموم شده بود کنسل‌کردن کوئری قفل رو آزاد نمی‌کنه و باید تراکنش یا کانکشن رو بکشی. تو CLI هم pscale branch connections top یه نمای زنده می‌ده که حدوداً هر ثانیه رفرش می‌شه.

نکات کلیدی:

• پیش‌فرض idle_in_transaction_session_timeout تو Postgres غیرفعاله؛ مقدار دادن به همین یه گزینه جلوی کل این صف‌بستن رو می‌گیره
• SELECT قفل ACCESS SHARE می‌گیره و ALTER TABLE قفل ACCESS EXCLUSIVE؛ کوئری‌های بعدی هم حق جلو زدن از صف ندارن
• تب Connections از یه کانکشن ادمین رزروشده استفاده می‌کنه، پس وقتی کانکشن‌ها پر شدن هم کار می‌کنه
• داشبورد ستونی داره که می‌گه کدوم کانکشن بقیهٔ کوئری‌ها رو معطل کرده
• روی Vitess هر پروسه دو گزینه داره: cancel query و terminate connection