بهبود کارایی SQL Server با ایزولیشن SNAPSHOT و READ_COMMITTED_SNAPSHOT

بهینه‌سازی کارایی SQL Server: راهنمای سطوح ایزولیشن READ_COMMITTED_SNAPSHOT و SNAPSHOT

سطوح ایزولیشن تراکنش در SQL Server به ایجاد تعادل بین همزمانی و یکپارچگی داده‌ها کمک می‌کنند. این سطوح مشخص می‌کنند که چگونه یک تراکنش از تغییرات ایجاد شده توسط سایر تراکنش‌های همزمان ایزوله می‌شود. SQL Server از انواع مختلفی از سطوح ایزولیشن مانند READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE, SNAPSHOT و READ COMMITTED SNAPSHOT پشتیبانی می‌کند.

چهار سطح ایزولیشن اول (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE) با استفاده از قفل‌ها (locks) به ایزولیشن دست می‌یابند. این سطوح ایزولیشن، مبتنی بر قفل نامیده می‌شوند و می‌توانند به دلیل وجود قفل‌ها، مشکلاتی از قبیل مسدود شدن (blocking) ایجاد کنند.

دو سطح ایزولیشن آخر (SNAPSHOT و READ COMMITTED SNAPSHOT) با استفاده از نسخه‌سازی سطرها (row versioning) به ایزولیشن دست می‌یابند. این سطوح ایزولیشن، مبتنی بر نسخه‌سازی سطر نامیده می‌شوند و قفل‌های اشتراکی (shared locks) روی داده‌هایی که در حال خواندن هستند ایجاد نمی‌کنند، در نتیجه از مشکلات مسدود شدن جلوگیری می‌کنند. در این مقاله به بررسی این دو سطح ایزولیشن جدید که با SQL Server 2005 معرفی شدند، خواهیم پرداخت.

سطح ایزولیشن READ_COMMITTED_SNAPSHOT

این سطح ایزولیشن، سازگاری خواندن در سطح دستور (statement-level read consistency) را فراهم می‌کند. هنگامی که READ_COMMITTED_SNAPSHOT برای یک پایگاه داده فعال می‌شود، تراکنش‌هایی که تحت سطح ایزولیشن READ COMMITTED اجرا می‌شوند، به جای قفل‌ها، از نسخه‌سازی سطر برای عملیات خواندن استفاده می‌کنند. SQL Server نسخه‌ای از هر سطر اصلاح شده را در tempdb نگهداری می‌کند.

هنگامی که یک تراکنش داده‌ای را تحت این سطح ایزولیشن می‌خواند، جدیدترین نسخه متعهد شده (committed version) از سطر را که در زمان شروع دستور وجود داشته، می‌خواند. این بدان معناست که کوئری هیچ تغییری که توسط سایر تراکنش‌های همزمان پس از شروع کوئری ایجاد شده باشد، مشاهده نخواهد کرد. اساساً، این سطح مانند یک snapshot از داده‌ها در ابتدای دستور عمل می‌کند.

برای فعال کردن READ_COMMITTED_SNAPSHOT، از دستور ALTER DATABASE استفاده می‌شود:

ALTER DATABASE AdventureWorks2019
SET READ_COMMITTED_SNAPSHOT ON;

می‌توانید بررسی کنید که آیا READ_COMMITTED_SNAPSHOT برای یک پایگاه داده فعال است یا خیر، با کوئری گرفتن از ستون is_read_committed_snapshot_on در کاتالوگ ویو sys.databases:

SELECT name, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = 'AdventureWorks2019';

برای درک بهتر، بیایید READ_COMMITTED_SNAPSHOT را با یک مثال عملی نشان دهیم. ابتدا، یک پایگاه داده و یک جدول ایجاد می‌کنیم:

CREATE DATABASE SnapshotDemo;
GO

USE SnapshotDemo;
GO

CREATE TABLE MyTable (
    ID INT PRIMARY KEY,
    Value VARCHAR(50)
);
GO

INSERT INTO MyTable (ID, Value) VALUES (1, 'Initial Value');
INSERT INTO MyTable (ID, Value) VALUES (2, 'Another Value');
GO

اکنون، READ_COMMITTED_SNAPSHOT را برای پایگاه داده SnapshotDemo فعال می‌کنیم:

ALTER DATABASE SnapshotDemo
SET READ_COMMITTED_SNAPSHOT ON;
GO

دو پنجره کوئری در SQL Server Management Studio (SSMS) باز کنید.

در پنجره کوئری 1، یک تراکنش را آغاز کرده و یک سطر را به‌روزرسانی کنید، اما هنوز آن را commit (متعهد) نکنید:

USE SnapshotDemo;
GO

BEGIN TRANSACTION;
UPDATE MyTable SET Value = 'Updated Value 1' WHERE ID = 1;
-- Do not commit yet

در پنجره کوئری 2، داده‌ها را از MyTable انتخاب کنید. از آنجا که READ_COMMITTED_SNAPSHOT فعال است، این کوئری نسخه متعهد شده از داده‌ها را که در زمان شروع دستور SELECT وجود داشته، می‌خواند. این عملیات توسط به‌روزرسانی در پنجره کوئری 1 مسدود نخواهد شد.

USE SnapshotDemo;
GO

SELECT * FROM MyTable;

باید مشاهده کنید که سطر با ID=1 دارای Value='Initial Value' است. اکنون، در پنجره کوئری 1، تراکنش را commit کنید:

COMMIT TRANSACTION;
GO

اگر اکنون دستور SELECT را در پنجره کوئری 2 دوباره اجرا کنید، سطر با ID=1 را با Value='Updated Value 1' مشاهده خواهید کرد.

سطح ایزولیشن SNAPSHOT

این سطح ایزولیشن، سازگاری خواندن در سطح تراکنش (transaction-level read consistency) را فراهم می‌کند. هنگامی که ایزولیشن SNAPSHOT فعال می‌شود و یک تراکنش به صراحت سطح ایزولیشن خود را به SNAPSHOT تنظیم می‌کند، تمام عملیات خواندن در آن تراکنش، نسخه متعهد شده از داده‌ها را که در زمان شروع تراکنش وجود داشته، مشاهده خواهند کرد. این بدان معناست که یک تراکنش هیچ تغییری را که توسط سایر تراکنش‌های همزمان پس از شروع تراکنش ایجاد شده باشد، مشاهده نخواهد کرد و نمایی ثابت و یکپارچه از داده‌ها را برای کل مدت زمان تراکنش تضمین می‌کند.

برای فعال کردن ایزولیشن SNAPSHOT، از دستور ALTER DATABASE استفاده می‌شود. توجه داشته باشید که فعال کردن ایزولیشن SNAPSHOT، به طور پیش‌فرض READ_COMMITTED_SNAPSHOT را نیز فعال می‌کند، مگر اینکه قبلاً روشن بوده باشد. این امر به این دلیل است که ایزولیشن SNAPSHOT بر روی همان مکانیزم نسخه‌سازی سطر تکیه دارد.

ALTER DATABASE AdventureWorks2019
SET ALLOW_SNAPSHOT_ISOLATION ON;

می‌توانید بررسی کنید که آیا ایزولیشن SNAPSHOT برای یک پایگاه داده فعال است یا خیر، با کوئری گرفتن از ستون is_allow_snapshot_isolation_on در کاتالوگ ویو sys.databases:

SELECT name, is_allow_snapshot_isolation_on
FROM sys.databases
WHERE name = 'AdventureWorks2019';

بیایید ایزولیشن SNAPSHOT را با استفاده از پایگاه داده SnapshotDemo و جدول MyTable که قبلاً ایجاد شده‌اند، نشان دهیم. ابتدا مطمئن شوید که ALLOW_SNAPSHOT_ISOLATION فعال است:

ALTER DATABASE SnapshotDemo
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

دو پنجره کوئری در SSMS باز کنید.

در پنجره کوئری 1، یک تراکنش را آغاز کرده و یک سطر را به‌روزرسانی کنید، اما هنوز آن را commit نکنید:

USE SnapshotDemo;
GO

BEGIN TRANSACTION;
UPDATE MyTable SET Value = 'Updated Value 2' WHERE ID = 1;
-- Do not commit yet

در پنجره کوئری 2، یک تراکنش را آغاز کرده و سطح ایزولیشن آن را به SNAPSHOT تنظیم کنید. سپس داده‌ها را از MyTable انتخاب کنید. این کوئری نسخه متعهد شده از داده‌ها را که در زمان شروع تراکنش در پنجره کوئری 2 وجود داشته، می‌خواند. این عملیات مسدود نخواهد شد و تغییر commit نشده از پنجره کوئری 1 را مشاهده نخواهد کرد.

USE SnapshotDemo;
GO

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT * FROM MyTable;
-- You will see ID=1 with 'Initial Value' or 'Updated Value 1' depending on previous commit status

شما باید سطر با ID=1 را با Value='Initial Value' مشاهده کنید (اگر UPDATE قبلی در پنجره کوئری 1 commit شده بود، آنگاه Updated Value 1 را می‌دیدید). نکته اصلی این است که داده‌ها برای زمان شروع این تراکنش ثابت هستند. اکنون، در پنجره کوئری 1، تراکنش را commit کنید:

COMMIT TRANSACTION;
GO

اکنون، اگر دستور SELECT را در پنجره کوئری 2 دوباره اجرا کنید (در همان تراکنش SNAPSHOT)، همچنان مقدار اصلی را که در زمان شروع تراکنش در پنجره کوئری 2 وجود داشته، مشاهده خواهید کرد.

SELECT * FROM MyTable;
-- Still shows the same value as before the commit in QW1

در نهایت، تراکنش را در پنجره کوئری 2 commit کنید:

COMMIT TRANSACTION;
GO

تفاوت‌های کلیدی و ملاحظات

هر دو سطح ایزولیشن READ_COMMITTED_SNAPSHOT و SNAPSHOT از نسخه‌سازی سطر در tempdb برای دستیابی به عملیات خواندن بدون مسدود شدن (non-blocking reads) استفاده می‌کنند. با این حال، آنها در محدوده سازگاری و نحوه فعال‌سازی/استفاده با یکدیگر متفاوت هستند.

در READ_COMMITTED_SNAPSHOT، سازگاری در سطح دستور (statement-level consistency) فراهم می‌شود. هر دستور در یک تراکنش، جدیدترین نسخه متعهد شده از داده‌ها را که در زمان شروع همان دستور وجود داشته، می‌خواند. این سطح ایزولیشن در سطح پایگاه داده فعال می‌شود و به طور ضمنی بر تمام تراکنش‌های READ COMMITTED تأثیر می‌گذارد.

در مقابل، SNAPSHOT سازگاری در سطح تراکنش (transaction-level consistency) را ارائه می‌دهد. تمام دستورات در یک تراکنش، نسخه متعهد شده از داده‌ها را که در زمان شروع تراکنش وجود داشته، می‌خوانند. این سطح نیز در سطح پایگاه داده فعال می‌شود (با ALLOW_SNAPSHOT_ISOLATION ON)، اما تراکنش‌های فردی باید به صراحت سطح ایزولیشن خود را به SNAPSHOT تنظیم کنند.

چه زمانی از کدام استفاده کنیم؟

استفاده از READ_COMMITTED_SNAPSHOT اغلب یک انتخاب پیش‌فرض مناسب برای بسیاری از برنامه‌های OLTP است؛ زیرا مشکلات مسدود شدن را که معمولاً با READ COMMITTED (سطح ایزولیشن پیش‌فرض در SQL Server) مرتبط هستند، به شدت کاهش می‌دهد، بدون اینکه نیاز به تغییرات در کد برنامه باشد. این یک روش عالی برای بهبود همزمانی است.

از SNAPSHOT زمانی استفاده کنید که برنامه شما نیاز به نمای ثابتی از داده‌ها برای کل مدت زمان یک تراکنش داشته باشد، بدون توجه به تغییرات همزمان دیگر. این بدان معناست که به عنوان مثال، یک گزارش طولانی‌مدت، داده‌های دقیقاً یکسانی را از ابتدا تا انتها مشاهده خواهد کرد، حتی اگر داده‌ها در طول اجرای آن در حال اصلاح باشند. استفاده از آن مستلزم تنظیم صریح SET TRANSACTION ISOLATION LEVEL SNAPSHOT در کد برنامه شما است.

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