2017/02/18

شرح ال SQL Server Transaction بالعربي

مقدمة

في هذا المقال سيتم شرح ما هي ال Transaction وانواع ال Isolation Levels الخاصة بها وكيف تؤثر على اداء وسلامة البيانات في قواعد البيانات مع توضيح ما هو ال Lock الذي يحدث ومشكلة ال Deadlock ومستويات العزل الخاصة بال Transactions.


 ما هو ال Transaction

ال Transaction هو مجموعة من العمل المطلوب تنفيذه داخل قاعدة البيانات .
وبالطبع يمكنك عمل Transaction واحد وان تكتب بداخله اكثر من Query انظر المثال التالي
BEGIN TRANSACTION;
DECLARE @ProductID INT = 1;
DECLARE @Quantity INT = 5;

INSERT  OrderDetail
        ( ProductID, Quantity )
VALUES  ( @ProductID, @Quantity ); 

UPDATE  Product
SET     Balance = Balance - @Quantity
WHERE   ProductID = @ProductID;

IF @@ERROR = 0 -- لفحص وجود خطأ ام لا
    COMMIT;

ELSE
    ROLLBACK;
تعني ان كل ما تم تنفيذه داخل هذا ال Transaction صحيح وليس به مشكلة وبهذا الامر ان تخبر قاعدة البيانات ان تنفذ هذه التعديلات على ال Data Files بحيث اي استعلام في اي Transaction اخر يستطيع رؤية هذه التعديلات.

Rollback

تعني انك تريد التراجع فيما قمت بتنفيذه وكأنه لم يكن.

والان قد يطرح في ذهنك سؤال لماذا اقوم بعمل Transaction فبدلا من ذلك اقوم بكتابة الاستعلامات مباشرة؟
كما ترى من المثال السابق انك تريد تسجيل عملية بيع في جدول الفواتير وفي نفس الوقت تقلل رصيد المنتج في جدول المنتجات فمثلا تخيل ان جدول المنتجات به Check Constraint انه لا يقبل رصيد بالسالب داخل عمود ال Balance وان في نفس الوقت عملية البيع هذه ستؤدي الى بيع كمية اكبر من الرصيد مما سيؤدي الى وجود رصيد سالب,
في هذه الحالة اذا لم يتم استخدام Transaction سيتم ادخال الفاتورة و التعديل في الرصيد سيعطي خطأ وسيظل كما هو مما يؤدي الى وجود بيانات خاطئة داخل قاعدة البيانات قد تكلف عميلك الكثير من الاموال.

بينما عند استخدام ال Transaction ستستطيع التحكم في المشكلة السابقة فاذا تمت كل الجمل بدون مشاكل تقوم بتنفيذ الامر Commit وتنتقل البيانات الى ال Data Files اما اذا وجدت خطأ ما تقوم بعمل Rollback فيتم الغاء كافة التعديلات كأنها لم تكن.

خصائص ال Transaction

لل Transaction اربعة سمات اساسية تختصر في الكلمة التالية ACID

Atomic 

تعني ان كل ما داخل ال Transaction يتم عمل Commit له او Rollback بالكامل

Consistent  

تعني انه يحافظ على اتساق المعلومات داخل قاعدة البيانات فمثلا في المثال السابق ال Transaction لم يتخطى ال Constraint الذي يمنع الرصيد السالب

Durable 

تعني انه بمجرد ان يتم اكتمال ال Transaction كل التعديلات التي حدثت داخله تكون دائمة ومكتوبة داخل قاعدة البيانات.

Isolation

العزل و تعني ان كل ما يحدث داخل ال Transaction يكون في درجة من العزل عن ال Transactions الاخرى
وخاصية العزل في الحقيقة من اكثر الخواص التي تحتاج الى توضيح لكيفية عملها لان معظم اختياراتك في درجة العزل (Isolation level) تؤثر في سلامة البيانات وايضا في اداء قاعدة البيانات.

ولكن ما هي المشاكل التي يحاول العزل تجنبها؟
في الحقية هم 3 مشاكل رئيسية

  • Dirty Reads
  • Non-Repeatable Reads
  • Phantom Reads

Dirty Reads

تعني انه قد يقرأ ال Transaction بيانات تم اضافتها او تعديلها من قبل Transaction اخر ولكن لم يتم عمل Commit له بعد
انظر الشكل التالي

فال Transaction 1 قام بادخل سجل في الجدول وبينما ال Transaction 1 مازال قائم قام ال Transaction 2 بقراءة البيان من نفس الجدول فقرأه بدون اي مشاكل في حين ان ال Transaction 1 قام بعمل تراجع بعد ذلك,
وهذا خطأ كبير يؤدي الى قراءة بيانات غير سليمة Dirty.

Non-Repeatable Reads

تعني ان يقوم ال Transaction بقراءة بيان ما في البداية مثلا ويقوم ببعض الاجراءات الاخرى وفي ذلك الوقت يقوم Transaction اخر بتعديل هذا البيان وعمل Commit  ثم بعد ذلك يقرأ ال Transaction الاول هذا البيان مرة اخرى فيجده بقيم جديدة
انظر الشكل التالي

Phantom Reads

وتعني انه اذا قام ال Transaction 1 بقراءة كل بيانات جدول ما ثم قام باجراءات اخرى وفي نفس الوقت قام Transaction 2 باضافة بيانات الى هذا الجدول وعمل Commit ثم قام ال Transaction 1 بقراءة البيانات مرة اخرى يجد بيانات الجدول مضاف اليها السجلات الجديدة.
انظر الشكل التالي


جميع ما سبق قد يؤدي الى حدوث مشاكل بقراءة بيانات خاطئة لم يتم اضافتها بالفعل وخاصية العزل  Isolation المفترض ان تقوم بحل هذه المشاكل ولكن كيف؟

لحل المشاكل السابقة يقوم ال SQL Server بعمل درجة من العزل لكل Transaction تسمى Isolation Level وتقسم الى فئتين
فئة متشائمة Pessimistic وفئة متفائلة Optimistic.

الفئة المتشائمة Pessimistic

هنا يقوم ال SQL Server بتحقيق العزل عن طريق عمل Lock (قفل) على البيانات ولل Lock مستويات عديده فهناك Lock على ال Table او على ال Page او على Range من ال Rows  ومستويات اخرى.
وايضا لل Lock انواع عديدة فهناك Lock يحدث عند قراءة البيانات وLock يحدث عند كتباة البيانات و Lock يحدث عند التعديل وانواع اخرى واهم نوعين هما

Exclusive Lock

وهو الذي يحدث عند الكتابة على البيانات ليضمن عدم كتابة اكثر من Transaction على نفس البيانات في نفس اللحظة فبوجود ال Lock سينتظر احد ال Transactions حتى ينتهي الاخر وهكذا

Shared Lock

وهو ال Lock الذي يحدث عند قراءة البيانات.

في حالة حدوث Exclusive Lock لا يسمح ال SQL Server بعمل اي نوع Lock اخر على البيانات سواء Shared Lock او غيره.
وفي نفس الوقت لا يمكن عمل Exclusive Lock الا على بيان لا يوجد عليه اي نوع Lock.

والان هيا لنعرف انواع ال Isolation Levels المتشائمة وعلاقتها بال Lock

1 - Read UnCommitted

في هذا النوع لا يقوم ال SQL Server بعمل اي نوع من ال  Lock عند قراءة البيانات ويقوم فقط بعمل Lock عند الكتابة على البيانات فتخيل معي السيناريو التالي
قمت بفتح Transaction ثم قمت باضافة و تعديل بعض البيانات ولم يتم عمل Commit بعد (في هذه الحالة سيوجد Exclusive Lock على البيانات)
قام Transaction اخر بقراءة البيانات من الجدول الذي قمت بالتعديل فيه ولكنه ايضا لا يقوم بعمل Shared Lock , اذا سيقرأ البيانات التي لم يتم عمل Commit لها
وهذه هي بالضبط مشكلة ال Dirty Reads
اذا في هذا المستوى من العزل تتحقق الثلاث مشاكل الرئيسية التي من المفترض ان يحلها ال Transactions وهي
Dirty Reads
Non-Repeatable Reads
Phantom Reads

2 - Read Committed

في هذا النوع يقوم ال SQL Server بعمل Lock على الكتابة و Shared Lock عند القراءة (جمل ال Select) ولكن يقوم بالغاء ال Lock بمجرد انتهاء جملة ال Select وليس عند نهاية ال Transaction
وبالتالي تم حل مشكلة ال Dirty Reads و لكن مازالت مشكلة ال Non-Repeatable Reads و Phantom Reads تحدث
وهذا النوع هو النوع الافتراضي لقاعدة البيانات.

3 - Repeatable Reads

في هذا النوع يقوم ال SQL Server بعمل Lock على الكتابة و Shared Lock عند القراءة حتى نهاية ال Transaction.
وبالتالي تم حل مشكلة ال Dirty Reads و Non-Repeatable Reads  و لكن مازالت مشكلة ال Phantom Reads تحدث.

4 - Serializable

في هذا النوع يقوم ال SQL Server بعمل Lock على الكتابة و Shared Lock عند القراءة حتى نهاية ال Transaction وهذا الLock يكون من مستوى Key Range اي انه يمنع ايضا اضافة اي Rows على مستوى ال Table.
في هذا النوع لا يحدث اي مشكلة من الثلاث مشاكل الخاصة بال Transaction

قد يتوارد الى ذهنك بعد قراءة ما سبق انك تختار النوع Serializable دائما وبذلك تكون بياناتك في امان تام ولكن للاسف هذا خطأ فالاختيار ليس بهذه السهولة حيث كلما زاد ال Lock تم حل المشاكل الثلاث ولكن سيؤدي الى بطيء في اداء قاعدة البيانات حيث كل استعلام ينتظر حتى يتم فتح ال Lock المعمول من قبل Transaction أخر وكلما كثر عدد المستخدمين كلما زادت المشاكل وايضا يوجد مشكلة اخرى تسمى Dead Lock تحدث بشدة في ال Serializable و اقل قليلا في Repeatable Reads واقل في Read Committed واليك توضيح هذه المشكلة.

Deadlock

تخيل ان لديك Table 1 و Table 2
ال Transaction 1 يقرا من Table 1 ويكتب في Table 2
ال Transaction 2 يقرأ من Table 2 ويكتب في Table 1

بدأ ال Transaction 1 اولا وقام بالقراءة من Table 1 وعمل Lock عليه
في هذه اللحظة بدأ Transaction 2 وقرأ من Table 2 وعمل Lock عليه

الان Transaction 1 ينتظر الى ان يتم فتح ال Lock على Table 2 الذي عمله Transaction 2
و Transaction 2 ينتظر الى ان يتم فتح ال Lock على Table 1 الذي عمله Transaction 1

وبالتالي سوف ينتظرون للابد وهذه هي مشكلة ال Deadlock
ولكن يقوم ال SQL Server باختيار احدهم وانهائه ويسمى Deadlock Victim وهذا حتى لا تتعطل قاعدة البيانات الى الابد ولكن من يتم اختياره؟
ممكن عند فتح Transaction اعطائه اولوية فاذا وجد ال SQL Server اولوية يختار الاكبر اما اذا لم يجد يختار ال Transaction الاقل كلفة.

الفئة المتفائلة Optimistic

هنا يتم تحقيق العزل عن طريق عمل نسخ للسجلات Row Version بدلا من عمل Lock
حيث عن التعديل او الكتابة في اي سجل يتم عمل نسخة منه في ال TempDB وبالتلي لا يتم عمل Lock له حتى نهاية ال Transaction وهناك احد مستويان

1 - Read Committed Snapshot

وهو بالضبط مثل ال Read Committed العادي في الكتابة حيث يقوم بعمل Lock
وعند القراءة بدلا من عمل Shared Lock  يقرأ من اخر Version للسجل من ال TempDB في وقت جملة الاستعلام وبذلك يعالج مشكلة الDirty Reads
ويحدث به مشكلة Non-Repeatable Reads  و Phantom Reads الا انه يقل فيه ال Deadlock لانه لا يعمل Lock عند القراءة.

2 - Snapshot Isolation Level 

في هذا المستوى لا يقوم بعمل Lock في الكتابة او القراءة ولكنه يعتمد كليا على ال Version وعند قراءة اي بيانات داخل ال Transaction يقوم بقراءة قيمتها في لحظة بداية ال Transaction من ال Versions وبذلك يقوم بحل جميع المشاكل الخاصة بال Transaction كما ان يقلل بشكل كبير مشكلة ال Deadlock لانه يعتمد بشكل كلي على ال Row Version وليس ال Lock.

ولكن مشكلة هذان المستويان هي ميزتهما في نفس الوقت حيث انهما يعتمدا على ال Row Version وبالتالي كل شيء يتم كتابته في ال TEMPDB وبالتالي ستحدث عمليات IO كتابة وقراءة كثيرة جدا.

كيفية تحديد ال Isolation Level
يتم تحديد ال Level قبل بداية ال Transaction انظر المثال التالي
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;  
GO  
BEGIN TRANSACTION;  
GO  
SELECT  *
FROM    HumanResources.EmployeePayHistory;  
GO  
SELECT  *
FROM    HumanResources.Department;  
GO  
COMMIT TRANSACTION;  
GO  
ولكن يجب ملاحظة التالي
- الاختيارات المتاحة لل Isolation Level هي

  • READ UNCOMMITTED  
  • READ COMMITTED  
  • REPEATABLE READ  
  • SNAPSHOT  
  • SERIALIZABLE  

- قاعدة البيانات الافتراضي لها اذا لم تقم بتحديد ال Isolation Level هو Read Committed.
- لتفعيل ال Read Committed Snapshot لابد من فتح الخاصية داخل قاعدة البيانات بالامر التالي
ALTER DATABASE [DBNAME] SET READ_COMMITTED_SNAPSHOT ON;
وبذلك اي Isolation level من نوع Read Committed سيتحول الى Read Committed Snapshot .

- لتفعيل امكانية اختيار المستوى SNAPSHOT لابد من فتحه في قاعدة البيانات بالامر التالي
ALTER DATABASE [DBNAME] SET ALLOW_SNAPSHOT_ISOLATION ON;

مراجع 




==============
تم بحمد الله







هناك 3 تعليقات:

Mostafa يقول...

معوماتك مفيدة جدا شكرااااااااااا

Unknown يقول...
أزال المؤلف هذا التعليق.
محمد الصيفي يقول...

تسلم يا بشمهندس على المعلومات القيمة.. قرأت كتير فيها واول مره توضح معايا ال Isolation level

مقدمة عن الDevOps

في هذا المقال سوف اتحدث عن مقدمة بسيطة عن ال DevOps ولماذا لابد ان تهتم فرق تطوير البرمجيات به وما هي النتائج المترتبة عن استخدام اساليبه ...