بروز رسانی تعداد زیادی از رکوردها بدون قفل کردن بانک

[Mohammadi_F]

سلام
آیا در SQL2000 میشه تعداد زیادی از رکوردها رو بروزرسانی کرد بدون اینکه بانک قفل بشه ؟
یعنی اینکه بانک اون اعمال رو انجام بده و اگر در این حین کاربری خواست که کاری رو روی بانک انجام بده ، این کار براش امکان پذیر باشه (حالا احتمالا با سرعت کمتر).
امیدوارم منظورم رو رسونده باشم.
ممنون از توجهتون

[darvishiali]

سلام؛

برای بروزرسانی (UPDATE) تعداد زیادی رکورد در جاهای مختلف، باید از TRANSACTION استفاده کنی. در استفاده از TRANSACTION باید به این مساله دقت کنی که جداول مورد استفاده Lock میشن. یعنی تا زمانیکه تغییرات رو نهایی نکردی، کسی نمیتونه به اطلاعات نهایی شده دسترسی داشته باشه.
حالا اگه میخوای کس دیگه ای بتونه داده ها رو بخونه، باید به اون اجازه Dirty Read بدی. یعنی اینکه بتونه داده هارو قبل از نهایی شدن بخونه (با هر تغییری که تابحال انجام شده).
برای اینکار باید از ISOLATION LEVEL ها کمک بگیری.
قبل از TRANSACTION این دستور رو بنویس:


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

این کد باعث میشه بقیه افراد بتونن Dirty Read کنن.

ولی در مورد استفاده از این کد خیلی خیلی باید احتیاط کنی! چون به داده های برگردونده شده توسط کوئری هایی که Dirty Read میکنن اصلا نمیشه اعتماد کرد؛ چون ممکنه کاربر اول TRANSACTION رو ROLLBACK کنه یا اینکه در ادامه کارهای دیگه ای امجام بده!

[AminSobati]

اگر موقع Update رکوردها Lock نشن، مشکلات Data Integrity بوجود میاد، حتی Transaction بی معنی میشه! در SQL Server 2005 به خاطر امکانات Snapshot این منطق کمی متفاوته و میشه Lock نکرد، اما در 2000 خیر. نهایتا میتونین Update رو در دسته های کوچکتر انجام بدین

[SeniorDevs]

سلام،

دوست عزیز، SQL SERVER 2000 از تکنولوژی به نام Multi-Threading استفاده می کند که حداکثر تا 255 Thread را می تواند بطور همزمان به CPU متصل کند و در واقع 255 QUERY همزمان را در هر 255 میلی ثانیه می تواند انجام دهد.

حتی اگر شما با استفاده از TRANSACT-SQL دستور UPDATE بی پایان را به SQL SERVER بدهید فقط به اندازه 5 ثانیه در اجرای دستورات SQL تاخیر بوجود می آورید اما نمی توانید در کار آن اختلال ایجاد کنید.

بنابراین شما با خیال راحت می توانید با دستور UPDATE حجم عظیمی از Record ها را بروز کنید و کاربران دیگر، می توانند از Table های دیگر براحتی استفاده کنند.

حال اگر کاربری بخواهد به همان Table و Record هایی که شما در حال UPDATE آن هستید دسترسی داشته باشد این دیگر به نوع Lock شما (Optimistic, Pessimistic, ...) که بر روی آن Table و Record ها البته قبل از اجرای دستور اعمال کردید بستگی دارد.

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

[AminSobati]

Optimistic, Pessimistic برای خواندن اطلاعات استفاده میشه، نه Update. شما نمیتونین رکورد رو Update کنین بدون اینکه Lock کنین، در حقیقت این کار رو خود SQL Server انجام میده. ضما شما عدد 5 ثانیه رو از کجا آوردین!

[SeniorDevs]

دوست عزیز، منظورم در ADO بود که خاصیت LockType برای ویرایش و بروز کردن RecordSet مورد استفاده قرار می گیرد.

adLockOptimistic قفل خوشبینانه، رکورد ها تنها هنگام Update رکوردست قفل می شوند.

adLockPessimistic قفل بد بینانه، رکورد ها به محض شروع Update قفل می شوند.

من خودم بر روی بیش از 30 کامپیوتر Industrial Servers متفاوت این را تست کردم و تقریبا 5 ثانیه تاخیر برای همه یکسان بود.

[AminSobati]

من خودم بر روی بیش از 30 کامپیوتر Industrial Servers متفاوت این را تست کردم و تقریبا 5 ثانیه تاخیر برای همه یکسان بود.

ببخشید شاید من درست متوجه نمیشم، منظورتون اینه که دستور Update همیشه حداکثر 5 ثانیه طول میکشه؟

[Mohammadi_F]

ممنون از همگی ولی فکر کنم جوابم رو آقای SeniorDev دادند. من چندین رکورد دارم که می خوام update بشه و می خوام این کار درbackground انجام بشه حالا اگه موقع update یک رکورد به مدت چند صدم ثانیه یک کاربر دیگه معطل بشه عیبی نداره ولی بایستی بعد از اون کاربر بایستی بتونه کاری رو که از SqlServer درخواست کرده انجام بده . دقیقا چیزی مثل thread ها در دلفی . ولی من چیز زیادی در مورد thread در SqlServer نمی دونم ، میشه کمی بیشتر توضیح بدید که من باید چکار کنم.
ممنون

[DonetKarvb]

ممنون از همگی ولی فکر کنم جوابم رو آقای SeniorDev دادند. من چندین رکورد دارم که می خوام update بشه و می خوام این کار درbackground انجام بشه حالا اگه موقع update یک رکورد به مدت چند صدم ثانیه یک کاربر دیگه معطل بشه عیبی نداره ولی بایستی بعد از اون کاربر بایستی بتونه کاری رو که از SqlServer درخواست کرده انجام بده . دقیقا چیزی مثل thread ها در دلفی . ولی من چیز زیادی در مورد thread در SqlServer نمی دونم ، میشه کمی بیشتر توضیح بدید که من باید چکار کنم.
ممنون
فکر کنم دوستمون darvishiali جواب بهتری دادند. به نظر من شما نگران نباشید که کاربران چه مدت Lock خواهند بود. اگر از شیوه ای که دوستمون darvishiali بیان کردند استفاده کنید که میشه (نه حتما")گفت lock در کار نخواهد بود. پس زیاد خودت رو درگیر این مسئله نکن. شما زمانی به سراغ این موضوع خواهید رفت که ببینید شرایط قفل گذاری موجود با شرایط شما همخوانی ندارد. و گرنه از بابت پرفرمنس مطمئن باشید مایکروسافت یک فکری به حالش کرده بود.
آیا تا به حال شما به مشکلی برخوردید؟

[SeniorDevs]

دوست عزیز، جناب Mohammadi_F خوشبختانه تنظیمات پیش فرض SQL SERVER 2000 بر روی 255 Thread بنا شده است که به نظر من برای کار شما عالی است و با استفاده از آنها تمامی دستورات SQL را در Background انجام می دهد تا در کار سرویس های دیگر ویندوز اختلال ایجاد نکند بنابراین شما نیاز ندارید که تنظیمات دیگری اعمال کنید.

[AminSobati]

اصلا توصیه نمیکنم از READ UNCOMMITTED بصورت معمول در گزارشات استفاده کنید. کاربردش بسیار خاصه و باید کاملا آگاهانه (و با قبول ریسک Data Inconsistency) استفاده بشه. فرض کنید یک Transaction داره موجودی بانکی یک فرد رو Update میکنه (مثلا افزایش) و در همین لحظه کاربر دیگری Select کنه. چون Transaction اولی هنوز بازه، این رکورد تحت Update دارای Exclusive Lock هستش ولی چون Select در حالت READ UNCOMMITTED انجام شده، میتونه مقدار جدید رو بخونه، در حالیکه Transaction کارش با اشکال مواجه میشه و Rollback میکنه. مقداری که در Select رویت میشه حقیقی نیست! این میتونه جزو ساده ترین اشکالاتی باشه که READ UNCOMMITTED پیش میاره.

[AminSobati]

دوست عزیز، جناب Mohammadi_F خوشبختانه تنظیمات پیش فرض SQL SERVER 2000 بر روی 255 Thread بنا شده است که به نظر من برای کار شما عالی است و با استفاده از آنها تمامی دستورات SQL را در Background انجام می دهد تا در کار سرویس های دیگر ویندوز اختلال ایجاد نکند بنابراین شما نیاز ندارید که تنظیمات دیگری اعمال کنید.
تعداد 255 Thread و Update و خود ویندوز ارتباطی به هم ندارند. تنها یک کاربر در SQL Server فقط با یک Thread (از بین 255 تا!) میتونه CPU Usage رو به 100% برسونه تا برنامه های دیگه با مشکل مواجه بشن. به نظر من مطرح کننده سوال اصلی قصدشون از Background Update اینه که کار Update توسط یک کاربر، حداقل مزاحمت برای سایر کاربرها رو داشته باشه. این مشکل با داستان Thread حل نمیشه. حتی اگر 64 تا CPU هم داشته باشید، یک کاربر با Update میتونه دیگران رو Block کنه. مگر اینکه بریم سراغ READ UNCOMMITTED که اون هم مسائل خاص خودش رو داره.
نتیجه گیری اخلاقی از این تاپیک:
سعی کنید با ایندکس مناسب و Query بهینه سرعت کار Update رو بالا ببرید تا این عمل در حداقل زمان انجام بشه. با این روش نه کاربرها بطور محسوس Block میشن نه ریسک READ UNCOMMITTED رو متحمل میشین.

[SeniorDevs]

تعداد 255 Thread و Update و خود ویندوز ارتباطی به هم ندارند. تنها یک کاربر در SQL Server فقط با یک Thread (از بین 255 تا!) میتونه CPU Usage رو به 100% برسونه تا برنامه های دیگه با مشکل مواجه بشن. به نظر من مطرح کننده سوال اصلی قصدشون از Background Update اینه که کار Update توسط یک کاربر، حداقل مزاحمت برای سایر کاربرها رو داشته باشه. این مشکل با داستان Thread حل نمیشه. حتی اگر 64 تا CPU هم داشته باشید، یک کاربر با Update میتونه دیگران رو Block کنه. مگر اینکه بریم سراغ READ UNCOMMITTED که اون هم مسائل خاص خودش رو داره.
نتیجه گیری اخلاقی از این تاپیک:
سعی کنید با ایندکس مناسب و Query بهینه سرعت کار Update رو بالا ببرید تا این عمل در حداقل زمان انجام بشه. با این روش نه کاربرها بطور محسوس Block میشن نه ریسک READ UNCOMMITTED رو متحمل میشین.

دوست عزیز، جناب AminSobati نحوه عملکرد Thread ها به CPU Time Slice که بر حسب میلی ثانیه است بستگی دارد یعنی می توان با یک حلقه ساده CPU Usage را در SQL SERVER به 100% رساند اما نمی توان CPU Kernel Time را به 100% رساند چرا چون CPU فقط به هر Thread یک زمانی برحسب میلی ثانیه اختصاص داده است نه 100% زمان CPU را.

بنابراین اگر Thread #1 در SQL SERVER فعالیت زیادی داشته باشد و Thread #2 فعالیت کمتری داشته باشد در این صورت Kernel32.DLL که وظیفه مدیریت Thread ها را بر عهده دارد زمان بیشتری Time Slice را در اختیار Thread #1 قرار می دهد نه کل زمان CPU را.

دوستان دقت داشته باشند که:


CPU Usage Time <> CPU Kernel Time


مخالف هم هستند یعنی CPU Usage که برابر 100% می شود هر گز CPU Kernel Time برابر با 100% نخواهد شد مگر در شرایطی که ما به صورت دستی Process Thread Priority اولویت Thread ها را عوض کنیم.

بنابراین شما هرگز نمی توانید با یک دستور UPDATE سنگین دیگران را Block کنید. فقط بر روی SQL SERVER 2000 خودتان تست کنید تا نتیجه ببینید.

[DonetKarvb]

بنابراین شما هرگز نمی توانید با یک دستور UPDATE سنگین دیگران را Block کنید. فقط بر روی SQL SERVER 2000 خودتان تست کنید تا نتیجه ببینید.
نه هرگز چنین نیست. این اصلا" ربطی به ویندوز ندارد. شما کمی در مورد همزمانی کاربران که توسط SQL Server انجام میشود مطالعه کنید. الان بحث روی این مبحث است نه نحوه مدیریت Thread ها توسط ویندوز.

[AminSobati]

عزیزم هر Process یا در حالت User Mode با CPU کار میکنه یا Kernel Mode. تمام برنامه هایی که مینویسیم در حالت User Mode کار میکنند. برنامه هایی که Kernel Mode کار میکنند بعضی از Device Driverها هستند، مثل Driver هارد دیسک. یعنی تمام Requestهایی که به هارد دیسک میره درKernel Mode کنترل میشه تا Busy بودن CPU باعث نشه هارد دیسک از فعالیت بمونه. بگذریم، فکر میکنم از بحث اصلی دور شدیم...
شما به Update سنگین نیاز ندارید تا کاربرها رو Block کنید. با ویرایش یک رکورد هم میشه همه کاربرها رو Block کرد! به شرط اینکه Transaction مدت طولانی باز بمونه. البته گاهی اوقات باز موندن Transaction به خاطر طولانی بودن Update هست. اما امتحانش برای یک رکورد خیلی راحته:
یک Connection باز کنید و بنویسید:


BEGIN TRAN
UPDATE MyTable SET SomeColumn='New Value'
WHERE ColID=1

حالا یک Connection دیگه باز کنید:


SELECT * FROM MyTable

میبینید که Block شدین!

[SeniorDevs]

دوست عزیز، اگر پست قبلی من را دوباره بخوانید متوجه می شوید که هیچ اثری از T-SQL نمی بینید منظور من از دستور Update استفاده از Transact-SQL نبود بلکه منظورم یک دستور Update ساده و بدون استفاده از T-SQL بود همانطور که در پست قبلی ام نوشتم.

در ضمن دستور Begin Tran باعث قفل شدن Table می شود و تا زمانی که به پایان نرسد به دلیل اعمال قفل بر روی کل Table دسترسی کاربران دیگر را مسدود می کند نه بدلیل CPU Usage بالا. SQL SERVER خودش به دلیل استفاده از دستور Begin Tran جدول مورد نظر را قفل کرده و اصلا این چه ربطی دارد به صحبت های قبلی من.

اگر پست قبلی من را به دقت بخوانید متوجه می شوید که من نگفتم شما با استفاده از یک دستور مانند Begin Tran جدول را قفل کنید.

منظور من از استفاده SQL SERVER 2000 از تکنولوژی Multi-Threading این بود که به کاربران این آگاهی را بدهم که SQL SERVER در ترافیک بالا Lock نمی شود فقط شاید از سرعت پاسخ دهی اش کاسته شود نه اینکه شما با یک دستور Table ی را قفل کنید و بگویید دیدی SQL SERVER پاسخ نداد.

واقعا با دستوری که شما در پست تان نوشتید SQL SERVER از پاسخ دادن باز مانده است یا خود خود خود SQL SERVER به دلیل اعمال قفل خودش روی جدول پاسخ کاربران دیگر را نمی دهد و این دستور شما هیچ ارتباطی به Multi-Threading ی که من راجع به آن صحبت می کردم نداشت.

با آرزوی موفقیت برای دوستان:لبخندساده:

[DonetKarvb]

دوست عزیز، اگر پست قبلی من را دوباره بخوانید متوجه می شوید که هیچ اثری از T-SQL نمی بینید منظور من از دستور Update استفاده از Transact-SQL نبود بلکه منظورم یک دستور Update ساده و بدون استفاده از T-SQL بود همانطور که در پست قبلی ام نوشتم.

پس چطور باید آپدیت انجام بشه؟؟ تو بخش SQl Server در مورد دستور آپدیت از T-SQl استفاده نکنیم. چه دستوری برای آپدیت کردن باید استفاده کنیم؟؟



در ضمن دستور Begin Tran باعث قفل شدن Table می شود و تا زمانی که به پایان نرسد به دلیل اعمال قفل بر روی کل Table دسترسی کاربران دیگر را مسدود می کند نه بدلیل CPU Usage بالا. SQL SERVER خودش به دلیل استفاده از دستور Begin Tran جدول مورد نظر را قفل کرده و اصلا این چه ربطی دارد به صحبت های قبلی من.

اگر پست قبلی من را به دقت بخوانید متوجه می شوید که من نگفتم شما با استفاده از یک دستور مانند Begin Tran جدول را قفل کنید.

منظور من از استفاده SQL SERVER 2000 از تکنولوژی Multi-Threading این بود که به کاربران این آگاهی را بدهم که SQL SERVER در ترافیک بالا Lock نمی شود فقط شاید از سرعت پاسخ دهی اش کاسته شود نه اینکه شما با یک دستور Table ی را قفل کنید و بگویید دیدی SQL SERVER پاسخ نداد.

واقعا با دستوری که شما در پست تان نوشتید SQL SERVER از پاسخ دادن باز مانده است یا خود خود خود SQL SERVER به دلیل اعمال قفل خودش روی جدول پاسخ کاربران دیگر را نمی دهد و این دستور شما هیچ ارتباطی به Multi-Threading ی که من راجع به آن صحبت می کردم نداشت.

با آرزوی موفقیت برای دوستان:لبخندساده:
وقتی که شما یک دستور را در SQl Server اجرا میکنید اتوماتیک یک ترنزکشن باز کرده اید.و بعد از اتمام دستور شما این ترنزکشن بسته خواهد شد. دستور زیر رو اجرا کن و نتیجه اش رو ببین.


Update Customers Set Country'='uk'
Dbcc log('Nothwind')

اون باز کردن ترنزکشن هم توسط استاد ثباتی به احتمال زیاد به همین دلیل بوده. خواسته برای شما این حالت رو به صورت Slow motion پیاده سازی کنه!

[AminSobati]

واقعا با دستوری که شما در پست تان نوشتید SQL SERVER از پاسخ دادن باز مانده است یا خود خود خود SQL SERVER به دلیل اعمال قفل خودش روی جدول پاسخ کاربران دیگر را نمی دهد و این دستور شما هیچ ارتباطی به Multi-Threading ی که من راجع به آن صحبت می کردم نداشت.


دوست من، آخه اصل سوال به بحث Threadها ارتباطی نداره که شما روی اون اینقدر پافشاری میکنین! بنده خدا خواست بدونه Update سنگین میتونه مانع کار دیگران بشه، که در جواب عرض کردم بله، میتونه مانع بشه. حالا چه یک CPU داشته باشید چه n تا. و دقیقا به خاطر قفل خود SQL Server هست.
نکته دیگه اینکه شما وقتی بدون Begin Tran یک دستور ویرایشی صادر کنید، یعنی در حالت Auto Commit، خود SQL Server یک Begin Tran اضافه خواهد کرد و وقتی کار ویرایش بدون اشکال تموم بشه، خودش Commit Tran انجام میده. من Begin Tran رو نوشتم تا طول مدت Transaction دست خودمون باشه. ولی اگر بدون Begin Tran یک Update سنگین انجام بدین، در تمام مدت، رکوردهای تحت ویرایش Lock خواهند بود.

[Mohammadi_F]

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

[AminSobati]

نتیجه اخلاقیی که نوشتم رو ببینید!

[Mohammadi_F]

salam
ghabl az har chiz mazerat mikham ke fenglisi neveshtam chon windows e computeram rikhte be ham o farsi neminevise.
aghaye AminSobati natije akhlaghi ke neveshte boodid ro khoondam vali ba daneshe man amali nist, shoma do milioon record ro ba che indexi mikhahid too modate chand saniye peymayesh konid ke user haye dige block nashan hala bemanad ke rooye har record bayad kolli mohasebe anjam beshe.
tooye avalin noskheye barnamam in kar 3 sa'at tool mikeshid(!) vali ba koli normalsazi o estefadeh az dastoorate SQL o mokh terekoondan too akharin virayeshe barname oon ro be hodoode 15 daghighe resoondam.
shoma jaye man boodid chekar mikardid?
by.

[AminSobati]

همیشه ترفندهای نرم افزاری "بخشی" از بهینه سازی رو پاسخگو هستند. نمیشه پردازش چند میلیون رکورد رو با این روش به زمان ناچیز رسوند. ادامه بهینه سازی برمیگرده به سخت افزار شما. ضرب المثل "هر که بامش بیش برفش بیشتر" در مورد Update هم صدق میکنه! شما به دیسک سریعتر یا RAID نیاز دارید اگر میبایست زمان رو به چند ثانیه برسونید. از طرفی من بعید میدونم این Update وسیع شما که روی همه رکوردها اعمال میشه، چیزی باشه که در طول روز بارها شما نیاز به انجامش داشته باشید.
از طرفی این رو فراموش کنید که در حین Update، کاربرها بتونن Select کنن و Lock نشن. چون از نظر یکپارچگی اطلاعات این کار کاملا غیر اصولی (و مضر) هستش. پس به سخت افزار خود رجوع کنین و توقع منطقی داشته باشید.

[Mohammadi_F]

در مورد آپدیت کردن باید بگم هر روز یک بار صورت می گیره.
و از پاسختون متشکرم.
پیروز و سربلند باشید.