درک تأثیر Nolock و با نکات جدول Nolock در SQL Server

هر چند وقت یکبار ، مدیران پایگاه داده SQL Server خود را با همتایان توسعه دهنده برنامه خود اختلاف نظر می دانند-به ویژه وقتی صحبت از برخی از شیوه های توسعه Transact SQL (T-SQL) دوم می شود. یکی از اولین مشاهدات من هنگام پیوستن به کارفرمای فعلی من این است که تقریباً تمام اسکریپت های T-SQL که توسط برنامه نویسان برنامه نوشته شده است ، از Nolock Table استفاده می کند. با این حال ، از تعامل هایی که من با این توسعه دهندگان محترم داشته ام به نظر نمی رسد که آنها درک کنند که چگونه جدول Nolock کار می کند. علاوه بر این ، اگرچه به نظر می رسد آنها از تمایز بین Nolock و Nolock Table Taint آگاه هستند ، اما به نظر نمی رسد که آنها دوباره درک کنند که این دو با یکدیگر تفاوت دارند. در این مقاله ، من به کارهای داخلی جدول Nolock اشاره می کنم و پیامدهای حذف کلمه کلیدی را بررسی می کنم.

درک اشاره Nolock

رفتار پیش فرض در SQL Server برای هر پرس و جو است که قبل از خواندن داده ها از یک جدول معین ، قفل مشترک خود را بدست آورد. این رفتار تضمین می کند که شما فقط داده های متعهد را می خوانید. با این حال ، نکته جدول Nolock به شما امکان می دهد بدون به دست آوردن قفل اختصاصی یا مشترک ، به Optimiser پرس و جو دستور دهید تا یک جدول معین را بخوانید. مزایای داده های پرس و جو با استفاده از نکته جدول Nolock این است که به حافظه کمتری نیاز دارد و مانع از بروز بن بست با هر پرس و جو دیگری که ممکن است داده های مشابهی را بخواند ، جلوگیری می کند. تنها اشکال این است که استفاده از نکته جدول Nolock ممکن است به طور تصادفی منجر به خواندن داده های غیرقابل قبول "کثیف" شود. در بخش های بعدی ، نمونه های عملی را برای استفاده از Nolock و با نکات جدول Nolock ارائه می دهم. برای اهداف این بحث ، من از یک جدول SQL Server 2016_FIFABALLONDOR استفاده می کنم که شامل 3 فوتبالیست برتر نامزد دریافت جایزه Ballon D'OR FIFA در سال 2016 است. پیش نمایش این جدول SQL Server در جدول 1 نشان داده شده است.

جدول 1 اکنون فرض می کنیم که با دیدن شماره پیراهن مخالفان خود ، لیونل مسی بعداً تصمیم می گیرد شماره پیراهن خود را از 10 به 7 تغییر دهد تا این تغییر را منعکس کند ، ما باید یک بیانیه به روزرسانی را در برابر جدول سرور SQL خود اجرا کنیم. اسکریپت بروزرسانی نمونه که باید اجرا کنیم در اسکریپت 1 نشان داده شده است. معاملات UpdateJerseynr را شروع کنید به روزرسانی a [شماره جرسی] = 7 را تنظیم کنید از [sqlshack].[DBO].[2016_fifaballondor] a جایی که [نامزد] = 'لیونل مسی'

اسکریپت 1 متوجه خواهید شد که اسکریپت 1 یک تعهد مربوط به معامله به روزرسانی Jerseynr ندارد ، به این معنی که تغییراتی که ما به تازگی انجام داده ایم هنوز به دیسک متعهد نشده اند. بنابراین ، اگر کاربر پایگاه داده دیگری سعی در خواندن مقدار شماره پیراهن لیونل مسی همانطور که در شکل 1 نشان داده شده است ، می توانند یک پیام پرس و جو اجرا بی پایان دریافت کنند. Figure 1 Well, what is happening here is that as part of retrieving Lionel Messi’s jersey number, the script in Figure 1 needs to firstly acquire a shared lock against the 2016_FIFABallonDOr table, but it ends up having to wait for Script 1 to complete its changes first. Figure 2 This is where the NOLOCK hint can be useful as it can allow for the retrieval of Lionel Messi’s jersey number without having to wait for any shared locks as shown in Script 2 and Figure 3 , respectively.

[شماره جرسی] را انتخاب کنید از [sqlshack].[DBO].[2016_fifaballondor] (Nolock) جایی که [نامزد] = 'لیونل مسی'

اسکریپت 2 شکل 3 با این حال ، خطرات استفاده از نکته Nolock این است که اگر معامله در اسکریپت 1 به عقب برگردانده شود ، ارزش شماره پیراهن لیونل مسی به 10 باز می گردد ، به این معنی که اگر بخواهید اسکریپت 2 را دوباره انجام دهیدارزش متفاوتی از آنچه زودتر دریافت کردید دریافت می کند.

مزایای استفاده از کلمه کلیدی

از نظر پرس و جو از شماره پیراهن لیونل مسی با اضافه کردن کلمه کلیدی در مقابل اشاره Nolock ، شما هنوز هم نتایج مشابهی را که در اسکریپت 3 و شکل 4 نشان داده شده است ، بازیابی می کنید. در حقیقت ، حتی برنامه اجرای و آمار زمان/IO اسکریپت 3 هنوز شبیه به اسکریپت 1 است.

[شماره جرسی] را انتخاب کنید از [sqlshack].[DBO].[2016_fifaballondor] با (Nolock) جایی که [نامزد] = 'لیونل مسی'

اسکریپت 3 شکل 4 اگر خروجی اسکریپت ها با استفاده از Nolock و با نکات جدول Nolock بسیار یکسان است ، پس چرا باید نگران استفاده از دیگری باشیم؟خوب ، به نظر می رسد که در واقع چندین تفاوت بین این دو وجود دارد:

1. پشتیبانی از نکات بدون کلمه کلیدی به زودی کاهش می یابد

طبق مستندات مایکروسافت که در شکل 5 نشان داده شده است ، با ادامه حذف کلمه کلیدی در یک جدول ، به این معنی است که شما اساساً بدهی فنی خود را افزایش می دهید ، زیرا مجبور خواهید شد به عقب برگردید و اسکریپت های خود را پس از حذف این قابلیت در نسخه های بعدی SQL حذف کنید. سرورشکل 5

2. چندین نکته جدول را با استفاده از کلمه کلیدی مشخص کنید

به غیر از این واقعیت که سازندگان SQL Server اساساً هنگام تعیین نکات جدول، کلمه کلیدی WITH را به ما آموزش داده اند، یکی دیگر از مزایای استفاده از کلمه کلیدی WITH این است که می توانید نکات جدولی متعددی را در برابر جدول مشابهی که در اسکریپت 4 نشان داده شده است اضافه کنید.

انتخاب کنید [شماره پیراهن] از [SQLShack].[ dbo ] .[ 2016_FIFABallonDOr ] WITH ( TABLOCK , HOLDLOCK ) WHERE [ نامزد ] = 'کریستیانو رونالدو'

اسکریپت 4 اگر چند نکته را پس از حذف کلمه کلیدی WITH مشخص کنید، به ترتیب به خطای نشان داده شده در اسکریپت 5 و شکل 6 منجر می شود.

انتخاب کنید [شماره پیراهن] از [SQLShack].[ dbo ] .[ 2016_FIFABallonDOr ] (TABLOCK، HOLDLOCK) WHERE [ نامزد ] = 'کریستیانو رونالدو'

اسکریپت 5 شکل 6

3. بدون کلمه کلیدی WITH شما به سادگی یک جدول مستعار دارید

در این مرحله شما باید همیشه به سمت مشخص کردن کلمه کلیدی WITH در هنگام استفاده از نکات جدول تمایل داشته باشید، اما فقط برای متقاعد کردن بیشتر شما، حذف کلمه کلیدی WITH می تواند عواقب نامطلوبی داشته باشد. اگر فراموش کردید که NOLOCK را در داخل پرانتزهای گرد قرار دهید، می توان اشاره NOLOCK را با نام مستعار جدول توسط بهینه ساز پرس و جو اشتباه گرفت، به این معنی که پرس و جو باید قبل از شروع خواندن جدول معین، منتظر قفل مشترک بماند. به عنوان مثال، فراموش کردم که NOLOCK را در براکت های گرد باز و بسته قرار دهم و این منجر به اجرای بی انتها پرس و جو در حالی که منتظر یک قفل مشترک بود. Figure 7 Figure 8

نتیجه

مشخص کردن اشاره جدول NOLOCK دارای مزایا و معایبی است، در نتیجه نباید آنها را فقط در هر اسکریپت T-SQL بدون درک روشنی از کاری که انجام می دهند گنجانده شود. با این وجود، اگر تصمیمی برای استفاده از اشاره جدول NOLOCK گرفته شود، توصیه می شود کلمه کلیدی WITH را وارد کنید.

Sifiso معمار داده و رهبر فنی در SELECT SIFISO است - یک شرکت مشاوره فناوری که بر مهاجرت های ابری، جذب داده ها، DevOps، گزارش دهی و تجزیه و تحلیل تمرکز دارد. Sifiso بیش از 15 سال در بخش های تجاری خصوصی و عمومی فعالیت دارد و به کسب وکارها کمک می کند تا راه حل های فناوری مایکروسافت، AWS و منبع باز را پیاده سازی کنند. او عضو گروه کاربری SQL ژوهانسبورگ است و همچنین دارای مدرک کارشناسی ارشد مدیریت فناوری اطلاعات MCom از دانشگاه ژوهانسبورگ است.

آخرین پست های Sifiso Ndlovu (مشاهده همه)

• نگاشت ستون پویا در SSIS: کلاس SqlBulkCopy در مقابل جریان داده - 14 فوریه 2020
• نظارت بر بیانیه های دسته ای ویژگی Get Data در Power BI با استفاده از رویدادهای توسعه یافته SQL Server - 1 ژوئیه 2019
• مهاجرت مدل انبوه در خدمات داده اصلی SQL Server - 30 مه 2019