نظرسنجی درباره Lock کردن رکوردها

[حمیدرضاصادقیان]

سلام دوستان. من برای lock کردن رکوردها به نظرم رسید که یک جدول داشته باشم با دو تا فیلد یکی شماره سند یا فاکتورها یکی هم نام کاربر.هروقت کاربری خواست رکوردی رو اصلاح کنه اون شماره سند رو با نام کاربر در جدول اضافه کنه.بعد از اینکه کاربر تائید کرد اون رکورد رو از جدول پاک کنه.هنگامی که کاربر دیگه ای هم خواست اون رکورد رو اصلاح یا حذف کنه بهش پیغام میده.
اگر هم سیستم crash کرد در برنامه یک گزینه قرار میدهیم برای unlock کردن رکوردها.که با زدن اون تمام محتویات جدول پاک خواهد شد.فکر کنم این بهتر از lock کردن خود sql هست.و به نظر من سربارش کمتره.درسته؟
منتظر نظرات سازنده اساتید هستم

[whitehat]

این روش ،روش مناسبی برای این کار نیست شما بهتره از یک SP به همراه یک Transaction استفاده کنید. در صورتی که سیستم شما چند کاربره باشد در صورتی که عمل حذف از جدول بدرستی انجام نشود هیچ کاربر دیگری نمی تواند آن رکورد را ویرایش کند و شما مجبورید همه کاربران را از جدول حذف کنید. یا اگر دو کاربر همزمان عمل Edit را بخواهند انجام دهند و کاربر اول جدول را بخواند و قبل از اینکه کاربر اول چیزی در جدول بنویسد کاربر دوم نیز جدول را بخواند مشکل شما کماکان پابرجا می ماند. البته این فرضیات زمانی است که شما سیستمی چند کاربره و تحت شبکه داشته باشید.
در ضمن استفاده از تراکنش ها بسیار بهتر از اضافه کردن یک سربار اضافی و ذخیره در یک جدول جدید می باشد.
موفق باشید

[حمیدرضاصادقیان]

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

[whitehat]

منظورم وقتی است که دو کاربر تائید را بزنند ، در آن زمان هر دو می توانند مرحله اول که چک کردن Lock شدن توسط دیگری است ،را با موفقیت به پایان برسانند و بعد هر دو edit کنند (امیدوارم درست متوجه شده باشم)
اگر شما از Transaction استفاده کنید براحتی می توانید این کار را مدیریت کنید، در زمانی که یک تراکنش انجام می شود تراکنش دیگر قادر به اجرا نیست (می تواند Rollback شود یا منتظر بماند) در این حال تراکنشی موفق تر است که زودتر اجرا شود. در ضمن حتما این کار را طرف Server (مثلا با استفاده از SP) انجام دهید و در صورت Fail شدن یکی نتیجه را به Clientها اعلام کنید.

[حمیدرضاصادقیان]

یعنی هیچ وقت حتی یک دهم ثانیه هم ممکن نیست بین زدن دکمه اصلاح دو کاربر مختلف باهم تاخیر بیافتد؟
درضمن ببینید من از کامپوننتهای db در دلفی استفاده نمیکنم.
گفتم ابتدا کاربر اصلاح رو میزنه همه کارهاشو انجام میده و تا وقتی تائید نزده هیچ کاری روی جداول من انجام نمیشه. خوب حالا وقتی دو کاربر باهم یک رکورد روedit کردند یا کاربر اول اونو edit کرد و کاربر دوم اونو حذف کرد چه اتفاقی خواهد افتاد؟

[AminSobati]

این روش خوبه و تا حدی متداول! عمده ترین مشکلش وقتیه که کاربر ویرایش کننده (کسی که رکورد رو تحت اختیار گرفته یا به عبارتی Lock کرده) کامپیوترش مثلا Crash میکنه. در این حالت تشخیص اینکه رکورد Lock شده واقعا کاربرش داره کار انجام میده یا Crash کرده، با دردسرهایی همراهه. دو چیز میتونه جایگزین بشه، یکی استفاده از روش Timestamp که به جهت شهرت زیاد از توضیحش خودداری میکنم، ولی دومی که به ذهنم رسید استفاده از Lock Timeout هستش. یعنی کاربر ویرایش کننده Transaction رو از سمت کلاینت باز نگه داره، ولی سایر کاربرها برای دیدن و سپس تحت ویرایش بردن رکورد، از Lock Timeout که قابل تنظیم هست استفاده کنند (مثلا Timeout به مقدار 5 ثانیه). وقتی از 5 ثانیه مدت زمان Query تجاوز کرد، Error تولید میکنه SQL Server و شما به کاربر اطلاع میدین که رکورد در حال ویرایشه و Try again later!
مزیت مهم این روش در rollback شدن اتوماتیک Transaction (و آزاد شدن رکورد) هست زمانی که کلاینت crash میکنه. اما این یک تئوری بود و تست کردنش با شما!

[حمیدرضاصادقیان]

با تشکر از پاسخ شما.حالا اگر جایی قرار بدیم که لیست رکوردهای lock شده رو نشون بده و کاربر بتونه اونو unlock کنه بازم مشکلی باقی میمونه؟
اخه من برای transaction کد فوق رو بررسی کردم و امکان اینکار نیست که ازاین روش استفاده کنم.

[AminSobati]

شاید برای حل و فصل اتوماتیک Lockها بشه این روش رو استفاده کرد:
وقتی یک رکورد قراره Lock بشه، SPID@@ مربوط به کاربر در فیلدی(یک Flag) برای اون رکورد ذخیره بشه. بعد از اتمام کار، SPID رو به NULL تغییر بده. هر وقت کاربری قصد ویرایش داره، فیلد Flag رو چک کنه، اگر NULL بود یعنی آزاده و اگر نه یعنی در حال ویرایشه اما باید EXEC SP_WHO رو بررسی کنه که آیا واقعا این SPID الان وجود داره یا کاربرش Crash کرده.
هر کاربر به محض کانکت شدن به SQL Server، باید یک Update روی جدول بزنه و هرچی SPID برابر با مقدار SPID خودش وجود داره NULL کنه. با این کار مطمئن میشین SPID کاربری که Crash کرده در جدول باقی نمیمونه.
نتیجتا وقتی یک رکورد رو قصد دارین ویرایش کنین، اگر Flagش برابر با NULL بود که مشکلی نیست، اما اگر NULL نبود پس نمیشه گفت کاربرش الان در سیستم نیست.
بعنوان جمع بندی این میتونه الگوریتم کار باشه:

1) کاربر Login میکنه به SQL Server، قبل از انجام هر کاری، در جدول مورد بحث یک Update انجام میده که اگر رکوردی با Flag برابر SPID خودش هست همه NULL بشن. چون SPIDها یونیک هستند.

2) کاربر قصد میکنه یکی از رکوردها رو ویرایش کنه. اول Flag رو چک میکنه، اگر NULL بود Then ادامه کار! اگر NULL نبود دو حالت پیش میاد:
الف) رکورد واقعا در حال ویرایشه
ب) رکورد تحت ویرایش بوده اما کاربرش Crash کرده
با چک کردن نتیجه SP_WHO چنانچه واقعا SPID ثبت شده در Flag در SP_WHO وجود داشته باشه، قطعا رکورد در حال ویرایشه، اما اگر کاربرش Crash کرده باشه و Flag هنوز مقدار داره، پس SPID موجود در Flag در SP_WHO نیست که تشخیص میدیم میتونیم Lock کنیم، یعنی SPID خودمون رو در Flag قرار بدیم

3) کاربر کار ویرایش رو تموم میکنه و Flag رو برابر با NULL قرار میده.

نکته مهم:
اینکه در مرحله 1 کاربر بلافاصله پس از ورود، SPIDهای مثل خودش در جدول رو NULL میکنه بسیار حائز اهمیته چون اگر NULL نشه این مشکل بوجود میاد:
فرض کنین یک کاربر در حال ویرایش رکوردی بوده و SPID خودش رو که 55 بوده در Flag قرار داده. کاربر Crash میکنه. عدد 55 بعد از چند ثانیه از SP_WHO حذف میشه ولی جدول هنوز Flag=55 رو داره. کاربر دیگه ای Login میکنه و SQL Server عدد 55 رو بعنوان SPID بهش میده. همین کاربر قصد داره اون رکورد رو ویرایش کنه و میبینه SPID=55 رکورد رو در اختیار داره. از طرفی SP_WHO رو چک میکنه و میبینه 55 واقعا وجود داره، پس ویرایش امکان پذیر نیست! اما اگر در بدو Login مقدار Flag رو NULL کرده بود، پس خودش خودش رو Block نمیکرد!
یک حالت دیگه:
فرض کنیم 55 دوباره Crash کرده، پس Flag برای رکورد باقی مونده. کاربر دیگه ای وارد سیستم میشه و 55 بهش داده میشه اما با ویرایش اون رکورد کاری نداره. حالا کاربر سوم وارد سیستم میشه و میخواد اون رکورد رو ویرایش کنه. میبینه SPID=55 در SP_WHO وجود داره پس ویرایش میسر نیست. در حالیکه اگر کاربر دوم (55 واقعی) در ابتدای کار NULL کرده بود، کاربر سوم Block نمیشد.
تا جائیکه خاطرم هست (خیلی وقت پیش تست کردم) وقتی Client با حالت Crash از سیستم بیرون بره، SQL Server بلافاصله قطع شدن این Connection رو تشخیص نمیده و کمی تاخیر داره. در این فاصله کسی که قصد ویرایش رکوردی رو داشته باشه، هنوز مثلا Flag=55 رو در رکورد و همچنین در SP_WHO میبینه لذا ویرایش نمیتونه بکنه. به نظر من وجود این وقفه اجتناب ناپذیره و میشه باهاش کنار اومد!

[حمیدرضاصادقیان]

ممنون استاد.روش قشنگی است.ولی من اینجاشو متوجه نشدم که شما گفتین در بدو ورود باید spid های مشابه خودش رو null کنه.یعنی باید در تمام جداول بگرده این کارو بکنه یا در هرجدولی که وارد میشه باید این تست رو انجام بده.
بعد سربار روی سیستم زیاد نمیشه.و مشکل کندی پیش نخواهد اومد.؟

[AminSobati]

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

[abolfazl585]

اگر شما از Transaction استفاده کنید براحتی می توانید این کار را مدیریت کنید، در زمانی که یک تراکنش انجام می شود تراکنش دیگر قادر به اجرا نیست (می تواند Rollback شود یا منتظر بماند) در این حال تراکنشی موفق تر است که زودتر اجرا شود. در ضمن حتما این کار را طرف Server (مثلا با استفاده از SP) انجام دهید و در صورت Fail شدن یکی نتیجه را به Clientها اعلام کنید

لطفا یک مثال واقعی بزنید. من تا حالا با Transaction کار نکردم.

یکی استفاده از روش Timestamp که به جهت شهرت زیاد از توضیحش خودداری میکنم

نه خودداری نکنید. شاید یکی مثل من چیزی در موردش نمیدونه. لطف کنید یه مثال بزنید.
ممنون از همه.

[سپيده تيموري]

درصورتيكه تمامي كاربران با sa به sql server وارد شوند،‌ مي توان از اطلاعات كمكي ديگري مثلا نام كاربري ويندوز آنها استفاده كرد؟

[Yousef_bs]

منظورم وقتی است که دو کاربر تائید را بزنند ، در آن زمان هر دو می توانند مرحله اول که چک کردن Lock شدن توسط دیگری است ،را با موفقیت به پایان برسانند و بعد هر دو edit کنند (امیدوارم درست متوجه شده باشم)
اگر شما از Transaction استفاده کنید براحتی می توانید این کار را مدیریت کنید، در زمانی که یک تراکنش انجام می شود تراکنش دیگر قادر به اجرا نیست (می تواند Rollback شود یا منتظر بماند) در این حال تراکنشی موفق تر است که زودتر اجرا شود. در ضمن حتما این کار را طرف Server (مثلا با استفاده از SP) انجام دهید و در صورت Fail شدن یکی نتیجه را به Clientها اعلام کنید.

ممکن توضیح بدین چرا حتما این کار باید طرف سرور انجام بشه ؟

[m_omrani]

يک سوال:
آيا SPID در کل زمان زندگي ديتابيس سرور ( از لحظه نصب اون الي الابد!) منحصر به فرده يا فقط در زماني که ديتابيس سرور سرپا است؟
اميدوارم منظورم رو متوجه شده باشين.
مقصودم اينه که آيا هر بار که يک کانکشن به ديتابيس سرور زده مي شه، SQL Server يه SPID کاملاً جديد که اصلاً قبلاً وجود نداشته توليد مي کنه و به کانکشن جديد مي ده يا ممکنه عددي رو بهش بده که قبلاً هم در گذشته ممکنه مال يک پروسه ديگه بوده که تموم شده و رفته؟

اگه جواب منفي باشه، يعني SPID در Life Time ديتابيس سرور منحصر به فرد نباشه، به نظرم الگوريتم بررسي SPID نياز به اين داره که با يه چيزي مثل يه فيلد DateTime، منحصر به فرد بودن SPID رو تضمين کنيم تا يک کاربر اشتباهاً رکورد در حال ويرايش يک کاربر ديگه رو ويرايش نکنه.

اين سناريو ممکنه خيلي بعيد به نظر بياد اما غير ممکن هم نيست:

کاربر با SPID برابر 55 وارد مي شه و شروع به اديت مي کنه. بعد Crash مي کنه. رکورد از نظر منطقي (از نظر سازنده برنامه) قفل شده. مدتي بعد سرور به هر دليلي ريستارت مي شه. بعد از بالا اومدن مجدد سرور، يه کاربر ديگه Login مي کنه و مي خواد رکورد رو ويرايش کنه، از قضا SQL Server دقيقاً همون شماره 55 رو به SPID اش مي ده. لذا وقتي برنامه مي خواد بررسي کنه که آيا اين رکورد قفل هست يا نه فکر مي کنه که رکورد قفل نيست، چون SPID خودش رو توش مي بينه. البته شايد در همين لحظه هم بشه قضيه رو فهميد، چون طبعاً وقتي کسي مي خواد اقدام به ويرايش يک رکورد بکنه، طبعاً يا فلگ بايد NULL باشه يا يک SPID موجود در SP_WHO به جز SPID خودش در اون باشه.

نکته بسيار مهم قضيه که به نظرم بايد بهش دقت کرد اينه که روش Transaction هميشه عملي نيست، خصوصاً وقتي پاي چندين سيستم جدا از هم مطرح باشه. سيستم بانک هاي عضو شتاب رو ببينيد. شما مثلاً با کارت بانک ملي به دستگاه بانک تجارت مراجعه مي کنيذ مي خوايد پول برداشت کنيد، پول از حساب شما کسر مي شه اما دستگاه يهو به دليل يه مشکل و نقص فني مکانيکي يا هر دليل ديگه به شما پول نمي ده. حتماً برخورد کرديد يا شنيديد که پول به حساب تون بر مي گرده ولي با تاخير يک هفته اي. در چنين مواردي نمي شه از Transaction هم استفاده کرد و بايد از روش هاي ابتکاري استفاده کرد مثل پيشنهاد مهندس ثباتي.

اين مطلب مشابه بحث مديريت پردازه ها و همروندي در درس سيستم عامل هستش که الگوريتم هاي مختلفي توسط افراد مختلف به منظور جلوگيري از بن بست (Dead Lock) پيشنهاد داده شده. همه هم روش هاي ابتکاريه.

لذا به نظر من هيچ امتناعي از ابداع راه هاي ابتکاري نبايد داشت و نبايد فکر کرد که حتماً يک شرکتي، يک محصولي قبلاً امکاني براي اين کار فراهم کرده باشه و اونو حل کرده باشه.

به عقيده من چيزي که مهمه و صد در صديه الگوريتمه يک کاره. اگه يک الگوريتم درست باشه، سناريوهاي مختلف رو بتونه جواب بده که دچار بن بست نشه، ديگه روش پياده سازي مهم نيست. مي خواد يکي توي SQL Server براي Lock کردن منطقي رکوردهاي برنامه اش ازش استفاده کرده باشه، مي خواد توي مبحث سيستم عامل براي جلوگيري از بن بست پردازه ها در استفاده از منابع (Resource) ها استفاده کرده باشه.

من به شدت توصيه مي کنم حتماً مبحث مديريت پردازه ها در سيستم عامل و الگوريتم هاي مختلف جلوگيري از بن بست رو مطالعه کنيد.

خيلي بهتون ديد مي ده.

و يک چيزي که به نظرم مي تونه به الگوريتم پيشنهادي مهندس ثباتي اضافه بشه، ايجاد يک Job هست که مثلاً هر 1 دقيقه اجرا بشه و وضعيت پردازه هاي جاري و رکوردهاي قفل شده رو بررسي کنه تا بتونه بن بست ها بر طرف کنه.

[FastCode]

من حوصله نداشتم همه رو بخونم.
ولی روشی که من خودم در چنین شرایطی ممکنه استفاده کنم اینه که وقتی سیستم کاربر crash کرد در login بعدی همه lock های ایجاد شده توسط اون کاربر حذف بشه.
به همین راحتی.

[حمیدرضاصادقیان]

سلام.البته یک نکته رو گوشزد کنم اونم اینه که این تاپیک مربوط به سال 86 هست.فکر میکنم تاحالا مشکل حل شده باشه.
در مورد SPid که فرمودید هر دفعه که یک User از سرور خارج میشه و user دیگه ای login میکنه ممکنه همون Spid رو بگیره.ولی نام کامپیوترش زمان login و خیلی پارامترهای دیگه اون با Spid های قبلی متفاوت هستند.
یکی از مناسبترین راهها استفاده از فیلدهای Timestamp هست برای اینکه متوجه بشیم ایا رکورد مورد نظر تغییری کرده یانه.زیرا در هربار تغییر مقدار آن متفاوت خواهد بود.
همچنین میتوان لاگ تغییرات اون و اینکه توسط چه کاربرانی تغییر کرده رو نیز ذخیره کرد.
روشهای زیادی موجود هست که بستگی به حساسیت پروژه و سبک کاری پروژه میتوان ازش استفاده کرد.
موفق باشید