راهنمای کامل Lock ها در دیتابیس SQL Server

مقدمه: چرا Lock اهمیت دارد؟

سیستم‌های مدیریت پایگاه‌داده رابطه‌ای برای تضمین درستی داده‌ها و ایزولیشن تراکنش‌ها از مکانیزم Locking استفاده می‌کنند. قفل‌ها مانع تداخل مضر عملیات همزمان می‌شوند و با Isolation Level انتخابی شما تعامل مستقیم دارند. شناخت انواع قفل، شرایط اعمال و سازگاری آن‌ها برای کاهش Deadlock، جلوگیری از Dirty/Non-repeatable/Phantom Reads و بهبود کارایی ضروری است.

مفاهیم پایه و گرانولاریتی قفل

سطح قفل (Granularity): Row/Key، Page، Table، Database. موتور بر اساس هزینه و حجم داده تصمیم می‌گیرد.
Escalation: وقتی تعداد قفل‌های ریز زیاد شود، برای کاهش هزینه مدیریت قفل‌ها، قفل‌ها به سطح بالاتر (Page/Table) ارتقا می‌یابند.
Latches vs Locks: لچ‌ها (Latch) برای همزمانی داخلی حافظه/صفحه هستند و با قفل‌های تراکنشی متفاوتند.

انواع قفل‌ها

Shared (S)

برای خواندن. چند تراکنش می‌توانند همزمان S روی یک منبع داشته باشند. در حضور S، نوشتن (X) مسدود می‌شود.

Exclusive (X)

برای نوشتن (INSERT/UPDATE/DELETE). در حضور X هیچ قفل دیگری روی همان منبع سازگار نیست.

Update (U)

مرحلهٔ میانی برای Update: ابتدا U روی منبع گرفته می‌شود و هنگام تغییر به X ارتقا می‌یابد. هدف: کاهش احتمال Deadlock در الگوی «خواندن سپس نوشتن».

Intent (IS, IX, SIX)

نشانگرِ قصد قفل‌گذاری در سطح پایین‌تر. روی Table/Page گرفته می‌شوند تا موتور بداند در سطح Row/Key قفل خواهد بود.
IS: قصد Shared — IX: قصد Exclusive — SIX: Shared روی کل + Exclusive روی برخی ردیف‌ها.

Key/Range Locks

روی کلیدهای ایندکس و محدوده‌ها اعمال می‌شود؛ برای جلوگیری از Phantom در Serializable.

Page / Table Locks

قفل روی کل صفحه (۸KB) یا کل جدول. برای عملیات وسیع یا بر اساس تصمیم بهینه‌ساز.

Schema Locks (Sch-S, Sch-M)

برای عملیات متادیتا/DDL. Sch-S (پایداری اسکیمای خواندنی) در زمان اجرای Query؛ Sch-M (تغییر اسکیمای جدول) هنگام ALTER/DROP و مانند آن.

Bulk Update (BU)

مخصوص Bulk Insert/Load؛ اجازهٔ بارگذاری سریع و جلوگیری از تداخل خواندن ناهمساز.

جدول سازگاری قفل‌ها (Compatibility Matrix)

ماتریس زیر رایج‌ترین قفل‌ها را در سطح یک منبع واحد (Row/Page/Table) نشان می‌دهد. ✅ یعنی سازگار و ❌ یعنی ناسازگار.

Requested \ Held	S	U	X	IS	IX	SIX
S	✅	✅	❌	✅	❌	❌
U	✅	❌	❌	✅	❌	❌
X	❌	❌	❌	❌	❌	❌
IS	✅	✅	❌	✅	✅	✅
IX	❌	❌	❌	✅	✅	❌
SIX	❌	❌	❌	✅	❌	❌

نکته: سازگاری بالا برای «یک منبع یکسان» بیان شده است. در عمل، Intent Lockها روی سطح بالاتر (Table/Page) گرفته می‌شوند تا قفل‌های ریز (Row/Key) در سطح پایین مدیریت‌پذیر شوند.

شرایط اعمال قفل و نقش Isolation Level

نوع عملیات: SELECT → S؛ INSERT/UPDATE/DELETE → X (اغلب با Intent در سطح بالاتر).
Isolation Level: Read Uncommitted قفل‌گذاری حداقلی (ممکن است Dirty Read)، Read Committed پیش‌فرض (قفل‌گذاری خواندن/نوشتن)، Repeatable Read حفظ S روی رکوردهای خوانده‌شده، Serializable استفاده از Range/Key Locks برای جلوگیری از Phantom، Snapshot/RCSI استفاده از Row Versioning (کاهش بن‌بست‌ها و قفل‌های خواندن).
تصمیم بهینه‌ساز: انتخاب Row/Page/Table Lock و Escalation بر اساس هزینه/تعداد ردیف‌ها.

Deadlock، Update Lock و توصیه‌های پیشگیری

Deadlock زمانی رخ می‌دهد که دو تراکنش منابع یکدیگر را منتظر بگذارند. استفاده از U Lock در الگوی «خواندن سپس نوشتن» باعث می‌شود به‌جای چندین S همزمان (که همگی درخواست ارتقای X می‌دهند)، از ابتدا مسیر ارتقا مدیریت‌پذیر باشد.

دسترسی به منابع را به یک ترتیب ثابت استاندارد کنید.
تراکنش‌ها را کوتاه نگه دارید؛ از انتظارهای غیرضروری داخل تراکنش پرهیز کنید.
ایندکس‌گذاری مناسب برای کاهش اسکن‌ها و دامنهٔ قفل‌ها.
در سناریوهای خواندن زیاد، Snapshot/RCSI را بررسی کنید.

Lock Hints (نمونه‌های پرکاربرد) و مثال‌ها

برخی Hintهای رایج

WITH (HOLDLOCK) ≈ Serializable روی محدودهٔ هدف
WITH (UPDLOCK) گرفتن U به‌جای S برای کاهش بن‌بست
WITH (ROWLOCK|PAGLOCK|TABLOCK) پیشنهاد سطح قفل
WITH (NOLOCK) ≈ Read Uncommitted (خطر Dirty Read)
WITH (XLOCK|S|U) اجبار نوع قفل

فعال‌سازی Row Versioning

-- Read Committed Snapshot Isolation (RCSI)
ALTER DATABASE YourDb SET READ_COMMITTED_SNAPSHOT ON;

-- Snapshot Isolation (نیاز به SET ALLOW_SNAPSHOT_ISOLATION ON)
ALTER DATABASE YourDb SET ALLOW_SNAPSHOT_ISOLATION ON;

-- استفاده:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
  SELECT ... FROM dbo.T WHERE ...;
COMMIT;

توجه: RCSI به‌صورت شفاف روی Read Committed اثر می‌گذارد؛ Snapshot نیازمند SET سطح تراکنشی است.

نمونه سناریوی بهبود بن‌بست با UPDLOCK

-- جدول نمونه
CREATE TABLE dbo.Inventory(
  Id int IDENTITY PRIMARY KEY,
  Sku varchar(50) NOT NULL,
  Qty int NOT NULL
);
INSERT INTO dbo.Inventory(Sku, Qty) VALUES ('A-100', 5), ('B-200', 3);

-- تراکنش 1
BEGIN TRAN;
  -- خواندن با UPDLOCK به جای S
  SELECT Qty FROM dbo.Inventory WITH (UPDLOCK, ROWLOCK)
    WHERE Sku = 'A-100';
  -- منطق برنامه...
  UPDATE dbo.Inventory SET Qty = Qty - 1 WHERE Sku = 'A-100';
COMMIT;

-- در تراکنش‌های همروند مشابه، UPDLOCK احتمال بن‌بست را کم می‌کند.

مشاهده و مانیتورینگ قفل‌ها

DMVها

-- قفل‌های جاری
SELECT
  tl.request_session_id AS spid,
  tl.resource_type,
  tl.resource_database_id,
  tl.resource_associated_entity_id AS hobt_id,
  tl.request_mode,
  tl.request_status
FROM sys.dm_tran_locks AS tl
ORDER BY tl.request_session_id;

-- درخواست‌های درحال اجرا
SELECT
  r.session_id, r.status, r.command, r.wait_type, r.wait_time,
  r.blocking_session_id, r.cpu_time, r.logical_reads, r.row_count, r.sql_handle
FROM sys.dm_exec_requests AS r
WHERE r.session_id > 50;

-- تراکنش‌های باز
DBCC OPENTRAN;

برای نگاشت hobt_id به جدول/ایندکس از sys.partitions و sys.indexes کمک بگیرید.

نمونهٔ اتصال قفل به آبجکت

SELECT
  tl.request_session_id,
  tl.resource_type,
  tl.request_mode,
  OBJECT_NAME(p.object_id) AS object_name,
  i.name AS index_name
FROM sys.dm_tran_locks AS tl
JOIN sys.partitions AS p
  ON p.hobt_id = tl.resource_associated_entity_id
LEFT JOIN sys.indexes AS i
  ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE tl.resource_type IN ('OBJECT','PAGE','KEY','RID')
ORDER BY tl.request_session_id;

Serializable و Range/Key Locks (جلوگیری از Phantom)

-- جدول نمونه
CREATE TABLE dbo.Orders(
  Id int IDENTITY PRIMARY KEY,
  CustomerId int NOT NULL,
  Amount money NOT NULL
);
INSERT INTO dbo.Orders(CustomerId, Amount) VALUES (1, 120), (1, 80), (2, 200);

-- تراکنش A: جلوگیری از رکوردهای شبح (Phantom)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN;
  -- این کوئری Range/Key Locks می‌گیرد
  SELECT SUM(Amount) FROM dbo.Orders WHERE CustomerId = 1;

  -- تا پایان تراکنش، درج/حذف رکوردهای جدید با CustomerId=1 مسدود می‌شود.
  -- ... منطق برنامه ...
COMMIT;

در این حالت درج رکورد جدید با CustomerId = 1 توسط تراکنش دیگر، تا پایان تراکنش A بلوکه می‌شود.

نکات و الگوهای عملی برای کنترل Lock

ایندکس‌های هدفمند بسازید تا محدودهٔ قفل کوچک‌تر شود (Row/Key به جای Page/Table).
ترتیب دسترسی به جداول/رکوردها را استاندارد کنید تا Deadlock حلقه‌ای شکل نگیرد.
تراکنش‌ها را کوتاه نگه دارید؛ I/O خارجی (Network/Prompt/UI) را بیرون از تراکنش انجام دهید.
برای بارخوانی سنگین، RCSI/Snapshot را بررسی کنید تا قفل‌های خواندن حذف/کاهش یابد.
Escalation را زیر نظر بگیرید؛ در صورت نیاز با ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE|AUTO|TABLE) تنظیم کنید.
از Hintها با احتیاط استفاده کنید؛ NOLOCK می‌تواند نتایج نادرست بدهد.

جمع‌بندی

قفل‌ها ستون فقرات کنترل همزمانی در RDBMS هستند. با شناخت انواع قفل (S/U/X/IS/IX/SIX)، ماتریس سازگاری، گرانولاریتی و نقش Isolation Level می‌توانید تجربه‌ای پایدار، درست و کارا برای تراکنش‌ها رقم بزنید. در سناریوهای «خواندن زیاد»، نسخه‌گذاری ردیف (Snapshot/RCSI) و در سناریوهای «نوشتن رقابتی»، طراحی تراکنش کوتاه، ایندکس خوب و الگوی UPDLOCK معمولاً بهترین نتایج را می‌دهند.

خلاصه مقاله

این مقاله با مرور انواع قفل‌های دیتابیس (S، U، X، Intentها، Key/Range، Page/Table و Schema) و نشان‌دادن ماتریس سازگاری، توضیح داد که قفل‌ها چگونه بر اساس نوع عملیات، Isolation Level و تصمیم بهینه‌ساز اعمال می‌شوند و چطور می‌توان با الگوهایی مانند UPDLOCK، ایندکس‌گذاری هدفمند، تراکنش کوتاه و Snapshot/RCSI، از بن‌بست و ناهماهنگی داده جلوگیری کرد. همچنین با DMVها و نمونه‌کدها، روش مشاهده و تحلیل قفل‌ها ارائه شد تا بتوانید در پروژه‌های واقعی تعادل بین صحت و کارایی را برقرار کنید.

Requested \ Held	S	U	X	IS	IX	SIX
S	✅	✅	❌	✅	❌	❌
U	✅	❌	❌	✅	❌	❌
X	❌	❌	❌	❌	❌	❌
IS	✅	✅	❌	✅	✅	✅
IX	❌	❌	❌	✅	✅	❌
SIX	❌	❌	❌	✅	❌	❌

Requested \ Held	S	U	X	IS	IX	SIX
S	✅	✅	❌	✅	❌	❌
U	✅	❌	❌	✅	❌	❌
X	❌	❌	❌	❌	❌	❌
IS	✅	✅	❌	✅	✅	✅
IX	❌	❌	❌	✅	✅	❌
SIX	❌	❌	❌	✅	❌	❌

Requested \ Held	S	U	X	IS	IX	SIX
S	✅	✅	❌	✅	❌	❌
U	✅	❌	❌	✅	❌	❌
X	❌	❌	❌	❌	❌	❌
IS	✅	✅	❌	✅	✅	✅
IX	❌	❌	❌	✅	✅	❌
SIX	❌	❌	❌	✅	❌	❌