خواندن داده ها در پایگاه داده به صورت سازگار و با قفل گذاری

سلام.از اینکه سایت  سوفرا  را، برای پاسخ به سوالات خود انتخاب کردید خوشحال هستیم . با محمودی همراه باشید.

خواندن سازگار داه ها با قفل گذاری

خواندن سازگار بدون قفل گذاری یک خواندن سازگار بدین معنی است که اینودی‌بی از چند نسخه ای برای تهیه یک تصویر از پایگاه داده موقع اجرای پرس و جو استفاده می کند و آن پرس و جو فقط تغییرات اعمال شده توسط تراکنش های اجرا شده قبل از آن نقطه از زمان را می بیند و تغییرات اعمال شده توسط تراکنش های بعدی و یا آنهایی که اعمال نشده اند را نمی تواند مشاهده کند. اما استثنای این قانون این است که پرسجو تغییرات قبلی اعمال شده در همان تراکنش را مشاهده می کند. این استثنا باعث بوجود آمدن این اختلاف می شود که ممکن است هنگامی که یک تراکنش تعدادی از سطر ها را بروز رسانی میکند دستور Select علاوه بر تغییرات جدید اعمال شده نسخه های قدیمی سطر ها را نیز ببیند و اگر نشست های دیگر نیز به طور پیوسته جدول را بروز رسانی کنند ممکن است تراکنش نسخه ای از جدول را که اکنون در پایگاه داده وجود ندارد را مشاهده کند. اگر سطح ایزوله سازی برابر با مقدار پیشفرض REPEATEABLE_READ باشد تمام خواندن های سازگار در یک تراکنش از یک تصویر ایجاد شده در اولین خواندن استفاده می کنند و می توانید با اعمال تراکنش جاری و ایجاد یک پرس و جوی جدید تصاویر جدیدتری ایجاد نمایید. با استفاده از سطح READ_COMMITED هر یک از خواندن های سازگار از تصویر جدید ایجاد شده برای خود استفاده می کنند. خواندن سازگار بطور پیشفرض در اینودی‌بی برای پردازش SELECT در سطوح READ_COMMITED و REPEATED_READ استفاده می شود. یک خواندن سازگار هیچ قفلی را روی جدول های مورد دسترسی خود ایجاد نمی کند و به همین دلیل سایر نشست ها مجاز به تغییر آن جدول در هنگامی که خواندن انجام می شود هستند. فرض کنید شما از حالت پیشفرض استفاده می کنید. در این صورت با اجرای یک خواندن سازگار که همان دستور SELECT معمولی هست یک نقطه زمانی به تراکنش شما اعطا می شود که در آن نقطه پرس و جو پایگاه داده را مشاهده میکند. اگر یک تراکنش دیگر بعد از آن نقطه زمانی یک سطر جدول را حذف کند و تغییرات را اعمال کند شما آن سطر را نخواهید دید به همین طریق با دستور های بروزرسانی و درج برخورد می شود. تصویر وضعیت پایگاه داده برای دستور SELECT در تراکنش اعمال می شود و نه الزاما برای دستور های DML. اگر شما سطر هایی را تغییر دهید یا درج نمایید و آن تراکنش را اعمال کنید یک دستور خواندن یا بروزرسانی که توسط یک تراکنش همزمان از نوع REPEATEABLE_READ صادر شده باشد سطر های جدید اعمال شده را تحت تاثیر قرار می دهد حتی اگر پرس و جو نتواند آنها را ببیند. اگر یک تراکنش سطر های اعمال شده توسط یک تراکنش دیگر را بروزرسانی یا حذف کند آن تغییرات برای تراکنش جاری مشاهده پذیر خواهند بود. به عنوان مثال ممکن است با یک موقعیت مانند آنچه در پایین اشاره شده مواجه شوید: SELECT COUNT(c1) FROM t1 WHERE c1 = ‘xyz’; — Returns 0: no rows match. DELETE FROM t1 WHERE c1 = ‘xyz’; — Deletes several rows recently committed by other transaction. SELECT COUNT(c2) FROM t1 WHERE c2 = ‘abc’; — Returns 0: no rows match. UPDATE t1 SET c2 = ‘cba’ WHERE c2 = ‘abc’; — Affects 10 rows: another txn just committed 10 rows with ‘abc’ values. SELECT COUNT(c2) FROM t1 WHERE c2 = ‘cba’; — Returns 10: this txn can now see the rows it just updated. شما می توانید نقطه زمانی را با اعمال کردن تراکنش جاری و اجرای یک دستور مجدد مانند SELECT یا START_TRANSACTION_WITH_CONSISTENT_SNAPSHOT جلو ببرید. به این عمل کنترل سازگاری چند نسخه ای می گویند. در مثال زیر نشست A سطر های درج شده توسط B را فقط در صورتی که B آنها را اعمال کند و A نیز به همین صورت اعمال شود تا نقطه زمانی آن جلو تر از زمان اعمالB برود ، مشاهده خواهد کرد. Session A Session B SET autocommit=0; SET autocommit=0; time | SELECT * FROM t; | empty set | INSERT INTO t VALUES (1, 2); | v SELECT * FROM t; empty set COMMIT; SELECT * FROM t; empty set COMMIT; SELECT * FROM t; ——————— | ۱ | ۲ | ——————— ۱ row in set اگر می خواهید جدید ترین تصویر پایگاه داده را ببینید باید یا از سطح READ_COMMITED و یا خواندن های با قفل گذاری استفاده کنید. SELECT * FROM t LOCK IN SHARE MODE; با استفاده از سطح READ_COMMITED هر تراکنش با ایجاد یک تصویر جدید از پایگاه داده برای خود از آن برای خواندن استفاده می کند. اما با ساتفاده از LOCK IN SHARE MODE یک قفل گذاری اتفاق می افتد و دستور SELECT تا زمانی که تراکنش دارای جدید ترین تصویر به اتمام برسد مسدود می شود. خواندن های سازگار روی برخی از دستورات DDL کار نمی کنند: • خواندن های سازگار روی DROP TABLE کار نمیکنند چون که مای اسکیوال نمی تواند با جدول های Drop شده کار کند و اینودی‌بی آن جدول ها را پاک می کند. • خواندن های سازگار روی ALTER TABLE کار نمی کند به این دلیل که این دستور یک کپی موقت از جدول تهیه می کند و بعد از آن جدول اصلی را حذف میکند. هنگامی که یک دستور خواندن سازگار در یک تراکنش صادر شود سطر ها و جدول ها قابل مشاهده نیستند زیرا هنگامی که تصویر تراکنش گرفته شده است وجود نداشته اند. در این مورد یک خطا به صورت ER_TABLE_DEF_CHANGED برمیگرداند. انواع خواندن برای دستور های SELECT متفاوت هست مانند INSERT INTO … SELECT ، UPDATE … SELECT ، و CREATE TABLE … SELECT که در آنها FOR UPDATE یا LOCK IN SHARE MODE مشخص نشده است: • به صورت پیشفرض اینودی‌بی از قفل های قوی تر استفاده میکند و SELECT نیز به صورت READ COMMITED استفاده می شود. • برای استفاده از یک خواندن سازگار در این شرایط باید گزینه ی INNODB_LOCKS_UNSAFE_FOR_BINLOC را فعال نمایید و سطح ایزوله سازی را به یکی از حالت های READ UNCOMMITED ، READ COMMITED یا REPEATED READ تغییر دهید و در این صورت هیچ قفلی روی سطر های درحال خوانده شدن قرار نمی گیرید. خواندن با قفل گذاری اگر شما در یک ترامنش علاوه بر پرس و جوی داده ها اقدام به درج یا بروزرسانی داده ها نمایید استفاده از دستور معمولی SELECT حفاظت کافی را ایجاد نمی کند و سایر تراکنش ها می توانند سطر هایی را که جستجو کرده اید را بروزرسانی یا حذف کنند . اینودی‌بی از دو نوع خواندن با قفل گذاری پشتیبانی میکند که محافظت بیشتری را ارائه میکنند: • دستور SELECT …. LOCK IN SHARE MODE یک قفل از نوع اشتراکی روی سطر ها هنگام خواندن ایجاد می کند در این صورت سایر تراکنش ها می توانند آن سطر ها را بخوانند ولی نمی توانند تغییراتی را روی آن اعمال کنند تا زمانی که تراکنش شما به پایان برسد. همچنین در این حالت اگر سطر هایی که می خواهید بخوانید توسط تراکنش دیگری تغییر کرده باشند تراکنش شما منتظر می ماند تا آن تغییرات اعمال شوند و سپس از آخرین مقادیر برای خواندن استفاده خواهد کرد. • برای رکورد های شاخص ،دستور SELECT …. FOR UPDATE سطر ها و تمامی شاخص های وابسطه را مانند هنگام بروزرسانی قفل گذاری میکند در این صورت سایر تراکنش ها نمی توانند آن سطر ها را بروزرسانی نموده یا دستور SELECT .. LOCK IN SHARE MODE را روی آن اجرا کنند. خواندن های سازگار تمامی قفل های موجود روی سطر ها را نادیده میگیرند. این نوع دستورات هنگام برخورد با داده های دختی یا گرافی در یک جدول یا به صورت جدا در چندین جدول مفید خواهند بود. در این صورت شما رائس های گراف یا برگ های درخت را پیمایش نموده و حق دارید به عقب برگشته و هر یک از این اشاره گرها را تغییر دهید. تمام قفل های تنظیم شده توسط LOCK IN SHARE MODE و FOR UPDATE زمانی که تراکنش ها اعمال شوند یا به عقب بازگردند آزاد خواهند شد. نکته : قفل کردن سطر ها برای بروزرسانی با استفاده از SELECT FOR UPDATE زمانی که AUTOCOMMIT غیر فعال باشد کار میکنند و در صورتی که AUTOCOMMIT فعال باشد سطر های مشخص شده قفل نخواهند شد.