خانه/ تراکنش‌ها/ قفل‌گذاری

قفل‌گذاری: چطور SQL Server از تداخل جلوگیری می‌کند

متوسط ۱۲ دقیقه مطالعه
پیش از شروع: قفل‌گذاری فقط وقتی معنا پیدا می‌کند که چند اتصال هم‌زمان در حال کار با پایگاه‌داده باشند. Playground این سایت در یک تب مرورگر و با یک اتصال تنها اجرا می‌شود، پس امکان نمایش زنده‌ی رفتار قفل‌گذاری وجود ندارد. در این درس، مفهوم را با سناریوی دو کاربر فرضی («Session A» و «Session B») توضیح می‌دهیم — دقیقاً مثل چیزی که در دنیای واقعی، با دو کاربر هم‌زمان روی یک اپلیکیشن اتفاق می‌افتد.

وقتی دو نفر هم‌زمان بخواهند یک ردیف را بخوانند یا تغییر دهند، SQL Server باید تصمیم بگیرد چطور از تداخل جلوگیری کند. ابزار اصلی‌اش برای این کار، قفل (Lock) است: علامتی موقت که SQL Server روی یک منبع (ردیف، صفحه یا کل جدول) می‌گذارد تا مشخص کند چه کسی الان اجازه‌ی دسترسی دارد.

دو نوع قفل اصلی

نوع قفلکِی گرفته می‌شودبا چه چیزی تداخل دارد
اشتراکی (Shared / S)هنگام خواندن (SELECT)با قفل انحصاری تداخل دارد؛ اما چند قفل اشتراکی می‌توانند هم‌زمان روی یک ردیف باشند
انحصاری (Exclusive / X)هنگام نوشتن (UPDATE/DELETE/INSERT)با هر قفل دیگری (اشتراکی یا انحصاری) تداخل دارد — هیچ‌کس دیگر تا رهاشدنش، نمی‌تواند به آن ردیف دسترسی داشته باشد

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

سطح‌های قفل: از ردیف تا کل جدول

SQL Server می‌تواند قفل را در سطوح مختلفی بگذارد:

سطحتوضیح
ردیف (Row)دقیق‌ترین سطح؛ فقط همان یک ردیف قفل می‌شود — بیشترین هم‌زمانی، کمترین تداخل
صفحه (Page)یک بلوک ۸ کیلوبایتی از داده (شامل چند ردیف) قفل می‌شود
جدول (Table)کل جدول قفل می‌شود — کمترین هم‌زمانی، اما کمترین سربار مدیریتی

SQL Server خودش، بر اساس حجم عملیات، تصمیم می‌گیرد کدام سطح را استفاده کند (این فرآیند Lock Escalation نام دارد) — معمولاً از ردیف شروع می‌کند و اگر تعداد قفل‌ها خیلی زیاد شود، برای کاهش سربار، آن‌ها را به قفل صفحه یا جدول ترفیع می‌دهد.

یک قفل عملاً چطور به‌نظر می‌رسد؟

فرض کن Session A در حال به‌روزرسانی حساب یک مشتری است، اما هنوز COMMIT نکرده:

Session A
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100000 WHERE id = 1;
-- هنوز COMMIT نکرده؛ قفل انحصاری روی ردیف id=1 باز است
Session B
SELECT * FROM accounts WHERE id = 1;
-- منتظر می‌ماند تا Session A قفلش را رها کند

تا وقتی Session A تراکنشش را با COMMIT یا ROLLBACK تمام نکند، Session B معطل می‌ماند. این حالت انتظار، دقیقاً موضوع درس بعدی (Blocking) است.

تفاوت مهم: مدل قفل‌گذاری SQLite

SQLite (موتور همین Playground) مدل قفل‌گذاری بسیار ساده‌تری دارد: به‌جای قفل‌های دقیق در سطح ردیف، معمولاً کل فایل پایگاه‌داده را قفل می‌کند (یا در حالت WAL، کمی انعطاف‌پذیرتر عمل می‌کند). این یکی از دلایلی است که SQLite برای اپلیکیشن‌های کوچک و تک‌کاربره (یا با نوشتن کم) عالی است، اما برای سیستم‌هایی با نوشتن هم‌زمان سنگین از طرف صدها کاربر، طراحی نشده — دقیقاً جایی که SQL Server با قفل‌گذاری دقیق در سطح ردیف می‌درخشد.

جمع‌بندی این درس

  • قفل، مکانیزم SQL Server برای جلوگیری از دسترسی هم‌زمان و متضاد به یک منبع است.
  • قفل اشتراکی (خواندن) با قفل اشتراکی دیگر تداخل ندارد؛ قفل انحصاری (نوشتن) با هر قفلی تداخل دارد.
  • قفل می‌تواند در سطح ردیف، صفحه یا کل جدول گرفته شود.
  • SQLite مدل قفل‌گذاری ساده‌تر و درشت‌دانه‌تری (معمولاً کل فایل) دارد، برخلاف قفل‌گذاری دقیق سطح‌ردیف SQL Server.

در درس بعدی می‌بینیم دقیقاً همین انتظار Session B چه اسمی دارد و چه زمانی به مشکل واقعی تبدیل می‌شود: Blocking.

درس قبلی
ACID
© ۱۴۰۴ SQLFarsi — سوالات متداول · ساخته شده توسط مصطفی موسوی