المعاملة في SQL Server هي مجموعة متسلسلة من البيانات أو الاستعلامات لأداء مهام واحدة أو متعددة في قاعدة البيانات. قد تحتوي كل معاملة على عمليات قراءة أو كتابة أو تحديث أو حذف واحدة أو مجموعة من كل هذه العمليات. يجب أن تحدث كل معاملة في شيئين في SQL Server:
- إما أن يكون كل التعديل ناجحًا عند تنفيذ المعاملة.
- أو، سيتم التراجع عن كافة التعديلات عند التراجع عن المعاملة.
لا يمكن أن تكون المعاملة ناجحة حتى يتم إكمال جميع العمليات في المجموعة. وهذا يعني أنه في حالة فشل أي وسيطة، ستفشل عملية المعاملة. تبدأ كل معاملة بأول عبارة SQL قابلة للتنفيذ وتنتهي عندما تجد التزامًا أو تراجعًا، سواء بشكل صريح أو ضمني. يستخدم يقترف أو التراجع العبارات بشكل صريح، وكذلك ضمنيًا عند استخدام عبارة DDL.
الصورة التالية توضح عملية المعاملة:
المثال التالي سوف يشرح مفهوم الصفقة:
سيستخدم هذا المثال نظام قاعدة البيانات المصرفية لشرح مفهوم المعاملة. لنفترض أن أحد عملاء البنك يريد سحب الأموال من حسابه باستخدام وضع ماكينة الصراف الآلي. ويمكن لجهاز الصراف الآلي إنجاز هذه العملية في ثلاث خطوات:
أفضل 10 هنتاي
- ال الخطوة الأولى هو التأكد من توفر المبلغ المطلوب في الحساب.
- ال الخطوة الثانية يقوم بخصم المبلغ من الحساب في حالة توفر المبلغ ثم يقوم بتحديث رصيد الحساب.
- ال خطوة ثالثة هو كتابة عملية سحب الأموال في ملف السجل. تكتب هذه الخطوة حول ما إذا كانت المعاملة ناجحة أم فاشلة. في حالة النجاح، قم بكتابة تعديل البيانات في قاعدة البيانات. وإلا سيتم إرجاع المعاملة إلى حالتها السابقة.
المبدأ الأساسي وراء المعاملات هو أنه إذا قامت إحدى البيانات بإرجاع خطأ، فسيتم التراجع عن مجموعة التغييرات بأكملها لضمان سلامة البيانات. وإذا نجحت المعاملات، فستكون جميع التغييرات دائمة في قاعدة البيانات. وبالتالي، إذا كان هناك انقطاع في التيار الكهربائي أو مشاكل أخرى عند سحب الأموال من أجهزة الصراف الآلي، فإن المعاملات تضمن بقاء رصيدنا ثابتًا. يؤدي بيان المعاملة هذه العمليات بشكل أفضل لأن الخصائص الرئيسية الأربعة للمعاملة تجعل جميع العمليات أكثر دقة واتساقًا. تتم الإشارة إلى الخصائص الأربعة للمعاملة باسم ACID.
خصائص الصفقة
يُشار إلى خصائص المعاملة باسم خاصية ACID (الذرية، والاتساق، والعزل، والمتانة)، والتي سيتم مناقشتها بالتفصيل أدناه:
الذرية: تضمن هذه الخاصية أن كافة البيانات أو العمليات المضمنة في المعاملة يجب أن يتم تنفيذها بنجاح. وإلا، سيتم إحباط المعاملة بأكملها، وسيتم إرجاع جميع العمليات إلى حالتها السابقة عند فشل أي عملية.
تناسق: تضمن هذه الخاصية أن قاعدة البيانات تتغير حالتها فقط عندما يتم تنفيذ المعاملة بنجاح. كما أنها مسؤولة عن حماية البيانات من الأعطال.
عزل: تضمن هذه الخاصية عزل جميع المعاملات عن المعاملات الأخرى، مما يعني أن كل عملية في المعاملة يتم تشغيلها بشكل مستقل. كما يضمن أيضًا أن تكون البيانات شفافة بالنسبة لبعضها البعض.
متانة: تضمن هذه الخاصية استمرار نتيجة المعاملات الملتزم بها في قاعدة البيانات بشكل دائم حتى في حالة تعطل النظام أو فشله.
أوضاع المعاملات في SQL Server
هناك ثلاثة أوضاع مختلفة للمعاملات يمكن لـ SQL Server استخدامها:
وضع معاملة الالتزام التلقائي: إنه وضع المعاملة الافتراضي لـ SQL Server. سيتم تقييم كل عبارة SQL كمعاملة، وسيتم الالتزام بالنتائج أو التراجع عنها وفقًا لذلك. وبالتالي يتم تنفيذ العبارات الناجحة على الفور، بينما يتم التراجع عن العبارات الفاشلة على الفور.
وضع المعاملات الضمنية. يسمح هذا الوضع لـ SQL Server ببدء المعاملة الضمنية لكل عبارة DML، ولكنه يتطلب بشكل صريح استخدام أوامر الالتزام أو التراجع في نهاية البيانات.
وضع المعاملة الصريحة: يتم تعريف هذا الوضع من قبل المستخدم والذي يسمح لنا بتحديد نقاط بداية ونهاية المعاملة بدقة. سيتم إحباطه تلقائيًا في حالة حدوث خطأ فادح.
مراقبة المعاملات
فيما يلي الأوامر المستخدمة للتحكم في المعاملات:
ملاحظة: يمكننا استخدام عبارات DML فقط (INSERT وUPDATE وDELETE) لأوامر لغة التحكم في المعاملات. ولا يمكننا استخدامها أثناء إنشاء الجداول أو إسقاطها لأن هذه العمليات تلتزم بقاعدة البيانات تلقائيًا.
حالة المعاملة
إنه يشير إلى كيفية سير المعاملات خلال فترة حياتهم. ويصف الحالة الحالية للمعاملة وكذلك كيفية معالجة المعاملة في المستقبل. تحدد هذه الحالات القواعد التي تحدد ما إذا كان سيتم تنفيذ المعاملة أو إحباطها.
دعونا نصف حالات كل معاملة في SQL Server:
الحالة النشطة: تكون المعاملة في حالة نشطة أثناء تنفيذ تعليمات المعاملة. يتغير إلى 'دولة ملتزمة جزئياً' إذا اكتملت جميع عمليات 'القراءة والكتابة' بدون أخطاء. إذا فشلت أي تعليمات، فإنها تتغير إلى 'الحالة الفاشلة'.
ملتزم جزئيا: عند اكتمال جميع عمليات القراءة والكتابة، يتم إجراء التغيير على الذاكرة الرئيسية أو المخزن المؤقت المحلي. ستذهب الدولة إلى 'دولة ملتزمة' إذا تم إجراء التغييرات بشكل دائم على قاعدة البيانات. وإلا فإنه يذهب إلى 'الدولة الفاشلة'.
حكومه فاشله: تنتقل المعاملة إلى الحالة الفاشلة عند فشل تعليمات أي معاملة أو فشل التعديل الدائم على قاعدة البيانات.
الدولة المجهضة: تنتقل الصفقة من أ 'حكومه فاشله' إلى 'حالة مجهضة' عند حدوث أي نوع من الفشل. تتم إزالة التغييرات أو التراجع عنها لأن هذه التغييرات يتم إجراؤها فقط على المخزن المؤقت المحلي أو الذاكرة الرئيسية في الحالات السابقة.
الدولة الملتزمة: اكتملت المعاملة وتصل إلى هذه الحالة عندما تصبح التغييرات دائمة في قاعدة البيانات ويتم إنهاؤها في 'الحالة المنتهية'.
الدولة المنتهية: إذا لم يكن هناك التراجع وكانت المعاملة في 'الدولة الملتزمة' النظام متسق وجاهز لمعاملة جديدة أثناء إنهاء المعاملة القديمة.
تنفيذ المعاملات في SQL Server
دعونا نأخذ بعض الأمثلة لفهم كيف يمكننا تنفيذ المعاملة في SQL Server. هنا سوف نستخدم 'منتج' الجدول لتوضيح كافة حالات المعاملة.
تقوم برامج SQL النصية التالية بإنشاء جدول المنتج في قاعدة البيانات المحددة:
ج رمز القيمة المطلقة
CREATE TABLE Product ( Product_id INT PRIMARY KEY, Product_name VARCHAR(40), Price INT, Quantity INT )
بعد ذلك، قم بتنفيذ البرامج النصية أدناه لإدراج البيانات في هذا الجدول:
INSERT INTO Product VALUES(111, 'Mobile', 10000, 10), (112, 'Laptop', 20000, 15), (113, 'Mouse', 300, 20), (114, 'Hard Disk', 4000, 25), (115, 'Speaker', 3000, 20);
قم بتنفيذ عبارة SELECT للتحقق من البيانات:
مثال على معاملة الالتزام
إنها فكرة جيدة أن يتم تقسيم عبارات SQL المستخدمة في المعاملة إلى أجزاء منطقية متعددة. وبعد ذلك، يمكننا أن نقرر ما إذا كنا سنلتزم بالبيانات أو نتراجع عنها. توضح الخطوات التالية لإنشاء معاملة:
- ابدأ المعاملة باستخدام ابدأ المعاملة يأمر.
- اكتب عبارات SQL وقم بتقسيمها بناءً على احتياجاتنا
- استخدم ال يقترف بيان لإكمال المعاملة وحفظ التغييرات بشكل دائم.
فيما يلي الأوامر التي تشرح عمليات COMMIT في SQL Server:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements INSERT INTO Product VALUES(116, 'Headphone', 2000, 30) UPDATE Product SET Price = 450 WHERE Product_id = 113 -- Commit changes COMMIT TRANSACTION
إذا لم يتم العثور على أي خطأ، فسنرى الإخراج التالي حيث يتم تنفيذ كل عبارة SQL للمعاملة بشكل مستقل:
لا يمكن التراجع عن عبارات INSERT وUPDATE بعد تنفيذ المعاملة. عندما نتحقق من الجدول بعد عملية الالتزام، سنرى البيانات التالية:
مثال على معاملة ROLLBACK
سنستخدم أمر ROLLBACK للتراجع عن أي معاملات لم يتم حفظها في قاعدة البيانات بعد والعودة إلى النقطة التي بدأت فيها المعاملة. يشرح المثال التالي عملية ROLLBACK في SQL Server:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116
بمجرد تنفيذ المعاملة المذكورة أعلاه، يمكننا أن نرى أنه سيتم تنفيذها بنجاح. ومع ذلك، لن يؤثر ذلك على أي تغييرات في قاعدة البيانات لأنه حتى لا ننفذ عبارة COMMIT أو ROLLBACK، لا يمكن أن تصبح التغييرات دائمة. لذلك، لدينا خيار استخدام أمر معاملة ROLLBACK لاسترجاع جميع عمليات قاعدة البيانات. فيما يلي بيان المعاملة الكامل:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116 --Undo Changes ROLLBACK TRANSACTION
استخدام المتغير @@Error Global في المعاملات:
هذا المتغير هو يستخدم للتحقق مما إذا كان هناك خطأ أم لا. المثال أدناه يشرح مفهومه. هنا سنبدأ المعاملة أولاً باستخدام أمر BEGIN ثم نكتب عبارتي إدراج. بعد ذلك، سوف نستخدم متغير النظام العالمي @@خطأ في ال بيان إذا للتحقق من الخطأ. إذا كانت القيمة أكبر من 0، فهذا يعني أن هناك خطأ ما. الآن، تم التراجع عن المعاملة؛ وإلا فإن الصفقة ملتزمة.
BEGIN TRANSACTION INSERT INTO Product VALUES(115,'Speaker', 3000, 25) -- Check for error IF(@@ERROR > 0) BEGIN ROLLBACK TRANSACTION END ELSE BEGIN COMMIT TRANSACTION END
عند تنفيذ المعاملة المذكورة أعلاه، سنلاحظ أنه تم التراجع عنها. ويرجع ذلك إلى محاولتنا لإدراج قيمة مكررة في عمود المفتاح الأساسي.
معاملة التراجع التلقائي
تحتوي معظم المعاملات على أكثر من استعلام واحد. أثناء تنفيذ المعاملة، إذا أنتجت أي من عبارات SQL خطأ، فلن تحدث أي تعديلات في قاعدة البيانات، ولن يتم تنفيذ العبارات المتبقية. يُعرف هذا المفهوم بمعاملة التراجع التلقائي في SQL Server. دعونا نستخدم مثالا بسيطا لتوضيح هذه العملية.
BEGIN TRANSACTION INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Quantity = 'ten' WHERE Product_id = 113 SELECT * FROM Product COMMIT TRANSACTION
تنتج هذه المعاملة المخرجات التالية:
في هذا الإخراج، يمكننا أن نرى أنه تم تنفيذ عبارة الإدراج بنجاح. ومع ذلك، أدى تنفيذ بيان التحديث إلى اكتشاف خطأ بسبب مشكلة تحويل نوع البيانات. في هذه الحالة، لا يسمح SQL Server بأي تغييرات في قاعدة البيانات، مما يعني أن عملية الإدراج لا تضيف أي قيمة، ولا يتم تنفيذ عبارة التحديد.
Savepoint في المعاملات
تُدرج نقطة الحفظ علامة خاصة في المعاملة التي تتيح لنا التراجع عن جميع التغييرات التي تم تنفيذها بعد نقطة الحفظ. كما يتم استخدامه لاسترجاع أي جزء محدد من المعاملة بدلاً من المعاملة بأكملها. يمكننا تعريفه باستخدام حفظ المعاملة sp_name إفادة. سيوضح المثال التالي استخدام نقطة الحفظ في المعاملات التي تنفذ عبارة الإدراج وتتراجع عن عبارة الحذف.
BEGIN TRANSACTION INSERT INTO Product VALUES(117, 'USB Drive', 1500, 10) SAVE TRANSACTION InsertStatement DELETE FROM Product WHERE Product_id = 116 SELECT * FROM Product ROLLBACK TRANSACTION InsertStatement COMMIT SELECT * FROM Product;
انظر النتيجة أدناه حيث يمكننا أن نرى حذف معرف المنتج 116، وإدراج 117 في الإخراج الأول. ومع ذلك، في الإخراج الثاني، يتم التراجع عن عملية الحذف بسبب نقطة الحفظ.
كيفية تحرير نقطة الحفظ في المعاملة؟
يتم استخدام نقطة الحفظ للإصدار لإزالة نقطة الحفظ المسماة من المعاملة الحالية دون الرجوع إلى نتائج الاستعلامات التي تم تنفيذها بعد نقطة الحفظ. يحتوي MySQL على هذا الأمر، لكن SQL Server لا يوفر أي أمر لتحرير نقطة حفظ. وبدلاً من ذلك، يتم تحريرها تلقائيًا في نهاية معاملة الالتزام أو التراجع، لذلك لا داعي للقلق بشأنها في منتصف الأمر.
المعاملات الضمنية في SQL Server
يمكننا تحديد معاملة ضمنية من خلال تمكين خيار IMPLICIT_TRANSACTIONS. والمثال التالي سوف يشرح هذا المفهوم بسهولة:
SET IMPLICIT_TRANSACTIONS ON UPDATE Product SET Quantity = 10 WHERE Product_id = 113 SELECT IIF(@@OPTIONS & 2 = 2, 'Implicit Transaction Mode ON', 'Implicit Transaction Mode OFF' ) AS 'Transaction Mode' SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
في هذه الصفقة، استخدمنا خيارين @@OPTION و@@TRANCOUNT. يوفر @@OPTOPN المعلومات المتعلقة بخيارات SET الحالية، ويوفر @@TRANCOUNT بيان بدء المعاملة في الجلسة الحالية.
الآن، سيؤدي تنفيذ المعاملة إلى إرجاع المخرجات التالية:
معاملة صريحة في SQL Server
يجب تعريف المعاملة الصريحة من خلال أمر BEGIN TRANSACTION لأنه يحدد نقطة البداية للمعاملة الصريحة. يمكننا تحديد المعاملة الصريحة في SQL Server على النحو التالي:
برامج بايثون
BEGIN TRANSACTION [ @trans_name_variable [WITH MARK ['description']]]
في بناء الجملة، يشير خيار trans_name إلى اسم فريد للمعاملة. ال @trans_name_var يشير إلى متغير محدد من قبل المستخدم يقوم بتخزين اسم المعاملة. وأخيرا، فإن علامة يتيح لنا الخيار وضع علامة على معاملة معينة في ملف السجل.
حصلت المعاملة الصريحة من خلال أمر BEGIN TRANSACTION على قفل اعتمادًا على مستوى عزل الموارد المرتبطة بالمعاملة. يساعد على تقليل مشاكل القفل. انظر المثال أدناه:
BEGIN TRANSACTION UPDATE Product SET Quantity = 15 WHERE Product_id = 114 SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
هنا هو الإخراج:
المعاملة المميزة في SQL Server
يتم استخدام المعاملة المميزة لإضافة وصف لمعاملة معينة في ملفات السجل. يمكننا استخدامه كنقطة استرداد بدلاً من التاريخ والوقت عند استعادة قاعدة البيانات إلى حالتها السابقة. يجب أن نعلم أن العلامة تتم إضافتها في ملفات السجل فقط عندما تقوم المعاملة المميزة بتعديل قاعدة البيانات. يمكننا أن نفهم مفهومها باستخدام المثال التالي.
لنفترض أننا قمنا بتعديل قاعدة البيانات عن طريق الخطأ، ولا نعرف بالضبط اللحظة التي تم فيها تغيير البيانات؛ وفي هذه الحالة، يمكن أن تستغرق استعادة البيانات وقتًا طويلاً. ومع ذلك، إذا استخدمنا المعاملات المميزة، فيمكن أن تكون أداة مفيدة لتحديد التوقيت الدقيق لتعديلات البيانات.
يوضح بناء الجملة التالي المعاملة المميزة في SQL Server:
BEGIN TRANSACTION trans_name WITH MARK 'description';
هنا يجب علينا تحديد اسم المعاملة ثم إضافة خيار With MARK. في المثال أدناه، سنقوم بحذف السجلات وإضافة العلامة في ملف السجل:
BEGIN TRANSACTION DeleteProduct WITH MARK 'Deleted Product with id = 117' DELETE Product WHERE Product_id = 117 COMMIT TRANSACTION DeleteProduct
ال com.logmarkhistory يتم تضمين الجدول في قاعدة بيانات ام اس دي بي ويخزن المعلومات المتعلقة بكل معاملة مميزة تم الالتزام بها. قم بتنفيذ العبارة التالية للحصول على التفاصيل من جدول logmarkhistory:
SELECT * FROM msdb.dbo.logmarkhistory
المعاملة المسماة في SQL Server
يمكننا أيضًا تقديم اسم لمعاملتنا في SQL Server. يوصى دائمًا باستخدام المعاملة المسماة عند التعامل مع العديد من المعاملات في استعلام واحد. يوضح المثال التالي كيفية إعادة تسمية المعاملة:
BEGIN TRANSACTION AddProduct INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Product_name = 'Pen Drive' WHERE Product_id = 117 COMMIT TRANSACTION AddProduct
هنا هو الإخراج:
خاتمة
ستقدم هذه المقالة نظرة عامة كاملة على المعاملة في عبارات SQL Server. تعتبر المعاملات مفيدة في أنظمة قواعد البيانات العلائقية لأنها تضمن سلامة قاعدة البيانات.