وصف الحظر والتأمين

مكتمل

التأمين هو ميزة رئيسية لقواعد البيانات الارتباطية، وهي ضرورية للحفاظ على خصائص الذرية والاتساق والعزل لنموذج ACID. تحظر جميع إجراءات RDBMSs التي من شأنها انتهاك تناسق وعزل عمليات كتابة قاعدة البيانات. يجب على مبرمجي SQL بدء المعاملات وإنهاءها في النقاط الصحيحة لضمان تناسق البيانات. يوفر محرك قاعدة البيانات آليات تأمين لحماية التناسق المنطقي للجداول المتأثرة، وهو أساسي للنموذج العلائقي.

في SQL Server، يحدث الحظر عندما تحتفظ عملية واحدة بتأمين على مورد معين (صف وصف وصفحة وجدول وقاعدة بيانات)، وتحاول عملية ثانية الحصول على تأمين بنوع تأمين غير متوافق على نفس المورد. عادة ما يتم الاحتفاظ بتأمين لفترة قصيرة، وبمجرد أن تقوم العملية التي تحمل التأمين بإصداره، يمكن للعملية المحظورة الحصول على التأمين وإكمال معاملتها.

يقوم SQL Server بتأمين أصغر كمية من البيانات المطلوبة لإكمال المعاملة، ما يسمح بأقصى قدر من التزامن. على سبيل المثال، إذا قام SQL Server بتأمين صف واحد، تظل جميع الصفوف الأخرى في الجدول متاحة للعمليات الأخرى، مما يتيح العمل المتزامن. ومع ذلك، يتطلب كل تأمين موارد ذاكرة، لذلك من غير الفعال من حيث التكلفة لعملية واحدة الاحتفاظ بآلاف الأقفال الفردية على جدول واحد. لموازنة التزامن مع التكلفة، يستخدم SQL Server تقنية تسمى تصعيد التأمين. إذا كان هناك أكثر من 5000 صف في كائن واحد يجب تأمينه في عبارة واحدة، يقوم SQL Server بتصعيد تأمينات الصفوف المتعددة إلى تأمين جدول واحد.

التأمين هو سلوك عادي ويحدث بشكل متكرر على مدار اليوم. يصبح الأمر إشكاليا فقط عندما يتسبب في حظر لم يتم حله بسرعة. هناك نوعان من مشكلات الأداء الناتجة عن الحظر:

• تحتفظ العملية بتأمين مجموعة من الموارد لفترة طويلة قبل تحريرها، ما يتسبب في حظر العمليات الأخرى وتدهور أداء الاستعلام والتزامن.
• تكتسب العملية تأمينات على مجموعة من الموارد ولا تحررها أبدا، مما يتطلب تدخل المسؤول لحلها.

التوقف التام هو سيناريو حظر آخر يحدث عندما تحتفظ معاملة واحدة بتأمين على مورد، وتحتفظ معاملة أخرى بتأمين مورد مختلف. ثم تحاول كل معاملة الحصول على تأمين على المورد المؤمن حاليا بواسطة المعاملة الأخرى، مما يؤدي إلى انتظار لا نهائي حيث لا يمكن إكمال أي عملية. يكتشف محرك SQL Server هذه السيناريوهات ويحل حالة التوقف التام عن طريق قتل إحدى المعاملات، استنادا إلى المعاملة التي نفذت أقل قدر من العمل الذي يحتاج إلى التراجع. تعرف المعاملة التي تم قتلها باسم ضحية حالة التوقف التام. يتم تسجيل حالات التوقف التام في جلسة الحدث الممتدة system_health ، والتي يتم تمكينها افتراضيا.

من المهم فهم مفهوم المعاملة. الالتزام التلقائي هو الوضع الافتراضي ل SQL Server وقاعدة بيانات Azure SQL، مما يعني أن التغييرات التي تم إجراؤها بواسطة العبارة التالية سيتم تسجيلها تلقائيا في سجل معاملات قاعدة البيانات.

INSERT INTO DemoTable (A) VALUES (1);

من أجل السماح للمطورين بالتحكم بشكل أكثر دقة في التعليمات البرمجية للتطبيق الخاص بهم، يسمح لك SQL Server أيضا بالتحكم بشكل صريح في معاملاتك. سيتخذ الاستعلام التالي تأمينا على صف في جدول DemoTable لن يتم تحريره حتى تتم إضافة أمر لاحق لتنفيذ المعاملة.

BEGIN TRANSACTION

INSERT INTO DemoTable (A) VALUES (1);

الطريقة المناسبة لكتابة الاستعلام التالي هي كما يلي:

BEGIN TRANSACTION

INSERT INTO DemoTable (A) VALUES (1);

COMMIT TRANSACTION

يقوم الأمر COMMIT TRANSACTION بالتزام صريح بسجل التغييرات التي تم إجراؤها على سجل الحركات. ستشق البيانات التي تم تغييرها في النهاية طريقها إلى ملف البيانات بشكل غير متزامن. تمثل هذه المعاملات وحدة عمل لمحرك قاعدة البيانات. إذا نسي المطور إصدار COMMIT TRANSACTION الأمر، تظل المعاملة مفتوحة ولن يتم تحرير التأمينات. هذا هو أحد الأسباب الرئيسية للمعاملات طويلة الأمد.

الآلية الأخرى التي يستخدمها محرك قاعدة البيانات للمساعدة في تزامن قاعدة البيانات هي تعيين إصدار الصف. عند تمكين مستوى عزل تعيين إصدار صف إلى قاعدة البيانات، يحتفظ المحرك بإصدارات كل صف معدل في TempDB. يتم استخدام هذا عادة في أحمال العمل ذات الاستخدام المختلط، لمنع قراءة الاستعلامات من حظر الاستعلامات التي تكتب إلى قاعدة البيانات.

لمراقبة المعاملات المفتوحة التي تنتظر التثبيت أو العودة إلى الحالة السابقة، قم بتشغيل الاستعلام التالي:

SELECT tst.session_id, [database_name] = db_name(s.database_id)
    , tat.transaction_begin_time
    , transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
    , transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
        WHEN 2 THEN 'Read-only transaction'
        WHEN 3 THEN 'System transaction'
        WHEN 4 THEN 'Distributed transaction' END
    , input_buffer = ib.event_info, tat.transaction_uow     
    , transaction_state  = CASE tat.transaction_state    
        WHEN 0 THEN 'The transaction has not been completely initialized yet.'
        WHEN 1 THEN 'The transaction has been initialized but has not started.'
        WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
        WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
        WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
        WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
        WHEN 6 THEN 'The transaction has been committed.'
        WHEN 7 THEN 'The transaction is being rolled back.'
        WHEN 8 THEN 'The transaction has been rolled back.' END 
    , transaction_name = tat.name, request_status = r.status
    , tst.is_user_transaction, tst.is_local
    , session_open_transaction_count = tst.open_transaction_count  
    , s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat 
INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib
ORDER BY tat.transaction_begin_time DESC;

مستويات العزل

يوفر SQL Server عدة مستويات عزل للسماح لك بتحديد مستوى التناسق والتصحيح الذي تحتاج إلى ضمانه لبياناتك. تتيح لك مستويات العزل إيجاد توازن بين التزامن والاتساق. لا يؤثر مستوى العزل على التأمينات المأخوذة لمنع تعديل البيانات. ستحصل المعاملة دائما على تأمين حصري على البيانات التي يتم تعديلها. ومع ذلك، يمكن أن يؤثر مستوى العزل الخاص بك على طول الوقت الذي يتم فيه الاحتفاظ بتأمينك. تؤدي مستويات العزل المنخفضة إلى زيادة قدرة عملية مستخدم متعددة على الوصول إلى البيانات في نفس الوقت، ولكنها تزيد من مخاطر تناسق البيانات التي يمكن أن تحدث. مستويات العزل في SQL Server هي كما يلي:

• اقرأ غير ملتزم - أدنى مستوى عزل متاح. يسمح بالقراءة غير الصالحة، مما يعني أن معاملة واحدة قد ترى تغييرات تم إجراؤها بواسطة معاملة أخرى لم يتم تنفيذها بعد.

• قراءة ملتزمة - يسمح للمعاملة بقراءة البيانات التي تمت قراءتها مسبقا ، ولكن لم يتم تعديلها بواسطة معاملة أخرى دون انتظار انتهاء المعاملة الأولى. يصدر هذا المستوى أيضا تأمينات القراءة بمجرد تنفيذ عملية التحديد. هذا هو مستوى SQL Server الافتراضي.

• قراءة قابلة للتكرار - يحتفظ هذا المستوى بأقفال القراءة والكتابة التي يتم الحصول عليها على البيانات المحددة حتى نهاية المعاملة.

• قابل للتسلسل - هذا هو أعلى مستوى من العزل حيث يتم عزل المعاملات. يتم الحصول على تأمين القراءة والكتابة على البيانات المحددة ولا يتم تحريرها حتى نهاية المعاملة.

يتضمن SQL Server أيضا مستويين من العزل يتضمنان تعيين إصدار الصف.

• قراءة لقطة ملتزمة – في هذا المستوى، لا تأخذ عمليات القراءة أي أقفال صفوف أو صفحات، ويقدم المحرك كل عملية بلقطة متسقة للبيانات كما كانت موجودة في بداية الاستعلام. يستخدم هذا المستوى عادة عندما يقوم المستخدمون بتشغيل استعلامات التقارير المتكررة مقابل قاعدة بيانات OLTP، من أجل منع عمليات القراءة من حظر عمليات الكتابة.

• لقطة - يوفر هذا المستوى تناسق قراءة مستوى المعاملة من خلال تعيين إصدار الصف. هذا المستوى عرضة لتعارضات التحديث. إذا كانت إحدى المعاملات التي تعمل ضمن هذا المستوى تقرأ البيانات المعدلة بواسطة معاملة أخرى، إنهاء تحديث بواسطة معاملة اللقطة والعودة إلى حالتها السابقة. هذه ليست مشكلة في عزل اللقطة الملتزم بها للقراءة.

يتم تعيين مستويات العزل لكل جلسة باستخدام الأمر T-SQL #D0، كما هو موضح:

SET TRANSACTION ISOLATION LEVEL

 { READ UNCOMMITTED

 | READ COMMITTED

 | REPEATABLE READ

 | SNAPSHOT

 | SERIALIZABLE

 }

لا توجد طريقة لتعيين مستوى عزل عمومي لجميع الاستعلامات التي تعمل في قاعدة بيانات، أو لجميع الاستعلامات التي يشغلها مستخدم معين. إنه إعداد على مستوى الجلسة.

مراقبة مشاكل الحظر

يمكن أن يكون تحديد مشكلات الحظر أمرا صعبا نظرا لطبيعتها المتقطعة. يوفر DMV sys.dm_tran_locks، عند الانضمام إلى sys.dm_exec_requests، معلومات حول الأقفال التي تحتفظ بها كل جلسة عمل. هناك طريقة أكثر فعالية لمراقبة مشكلات الحظر وهي استخدام محرك الأحداث الموسعة بشكل مستمر.

عادة ما تندرج مشاكل الحظر في فئتين:

• تصميم المعاملات الضعيف: على سبيل المثال، لن تنتهي المعاملة بدون COMMIT TRANSACTION . يمكن أن تؤدي محاولة القيام بالكثير من العمل في معاملة واحدة أو وجود معاملة موزعة باستخدام اتصال خادم مرتبط إلى أداء لا يمكن التنبؤ به.
• المعاملات طويلة الأمد الناجمة عن تصميم المخطط: غالبا ما يتضمن ذلك تحديثا على عمود يحتوي على فهرس مفقود أو استعلام تحديث غير مصمم بشكل جيد.

تسمح لك مراقبة مشكلات الأداء المتعلقة بالقفل بتحديد انخفاض الأداء بسرعة فيما يتعلق بالإقفال.

لمزيد من المعلومات حول كيفية مراقبة الحظر، راجع فهم مشكلات حظر SQL Server وحلها.