تکنیک‌های جلوگیری از Blocking و Deadlock در SQL Server

در مقاله‌ قبلی درباره‌ ماهیت Lock، انواع آن، علت رخداد Deadlock و روش‌های تشخیص و جلوگیری از Deadlock در SQL Server صحبت کردیم. اما در بسیاری از سازمان‌ها، مشکل فقط «فهم مفهوم» نیست. مشکل اصلی این است که Queryها و Stored Procedureها به شکل اشتباه نوشته شده‌اند و همین مسئله روزانه هزاران بار منجر به Blocking و گاهی Deadlock می‌شود.

در این مقاله، روی اصلاح Query تمرکز می‌کنیم.
یعنی:
چگونه T-SQL بنویسیم که:

• قفل‌ها سبک شوند.
• تراکنش‌ها کوتاه شوند.
• منابع اشتراکی کمتر شود.
• و Engine مجبور به صبر و انتظار نشود.

اصل «کمترین سطح Lock» در طراحی Query

SQL Server بر اساس نیاز Query تصمیم می‌گیرد که Lock در چه سطحی بگیرد:

یعنی:

اگر ایندکس درست داشته باشی → قفل کوچک (Row / Page)
اگر ایندکس نداشته باشی → قفل بزرگ (Table Lock → Blocking تضمینی)

نمونه کد اشتباه

UPDATE Orders
SET Status = 'Done'
WHERE CustomerID = 1022;

اگر روی CustomerID ایندکس نباشد → Table Scan → IX Lock روی کل جدول → Blocking بالا

نسخه صحیح با ایندکس هدفمند

CREATE INDEX IX_Orders_CustomerID ON Orders(CustomerID);

بهترین پیشگیری و جلوگیری از Deadlock در SQL Server؟
ایندکس درست، نه HINT و نه جادو.

اصل «فقط داده لازم را بخوان»

یکی از رایج‌ترین اشتباه‌ها:

SELECT * FROM Sales WHERE OrderDate > '2025-01-01';

مشکل:

• استفاده از * باعث می‌شود موتور همه ستون‌ها را لود کند.
• اگر ستون‌های غیرایندکسی نیاز شود → مجدد Table Scan

نسخه صحیح:

SELECT OrderID, Amount, OrderDate
FROM Sales
WHERE OrderDate >= @DateFrom;

قاعده

همیشه ستون‌ها را مشخص کن.

استفاده‌ حرفه‌ای از Isolation Levels برای کاهش Lock

سناریو

گزارش‌گیری Real-Time بدون ایجاد Blocking

راه‌حل درست:

ALTER DATABASE MyDB SET READ_COMMITTED_SNAPSHOT ON;

جایگزین حرفه‌ای برای NOLOCK که Dirty Read ندارد.

تراکنش باید کوتاه باشد، «منطقه بحرانی» را کوچک کنید.

بد:

BEGIN TRAN
UPDATE Accounts SET Balance = Balance + 100 WHERE ID = 10;

-- خطا: کاربر نمی‌داند چه کند → تراکنش باز مانده!
WAITFOR DELAY '00:00:05';  

UPDATE Logs SET Msg = 'Done' WHERE LogID = 99;
COMMIT;

خوب:

UPDATE Accounts SET Balance = Balance + 100 WHERE ID = 10;
UPDATE Logs SET Msg = 'Done' WHERE LogID = 99;

یا اگر تراکنش الزامی است:

BEGIN TRAN
    UPDATE Accounts ...
    UPDATE Logs ...
COMMIT;

قاعده

فقط بخش تغییر دیتا باید داخل تراکنش باشد — نه Logic و نه Delay.

ترتیب دسترسی به جداول را استاندارد کنید (Deadlock Killer قانون طلایی)

اگر تمام Stored Procedureهای سیستم به ترتیب زیر جدول‌ها را تغییر دهند:

Customers → Orders → Payments

Deadlock تقریباً صفر می‌شود.

اگر برخی به ترتیب زیر عمل کنند:

Customers → Orders

و برخی دیگر:

Orders → Customers

بن‌بست قطعی است.

پس

یک نقشه‌ «ترتیب استاندارد دسترسی به جداول» باید در تیم تعریف شود.

به این می‌گوییم:

Global Lock Acquisition Order

این چیزی است که در سازمان‌های Enterprise اجرا می‌شود و لاندا آن را مستند و enforce می‌کند.

استفاده حرفه‌ای از Lock Hints (اما فقط در جای درست)

مثال واقعی پیشگیری Deadlock

UPDATE Orders WITH (UPDLOCK, ROWLOCK)
SET Status = 'Processing'
WHERE OrderID = @OrderID;

کاری که این Hint می‌کند:

جلوی Shared Lockهای رقبا را می‌گیرد → قفل گرفتن در ابتدای تراکنش شفاف و یکدست می‌شود.

تشخیص Blocking در لحظه

SELECT
    blocking_session_id,
    wait_duration_ms,
    wait_type,
    resource_description,
    TEXT
FROM sys.dm_os_waiting_tasks
CROSS APPLY sys.dm_exec_sql_text(sql_handle);

قانون

اگر یک Query بیشتر از ۲ ثانیه Blocking ایجاد کرد → آن Query باید بازنویسی شود.

مانیتورینگ Lock توسط لاندا

لاندا در سرویس‌های DBA خود:

• گراف قفل‌ها را به‌صورت Real-Time ترسیم می‌کند.
• Queryهای پرریسک را Mark می‌کند.
• الگوی رفتاری Blocking را یاد می‌گیرد.
• قبل از رخداد Deadlock هشدار می‌دهد.

این چیزی است که ابزارهای عمومی ندارند.

نتیجه‌گیری

Deadlock حاصل اتفاق نیست، حاصل طراحی نامشخص است.

در معماری درست، Deadlock اصلاً نباید رخ دهد.

پیشنهاد مطالعه:

• تحلیل Wait-Stats در SQL Server

اقدام پیشنهادی لاندا

اگر سیستم شما:

• کند شده
• کاربران پیام “سیستم هنگ کرد” می‌دهد
• گزارش‌ها دیر رفرش می‌شود
• یا Deadlockهای شبانه در لاگ وجود دارد

لاندا می‌تواند:

• Queryهای پرریسک را شناسایی کند.
• الگوی قفل‌ها را تحلیل کند.
• ترتیب استاندارد دسترسی به جدول‌ها را مستند کند.
• Snapshot Isolation را بدون ریسک فعال کند.
• و Performance را در همان هفته اول بهبود دهد.

برای دریافت مشاوره تخصصی یا فعال‌سازی مانیتورینگ هوشمند، تماس ✆ بگیرید.