چگونه LOCK‌ها را در جداول PostgreSQL شناسایی و برطرف کنیم؟

گاهی اوقات هنگام اجرای یک کوئری در پایگاه داده، ممکن است با مشکل هنگ کردن کوئری مواجه شوید یا تراکنشی را باز کنید که هرگز پایان نمی‌یابد. چنین مواردی می‌توانند منجر به ایجاد LOCK روی جداول در PostgreSQL شوند. برای مدیریت این شرایط، باید LOCK‌ها و فرآیندهایی که آنها را ایجاد کرده‌اند شناسایی کرده و در صورت لزوم آنها را برطرف کنید.

شناسایی LOCK‌ها

اولین نشانه وجود LOCK روی یک جدول، ناتوانی در اجرای عملیات ساده‌ای مانند حذف یک ردیف از جدول است. به‌عنوان مثال:

design_system=> DELETE FROM search_hit WHERE id = 154193;

Cancel request sent

ERROR:  canceling statement due to user request

CONTEXT:  while deleting tuple (22286,5) in relation “search_hit”

مشاهده لیست LOCK‌ها

PostgreSQL اطلاعات آماری جامعی در جداول متادیتا ذخیره می‌کند. یکی از این جداول pg_locks است. با پیوند این جدول به pg_class می‌توانید LOCK‌های موجود در جداول خاص را شناسایی کنید. برای مثال، جهت بررسی LOCK‌های روی جدول search_hit می‌توانید از کوئری زیر استفاده کنید:

SELECT pid

FROM pg_locks l

JOIN pg_class t ON l.relation = t.oid

WHERE t.relkind = ‘r’

AND t.relname = ‘search_hit’;

خروجی مشابه زیر خواهد بود:

pid

——-

۱۱۳۳۷

۱۶۳۸۹

۱۶۳۸۹

۱۱۹۲۹

(۴ rows)

این PIDها نشان‌دهنده فرآیندهایی هستند که منجر به LOCK شدن جدول شده‌اند.

تطبیق کوئری با LOCK‌ها

برای شناسایی کوئری‌هایی که LOCK‌ها را ایجاد کرده‌اند، می‌توانید از جدول pg_stat_activity استفاده کرده و اطلاعات مرتبط را فیلتر کنید:

SELECT pid, state, usename, query, query_start

FROM pg_stat_activity

WHERE pid IN (

SELECT pid

FROM pg_locks l

JOIN pg_class t ON l.relation = t.oid

WHERE t.relkind = ‘r’

AND t.relname = ‘search_hit’

);

این کوئری، اطلاعات مفصلی درباره فرآیندهای LOCK‌کننده، وضعیت آنها، نام کاربر، کوئری در حال اجرا و زمان شروع آن ارائه می‌دهد.

برطرف کردن LOCK‌ها

اگر فرآیندهایی که LOCK‌ها را ایجاد کرده‌اند همچنان فعال باشند، می‌توانید با متوقف کردن یا خاتمه دادن به این فرآیندها، LOCK‌ها را برطرف کنید.

روش ۱: لغو فرآیند با pg_cancel_backend

برای متوقف کردن یک فرآیند می‌توانید از pg_cancel_backend استفاده کنید. به‌عنوان مثال:

SELECT pg_cancel_backend(11929);

این دستور فرآیند مربوطه را متوقف می‌کند. با این حال، ممکن است این روش همیشه موفقیت‌آمیز نباشد. برای اطمینان، دوباره وضعیت LOCK‌ها را بررسی کنید.

روش ۲: خاتمه فرآیند با pg_terminate_backend

اگر فرآیند لغو نشود، می‌توانید با استفاده از pg_terminate_backend آن را به‌طور کامل خاتمه دهید:

SELECT pg_terminate_backend(11929);

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

با استفاده از روش‌های فوق می‌توانید LOCK‌های جداول PostgreSQL را به‌سرعت شناسایی و مدیریت کنید تا از تأخیر در عملکرد پایگاه داده جلوگیری شود.