هم زمانی داده ها در پایگاه داده

[antisocial]

قبلا بعضی از دوستان راجع به این موضوع چند سوال مطرح کرده بودند من هم
تصمیم گرفتم که هرچه اطلاعات راجع بهسطوح ایزولاسیون دارم در اختیار همه بذارم :
---------------------------------------------------------------------------------------
به طور کلی همزمانی تراکنش ها باعث 4 نوع خطا میشوندکه تنها به ذکرنام آنها میپردازیم :
1.خواندن نامساعد dirtyread
2.تغییرات از دست رفته lastupdates
3.خواندن های غیر تکراری unrepeatableread
4.فانتوم ها



set transaction isolation level read uncommited

در فرمان بالا هیچ گونه قفلی گذاشته نمیشود


set transaction isolation level read commited

فرمان بالا جلوی dirty read را میگیرد


set transaction isolation level repeatable read

فرمان بالا هم جلوی خواندن های تکراری را میگیرد و هم dirty read


set transaction isolation level serializable

فرمان بالا به غیر از lastupdate از همه ی خطاها جلوگیری میکند
مثال:
set transaction isolation level repeatable read
begin transaction
select * from orders
commit transaction

قفلها :
قفلها اشیائی هستند که از بروز اشکالات ناشی از اجرای هم زمان تراکنشها جلوگیری به عمل میاورند.
قفلها میتوانند روی سطوح مختلفی مانند سطرها،صفحه ها،جداول،پایگاه های داده و غیره قرار گیرند و از انجام عملیات همزمان جلوگیری به عمل آورند،قفلها را میتوان به گروه های زیر تقسیم نمود:

قفل اشتراکی (shared lock) یا shared(s
این نوع قفلها برروی سطرها یا داده هایی ایجاد میشوند که عمل خواندن روی آنها ایجاد گردد.تا از تغییر یا حذف داده ی مربوطه در زمان خواندن آن جلوگیری به عمل آید.
استفاده از چندین قفل اشتراکی بوسیله ی چند تراکنش،به طور همزمان امکان پذیر است.
قفل انحصاری exclusive lock
این نوع قفلها بر روی داده هایی قرار میگیرند که در حال درج یا به روز رسانی یا حذف میباشند.در هر لحظه فقط یک تراکنش میتواند برروی یک منبع داده از این نوع قفل استفاده کند
قفل update lock
زمانی استفاده میشود،که یک تراکنش در حال تغییر داده ی مربوطه میباشد.در یک لحظه فقط یک تراکنش میتواند از این قفل برروی منبع داده استفاده کند
قفل intent
این قفل از انواع قفلهای داخلی در sql محسوب میشود با استفاده ازین قفل میتوان سایر تراکنش ها را از وجود قفل برروی منبع داده مطلع نمود تا از تداخل قفل ها جلوگیری به عمل آید
قفل intent میتواند به صورت IX,IS,IU مورد استفاده قرار گیرد
قفل schema
از این قفل زمانی استفاده میشود که از حذف یا تغییر جداولی که تراکنش های دیگر روی آنها عملیات انجام میدهند جلوگیری به عمل آورد

با استفاده از رویه ی سیستمی sp_lock میتوان از قفلهای فعال در پایگاه داده اطلاع حاصل کرد
exec sp_lock
برای اطلاعات بیشتر میتوان به آدرس زیر مراجعه کرد:
MSDN=>index=>تایپ کنید sp_lock

مفهوم deadlock یا بن بست:
زمانی رخ میدهد که دو تراکنش میخواهند به طور هم زمان بر روی یک منبع داده،قفل گذاری نمایند در این صورت یکی از تراکنش ها بایستی لغو گردد یا rollback شود

منبع : شعبه ی حافظ مجتمع فنی تهران (دپارتمان برنامه نویسی)
امیدوارم به درد همه بخوره:خجالت:

[sia_2007]

سلام دوست عزیز؛
البته در بعد از 2005 ؛ سطح های ایزوله به 5 سطح افزایش پیدا کرده است.
---
ببینید دوستان؛ مبحث Lock بر 2 قسم است :
یکی سطح آن : در سطح رکوردها یا سطح جداول
دوم شدت آن
---
1 Read Commited
Read Uncommitted 2
Repeatable Read 3
Serializable Read 4
Snap Shot 5
---
SP_GetAppLock
---
برای این که فقط مقادیر قطعی خوانده شوند از 1 استفاده میکنیم.
فرض بفرمایید یک نفر دارد کلیه رکوردهای یک جدول را بروز رسانی میکند.
حال چه با دستور Update و چه با تراکنشی که دارای این دستور است.
در این صورت اگر شما بخواهید همان رکورد ها را بخوانید باید صبر کنید تا کار او تمام شود.
---
حال فرض کنید که گزارشی که شما میخواهید بگیرید بسیار واجب و در اصطلاح مدیریتی است.
در این صورت میتوانید Dirty Read بکنید.
مثلا رکوردهای Lock شده که در حال بروز رسانی هستند یک سری فاکتورهای فروش باشند.
در این صورت اگر در جمع مقادیر فروش از بین 1000 رکورد 2 تا به مشکل بخورند که اشکالی ندارد.
تازه به مشکل نمیخورند؛ بلکه مقدار غیر قطعی قبلی را بر میگردانند.
البته استفاده از این در مسائل مالی جاری اصلا توصیه نمیشود و بهتر است همان حالت معمولی بخوانید ولو کمی علاف شوید.
مثلا گزارشی که بر اساس آن چک صادر میشود.
برای خواندن کثیف (حالت 2) داریم :
SELECT * FROM WITH (NOLOCK) WHERE GROUP BY Order
و برای مثال در Join 2 جدول در صورتی که هر دویشان Lock دارند؛ این را جلوی هر دویشان مینویسیم.
و یا قبل از شروع تراکنش داریم :
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
با 2 تا T
SET XACT_ABORT ON
BEGIN TRAN
SELECT * FROM tbl_Customer
COMMIT
XACT_ABORT ON برای این است که در صورتی که تمامی دستورات به درستی انجام شوند؛ خودکار Commit و اگر یکی از دستورات به مشکل خورد تمامی دستورات ROLL BACK شوند؛ زیرا که فلسفه TRAN هم همین است.
در این صورت لزومی برای تایپ WITH (NOLOCK) A وجود ندارد.
---
فرض کنید میخواهیم از وضعیت بورس وال استریت یک گزارش برای ساعت 10 بگیریم.
آیا قبول دارید که این کار ممکن است 10 ثانیه طول بکشد ؟
آیا سر اطلاعات که هنگام شروع کوئری به دست آمده با ته اش که 10 ثانیه بعد به دست میآید هیچ به هم نمیخواند ؟
چون در این 10 ثانیه کلی اطلاعات تغییر کرده است.
در این حالت 2 راه داریم :
یک : جلوی تغییر جدول را به مدت ده ثانیه بگیریم
یعنی نگذاریم کسی Insert / Update / Delete انجام دهد.
که معنای دیگر آن در فرهنگ لغت فاجعه است.
چون ما ده ثانیه ملت را علاف کرده ایم تا یک گزارش بگیریم
این حالت را با Serializable و Repeatable میتوان پیاده سازی کرد؛ با این تفاوت که Repeatable جلوی Insert را نمیگیرد.
و این Insert ها چیزهایی هستند که ما نمیخواهیم؛ در واقع نباید ببینیمشان؛ مثل شبح که نباید دیده شود.
که انگلیسی آن میشود Phantom که دوست عزیزمان AntiSocial اشاره فرمودند.
در واقع برای این کارSerializableبهتر است.
---
ولی اگر نخواهیم ملت را 10 ثانیه علاف کنیم چه ؟
از 2005 به بعد گزینه ای آمده به Snap Shot
این گزینه دیتای یک دست مورد نظر ما را میآورد؛ بقیه هم میتوانند کارشان را کنند که سر بسته میگویم اطلاعات آنها در tmpDB نوشته میشود و سپس دائمی میگردد.
ولی برای نوشتن
SET TRAN ISOLATION LEVEL SNAPSHOT
باید این قابلیت را دیتابیس فعال کنیم؛ البته این قابلیت با File Stream در تداخل است.
پس داریم :
USE master
GO
ALTER DATABASE [DryClean] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [DryClean]
SET READ_COMMITTED_SNAPSHOT ON
GO
ALTER DATABASE [DryClean]
SET MULTI_USER
GO
البته شما به جای DryClean نام دیتابیس خودتان را بزنید.
و من در مورد جزئیات این کد توضیح نمیدهم؛ زیرا که بسیار واضح است.
و البته اگر ندانید هم اهمیتی ندارد.
---
نکته ای که مهم است قانونی است که فکر میکنم نامش کانکارنسی است.
اگر کاربر 1 کسی باشد که Snap Shot گرفته و کاربر 2 کسی است که میخواهد اطلاعات Lock شده را تغییر دهد؛ داریم :
کاربر 1 : Snap Shot
کاربر 2 : یکی از رکوردها را تغییر میدهد. مثلا رکورد سوم
کاربر 1 : رکورد شماره چهار را تغییر میدهد.
تا این جا هیچ مشکلی وجود ندارد.
ولی اگر کاربر 1 رکوردی را تغییر دهد که دیگران تغییرش داده باشند؛ در این مثال رکورد شماره سه که کاربر 2 تغییرش داده؛ خطایی رخ میدهد که به آن خطای کانکارنسی میگویند.
منبع : کتاب سیستم عامل آقای مهرداد فهیمی.
لکن من خلاصه اش را برای شما میگویم :
شما میتوانید چیزی را تغییر دهید که دارید.
این ربطی به Sql ندارد؛ به سیستم عامل هم ربطی ندارد؛ بلکه یک اصل منطقی است.
کاربر 1 میخواهد رکورد را در Snap Shot تغییر دهد؛ اما مقدار اصلی در tmpDB است؛ پس وی نمیتواند آنرا تغییر دهد.
خطای کانکارنسی فقط در Snap Shot و Cursor Programming رخ میدهد.
نگران رخ دادن آن حین دستورات عادی نباشید.
--- و اما چند نکته دوستانه :
1- در حالت عادی هیچ مشکلی وجود ندارد.
یک نفر 2 ثانیه جدول را Lock میکند؛ یکی دیگه 2 ثانیه صبر میکند؛ و بعد میخواند.
این مشکلات برای کارهای شبکه ای بالاتر از 20 کاربر هست.
بحث از اون جا شروع شد که گفتیم چه کار کنیم ؟
2- سعی کنید حتی الامکان Tran هایتان کوتاه و سبک باشد. مثلا محاسبات را قبل از آن و درج موارد اظافی را برای بعد از آن قرار دهید.
3- در مبحث سیستم عامل داریم :
برای جلوگیری از بن بست باید منابع را به نوبت در اختیار گذاشت.
سیستم عامل آقای مهرداد فهیمی
این اصل به صورت زیر نقض میشود
Tran A :
Get Resource 1
Get Resource 2
---
Tran B :
Get Resource 2
Get Resource 1
---
در این حالت بن بست امری بدیهی است.
و من نیازی به توضیح نمیبینم.
اما شما در تخصیص منابع ( جداول مخصوصا ) ؛ به Tran ها حالت بر عکس یا ضربدری را پیاده نکنید.
---
4 - روی کمک من حساب کنید
---
و حال یک مبحث کاربردی تر :
ببینید فرض کنید چنین حالتی داریم :
Order :
OrderID = 881123001
OrderID = 881123002
OrderID = 881123003
و ...
و در جدول Order Detail داریم : یا هر Master و Detail دیگری.
881123001 >> 1 >> 2 >> 3 >> و هر چند تا ردیف فاکتور که این دارد.
881123002 >> 4 >> 5 >> 6 >> و هر چند تا ردیف فاکتور که این دارد.
881123003 >> 7 >> 8 >> 9 >> و هر چند تا ردیف فاکتور که این دارد.
که در این جا ؛ PK Order Detail از نوع Identity است.
چند نکته :
1- تا میتواید به این راحتی ها زیر بار کلید اصلی مفهومی نروید.
مثلا این کلید نشانه 3 فاکتور اول روز 23 ام ماه 11 سال 88 است.
---
2- اگر 2 سری Identity داشتید؛ کلید Master را با Identity@@ به دست بیاورید
نه با TOP 1 !!!
---
3- این نکته حائز اهمیت است که درج یک Master با کلیه Details هایش؛ یک تراکنش است.
یا همه یا هیچ؛ یا کل فاکتور با کل ردیف هایش ؛ یا هیچی.
تازه عجیب تر از آن کدهایی است که یک Master را با Details های یک Master دیگر مینشاند.
متاسفانه در کشور عزیزمان ایران؛ با یک کلید اصلی مفهومی؛ و در یک محیط شبکه ای سنگین این امر خیلی طبیعی است دوستان؛
اصغر و اکبر و حسن و نرگس میخواهند 3 تا فاکتور درج کنند با یک سری ردیف فاکتور
آیا باید کد مفهومی که اکبر دارد را به اصغر بدهیم؛ به اکبر خطای دوباره نویسی بدهیم و نرگس را دچار خطای Check Constraint کنیم ؟
واضح است که نه !
باید بگوییم اکبر بیا جلو؛ این رو بگیر و برو کل کارهایت را انجام بده؛ حالا اصغر و الی آخر ...
برای این کار داریم :
SET XACT_ABORT ON
DECLARE @LockStatus INT = 0;
EXEC @LockStatus = sp_getapplock @Resource = 'YekEsmKeDarDB Unique Bashad', @LockMode = 'Exclusive';
--NoLock
--Update
--SharedIntentExclusive
--IntentShared
--IntentExclusive
--UpdateIntentExclusive
--Shared
--Exclusive
BEGIN TRAN
-- کد معروف TOP و یا MAX
-- سپس کلید اصلی تان را مثلا بر اساس تاریخ و غیره بسازید
-- سپس مستر را وارد کنید
-- و سپس فرزندهای مستر
COMMIT
---
این بسیار کاربردی است و تنها روش صحیح درج مستر و فرزندانش است.
کپی این به اصطلاح مقاله چه بدون نام؛ و چه با نام خودتان آزاد است.
باشد که کمکی هر چند کم در راه کمک به افزایش سطح علمی کشور عزیز آریاییمان باشد.
---
در ضمن؛ ممنون از این دوست عزیزمون؛ من خودم از 4 سری قفل فقط Exclusive و Shared رو بلد بودم
---
در ضمن این مقاله رو خودم نوشتم؛ پس نگران آن بخش که گفته ام روی کمک من حساب کنید نباشید.
ولی اگر کسی آن را کپی میکند؛ یا این بخش را حذف کند؛ یا شجاعانه زیر بار مسولیتش رود.
---
خداییش تشویق نداشت ؟