بن بست SQL Server (Deadlocks) راهنمای کامل شناسایی و پیشگیری

درک عمیق بن‌بست‌ها (Deadlocks) در SQL Server: راهنمای جامع برای شناسایی و پیشگیری

بن‌بست‌ها (Deadlocks) یکی از چالش‌های رایج در سیستم‌های مدیریت پایگاه داده، به ویژه SQL Server، هستند که می‌توانند عملکرد و دسترسی به داده‌ها را به شدت مختل کنند. این پدیده زمانی رخ می‌دهد که دو یا چند تراکنش (transaction) به صورت همزمان منابعی را قفل کرده و منتظر آزادسازی قفل توسط تراکنش‌های دیگر باشند، که در نتیجه هیچ یک نمی‌توانند کار خود را ادامه دهند. SQL Server مکانیزم‌هایی برای شناسایی و حل بن‌بست دارد، اما درک نحوه عملکرد آنها و استفاده از ابزارهای مناسب برای شناسایی و پیشگیری، برای حفظ سلامت و کارایی پایگاه داده ضروری است. این مقاله به بررسی عمیق بن‌بست‌ها، نحوه شناسایی آنها با ابزارهای مختلف و استراتژی‌های پیشگیری می‌پردازد. تصور کنید دو تراکنش دارید که هر کدام سعی می‌کنند به داده‌هایی دسترسی پیدا کنند که دیگری آن را قفل کرده است. تراکنش A منبع X را قفل می‌کند و به منبع Y نیاز دارد که توسط تراکنش B قفل شده است. همزمان، تراکنش B منبع Y را قفل می‌کند و به منبع X نیاز دارد که توسط تراکنش A قفل شده است. در این سناریو، هیچ یک از تراکنش‌ها نمی‌توانند پیشرفت کنند و این وضعیت منجر به بن‌بست می‌شود. SQL Server بن‌بست را تشخیص داده و یکی از تراکنش‌ها را به عنوان “قربانی بن‌بست” (deadlock victim) انتخاب کرده و آن را خاتمه می‌دهد تا تراکنش دیگر بتواند ادامه یابد. درک این فرآیند و علت وقوع آن برای توسعه‌دهندگان و مدیران پایگاه داده حیاتی است. برای اینکه بن‌بست‌ها را بهتر درک کنیم، اجازه دهید یک سناریوی ساده را شبیه‌سازی کنیم. ابتدا، یک پایگاه داده و دو جدول آزمایشی با داده‌های نمونه ایجاد می‌کنیم. این جداول برای نمایش قفل‌گذاری و تداخل بین تراکنش‌ها استفاده خواهند شد.

برای شروع، پایگاه داده و جداول لازم را ایجاد می‌کنیم:

USE master;
GO

IF DB_ID('DeadlockDemo') IS NOT NULL
DROP DATABASE DeadlockDemo;
GO

CREATE DATABASE DeadlockDemo;
GO

USE DeadlockDemo;
GO

CREATE TABLE Account (
    AccountId INT PRIMARY KEY,
    Balance DECIMAL(18, 2)
);

CREATE TABLE TransactionLog (
    LogId INT IDENTITY(1,1) PRIMARY KEY,
    AccountId INT,
    Amount DECIMAL(18, 2),
    TransactionDate DATETIME DEFAULT GETDATE()
);

INSERT INTO Account (AccountId, Balance) VALUES (1, 1000.00);
INSERT INTO Account (AccountId, Balance) VALUES (2, 500.00);
GO

این کد یک پایگاه داده به نام `DeadlockDemo` و دو جدول `Account` و `TransactionLog` را ایجاد می‌کند. جدول `Account` اطلاعات حساب‌ها و موجودی آنها را ذخیره می‌کند، در حالی که `TransactionLog` برای ثبت جزئیات تراکنش‌ها به کار می‌رود. سپس دو حساب با موجودی اولیه وارد می‌شود.

حالا سناریوی بن‌بست را با اجرای دو تراکنش به صورت همزمان شبیه‌سازی می‌کنیم. این سناریو نیازمند دو پنجره کوئری جداگانه در SQL Server Management Studio (SSMS) است که هر کدام یک تراکنش را اجرا می‌کنند.

پنجره کوئری ۱ (تراکنش A):

USE DeadlockDemo;
GO

BEGIN TRAN A;

-- Update Account 1, placing an exclusive lock on it
UPDATE Account SET Balance = Balance - 100 WHERE AccountId = 1;

-- Simulate some work
WAITFOR DELAY '00:00:05';

-- Try to update Account 2, which will be locked by Transaction B
UPDATE Account SET Balance = Balance + 100 WHERE AccountId = 2;

COMMIT TRAN A;

پنجره کوئری ۲ (تراکنش B):

USE DeadlockDemo;
GO

BEGIN TRAN B;

-- Update Account 2, placing an exclusive lock on it
UPDATE Account SET Balance = Balance - 50 WHERE AccountId = 2;

-- Simulate some work
WAITFOR DELAY '00:00:05';

-- Try to update Account 1, which will be locked by Transaction A
UPDATE Account SET Balance = Balance + 50 WHERE AccountId = 1;

COMMIT TRAN B;

برای مشاهده بن‌بست، ابتدا کد پنجره کوئری ۱ را اجرا کنید و بلافاصله پس از آن، کد پنجره کوئری ۲ را اجرا نمایید. مشاهده خواهید کرد که هر دو تراکنش برای مدتی منتظر می‌مانند و سپس یکی از آنها با پیغام خطا مواجه می‌شود که نشان‌دهنده بن‌بست است. یکی از این تراکنش‌ها به عنوان قربانی بن‌بست انتخاب شده و خاتمه می‌یابد و تراکنش دیگر ادامه پیدا می‌کند. این سناریو به وضوح نمایش می‌دهد که چگونه قفل‌گذاری متقاطع منجر به بن‌بست می‌شود.

شناسایی بن‌بست‌ها در SQL Server

شناسایی و تحلیل بن‌بست‌ها گامی کلیدی در رفع مشکلات عملکردی است. SQL Server ابزارهای مختلفی برای کمک به این امر فراهم کرده است:

1. SQL Server Profiler (ردیاب SQL Server):

   Profiler یک ابزار گرافیکی است که به شما امکان می‌دهد رویدادهای در حال وقوع در SQL Server را مشاهده کنید. برای شناسایی بن‌بست‌ها، می‌توانید یک ردیابی (trace) را پیکربندی کنید تا رویداد `Deadlock Graph` را ثبت کند. هنگامی که یک بن‌بست رخ می‌دهد، Profiler جزئیات آن را به صورت یک نمودار XML نشان می‌دهد که شامل اطلاعاتی درباره تراکنش‌ها، قفل‌ها و منابع درگیر است.

   برای تنظیم Profiler، یک ردیابی جدید ایجاد کرده، سپس به تب `Events Selection` بروید. در زیر `Locks`، گزینه `Deadlock Graph` را انتخاب کنید. پس از اجرای ردیابی و شبیه‌سازی بن‌بست، نمودار بن‌بست در پنجره Profiler نمایش داده می‌شود.

2. Extended Events (رویدادهای توسعه‌یافته):

   Extended Events مکانیزم جدیدتر و کارآمدتری نسبت به Profiler برای جمع‌آوری داده‌های رویدادی در SQL Server است. این ابزار تأثیر کمتری بر عملکرد سیستم دارد و انعطاف‌پذیری بیشتری را فراهم می‌کند. برای ردیابی بن‌بست‌ها با Extended Events، می‌توانید یک جلسه (session) جدید ایجاد کرده و رویداد `system_health` یا یک رویداد سفارشی را پیکربندی کنید.

   رویداد `system_health` به طور پیش‌فرض فعال است و اطلاعات مهمی از جمله بن‌بست‌ها را ثبت می‌کند. می‌توانید از کوئری زیر برای مشاهده رویدادهای بن‌بست در `system_health` استفاده کنید:

   
   SELECT
       CAST(event_data AS XML) AS event_data_xml
   FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
   WHERE object_name = 'xml_deadlock_report';
   

   این کوئری تمام گزارش‌های بن‌بست XML ذخیره شده در فایل‌های `system_health` را بازیابی می‌کند. می‌توانید خروجی XML را باز کرده و نمودار بن‌بست را مشاهده کنید.

   همچنین می‌توانید یک جلسه Extended Events سفارشی برای جمع‌آوری اطلاعات بن‌بست ایجاد کنید:

   
   CREATE EVENT SESSION DeadlockMonitor
   ON SERVER
   ADD EVENT sqlserver.xml_deadlock_report
   ADD TARGET package0.asynchronous_file_target
   (SET filename = 'C:\Temp\DeadlockMonitor.xel', max_file_size = 5, max_rollover_files = 4);
   GO
   
   ALTER EVENT SESSION DeadlockMonitor
   ON SERVER
   STATE = START;
   GO
   

   پس از ایجاد و راه‌اندازی این جلسه، گزارش‌های بن‌بست در فایل `DeadlockMonitor.xel` در مسیر مشخص شده ذخیره می‌شوند. برای مشاهده این گزارش‌ها، می‌توانید از کوئری مشابه بالا استفاده کنید، فقط نام فایل و مسیر را تغییر دهید.

3. Trace Flags (پرچم‌های ردیابی):

   پرچم‌های ردیابی `1204` و `1222` می‌توانند اطلاعات بن‌بست را در گزارش خطای SQL Server (SQL Server Error Log) ثبت کنند. `1204` اطلاعات کلی بن‌بست را نشان می‌دهد، در حالی که `1222` یک خروجی XML دقیق‌تر از بن‌بست ارائه می‌دهد. این پرچم‌ها باید با دقت استفاده شوند زیرا می‌توانند حجم گزارش خطا را افزایش دهند.

   برای فعال کردن یک پرچم ردیابی به صورت موقت:

   
   DBCC TRACEON (1222, -1); -- -1 applies it globally
   GO
   

   برای غیرفعال کردن:

   
   DBCC TRACEOFF (1222, -1);
   GO
   

   اطلاعات بن‌بست سپس در گزارش خطای SQL Server قابل مشاهده خواهند بود.

استراتژی‌های پیشگیری و کاهش بن‌بست

پیشگیری از بن‌بست‌ها همیشه بهتر از تلاش برای رفع آنها پس از وقوع است. با پیاده‌سازی بهترین شیوه‌ها و طراحی صحیح تراکنش‌ها، می‌توان وقوع بن‌بست‌ها را به حداقل رساند. برخی از استراتژی‌های کلیدی عبارتند از:

• دسترسی به منابع به ترتیب ثابت:

  یکی از موثرترین روش‌ها، اطمینان از این است که تمام تراکنش‌ها همیشه به منابع مشترک به یک ترتیب ثابت دسترسی پیدا کنند. اگر تراکنش A ابتدا به منبع X و سپس به منبع Y دسترسی پیدا کند، تراکنش B نیز باید همین الگو را دنبال کند. این امر از سناریوی قفل‌گذاری متقاطع جلوگیری می‌کند.

• کوتاه نگه داشتن تراکنش‌ها:

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

• کاهش میزان داده‌های قفل شده:

  فقط داده‌هایی را که نیاز دارید قفل کنید. استفاده از قفل‌های دانه ریزتر (مانند قفل‌های ردیفی به جای قفل‌های جدولی) می‌تواند به کاهش تداخل کمک کند.

• استفاده از سطح جداسازی تراکنش مناسب:

  سطوح جداسازی پایین‌تر مانند `READ COMMITTED` (که سطح پیش‌فرض است) ممکن است نیاز به قفل‌های کمتری داشته باشند، اما ممکن است مشکلاتی مانند خواندن داده‌های تغییر یافته (dirty reads) را ایجاد کنند. `READ COMMITTED SNAPSHOT ISOLATION` یا `SNAPSHOT ISOLATION` می‌توانند با ارائه نسخه‌های داده‌ها، از مشکلات قفل‌گذاری جلوگیری کرده و به طور قابل توجهی بن‌بست‌ها را کاهش دهند.

  
  ALTER DATABASE DeadlockDemo SET READ_COMMITTED_SNAPSHOT ON;
  

  این کوئری `READ_COMMITTED_SNAPSHOT` را برای پایگاه داده `DeadlockDemo` فعال می‌کند. این گزینه با استفاده از `tempdb` برای ذخیره نسخه‌های ردیف‌ها، اجازه می‌دهد عملیات خواندن بدون قفل‌گذاری اجرا شوند و به طور موثری از بن‌بست‌های مربوط به خواندن و نوشتن جلوگیری می‌کند.

• اینجکس‌گذاری مناسب:

  ایندکس‌های کارآمد می‌توانند به تراکنش‌ها کمک کنند تا داده‌های مورد نیاز را سریع‌تر پیدا کرده و به حداقل رساندن مدت زمان نگه داشتن قفل‌ها کمک کنند.

• پیاده‌سازی منطق امتحان مجدد (Retry Logic):

  با وجود تمام تلاش‌ها، بن‌بست‌ها ممکن است همچنان رخ دهند. برنامه‌های کاربردی باید شامل منطق امتحان مجدد باشند که به طور خودکار تراکنش قربانی بن‌بست را دوباره اجرا کند. این کار باید با تأخیر تصادفی (randomized delay) همراه باشد تا از وقوع مجدد بن‌بست فوری جلوگیری شود.

• مدیریت زمان قفل‌ها (Lock Timeouts):

  می‌توانید `LOCK_TIMEOUT` را برای تراکنش‌ها تنظیم کنید تا مشخص شود یک تراکنش چه مدت زمانی برای به دست آوردن یک قفل منتظر بماند. اگر قفل در زمان مشخص شده به دست نیاید، تراکنش لغو می‌شود و خطا صادر می‌گردد. این کار از انتظار بی‌نهایت تراکنش‌ها جلوگیری می‌کند.

  
  SET LOCK_TIMEOUT 5000; -- 5 seconds
  

  این دستور `LOCK_TIMEOUT` را برای نشست فعلی روی ۵۰۰۰ میلی‌ثانیه (۵ ثانیه) تنظیم می‌کند. اگر یک تراکنش در این مدت نتواند قفل مورد نظر را به دست آورد، لغو می‌شود.

نتیجه‌گیری

بن‌بست‌ها بخش اجتناب‌ناپذیری از سیستم‌های پایگاه داده با همزمانی بالا هستند، اما می‌توان با طراحی دقیق تراکنش‌ها و استفاده صحیح از ابزارهای SQL Server، تأثیر آنها را به حداقل رساند. درک عمیق نحوه عملکرد بن‌بست‌ها، توانایی شناسایی آنها با استفاده از `Extended Events` یا `Profiler`، و پیاده‌سازی استراتژی‌های پیشگیرانه مانند دسترسی به منابع به ترتیب ثابت و استفاده از سطوح جداسازی مناسب، برای هر متخصص SQL Server ضروری است. با پیروی از این دستورالعمل‌ها، می‌توانید پایداری، عملکرد و قابلیت اطمینان سیستم‌های پایگاه داده خود را به طور چشمگیری افزایش دهید و تجربه کاربری بهتری را ارائه دهید.