طراحی بهینه و همزمانی تراکنش ها در SQL Server 2008 R2

طراحی تراکنش

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

بخش  مهمی از پشتیبانی از پایداری تراکنش ها در  SQL Server 2008،  توسط  log مربوط به تراکنش های سیستمی تحقق می یابد.

مدیریت بهینه تراکنش ها ، مباحثی چون قفل کردن(Locking) ، ناپایداری پایگاه داده و … را بشدت کاهش داده و تمامیت داده ها را فراهم خواهد کرد.یک برنامه کاربردی میتواند از تراکنش های مختلفی در زمینه های متفاوت استفاده نماید. بنابر این باید ویژگی های خاص هر تراکنش در نظر گرفته شود.

برنامه ها به طور کلی از طریق مشخص کردن ابتدا و انتهای تراکنش ها ، آنها را کنترل می نمایند. این کار به راحتی از طریق استفاده از یک عبارت Transact-SQL و یا استفاده از توابع رابط برنامه نویسی (API) امکان پذیر خواهد بود. سیستم نیز باید بتواند به صورت کامل،  خطاهایی که باعث توقف یک تراکنش قبل از به پایان رسیدن میشوند ، مدیریت نماید .به صورت پیش فرض تراکنش ها در سطح ارتباطی مدیریت می شوند. هنگام اتصال و آغاز  یک تراکنش ، همه ی عبارات بعد از آغاز تراکنش تا عبارت پایانی آن جزیی از تراکنش فعلی بوده و اجرا خواهند شد.

BEGIN TRANSACTION

Statement

                                    ROLLBACK

[irp posts=”۸۸۵۷″ name=”همه چیز در مورد بهینه سازی عملکرد در SQL Server 2008 R2″]

آغاز و تعریف انواع تراکنش ها:

با استفاده از توابع API و عبارات Transact-SQL ، میتوان تراکنش های یک موتور پایگاه داده SQL را به عنوان تراکنش های صریح ، ضمنی و مرتب شده خودکار (explicit, implicit, autocommit) ، تعریف و آغاز نمود.

• ·         تراکنش های صریح:

تراکنش ها به صورت صریح توسط توابع API  و یا توسط عبارت BEGIN TRANSACTION تعریف میشوند.

BEGIN { TRAN | TRANSACTION }

    [ { transaction_name | @tran_name_variable }

      [ WITH MARK [ ‘description’ ] ]

    ]

[ ; ]

• ·         تراکنش های مرتب شده خودکار:

حالت پیش فرض موتور پایگاه داده بوده و در این حالت نیازی به تعریف و یا مشخص نمودن هیچ عبارتی جهت کنترل کردن تراکنش نیست و هر تراکنش بعد از کامل شدن اعمال میشود.

• ·         تراکنش های ضمنی :

تراکنش ها به صورت ضمنی توسط عبارت SET IMPLICIT_TRANSACTIONS ON و یا توسط توابع API  تعریف می شوند.

SET IMPLICIT_TRANSACTIONS { ON | OFF }

لازم به ذکر است که انواع ذکر شده تراکنش های فوق ، مطابق آنچه قبلاذکر شد در سطح ارتباطی تعریف می شوند.

خاتمه تراکنش ها :

میتوان تراکنش ها را با استفاده از توابع API و یا عبارات Transact-SQL زیر خاتمه داد.

• ·         COMMIT

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

COMMIT { TRAN | TRANSACTION } [ transaction_name | @tran_name_variable ] ]

[ ; ]

• ·         ROLLBACK

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

ROLLBACK { TRAN | TRANSACTION }

     [ transaction_name | @tran_name_variable

     | savepoint_name | @savepoint_variable ]

[ ; ]

رخ دادن خطا هنگام اجرای تراکنش:

اگر به هر دلیلی در هنگام اجرای تراکنش های یک سیستم پایگاه داده خطایی رخ دهد SQL Server به صورت خودکار این تراکنش را برگشت داده و تمامی منابع در اختیار تراکنش فعلی را آزاد خواهد نمود. (مشابه کاری که توسط عبارت  SQLEndTran,ROLLBACK,COMMIT در Transact-SQL صورت میگیرد)

 دستور عمل های طراحی و همزمانی تراکنش ها:

از مباحث فوق العاده مهم در تراکنش ها ، طراحی و همزمانی تراکنش ها در یک سیستم پایگاه داده است. حائز اهمیت است که زمان اجرای تراکنش ها را هر چقدر که میتوان کوتاه و بهینه سازی کرد. هنگامی که یک تراکنش آغاز میگردد ، سیستم مدیریت پایگاه داده (DBSM) وظیفه دارد منابع زیادی را جهت حفظ ویژگی تجزیه ناپذیری(Atomicity) ،پایداری (Durability) و سازگاری تراکنش ، تا پایان اجرا محفوظ نگه دارد . اگر داده ای بخواهد تغییر داده شود ، سطر مورد نظر باید توسط DBMS قفل شود تا از دسترسی دیگر تراکنش ها جهت جلوگیری از تداخل محفوظ بماند.بر طبق تنظمیات سطح انزوای یک تراکنش (transaction isolation level)،عبارات SELECT در Transact-SQL شامل موارد ذکر شده خواهند بود یعنی نیاز دارند که DBMS پیاده سازی های لازم را انجام دهد. مخصوصا آنکه این عبارات در سیستم های پایگاه داده با کاربر های زیاد اجرا شوند و در اینجاست که مبحث ذکر شده برای کوتاهی و بهینه سازی تراکنش ها، جهت همزمانی ارتباطات در استفاده از منابع سیستمی اهمیت میبابد.

جهت طراحی بهینه و کوتاه کردن زمان اجرای تراکنش ها میتوان موارد زیر را همواره مد نظر داشت :

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

جهت جلوگیری از همزمانی و تداخل مشکلات ناشی در استفاده از منابع ، باید تراکنش های ضمنی را با دقت بیش از بیش استفاده نمایید زیرا هنگامی که از یک تراکنش ضمنی استفاده می نمایید ، عبارات پس از COMMIT و ROLLBACK به صورت خودکار یک تراکنش جدید را آغاز خواهند کرد و این امر باعث آن خواهد شد که هنگامی که برنامه شما در حال آنالیز داده ها میباشد و یا هنگامی که درخواست ورودی از کاربر دارد ، تراکنش دیگری اجرا شده و با مشکلات فراوانی چون محدودیت های منابع و قفل شدن های دیگری مواجه شود.

جهت رفع این مشکل میتوان به صورت زیر عمل کرد:

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

برای آشنایی با خطاهای مربوط به این برنامه می توانید مقاله رفع مشکل اتصال به سرور در sql server را مطالعه کنید.

منابع:

Technical Reference Guide for Designing Mission-Critical OLTP Solutions ,Microsoft

 SQL Server 2008 R2 Books Online, chapter 4