آموزش اصول transactions در SQL SERVER

تراکنش(transaction) در SQL Server

تراكنش (transactions) به یک فعل يا مجموعه ای از افعال و عملیات می گويند كه در قالب یا به صورت یک گروه و به منظور اطمینان حاصل کردن از موفقیت یا عدم موفقیت عملیات انجام می شود. به همين خاطر قوانينی وجود دارد كه بايد آنها را رعايت كرد. همچنين راهكارهایی برای استفاده بهينه ی از تراکنش طراحی شده است.
به تعریفی دیگر، تراکنش یک یا چند دستور SQL نظیر بروزرسانی (update)، درج (insert) و یا حذف (delete) یک یا چندین سطر یا رکورد از داده ها است که با یکدیگر یک واحد منطقی از کار را شکل می دهند. این دستورات SQL که تشکیل دهنده ی تراکنش هستند، به نوعی کاملا مرتبط و متصل به هم هستند و اعمال وابسته به هم را انجام می دهند. هر دستور در تراکنش بخشی از یک کار را انجام می دهد، اما برای تکمیل کار، همه ی دستورات مزبور ضروری و لازم هستند.
قواعدی که می بایست رعایت کرد، همگی در قالب سرنام ACID خلاصه می شوند:

1. Atomicity (A) تجزیه ناپذیری: چنانچه transaction شامل مجموعه ای از عمليات باشد، در این صورت همه آن ها می بايست اجرا شوند (با موفقیت انجام شوند)، در غير اين صورت حتی اگر فقط یک عمليات اجرا نشود باقی آن ها نيز اجرا نخواهند شد (کل عملیات با شکست مواجه می شود). در حقیقت یکپارچگی به معنای این است که تراکنش باید به عنوان یک واحد منسجم (غیر قابل تفکیک) در نظر گرفته شود.
2. Consistency (C) (سازگاری): عمليات بايد پیوسته باشند سازگاری زمانی وجود دارد که هر تراکنش، سیستم را در یک حالت سازگار قرار دهد (چه تراکنش به طور کامل انجام شود و چه به علت وجود خطایی خنثی و لغو گردد).
3. (I) Isolation(تفکیک یا ISOLATION):زمانی كه عمليات اجرا می شوند، این عملیات بایستی کاملا از يكديگر جدا باشند. علت ضرورت جدایی این عملیات، جلوگیری از درهم ترکیب شدن آنها در يک سرویس دهنده يا يک پایگاه داده می باشد.
4. :Durability (D) هنگام اجرای عمليات، رایانه تغييرات آن ها را دنبال می كند. اگر اشكال یا خطای باعث توقف عمليات شده و رایانه restart شود، پس از بالا آمدن خود به خود عمليات را ادامه می دهد. بعد از اجرای عمليات و اطمينان از بی نقص بودن، می توان آن را ذخیره كرده و برای دفعات بعد در آینده بكار برد (به عبارتی دیگر پایداری به معنای این است که تغییرات حاصل از نهایی شدن تراکنش، حتی در صورت خرابی سیستم نیز ماندگار می‌ماند).

ایجاد یک Transaction

قبل از ايجاد و راه اندازی یک تراکنش، شما باید عملياتی که قرار است اجرا شود را تعريف كنيد. برای مشخص كردن شروع يک transactions ، قبل از اولين عمليات، كلمه ی BEGIN TRAN يا BEGIN TRANSACTION را به همراه فرمول زير تايپ كنيد:

BEGIN { TRAN | TRANSACTION } 
    [ { transaction_name | @tran_name_variable } 
      [ WITH MARK [ 'description' ] ] 
    ] 
[ ; ] 

دستور را با عبارت BEGIN TRAN يا BEGIN TRANSACTION آغاز كرده، سپس در صورت تمایل transaction_name را درج نمایید. اگر قبلا متغیر متنی اعلان کرده اید (char، nchar، varchar یا (nvarchar و یک اسم تراکنش به آن تخصیص داده اید، در آن صورت می توانید transaction_name را حذف كرده و از اسم متغير استفاده كنید. چنانچه مايلید شرحی از transaction در log file (فایل ثبت گزارش) نوشته شود، عبارت WITH MARK را تايپ كرده و شرحی که باید در فايل نوشته شود را پس از عبارت ذکر شده، تایپ کنید.
كد بين BEGIN TRAN يا BEGIN TRANSACTION قسمتی از transaction است.

,br>

تایید ثبت یک تراکنش (transaction commission)

بعد از تعريف عمليات که بخشی از تراکنش را تشکیل می دهد، موتور database آن ها را به ترتیبی که نوشته شده اجرا می كند. شما بايد انتها یا پايان transaction ها را مشخص نمایید. برای این منظور (تعریف شروع تراکنش) عبارت BEGIN TRAN يا BEGIN TRANSACTION را تايپ كنيد

مثال:

BEGIN TRAN Name or BEGIN TRANSACTION Name 
    Operations 
COMMIT TRAN Name or COMMIT TRANSACTION Name 
USE Exercise; 
Go
CREATETABLEAdministration.Employees
‎(
‎EmployeeNumbernvarchar(10),
‎EmployeeNamenvarchar(50),
‎DateHireddate,
‎HourlySalarymoney
‎);
GO
INSERTINTOAdministration.Employees
VALUES(N'593705',N'Frank Somah',N'20061004', 26.15),
‎      (N'720947',N'Paul Handsome',N'20000802', 36.05);
GO
INSERTINTOAdministration.Employees(EmployeeName,EmployeeNumber,DateHired)
VALUES(N'Clarice Simms',N'971403',N'20011112');
GO
BEGINTRANSACTIONAddEmployees
INSERTINTOAdministration.Employees
VALUES(N'595002',N'John Meah',N'20000212', 32.25);
GO
INSERTINTOAdministration.Employees
VALUES(N'928375',N'Chuck Stansil',N'20080628');
GO
INSERTINTOAdministration.Employees
VALUES(N'792764',N'Orlando Perez',N'20000616', 12.95);
GO
COMMITTRANSACTIONAddEmployees;
GO
INSERTINTOAdministration.Employees(EmployeeName,EmployeeNumber,
‎HourlySalary,DateHired)
VALUES(N'Gina Palau',N'247903', 18.85,N'20080612');
GO 

كد حاضر از موتور پایگاه داده (database engine) درخواست ايجاد يک جدول به نام employee را در Administration schema پایگاه داده ی Exercise می كند. پس از ایجاد جدول، table بايد اول يک سطر و سپس دو سطر یا رکورد درخود ایجاد کند. سپس يک تراکنش که در آن 3 رکورد ایجاد می شوند را پردازش کند. بعد از آن تراکنش، عملیات درج داده با افزودن رکورد ادامه پيدا كرده برای ارائه ی امکان درک بهتر يک error را در كد تراکنش قرار می دهيم. نتیجه ی زیر را بدست می دهد:

جدول نهایی به شکل زیر خواهد بود:

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

باز گرداندن یک تراکنش به حالت اول (Rollback)

كد زير را در نظر بگيرید:

USE Exercise; 
GO
DROPTABLEAdministration.Employees
GO
CREATETABLEAdministration.Employees
‎(
‎EmployeeNumbernvarchar(10),
‎EmployeeNamenvarchar(50),
‎DateHireddate,
‎HourlySalarymoney
‎);
GO
BEGINTRANSACTIONAddEmployees
INSERTINTOAdministration.Employees
VALUES(N'593705',N'Frank Somah',N'20061004', 26.15),
‎      (N'720947',N'Paul Handsome',N'20000802', 36.05),
‎      (N'595002',N'John Meah',N'20000212', 32.25);
GO
INSERTINTOAdministration.Employees(EmployeeName,EmployeeNumber,DateHired)‎VALUES(N'Clarice Simms',N'971403',N'20011112');
GO
INSERTINTOAdministration.Employees
VALUES(N'928375',N'Chuck Stansil',N'20080628');
GO
INSERTINTOAdministration.Employees
VALUES(N'792764',N'Orlando Perez',N'20000616', 12.95);
GO
INSERTINTOAdministration.Employees(EmployeeName,EmployeeNumber,
‎                                     HourlySalary,DateHired)
VALUES(N'Gina Palau',N'247903', 18.85,N'20080612');
GO
COMMITTRANSACTIONAddEmployees;
GO

پس از اجرا، نتیجه ی زیر در query editor نمایش داده خواهد شد:

توجه كنيد كه در كد تراکنش یک خطا وجود دارد. رکوردهای ایجاد شده به صورت زیر خواهند بود:

همان طور که مشاهده می كنيد، با وجود خطا، تراکنش اجرا شده و بخشی كه خطا دارد ناديده گرفته شده است.
در برخی موارد، برای به كار بردن قواعد اشاره شده در بالا یا اصل تجزیه ناپذیری (atomicity)، ممكن است كه در صورت وجود خطا در حتی يک بخش، از اجرای کل تراکنش صرف نظر كنيد. به عبارت ديگر، يک تراکنش می بایست كامل بوده و به درستی اجرا شود و يا کلا اجرا نشود. به منظور درخواست از database engine برای تاييد درستی تراکنش یا رد کردن و لغو کل آن، تراکنش مورد نظر را به عقب یا به حالت اول برمی گردانیم (در اصطلاح rollback کنیم). به اين منظور پشتیبانی از این قابلیت، بجای تایید ثبت تراکنش (عبارت COMMIT TRANSACTION)، عبارت ROLLBACK TRANSACTION را به صورت زير استفاده می كنيم:

ROLLBACK { TRAN | TRANSACTION } 
[ transaction_name | @tran_name_variable 
     | savepoint_name | @savepoint_variable ] 
[ ; ] 

عبارت مورد نظر را با ROLLBACK TRAN يا ROLLBACK TRANSACTIONشروع كنيد. چانچه برای تراکنشی اسمی تخصیص یافته، در آن صورت اسم تراکنش يا متغير حاوی اسم تراکنش را تايپ كنيد. اگر قصد دارید این عملیات را ذخیره کنید، از عبارت savepoint_name يا @savepoint_variable استفاده نمایید.
در نمونه ی زیر ملاحظه می کنید که یک تراکنش در صورت موفق نبودن (کاملا با موفقیت اجر نشدن) به حالت اول برمی گردد.

USE Exercise; 
GO
DROPTABLEAdministration.Employees
GO
CREATETABLEAdministration.Employees
‎(
‎EmployeeNumbernchar(10),
‎EmployeeNamenvarchar(50),
‎DateHireddate,
‎HourlySalarymoney
‎);
GO
BEGINTRANSACTIONAddEmployees
INSERTINTOAdministration.Employees
VALUES(N'593705',N'Frank Somah',N'20061004', 26.15),
‎      (N'720947',N'Paul Handsome',N'20000802', 36.05),
‎      (N'595002',N'John Meah',N'20000212', 32.25);
GO
INSERTINTOAdministration.Employees(EmployeeName,EmployeeNumber,DateHired)
VALUES(N'Clarice Simms',N'971403',N'20011112');
GO
INSERTINTOAdministration.Employees
VALUES(N'928375',N'Chuck Stansil',N'20080628');
GO
INSERTINTOAdministration.Employees
VALUES(N'792764',N'Orlando Perez',N'20000616', 12.95);
GO
INSERTINTOAdministration.Employees(EmployeeName,EmployeeNumber,
‎            HourlySalary,DateHired)
VALUES(N'Gina Palau',N'247903', 18.85,N'20080612');
GO
ROLLBACKTRANSACTIONAddEmployees;
GO

اين كد ابتدا يک جدول به اسم Employee ایجاد می کند. سپس از database engine درخواست افزودن چند رکورد به آن جدول را می كند. تشكيل سطرها در تراکنشی که گزینه یا امکان بازگردانی و ROLLBACK در آن لحاظ شده، صورت می گیرد (گنجانده شده است).
ملاحظه می کنید كه يک خطا در تراکنش وجود دارد.

زمانی كه كد بالا اجرا می شود، می بینید که جدول مورد نظر بدون هیچ گونه خطایی ساخته می شود زیرا عملیات آن بیرون از تراکنش قرار دارد. با این حال خواهی دید که جدول نهايی تهی خواهد بود.

به خاطر وجود یک خطا كل تراکنش لغو یا رد خواهد شد.

مدیریت سطح جداسازی و منابع مورد نیاز تراکنش (Isolation Level)

در یک پایگاه داده، اگر که یک رکورد پس از آخرين بار باز کردن و استفاده از جدول، مورد ویرایش یا تغییراتی قرار گیرد در آن صورت به اصطلاح پایگاه داده dirtyخوانده می شود.
در زمان ایجاد يک تراکنش شما می توانيد طوری database engine را پیکربندی كنيد كه تراکنش را اجرا يا رد/لغو كند. برای استفاده از اين امکان می توانید فرمول زير را مورد استفاده قرار دهید:

 
SET TRANSACTION ISOLATION LEVEL 
    { READ UNCOMMITTED 
    | READ COMMITTED 
    | REPEATABLE READ 
    | SNAPSHOT 
    | SERIALIZABLE 
    } 
[ ; ] 

ابتدا عبارت SET TRANSACTION ISOLATION LEVEL را برای تنظیم سطح جداسازی درج نموده، سپس مقدار مورد نظر را وارد کنید.
:READ UNCOMMITTED.1اين مقدار به پایگاه داده دستور می دهد آن رکوردهایی را که dirty هستند ولی هنوز تایید ثبت یا commit نشده اند را بخواند.
:READ COMMITTED.2این مقدار مشخص می کند که رکوردهای dirty نبایست خواند شوند.
REPEATABLE READ.3: مشخص می کند که تراکنش جاری باید رکوردهای dirty که متعلق به تراکنش های دیگر هستند را نادیده بگیرد، همچنین در این حالت دیگر تراکنش ها دسترسی به رکوردهای این تراکنش را ندارند.
Snapshot .4
Serializable .5