‫جایگزینی برای راهنمای پرخطر NOLOCK در SQL Server

• مدل مبتنی بر قفل بدبینانه (Pessimistic Locking): فرض می‌کند تداخل حتماً رخ می‌دهد؛ بنابراین خوانندگان روی داده‌ها قفل اشتراکی (Shared Lock یا S-Lock) و نویسندگان قفل انحصاری (Exclusive Lock یا X-Lock) می‌گذارند. تا زمانی که نویسنده کارش تمام نشود، خواننده منتظر می‌ماند و برعکس.
• مدل کنترل همزمانی چندنسخه‌ای (MVCC - Multi-Version Concurrency Control): فرض می‌کند خواندن نباید مانع نوشتن شود. وقتی داده‌ای ویرایش می‌شود، موتور پایگاه داده نسخه قبلی آن را در فضایی مجزا نگه می‌دارد. در نتیجه خوانندگان نسخه‌ی تأییدشده‌ی قبلی (Snapshot) را می‌خوانند و هیچ فرآیندی معطل دیگری نمی‌ماند.

• خواندن کثیف (Dirty Read): خواندن داده‌های خامی که وسط یک تراکنش هستند و ممکن است با خطایی Rollback شوند.
• ردیف‌های تکراری یا گم‌شده: اگر در لحظه خواندن داده، تقسیم صفحه داده‌ای (Page Split) رخ دهد، کوئری شما ممکن است یک سطر را دو بار بخواند یا کلاً نادیده بگیرد.

• مکانیزم: هر بار که داده‌ای تغییر می‌کند (UPDATE یا DELETE)، نسخه قدیمی آن در Version Store واقع در tempdb ذخیره می‌شود.
• اقدام پیشگیرانه: مطمئن شوید دیسک tempdb روی درایوهای بسیار سریع (NVMe SSD) قرار دارد، چندین دیتافایل هم‌اندازه برای آن ایجاد شده و فضای خالی کافی دارد.

• مکانیزم: به ازای هر رکورد تغییریافته، ۱۴ بایت متادیتا (اشاره‌گر به نسخه قدیمی در tempdb) به انتهای ردیف اضافه می‌شود.
• ریسک: در جداولی که طول ردیف به سقف اندازه صفحه (۸۰۶۰ بایت) نزدیک است، افزودن این ۱۴ بایت باعث تقسیم صفحه (Page Split) و ایجاد تکه‌تکه‌شدن ایندکس (Fragmentation) می‌شود.

• ریسک: اگر برنامه‌نویسان کدها را با فرض اینکه "یک SELECT مانع ویرایش همزمان رکورد می‌شود" نوشته باشند، فعال‌سازی RCSI باعث همزمانی ناخواسته و از دست رفتن داده (Lost Updates) می‌شود؛ مگر اینکه به صورت صریح از هینت‌هایی مثل WITH (UPDLOCK) استفاده شده باشد.

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

USE master;
GO

ALTER DATABASE YourDatabaseName
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;
GO

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

-- بررسی میزان فضای مصرفی Version Store در tempdb (بر حسب مگابایت)
SELECT 
    SUM(version_store_reserved_page_count) * 8 / 1024.0 AS VersionStore_MB
FROM sys.dm_db_file_space_usage;

-- شناسایی تراکنش‌هایی که بیشترین حجم نسخه را در tempdb باز نگه داشته‌اند
SELECT 
    at.transaction_id,
    at.name,
    at.transaction_begin_time,
    DATEDIFF(minute, at.transaction_begin_time, GETDATE()) AS active_minutes
FROM sys.dm_tran_active_snapshot_database_transactions ast
JOIN sys.dm_tran_active_transactions at ON ast.transaction_id = at.transaction_id
ORDER BY ast.elapsed_time_seconds DESC;

• کوتاه نگه‌داشتن تراکنش‌ها: در بلاک‌های BEGIN TRAN ... COMMIT فقط دستورات ضروری نوشتن را قرار دهید و منطق پردازشی، فراخوانی وب‌سرویس‌ها یا محاسبات طولانی را خارج از تراکنش انجام دهید.
• خرد کردن کوئری‌ها (Batching): در حذف یا ویرایش‌های میلیونی، داده‌ها را در دسته‌های ۵۰۰۰ تایی تغییر دهید تا از Lock Escalation جلوگیری شود.
• جداسازی بار خواندن و نوشتن (Read Replicas): کوئری‌های سنگین گزارش‌گیری و هوش تجاری (BI) را از طریق قابلیت‌هایی مثل Always On Availability Groups به پایگاه‌های داده ثانویه (فقط‌خواندنی) هدایت کنید تا هیچ اثری بر عملکرد کاربران سامانه زنده نگذارند.