آموزش پیشرفته SQL Server (جلسه دهم) - همزمانی در SQL Server

بررسی همزمانی در SQL Server، قسمتی از آموزش پیشرفته SQL Server است، پیشنهاد می شود قبل از مطالعه ی همزمانی در SQL Server، قسمت های قبلی را مرور فرمایید.

همزمانی در SQL Server

از مباحث فوق العاده مهم در یک پایگاه داده، همزمانی یا Concurrency تراکنش هاست. هنگامی که یک تراکنش شروع می شود، سیستم مدیریت پایگاه داده (DBSM) وظیفه دارد منابع زیادی را جهت حفظ ویژگی ACID، تا پایان اجرا محفوظ نگه دارد.

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

انواع مشکلات همزمانی در SQL Server

1. Lost Updated (گم شدن تغییرات)
2. Uncommitted Dependency (وابستگی تأیید نشده)
3. Inconsistent Analysis (تحلیل متناقض)
4. Phantom Read (خواندن فریبنده)

در ادامه به توضیح مختصر هر کدام از موارد بالا خواهیم پرداتخت.

1- Lost Updated (گم شدن تغییرات)

Lost Update بمعنی از دست رفتن تغییرات است.

این مشکل زمانی رخ می دهد که عملیات Update با موفقیت انجام می شود اما بطور اتفاقی توسط کاربری دیگر بازنویسی می شود.

بعنوان مثال فرم ویرایش "مشخصات پرسنل" را در نظر بگیرید. دو کاربر مختلف همزمان قصد ویرایش یک پرسنلی خاص را دارند...! کاربر اول قصد ویرایش فیلد "آدرس" را دارد و کاربر دوم قصد ویرایش فیلد "شماره تلفن" را دارد.

کاربر اول فیلد آدرس را تغییر داده و روی دکمه "ویرایش" کلیک می کند، اما متأسفانه در این لحظه کاربر دوم تغییرات اعمال شده توسط دیگری را مشاهده نمی کند، یعنی آدرس قبلی کاربر را مشاهده می کند و نهایتاً بعد از تغییر "شماره تلفن" روی دکمه "ویرایش" کلیک می کند.

اتفاقی که در آخر می افتد این است که آدرسی که کاربر اول تغییر داده بود مجدداً توسط کاربر دوم به حالت اول باز می گردد.

در زیر به دو راه حل مختلف برای رفع مشکل Lost Updated اشاره شده است:

1. زمانی که یک رکورد در حالت ویرایش قرار می گیرد، دیگر کاربران قادر به ویرایش آن نباشند.
2. اضافه کردن یک فیلد از نوع TimeStamp به جدول، این فیلد بصورت خودکار زمان ایجاد و یا ویرایش، مقداردهی می شود، بنابراین می توانیم هنگام ویرایش آنرا خوانده و به فرم ارسال کنیم و زمانی که کاربر روی دکمه "ویرایش" کلیک می کند، مقدار آنرا همراه با دیگر فیلدها به پایگاه داده ارسال کنیم و قبل از اینکه عملیات Update را انجام دهیم، مقدار فیلد TimeStamp ارسال شده را با مقدار فعلی رکورد مقایسه کنیم و اگر یکسان بود، تغییرات را انجام دهیم و در غیر اینصورت پیغامی مناسب مبنی بر اینکه "اطلاعات توسط یک کاربر دیگر تغییر کرده" به کاربر نشان دهیم.

2- Uncommitted Dependency (وابستگی تأیید نشده)

نام دیگر آن Dirty Read یا خواندن کثیف است و زمانی رخ می دهد که کاربران رکوردی را که در دست تغییر است و هنوز کامل نشده می خوانند.

همان طور که می دانید زمانی که دستور Update روی یک رکورد انجام می شود، تا Commit شدن کامل Transaction مربوطه، رکورد مورد نظر Lock می شود و دیگر کاربران قادر به خواندن و یا تغییر رکورد مذکور نیستند. اما توجه داشته باشید که می توان عملیات Lock را بدلیل Wait نشدن دیگر کاربران غیرفعال نمود...!

حالا اگر کاربری داده هایی را  که هنوز تغییرات آنها Commit نشده است بخواند مشکل Uncommitted Dependency یا همان "خواندن کثیف" رخ داده است.

3- Inconsistent Analysis (تحلیل متناقض)

مشکل Inconsistent Analysis به نوعی وابسته به مشکل Dirty Read است.

داده ها هنگام تغییر توسط یک Process، توسط Process دیگری خوانده شوند.

به ازای هر بار عملیات خواندن، نتیجه متفاوت از قبل وجود دارد.

4- Phantom Read (خواندن فریبنده)

یک Process تصور کند به مجموعه ای پایدار رسیده در حالیکه Process دیگری در حال درج داده جدید است.

در این حالت تعدادی رکورد به ازای عملیات خواندن به شکل مرموز ظاهر می شوند. 

تا اینجا، شما را با 4 تا از مهمترین مشکلات همزمانی (Concurrency) آشنا کردیم در ادامه قصد داریم درباره مکانیزم های کنترل همزمانی توضیحاتی را ارائه دهیم.

مکانیزم های کنترل همزمانی در SQL Server

استفاده از Lock ساده ترین راه حل خلاص شدن از مشکلات همزمانی است.

با استفاده از مکانیزم Lock می توان تضمین نمود که درخواست ها و Transactionهای مختلف با هم تداخل پیدا نخواهند کرد.

از مکانیزم Lock، در Transactionها استفاده می شود.

وجود Lock باعث اطمینان از عدم تغییر شیء مورد نظر است.

مکانیزم Lock شامل دو ویژگی زیر است:

1. Locked Granularity (سطوح قفل گذاری): این ویژگی مشخص کننده اندازه منابعی است که در هر لحظه قفل می شوند. در واقع محدوده قفل گذاری را مشخص می کند. سطوح قفل گذاری می تواند روی یک یا چند ردیف، یک یا چند Page، یک یا چند Extent، یک یا چند جدول و یا کل پایگاه داده باشد. نکته ای که در اینجا وجود دارد این است که اگر مثلاً قفل گذاری روی ردیف ها به تعداد مشخصی برسد، SQL Server برای بالا بردن سرعت، سطح قفل گذاری را یک سطح افزایش می دهد و به سطح Page منتقل می کند.
2. Locked Mode (حالت یا انواع قفل): این ویژگی نحوه ی تعامل Lock با سایر Lockها و فرایندها را مشخص می کند و به انواع زیر تقسیم می شود:
   • Shared Lock یا قفل اشتراکی
   • Exclusive Lock یا قفل اختصاصی/انحصاری
   • Update Lock یا قفل به روز رسانی
   • Intent Lock یا قفل قصدی
   • Schema Lock  یا قفل اسکیما
   • Bulk Update Lock یا قفل به روز رسانی انبوه
   • Key-Range Lock یا قفل محدود

بالاترین و پایین ترین سطح Lock Granularity

در زیر، ترتیب سطوح از پایین ترین به بالاترین سطح، لیست شده است:

1. Row: قفل گذاری بر روی رکوردهای جدولی از نوع Heap اعمال می شود. (RID)
2. Key: قفل گذاری بر روی رکوردهای جدولی از نوع Clustered اعمال می شود. (RID)
3. Page: در این حالت کل داده های یک Page قفل می شوند.
4. Extent: در این حالت کل داده های یک Extent  قفل می شوند. هنگام Rebuild کردن یک ایندکس از این حالت استفاده می شود.
5. Heap Or B-Tree: این حالت برای محافظت از داده های ایندکس Non-Clustered ای که فاقد Clustered Index است، اعمال می شود.
6. Table: در این حالت، کل جدول بهمراه ایندکس ها قفل می شود.
7. File
8. Application
9. Metadata
10. Allocation Unit
11. Database: این قفل بر روی کل بانک اطلاعاتی اعمال می شود. در این حالت کلیه اشیاء بانک اطلاعاتی در دسترس نخواهد بود.

در ادامه آموزش همزمانی در SQL Server خواهید دید...!

با خرید جلسه 10 از بسته آموزشی "افزایش کارآیی پایگاه داده" موارد زیر را خواهید دید:

1. فرآیند انتخاب سطح قفل (Lock Granularity) چگونه است؟
2. Lock Scalation چیست و چگونه SQL Server تصمیم می گیرد که سطح قفل گذاری را افزایش دهد.
3. توضیح کامل و جامع در مورد انواع حالت های Locked  (مثل Shared Lock و Exlusive Lock و...)
4. DeadLock چیست و راه های جلوگیری از آن کدام است؟
5. توضیح بسیار جامع و کامل در مورد Isolation Level و رفع مشکل Wait ماندن کاربران بخاطر وجود Lock
6. و...

دسترسی به موارد آموزشی بالا در بسته خریداری شده

• شماره جلسه: 10
• نام فایل ویدئو: 03
• فرمت فایل: mp4.

نقطه شروع بحث بالا (همزمانی در SQL Server) در ویدئو: 24:33

برای خرید و دانلود کاملآموزش پیشرفته SQL Server کلیک کنید.