IN MEMORY TABLE – OLTP

The Problem

یکی از مهمترین مسائل در DBMS مسئله همزمانی می باشد، که مدیریت آن در هر لحظه احساس می شود، به عنوان مثال زمانی یک تراکنش به منظور خواندن یک تعداد رکورد، اقدام می کند، تا زمان پایان تراکنش، این رکورد ها بصورت Lock و غیر قابل دسترس برای دیگر تراکنش ها خواهند ماند، و همچنین این حقیقت برای تراکنش هایی که موجبات تغییر(insert،Update،delete) در یک دسته از رکورد ها می شوند، تکرار شده و رکورد های فوق مادامی که تراکنش به پایان نرسیده lock شده و غیر قابل دسترس خواهند بود. پس ایجاد Lock های این چنین استعداد ایجاد Deadlock را فراهم کرد، و هدف ارائه راه کاری برای جلوگیری از Lock  می باشد. پیش از مطالعه این مقاله آموزشی از وبسایت کندو، جهت آشنایی با دوره آموزش جامع sql server می توانید از این لینک اقدام نمایید.

Concurrency Problem

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

خلاصه: بدان معنا می باشد که دو تراکنش همزمان اقدام به بروز رسانی تعدادی رکورد کرده، پس به دلیل همزمانی اثر یک بروز رسانی بر روی دیگر بروز رسانی تاثیر گذاشته و موجبات ازبین رفتن یکی از Update ها خواهد شد.

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

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

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

این ایراد تحت عنوان Dirtily Read نیز شناخنه می شود و بدان معنا است که زمانی که یک دستور همانندInsert در یک تراکنش در حال انجام است، یک Select در یک تراکنش اقدام به دسترسی به رکوردهایی می کند، که هنوز Insert آنها Commit نشده، حال اگر ب هر علتی تراکنش حاوی Insert رول بک شود، اطلاعات Select شده توسط دیگر تراکنش اعتبار نخواهد داشت.

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

معروف به Reputable Read می باشد، یعنی اگر یک تراکنش اقدام به Select کرده و تعداد رکوردی را بخواند، و قرار باشد مجددا همین عمل در همان تراکنش صورت گیرد، اما در میان زمان بین دو Select یک تراکنش دیگر اقدام به تغییرات بر روی همان رکورد ها را داشته باشد، حال خروجی دو Select یکسان یکی نبود و اصطلاحا موجب تحلیل متناقض خواهد شد.

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

این مشکل زمانی رخ می دهد، که دو تراکنش یکی حاوی Update و دیگری حاوی Insert همزمان اجرا می شود، پس در این حالت update اقدام به بروز رسانی رکورد ها خواهد کرد که در میان این رکوردها ممکن است بعضی از رکوردهای Insert شده باشد و بعضی نباشد، و اینکه چه رکوردهای شامل این بعضی میشوند وابسته به زمان اجرای ترنزکشن حاوی Insert دارد.

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

منابع قابل قفل (Granularity Lock)

فعل Lock توسط SQL می تواند در مورد منابع مفتاوتی ایجاد گردد،6 منبع قابل قفل شدن برای SQL Server وجود دارد و آن‌ها سلسله مراتبی را تشکیل می‌دهند. هر چه سطح قفل بالاتر باشد، Granularity  کمتری دارد. در ترتیب آبشاری Granularity عبارتند از:

•  Databaseکل بانک اطلاعاتی قفل شده است، معمولاً طی تغییرات Schema بانک اطلاعاتی روی می‌دهد.
• Tableکل جدول قفل شده است، شامل همه اشیای مرتبط با جدول.
• Extend کل Extent قفل شده است.
• Page  همه داده‌ها یا کلیدهای Index در آن Page قفل شده اند.         
• Key قفلی در کلید مشخصی یا مجموعه کلید هایی Index وجود دارد. ممکن است سایر کلید‌ها در همان Index Page تحت تاثیر قرار نگیرند.
• Row or Row Identifier (RID) هر چند قفل از لحاظ فنی در Row Identifier قرار می‌گیرد ولی اساساً کل ردیف را قفل می‌کند.

Lock Escalate

هدف از ایجاد lock در SQL حفظ خصوصیت ACID تراکنش های میباشد، فلذا با این هدف اقدام به فرآیند Lock گذاری  بر روی منایع صورت می پذیرد،اما در این میان تشدید قفل گذاری، همچنین قفل گذاری در سطوح بالاتر می تواند عواقب منفی بر عملکرد سرویس خواهد داشت، به عنوان مثال قفل گذاری RID خیلی بهتر از Page و Page خیلی بهتر از Database است!از طرفی وقتی تعداد قفل در یک سطح به یک آستانه ای میرسدDBMS بصورت خودکار یک سطح بالاتر را قفل خواهد کرد.

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

انواع Lock

در ادامه به تشریح انواع Lock ایجاد شده می پردازیم:

• Shared Lock معروف به Lock S

زمانی که اقدام به خواندن اطلاعات در یک تراکنش می کنیم، رکورد های Select شده اصلاحا S lock شده تا کسی نتواند اقدام به تغییر رکوردهای خوانده شده شود، بدیهی است بعد از اتمام تارکنش این Lock بر روی رکوردهای فوق برداشته شده و امکان دسترسی به رکورد های فوق امکان پذیر خواهد شد.(  ممانعت از انجام Dirty Read)

یک دستور Insert

دستور Select

نتیجه:

• Exclusive Lock معروف به Lock X

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

مثال: برای ایجاد مثال یک کویری از  Write ایجاد میکنیم که اجرای آن طولانی هست، مثلا 2 دقیقه زمان میگیرید، حال کویری دیگری از جنس Write روی آن بوجود می آوریم، اگر یکی از این دو کویری Write که هردو یک Read ی داشته (Delete یا Update) در فاز Read باشند و هنوز وارد نوشتن نشده باشد، این لاک ایجاد میشود، اگر وارد فاز نوشتن شده باشد که Lock U رخ خواهد داد ،پس یک  selectی شیبح سازی میکنم و بعد یک Write ایجاد میکنیم تا این اتفاق رخ دهد:

فرض کنید تصویر زیر قسمت Select از یک کویری Update باشد!!

حالا یک دستور delete روی همین رکورد میاد:

نتیجه کار میشه لاک از نوع X

مثال: یک Delete زدیم، میره بگرده دیتا را پاک کنه یا حذف کنه، توی لحظه ای که داره میگرده (وقتی این گشتن طولانی باشه) یک کویری Delete دیگر هم می آید با همین شرایط:

کویری delete اول هست:

کویری Delete دوم هست:

نتیجه هست: فقط دقت شود، باید توی فاز جستجو باشد نه توی فاز Write

ü       Update Lock معروف به Lock U

زمانی استفاده میشود،که یک تراکنش در حال تغییر داده ی مربوطه میباشد.در یک لحظه فقط یک تراکنش میتواند از این قفل برروی منبع داده استفاده کند. این قفل ‌ها نوعی پیوند میان Shared Locks و Exclusive Locks هستند. برای انجام Update باید بخش Where را (در صورت وجود) تایید اعتبار کنید، تا دریابید فقط چه ردیف هایی را می‌خواهید بهنگام رسانی کنید. این بدان معنی است که فقط به Shared Lock نیاز دارید، تا زمانی که واقعاً بهنگام رسانی فیزیکی را انجام دهید. در زمان بهنگام سازی فیزیکی نیاز به Exclusive Lock دارید.Update Lock نشان دهنده این واقعیت است که دو مرحله مجزا در بهنگام رسانی وجود دارد، Shared Lock ا ی دارید که در حال تبدیل شدن به Exclusive Lock است. 

مثال ساده برای بوجود آوردن این لاک میتونه اجرای دو دستور Update بصورت همزمان در دو تراکنش متفاوت باشد و این update ها کوچیک باشه (به لحاظ زمانی) که در این حالت هر دو میره تو انتظار کامیت، یعنی Read کویری ها تمام میشه، سر Write که میرسه، چون کویری کوچیکه Write را کرده و منتظر Commit هست که کویری بعدی هم به همین ترتیب می آید، پس هردو میخواهند بنویسند و لاک کویری که زودتر آمده موجب انتظار کویری دوم خواهد شد. (البته لزموما هر دو Update نیست، میتونه هر کویری باشد که منجر به Write شود

هر دو کویری Update بصورت زیر آمده است:

نتیجه خواهد شد:

مثال: یک مثال از Delete همزمان داریم که هر دو وارد Write همزمان شده باشند، مشابه مثال بالا در لاک قبلی ولی با این تصور که توی مرحله Write کویری اول،کویری دوم ایجاد شده است و خواهیم داشت:

کویری اول و دوم :

نتیجه:

مثال: یک مثال از یک آپدیت و یک Insert  که همزمان هردو توی فاز Write همدیگه را لاک کرده اند

کویری دیلیت اول بصورت زیر خواهد بود:

کویری آپدیت بعدی بصورت زیر خواهد بود:

نتیجه خواهد شد:

Intent Lock قصد

این قفل از انواع قفلهای داخلی در sql محسوب میشود با استفاده از این قفل میتوان سایر تراکنش ها را از وجود قفل برروی منبع داده مطلع نمود تا از تداخل قفل ها جلوگیری به عمل آید. با سلسله مراتب شی سر و کار دارد. بدون Intent Lock، اشیای سطح بالاتر نمی‌دانند چه قفلی را در سطح پایین‌تر داشته اید.

• Intent Shared Lock معروف به IS

Shared Lock در نقطه پایین‌تری در سلسله مراتب، تولید شده یا در شرف تولید است. این نوع قفل تنها به Table و Page اعمال می‌شود.

• Intent Exclusive Lock معروف به IX

همانند Intent Shared Lock است اما در شرف قرار گرفتن در آیتم سطح پایین‌تر است.

• Shared with Intent Exclusive Lock معروف به SIX

Shared Lock در پایین سلسله مراتب شی تولید شده یا در شرف تولید است اما Intent Lock قصد اصلاح داده‌ها را دارد بنابراین در نقطه مشخصی تبدیل به Intent Exclusive Lock می‌شود.

• Schema Lock  معروف به SCH Lock

زمانی که در حال تغییر در ساختار جداول هستیم که این کار با دستوارت Alter،Create، و یا Drop صورت می پذیرد این نوع Lock ایجاد شده که مانع از دسترسی به محتویات آن شی خواهد شد. که خود دو نوع دارد نوع پیشگرانه SCH-S (Schema Stability Lock)و نوع زمان تغییر SCH-M (schema Modification Lock)

• Bulk Update Lock

در زمان ایجاد عملیات BCP و Bulk insert رخ داده و موجب عدم خواندن از اطلاعاتی می شود که در حال BCP و Bulk insert  می باشد، اما بقیه رکورد ها قابل خواند است.

Isolation Level

7-1- Read Committed (وضعیت پیش فرض):

پیش فرض SQL بوده، و نحوه کار بدین صورت می باشد، که زمانی یک تراکنش اقدام به Write  کرده یعنی inset،update،Delete رکوردها(یا بسته به وسعت هر سطحی) Lock شده و دیگر قابل Read یا Select توسط دیگر تراکنش های نخواهند بود.
با این سطح پیش فرض، می‌توانید مطمئن شوید جامعیت کافی برای پیشگیری از Dirty Read دارید، اما همچنان Phantoms و Non-Repeatable Read می‌تواند روی دهد.

7-2- Read Uncommitted:

خطرناک‌ترین گزینه از میان تمامی گزینه‌ها است، اما بالاترین عملکرد را به لحاظ سرعت دارد. در واقع با این تنظیم سطح تجربه همه مسائل متعدد هم زمانی مانند Dirty Read امکان پذیر است. در واقع با تنظیم این سطح به SQL Server اعلام می‌کنیم هیچ قفلی را تنظیم نکرده و به هیچ قفلی اعتنا نکند، بنابراین هیچ تراکنش دیگری را مسدود نمی‌کنیم.روان تر می توان گفت، برعکس حالت قبل عملیات Write هیچ Lock ایجاد نکرده و احتمال خواندن اطلاعاتی که هنوز Write آنها تثبیت نشده امکان پذیر است.
می‌توانید همین اثر Read Uncommitted را با اضافه کردن نکته بهینه ساز  NOLOCK در پرس و جو‌ها بدست آورید.

مثال: میتونیم یک کویری Write بزنیم داخل یک تراکنش تا یک لاک U بوجود آید، سپس در تراکنش دیگری ایزولیشن لول را تغییر بدیم و Select بزنیم ببینیم که جواب کویری می آید ولی احتمال Dirty Read وجود دارد.

و حالا Select میشود:

7-3- Repeatable Read:

این سطح از عملکرد بدین ترتیب رفتار کرده که حتی در صورت Select یک تعداد رکورد در قالب یک تراکنش نیز اقدام به لاک کردن این رکوردها خواهد کرد و در نتیجه مادامی که تراکنش به پایان نرسیده هیچ تراکنشی اجازه ویرایش و حتی خواندن این اطلاعات را نیز نخواهد داشت.در واقع سطح جداسازی را تا حدودی افزایش می‌دهد و سطح اضافی محافظت همزمانی را با پیشگیری از Dirty Read و همچنین Non-Repeatable Read فراهم می‌کند.پیشگیری از Non-Repeatable Read بسیار مفید است اما حتی نگه داشتن Shared Lock تا زمان پایان تراکنش می‌تواند دسترسی کاربران به اشیا را مسدود کند، بنابراین به بهره وری لطمه وارد می‌کند.
نکته بهینه ساز برای این سطح REPEATEABLEREAD است.

مثال: با تغییر ایزولیشن لول داریم:

همزمان کویری دیگری از جنس Write می آید ولی Select دیتا را لاک کرده:

نتیجه میشود: که اگر ایزولیشن لول را تغییر نیمدادیم و نرمال بود اینطور نمیشد

7-4- Serializable:

تراکنش های به همان ترتیب و یکی یکی و پشت سر هم اجرا شده و نه همزمان پس نتیجه آن بدین ترتیب خواهد شد که این سطح از تمام مسائل هم زمانی پیشگیری می‌کند به جز برای Lost Update ! این سطح به واقع بالاترین سطح آنچه را که سازگاری نامیده می‌شود، برای پایگاه داده فراهم می‌کند. در واقع فرآیند بهنگام رسانی برای کاربران مختلف به طور یکسان عمل می‌کند به گونه ای که اگر همه کاربران یک تراکنش را در یک زمان اجرا می‌کردند، این گونه می‌شد « پردازش امور به طور سریالی».
با استفاده از نکته بهینه ساز SERIALIZABLE یا HOLDLOCK در پرس و جو شبیه سازی می‌شود.

7-5- Snapshot:

در این سطح روش کار بسیار جالب می باشد بدین ترتیب که هر گونه تراکنشی که باعث تغییر بر روی دیتا باشد قبل از شروع،موجب تهیه یک تصویر از رکوردهای می شود که قرار است تغییر کنند که این تصویر در  Tempdb نگهداری خواهد شد،و در صورت نیاز به Read در حین اجرای تراکنش فوق، به این تصویر مراجعه خواهد شد، به عنوان مثال زمانی که یک Update رخ می دهد، ابتدا یک تصویر از رکوردهای Where Clues این Update قبل تغییر در Tempdb  قرار خواهد گرفت، لذا در این فاصله زمانی هر تراکنشی قصد خواندن همین رکوردها را داشته باشد،به این تصویر مراجعه خواهد کرد و هیچ Xlock بوجود نخواهد آمد،پس از این دیدگاه همانند Read Uncommitted ولی بدون رخداد Dirty Read رفتار خواهد شد چراکه به تصویر مراجعه خواهد شد! اما در مورد Delete و insert توسط یک تراکنش که همان دو رفتار رخداده در Update محسوب میشود، رفتار مشابه صورت گرفته و قبل از Delete رکورد ها، یک کپی از آن رکوردها در قالب یک تصویر در Tempdb  آماده پاسخ به تراکنش های داری Select خواهند بود، پس بازهم با Xlock مواجه نخواهیم بود، زمان insert نیز قبل از شروع کار از رکوردها تصویر تهیه خواهد شد!

پس تاثیر رکوردهای در حال Write بر روی رکورد های Read  نخواهد بود و می توان گفت در حال Read Committed بدون Xlockهستیم، از طرفی مشکل Non Repeatable Read نیز حل شده است، چراکه مادامی که تراکنش حاوی Write در حال انجام است، همواره اطلاعات از تصویر خوانده میشود. اما نهایتا تصاویر بعد از هر Write می توان گفت بروز می شوند(قبل از هر Write یک تصویر جدید تهیه می شود و زمانی هیچ Writeی نباشد به دیتابیس اصلی مراجعه صورت می پذیرد)

جدترین سطح جداسازی است که در نسخه 2005 اضافه شد، که شبیه ترکیبی از Read Committed و Read Uncommitted است. به طور پیش فرض در دسترس نیست، در صورتی در دسترس است که گزینه ALLOW_SNAPSHOT_ISOLATION برای بانک اطلاعاتی فعال شده  باشد.(برای هر بانک اطلاعاتی موجود در تراکنش)

ALTER DATABASE testDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;

ALTER DATABASE testDatabase SET READ_COMMITTED_SNAPSHOT ON;

اگر دستور دوم آورده نشود، قبل از هر Query می بایست آورده شود.

Snapshot مشابه Read Uncommitted هیچ قفلی ایجاد نمی‌کند. تفاوت اصلی آن‌ها در این است که تغییرات صورت گرفته در بانک اطلاعاتی را در زمان‌های متفاوت تشخیص می‌دهند. هر تغییر در بانک اطلاعاتی بدون توجه به زمان یا Commit شدن آن، توسط پرس و جو هایی که سطح جداسازی Read Uncommitted را اجرا می‌کنند، دیده می‌شود. با Snapshot فقط تغییراتی که قبل از شروع تراکنش، Commit شده اند، مشاهده می‌شود.
از شروع تراکنش Snapshot، تمامی داده‌ها دقیقاً مشاهده می‌شوند، زیرا در شروع تراکنش Commit شده اند.
نکته: در حالی که Snapshot توجهی به قفل‌ها و تنظیمات آنها ندارد، یک حالت خاص وجود دارد. چنانچه هنگام انجام Snapshot یک عمل Rollback (بازیافت) بانک اطلاعاتی در جریان باشد، تراکنش Snapshot قفل‌های خاصی را برای عمل کردن به عنوان یک مکان نگهدار  و سپس انتظار برای تکمیل Rollback تنظیم می‌کند. به محض تکمیل Rollback، قفل حذف شده و Snapshot به طور طبیعی به جلو حرکت خواهد کرد.

نتیجه مهم: کاری که در اینجا انجام خواهد شد، ایجاد یک snapshot یا یک کپی فقط خواندنی، از بانک اطلاعاتی کاری شما می‌باشد. بنابراین در این حالت، زمانیکه یک عبارت Select را فراخوانی می‌کنید، این خواندن، از بانک اطلاعاتی فقط خواندنی تشکیل شده، صورت خواهد گرفت. اما تغییرات بر روی دیتابیس اصلی کاری درج شده و سپس این snapshot به روز می‌شود.
حالت READ_COMMITTED_SNAPSHOT خصوصا برای برنامه‌های وبی که تعداد بالایی Read در مقابل تعداد کمی Write دارند، به شدت بر روی کارآیی و بالا رفتن سرعت و مقیاس پذیری آن‌ها تاثیر خواهد داشت؛ به همراه حداقل تعداد deadlockهای حاصل شده.

 DBCC PINTABLE

در نگارش‌های قدیمی‌تر اس کیوال سرور، دستوری وجود داشت به نام DBCC PINTABLE که سبب ثابت نگه داشتن صفحات جداول مبتنی بر دیسک یک دیتابیس، در حافظه می‌شد. به این ترتیب تمام خواندن‌های مرتبط با آن جدول، از حافظه صورت می‌گرفت. مشکل این روش که سبب منسوخ شدن آن گردید، اثرات جانبی آن بود؛ مانند خوانده شدن صفحات جدیدتر (با توجه به اینکه ساختار پردازشی و موتور بانک اطلاعاتی تغییری نکرده بود) و نیاز به حافظه‌ی بیشتر تا حدی که کل کش بافر سیستم را پر می‌کرد و امکان انجام سایر امور آن مختل می‌شدند. همچنین اولین ارجاعی به یک جدول، سبب قرار گرفتن کل آن در حافظه می‌گشت. به علاوه ساختار این سیستم نیز همانند روش مبتنی بر دیسک، بر اساس همان روش‌های قفل گذاری، ذخیره سازی اطلاعات و تهیه ایندکس‌های متداول بود.

تفاوت Optimize Table  با DBCC PINTABLE

اما جداول بهینه سازی شده‌ی برای حافظه، از یک موتور کاملا جدید استفاده می‌کنند؛ با ساختار جدیدی برای ذخیره سازی اطلاعات و تهیه ایندکس‌ها. دسترسی به اطلاعات آن‌ها شامل قفل گذاری‌های متداول نیست و در آن حداقل زمان دسترسی به اطلاعات درنظر گرفته شده‌است. همچنین در آن‌ها data pages یا index pages و کش بافر نیز وجود ندارد.

اگوریتم In Memory Optimize Table  

در این الگوریتم که از سطح عملکرد Snapshot  ایده گرفته، (یعنی هر رکورد که قرار است تغییر داشته باشد قبل از تغییر یک تصویر از آن تهیه میشد در TempDB ) با این تفاوت که هر رکورد دارای یک هدر است که در آن یکسری اطلاعات مربوط به پیاده سازی این الگوریتم قرار می گیرد، از جمله یک timestamp شروع و یک timestamp پایان ، timestamp شروع بیانگر تراکنشی است که رکورد را ثبت کرده و timestamp پایان برای مشخص سازی تراکنشی بکار می‌رود که رکرود را حذف کرده است. اگر timestamp پایان، دارای مقدار بی‌نهایت باشد، به این معنا است که رکورد متناظر با آن هنوز حذف نشده‌است. به روز رسانی یک رکورد در اینجا، ترکیبی است از حذف یک رکورد موجود و ثبت رکورد جدید. برای یک عملیات فقط خواندنی، تنها نگارش‌هایی از رکوردها که timestamp معتبری داشته باشند، قابل مشاهده خواهند بود و از مابقی صرفنظر می‌گردد.

پس زمانی که این نوع جداول شروع به کار کرده و تحت تاثیر تراکنش Insert صورت می گیرد، یک رکورد جدید با یک Begin Timestamp در حافظه ایجاد می شود، اما زمان حذف یک رکورد، بعد از جستجو آن رکورد در حافظه،آن رکورد مجددا با End Timestamp در حافظه قرار می گرد، نتیجه اینکه زمانی که یک تراکنش Select رخ می دهد، از بین نسخه هایی از رکورد که در حافظه وجود دارد، رکورد که جدیدتر Begin Timestamp را داشته و End Timestamp ندارد(که به جای آن مقدار بینهایت قرار داده میشود)، به عنوان رکورد معتبر انتخاب خواهد شد. به لحاظ بحث لاکینگ نیز بدین ترتیب رفتار خواهد شد،که زمانی تراکنش Select ایجاد میشود،آخرین نسخه رکورد(جدید ترین Begin Timestamp بدون End Timestamp) ارائه خواهد شد و منتظر هیچ xlock نخواهد شد.چرا که نوشتن با ایجاد نمونه رکورد جدید و حذف نیز با ایجاد یک رکورد جدید انجام میشود  (همانند Read Uncommitted بدون Dirtily Read) ، خوب Update هم که یک نوشتن و حذف است و همین روال بدون هیچ لاکی صورت می پذیرد.
در OLTP درون حافظه‌ای که از روش چندنگارشی همزمانی استفاده می‌کند، برای یک رکورد مشخص، ممکن است چندین نگارش وجود داشته باشند؛ بسته به تعداد باری که یک رکورد به روز رسانی شده‌است. در اینجا یک سیستم garbage collection همیشه فعال، نگارش‌هایی را که توسط هیچ تراکنشی مورد استفاده قرار نمی‌گیرند، به صورت خودکار حذف می‌کند؛ تا مشکل کمبود حافظه رخ ندهد.

نتیجه اینکه حتی اگر کل بانک اطلاعاتی مبتنی بر دیسک را در حافظه قرار دهید به کارآیی روش جداول بهینه سازی شده‌ی برای حافظه نخواهید رسید. زیرا در آن هنوز مفاهیمی مانند data pages و index pages به همراه یک buffer pool پیچیده وجود دارند. در روش‌های مبتنی بر دیسک، ردیف‌ها از طریق page id و row offset آن‌ها قابل دسترسی می‌شوند. اما در جداول بهینه سازی شده‌ی برای حافظه، ردیف‌های جداول با یک B-tree خاص به نام Bw-Tree  در دسترس هستند.

In Memory Table File and FileGroup

گروه فایل بهینه سازی شده برای حافظه، دارای چندین دربرگیرنده(Container) است که هر کدام چندین فایل را در خود جای خواهند داد:
– Root File که در برگیرنده‌ی متادیتای اطلاعات است.
– Data File که شامل ردیف‌های اطلاعات ثبت شده در جداول بهینه سازی شده‌ی برای حافظه هستند. این ردیف‌ها همواره به انتهای data file اضافه می‌شوند و دسترسی به آن‌ها ترتیبی است. کارآیی IO این روش نسبت به روش دسترسی اتفاقی به مراتب بالاتر است. حداکثر اندازه این فایل 128 مگابایت است و پس از آن یک فایل جدید ساخته می‌شود.
– Delta File شامل ردیف‌هایی است که حذف شده‌اند. به ازای هر ردیف، حداقل اطلاعاتی از آن را در خود ذخیره خواهد کرد؛ شامل ID ردیف حذف شده و شماره تراکنش آن. همانطور که پیشتر نیز ذکر شد، این موتور جدید درون حافظه‌ای، برای یافتن راه چاره‌ای جهت به حداقل رسانی قفل گذاری بر روی اطلاعات، چندین نگارش از ردیف‌ها را به همراه timestamp آن‌ها در خود ذخیره می‌کند. به این ترتیب، هر به روز رسانی به همراه یک حذف و سپس ثبت جدید است. به این ترتیب دیگر بانک اطلاعاتی نیازی نخواهد داشت تا به دنبال رکورد موجود برگردد و سپس اطلاعات آن‌را به روز نماید. این موتور جدید فقط اطلاعات به روز شده را در انتهای رکوردهای موجود با فرمت خود ثبت می‌کند.

با توجه به اینکه هدف ارتقا سرعت می باشد، و فایل های فوق الذکر در واقع حکم Log فایل برای جدول ما را داشته، لذا می بایست در یک هارد دیسک با سرعت بالا SSD قرار داده شود،تا موجبات کندی نباشد.

DURABILITY & Stability

یکی از خصوصیات این نوع جدول قابلیت ماندگاری اطلاعات بوده که بسیار مهم نیز می باشد، و برای آن دو انتخاب وجود دارد،توسط مقدار SCHEMA_AND_DATA   مشخص می‌کنیم که آیا قرار است اطلاعات و ساختار جدول، ذخیره شوند یا در حالت SCHEMA_ONLY تنها قرار است ساختار جدول ذخیره گردد، که روال ذخیره سازی از طریق فایل های اشاره شده صورت می پذیرد.  پس با توجه این نکته اگر به هر علتی برق سرور قطع شده یا … چون اطلاعات در حافظه موقت است، چه بر سر اطلاعات خواهد آمد، که این دقیقا بر می گردد به بحث Durability بدین ترتیب که در حالت  SCHEMA_ONLY ساختار جدول در Root File بوده، و داده آن فقط در حافظه بوده و لذا از بین خواهد رفت، اما در حالت SCHEMA_AND_DATA   تصاویر رکورد ها در Data و Delta فایل قرار داشته، و دقیقا همانند رفتار LDF قابل بازیافت خواهند بود. می توان تصور کرد SCHEMA_ONLY تصاویر در فایل های فوق ذخیره نمی شوند و فقط در حالت SCHEMA_AND_DATA  از دو فایل Delta و Data استفاده می گردد.

Indexing in Memory Table OLTP

ابتدا به ویژگی های ایندکس گذاری بر روی جداول مبتنی بر حافظه پرداخته سپس اقدام به معرفی الگوریتم کار آن خواهیم کرد:

1. فقط زمان ایجاد جدول قابل تعریف است، گرچه خصوصیات آن قابل تغییر است.(یک الگوریتم است)
2. این نوع ایندکس بر روی جدولی اعمال میشود که روی دیسک  و مبتنی بر Page نیست، بلکه نمونه هایی از رکورد ها در حافظه بوده و اگر قرار است ترتیبی اتخاذ شود ترتیب خواندن اطلاعات خواهد بود و نه ترتیب حقیقی رکورد ها با این منطق فقط از نوع Non Clustered می تواند باشد.
3. فقط دو نوع Index را ساپورت می کند، Range  (همان index معمول تا که بر روی دیسک بوده و….) و یا Hash که الگوریتم پیچیده ولی جالبی دارد.زمانی دستور Between داریم از Range استفاده می کنیم،چراکه در این حالات Hash کارایی لازم را ندارد.
4. با توجه به الگوریتم و استفاده از لیست پیوندی، می توان گفت Hash Index، 100 درصد Cover Index می باشد.
5. حداکثر می توان 8 ایندکس داشت از نوع Hash  که علت آن بر می گردد به نمونه اطلاعات موجود در حافظه.
6. فرآیند Re Index و Rebuild و اصلا Fragmentation کلا نداریم.
7. ایندکس های می توان unique یا غیر unique باشند(در 2016) که بهتر است unique باشد.

نکته مهم نحوه پیاده سازی Hash Index است،با این مقدمه که Hash Index در حافظه ساخته شده و نمونه مرتب شده از اطلاعات بر روی جدول مجزا (Non Clustered) و یا همان جدول(Clustered) نیست ،خواهیم داشت:

فرض کنید می خواهیم بر روی یک فیلد index تعریف کنیم از نوع Hash، پس در همان لحظه یک آرایه برای این index در حافظه ساخته شده که به هرخانه از این آرایه یک سطل یا Bucket می گوییم، حال اینکه این آرایه چند Bucket دارد قابل بحث می باشد.(مثلا در جدول تراکنش ها، کد تراکنش می خواهیم Hash index و حتی Primary key باشد).

از این لحظه به بعد با هر insert یک رکورد(دقت شود به نکته 1،یعنی بعد از ساخت جدول و ورود اطلاعات) ، یک تایع به نام F(x) که از توابع درون SQL است،اقدام به Hash کردن مقدار فیلدی که به عنوان index انتخاب شده برای رکورد در حال insert کرده و مقدار نتیجه را در یکی از این Bucketهای آرایه تعریف شده مربوط به این index (بصورت اتفاقی که خود قابل بحث است) قرار می دهد.(بدیهی است با شناخت الگوریتم Hash متوجه می شویم که هر Bucket ، 8 بایت است)، این  روال که برای insert هر رکورد انجام شده و نهایتا برای این index می توان چنین دیاگرامی  را تصور کرد:

خوب می دانیم رکورد ها در Memory دارای نسخه های متفاوتی هستند، پس بعد از Hash کردن مقدار  فیلد Index در  رکورد درحال insert ، در Bucket ذخیره  شده و اشاره گری از Bucket خارج شد که به رکورد مربوط به آن در حافظه اشاره خواهد داشت(مثلا رکورد تراکنش در حالی که در حافظه ورژن های متفاوتی دارد، مقدار کد تراکنش بصورت Hash شده در یکی از Bucket ها قرار داشته و اشاره گری به مکان حقیقی رکورد در حافظه اشاره دارد)

با این منطق هر گاه یک Query به DBMS تحویل شده که Where Clues آن فیلد انتخاب شده به عنوان index باشد، DMBS اقدام به Hash  کردن این مقدار کرده، سپس در آرایه جستجو کرده و Bucket مربوطه را بدست آورده.(چون الگوریتم Hash است و بر پایه مقایسه Hash های میباشد به سرعت Bucket مربوطه کشف خواهد شد، و ارتقا سرعت سوقات ما است!)حال از bucket کشف شده اشاره گری به سمت رکورد اصلی وجود خواهد داشت، که از طریق آن اطلاعات  رکورد فوق قابل بازیافت می باشد(کل اطلاعات از index نشاط گرفت، پس Cover رفتار کرد! اشاره به نکته 4)

مطابق این طرح پس حتما تعداد Bucket برای آرایه در نظر گرفته شده برای هر index باید به تعداد رکورد ها باشد، (ضمنا نباید مقدار تکراری داشته باشیم  در 2014و unique index لازم داریم،در 2016 به نظر می آید مقادیر تکراری در ادامه لینک لیست تکرار خواهد رفت، مثلا در شکل زیر رکورد های قرمز دارای مقدار index یکسان هستند،اشاره به نکته 7) اما در توضیحات بالا گفتیم انتخاب Bucket تصافی است، گرچه این انتخاب سعی می کند عادلانه باشد، و به اعضای هر مقدار index یک bucket خالی در نظر گیرد، اما با احتمال دارد انتخاب bucket بصورت اتفاقی منطبق با Bucket ی باشد که قبلا تخصیص داده شده است، (و یا حتی ممکن است به تعداد لازم bucket نداشته باشیم، که علت آن طراحی اشتباه خواهد بود) در چنین حالتی یک لینک از رکورد اصلی که قبلا مقدار آن Hash شده،خارج شده و به رکورد جدید اشاره داده شود، در نتیجه خواهیم داشت:

در بوک آنلاین آورده شده احتمال اینکه یک Bucket خالی بماند، 1/3 احتمال اینکه یک باکت تک ایندکس باشد 1/3 و احتمال اینکه باکت جفت ایندکس باشد 1/3 خواهد بود.اما اورده شده است اگر اندازه Bucket ها دو برابر distinct مقدار فیلد index باشد، احتمالات بالا بدست آماده و با این احتمال و تا دو سطح رکورد برای هر Bucket همه چیز طبیعی میباشد.(در تصویر بالا رکورد سبز مناسب است)

اما اگر تعداد bucket ها کمتر از اندازه توصیه شده باشد،احتمالا اینکه در زمان تخصیص bucket تداخل پیش آید وجود دارد، که به این حقیقت Bucket Collision می گوییم، و لیست پیوندی رکورد ها طولانی تر از 2 نود خواهد شد(در شکل بالا رکورد های قرمز رنگ به احتمال مقدار تکراری برای یک فیلد index یا نداشن Bucket آزاد یا Collision باشد( پس این یک اتفاق بد محسوب می شود، چرا که با اضافه شدن رکورد، ممکن است لیست های حاصل از Collision بزرگ و بزرگ تر شده که طبیعتا موقع بازیافت اطلاعات باید کل لیست پیمایش شده و موقع درج رکورد نیز ممکن است مجبور باشیم وسط لیست پیوندی نودی اضافه کنیم، که با افت شدید کارایی مواجه خواهیم شد. پس انتخاب تعداد Bucket برای هر Index شدیدا در کارایی آن تاثیر خواهد داشت.پس می توان گفت هرچه آرایه bucket ها بزرگتر باشد، و طول لیست های پیوندی کوچکتر، کارایی بهتر خواهد بود، اما طول آرایه Bucket را نیز باید هوشمندانه وبا توجه به اندازه حافظه آزاد و تعداد Distinct فیلد index باشد، و تعیین مقدار بی نهایت موجب پرشدن به منظور حافظه خواهد شد، و پیمایش بیهوده Bucket های خالی خواهد بود.

اگر به شکل زیر توجه کنید می بینید فقط 8 مکان برای اشاره گر تعریف شده،پس حداکثر می توان 8 ایندکس از این نوع داشت

به نظر می آید، به جای فرآیند Rebuild   و Re index و بررسی مقدار Fragment و بعد اعمال این دو عمل، می بایست مقدار Distinct فیلد index را در بازه های زمانی مشخص بدست آورده و ایندکس ها را به منظور اصلاح تعداد Bucket ها  Alter کرد(اشاره به نکته 6) و با روشن شدن سرویس SQL این ساختار آماده شده و در دیسک چیزی نگهداری نخواهد شد.

ALTER TABLE MyTable_memop
ADD INDEX ix_hash_Column2 UNIQUE
HASH (Column2) WITH (BUCKET_COUNT = 64);

همچنین مطایق این ساختار مشخص است اگر از دستور Between استفاده شود، پیمایش hash index صرفه ندارد و در اینصورت از همان Range Index یا همان  index بر روی دیسک استفاده خواهیم کرد.(مطابقه نکته 2)

Doing Index

ساخت جداول با دو نوع Index

— Create In-Memory Table with simple NonClustered Index (a.k.a Range Index):
CREATE TABLE dbo.MemOptTable_With_NC_Range_Index
(
ID INT NOT NULL
PRIMARY KEY NONCLUSTERED,
VarString VARCHAR(200) NOT NULL,
DateAdded DATETIME NOT NULL
) WITH (
MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_AND_DATA
)
GO

— Create In-Memory Table with NonClustered Hash Index:
CREATE TABLE dbo.MemOptTable_With_NC_Hash_Index
(
ID INT NOT NULL
PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 10000),
VarString VARCHAR (200) NOT NULL,
DateAdded DATETIME NOT NULL
) WITH (
MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_AND_DATA
)
GO

حال اقدام به تست جستجو خواهیم کرد

SELECT * FROM MemOptTable_With_NC_Hash_Index WHERE ID = 5000 — 4%
SELECT * FROM MemOptTable_With_NC_Range_Index WHERE ID = 5000 — 96%

SELECT * FROM MemOptTable_With_NC_Hash_Index WHERE ID BETWEEN 5000 AND 6000 — 99%
SELECT * FROM MemOptTable_With_NC_Range_Index WHERE ID BETWEEN 5000 AND 6000 — 1%

Index Tuning

Natively compiled stored procedures

مفهوم Natively Compiled بدان معنا می باشد که هر داده ای با هر منظور فقط یکبار در حافظه واکشی شده و پورسه های سیستمی و DBMS تنها یکبار بر روی آن انجام شود بطوری که در مراجعات بعدی، این پروسه تکرار نشود، حال با این مفهوم کلی Natively compiled در مورد Table و procedures قابل بحث است، بطوری که Natively compiled Table که حالت مقدماتی in memory Table است، یعنی واکشی اطلاعات یک جدول هنگام Start شدن SQL سرور در داخل حافظه که اگر با خصوصیت Multi Snapshot ترکیب شود، همان in memory Table  خواهد شد، به عنوان مثال داریم:

use master
go
create database db1
go
alter database db1 add filegroup db1_mod contains memory_optimized_data
go
— adapt filename as needed
alter database db1 add file (name=’db1_mod’, filename=’c:\data\db1_mod’) to filegroup db1_mod
go
use db1
go
create table dbo.t1
(c1 int not null primary key nonclustered,
c2 INT)
with (memory_optimized=on)
go

حال برای procedure نیز این داستان تکرار می شود، بدین ترتیب که SQL زمان شروع به کار یک SP را کامپایل کرده و حتی آن را بصورت یک dll تبدیل کرد، که فایل فوق جزو فایل های Database  و SQL نبوده (نیاز به Backup ندارد)بلکه در هر بار شروع به کار SQL و کامپایل SP  ساخته میشود، و زمان فرخوانی SP، این dll ازپیش کامپایل شده صدا زده شده و با ارتقای سرعت دستورات آن SP اجرا خواهند شد(در برنامه نویسی اصلاحا گفته میشود SP به زبان ماشین تبدیل شده است) این نوع SP ها که بصورت زیر اقدام به تعریف آن میشود در زمان استفاده از In memory Table و با فرض وجود قابلیت memory_optimized_data   بر روی پایگاه داده می تواند بسیار مفید واقع شود.

create procedure dbo.native_sp
with native_compilation, schemabinding, execute as owner
as
begin atomic
with (transaction isolation level=snapshot, language=N’us_english’)

declare @i int = 1000000
while @i > 0
begin
insert dbo.t1 values (@i, @i+1)
set @i -= 1
end

end
go
exec dbo.native_sp
go
— reset
delete from dbo.t1
go

حتی میتوان اقدام به مشاهده Dll های ساخته شود توسط SQL کرد،برای Table  که قبلا ساختیم خواهیم داشت:

— retrieve the path of the DLL for table t1 

select name, description FROM sys.dm_os_loaded_modules 

where name like ‘%xtp_t_’ + cast(db_id() as varchar(10)) + ‘_’ + cast(object_id(‘dbo.t1’) as varchar(10)) + ‘.dll’ 

go 

Used

– برنامه‌هایی که در آن‌ها تعداد زیادی تراکنش کوتاه مدت وجود دارد به همراه درجه‌ی بالایی از تراکنش‌های همزمان توسط تعداد زیادی کاربر.
– اطلاعاتی که توسط برنامه زیاد مورد استفاده قرار می‌گیرند را نیز می‌توان در جداول بهینه سازی شده جهت حافظه قرار داد.
– زمانیکه نیاز به اعمال دارای write بسیار سریع و با تعداد زیاد است. چون در جداول بهینه سازی شده‌ی برای حافظه، صفحات داده‌ها و ایندکس‌ها وجود ندارند، نسبت به حالت مبتنی بر دیسک، بسیار سریعتر هستند. در روش‌های متداول، برای نوشتن اطلاعات در یک صفحه، مباحث همزمانی و قفل‌گذاری آن‌را باید در نظر داشت. در صورتیکه در روش بهینه سازی شده‌ی برای حافظه، به صورت پیش فرض از حالتی همانند snapshot isolation و همزمانی مبتنی بر نگارش‌های مختلف رکورد استفاده می‌شود.
– تنظیم و بهینه سازی جداولی با تعداد Read بالا. برای مثال، جداول پایه سیستم که اطلاعات تعاریف محصولات در آن قرار دارند. این نوع جداول عموما با تعداد Readهای بالا و تعداد Write کم شناخته می‌شوند. چون طراحی جداول مبتنی بر حافظه از hash tables و اشاره‌گرهایی برای دسترسی به رکوردهای موجود استفاده می‌کند، اعمال Read آن نیز بسیار سریعتر از حالت معمول هستند.
– مناسب جهت کارهای data warehouse و ETL Staging Table. در جداول مبتنی بر حافظه امکان عدم ذخیره سازی اطلاعات بر روی دیسک سخت نیز پیش بینی شده‌است. در این حالت فقط اطلاعات ساختار جدول، ذخیره‌ی نهایی می‌گردد و اگر سرور نیز ری استارت گردد، مجددا می‌تواند اطلاعات خود را از منابع اصلی data warehouse تامین کند.

Memory Need

باید درنظر داشت که تمام جداول بهینه سازی شده‌ی برای حافظه، به صورت کامل در حافظه ذخیره خواهند شد. بنابراین بدیهی است که نیاز به مقدار کافی حافظه در اینجا ضروری است. توصیه صورت گرفته، داشتن حافظه‌ای به میزان دو برابر اندازه‌ی اطلاعات است. البته در اینجا چون با یک سیستم هیبرید سر و کار داریم، حافظه‌ی کافی جهت کار buffer pool مختص به جداول  مبتنی بر دیسک را نیز باید درنظر داشت.
همچنین اگر به اندازه‌ی کافی حافظه در سیستم تعبیه نشود، شاهد شکست مداوم تراکنش‌ها خواهید بود. به علاوه امکان بازیابی و restore جداول را نیز از دست خواهید داد. 
البته لازم به ذکر است که اگر کل بانک اطلاعاتی شما چند ترابایت است، نیازی نیست به همین اندازه یا بیشتر حافظه تهیه کنید. فقط باید به اندازه‌ی جداولی که قرار است جهت قرار گرفتن در حافظه بهینه سازی شوند، حافظه تهیه کنید که حداکثر آن 256 گیگابایت است.