يدعم MySQL 5.1 القفل على مستوى الجدول لجداول MyISAM وMEMORY، والقفل على مستوى الصفحة لجداول BDB، وInnoDB. قفل مستوى الصف. في كثير من الحالات، من الممكن تخمين نوع القفل الأفضل لتطبيق ما بناءً على التدريب، ولكن من الصعب عمومًا معرفة ما إذا كان نوع القفل المحدد أفضل من الآخر. كل شيء يعتمد على التطبيق، وقد تتطلب أجزاء مختلفة من التطبيق أنواعًا مختلفة من القفل. لتحديد ما إذا كنت تريد استخدام محرك تخزين تأمين على مستوى الصف، يجب عليك الاطلاع على ما يفعله تطبيقك وما هو مزيج عبارات التحديد والتحديث التي يستخدمها. على سبيل المثال، تقوم معظم تطبيقات الويب بإجراء العديد من التحديدات وعدد قليل من عمليات الحذف، وتحديثات فقط للقيم الأساسية، وعدد قليل فقط من إدراجات الجدول المحددة. تم ضبط إعداد MySQL MyISAM الأساسي جيدًا.
في MySQL، بالنسبة لمحركات التخزين التي تستخدم القفل على مستوى الجدول، لن يكون هناك توقف تام عند قفل الجدول. تتم إدارة ذلك من خلال طلب كافة عمليات التأمين الضرورية فورًا في بداية الاستعلام وتأمين الجدول دائمًا بنفس الترتيب.
بالنسبة للكتابة، تعمل طريقة قفل الجدول التي تستخدمها MySQL على النحو التالي:
◆ إذا لم يكن هناك قفل على الجدول، ضع قفل كتابة عليه.
◆ بخلاف ذلك، ضع طلب القفل في قائمة انتظار قفل الكتابة.
بالنسبة للقراءة، تعمل طريقة القفل التي يستخدمها MySQL على النحو التالي:
◆إذا لم يكن هناك قفل للكتابة على الطاولة، ضع قفل القراءة عليه.
◆ بخلاف ذلك، ضع طلب القفل في قائمة انتظار قفل القراءة.
عند تحرير القفل، يمكن الحصول على القفل عن طريق سلاسل الرسائل الموجودة في قائمة انتظار قفل الكتابة، ثم عن طريق سلاسل الرسائل الموجودة في قائمة انتظار قفل القراءة.
وهذا يعني أنه إذا كان لديك العديد من التحديثات على الجدول، فستنتظر عبارة SELECT حتى لا يكون هناك المزيد من التحديثات.
إذا لم تتعارض عبارات INSERT، فيمكنك مزج عبارات INSERT وSELECT المتوازية بحرية لجدول MyISAM دون قفل.
يستخدم InnoDB قفل الصفوف ويستخدم BDB قفل الصفحة. مع كلا محركي التخزين، من الممكن حدوث حالة توقف تام. وذلك لأن InnoDB يحصل تلقائيًا على أقفال الصفوف ويحصل BDB على أقفال الصفحة أثناء معالجة عبارة SQL، وليس عند بدء المعاملة.
مزايا القفل على مستوى الصف:
· لا يوجد سوى عدد قليل من تعارضات القفل عند الوصول إلى صفوف مختلفة في العديد من سلاسل الرسائل.
· التراجع مع عدد قليل فقط من التغييرات.
· يمكن قفل صف واحد لفترة طويلة.
مساوئ القفل على مستوى الصف:
· يستهلك ذاكرة أكبر من القفل على مستوى الصفحة أو على مستوى الجدول.
· عند استخدامه على أجزاء كبيرة من الجدول، يكون القفل أبطأ من القفل على مستوى الصفحة أو على مستوى الجدول لأنه يجب عليك الحصول على المزيد من التأمين.
· إذا كنت تقوم بشكل متكرر بإجراء عمليات GROUP BY على معظم بياناتك أو كان عليك فحص الجدول بأكمله بشكل متكرر، فسيكون ذلك أبطأ بشكل ملحوظ من الأقفال الأخرى.
· باستخدام القفل عالي المستوى، يمكنك أيضًا توسيع نطاق تطبيقك بسهولة من خلال دعم أنواع مختلفة من القفل لأن تكلفة القفل أقل من تكلفة القفل على مستوى الصف.
يكون لتأمين الجدول الأولوية على مستوى الصفحة أو مستوى الصف عندما:
· يتم استخدام معظم البيانات الموجودة في الجدول للقراءة.
· القراءة والتحديث باستخدام المفاتيح الصارمة، يمكنك تحديث أو حذف صف يمكن استخراجه باستخدام مفتاح قراءة واحد:
• تحديث العمود tbl_name SET = القيمة WHERE Unique_key_col = key_value؛
• الحذف من tbl_name WHERE Unique_key_col = key_value
· SELECT مدمج مع عبارات INSERT المتوازية وعدد قليل جدًا من عبارات UPDATE أو DELETE.
· هناك العديد من عمليات المسح أو عمليات GROUP BY على الجدول بأكمله دون أي عمليات كتابة.
الخيارات التي تختلف عن القفل على مستوى الصف أو على مستوى الصفحة:
· تعيين الإصدار (على سبيل المثال، التقنية المستخدمة في MySQL للإدراجات المتوازية)، حيث يمكن أن تكون هناك عملية كتابة واحدة والعديد من عمليات القراءة في نفس الوقت. وهذا يعني أن قاعدة البيانات أو الجدول يدعم طرق عرض مختلفة للبيانات، اعتمادًا على وقت بدء الوصول. المصطلحات الشائعة الأخرى هي "تتبع الوقت" أو "النسخ عند الكتابة" أو "النسخ عند الطلب".
· النسخ المتماثل عند الطلب له الأسبقية على مستوى الصفحة أو القفل على مستوى الصف في العديد من الحالات. ومع ذلك، في أسوأ الحالات، قد يستخدم ذاكرة أكبر من استخدام القفل العادي.
بالإضافة إلى القفل على مستوى الصف، يمكنك استخدام القفل على مستوى التطبيق، مثل GET_LOCK() و RELEASE_LOCK() في MySQL. هذه أقفال استشارية، ولن تعمل إلا في التطبيقات التي تعمل بشكل جيد.
لتحقيق أقصى سرعة للقفل، يستخدم MySQL قفل الجدول (بدلاً من قفل الصفحة أو الصف أو العمود) لجميع محركات التخزين باستثناء InnoDB وBDB. بالنسبة لجداول InnoDB وBDB، يستخدم MySQL قفل الجدول فقط إذا قمت بقفل الجدول بشكل صريح باستخدام LOCK TABLES؛ إذا كنت لا تستخدم LOCK TABLES، لأن InnoDB يستخدم قفلًا تلقائيًا على مستوى الصف ويستخدم BDB قفلًا على مستوى الصفحة لضمان عزل المعاملة.
لكن بالنسبة للجداول الكبيرة، يعد قفل الجدول أفضل من قفل الصفوف لمعظم التطبيقات، ولكن به بعض العيوب. يمكّن قفل الجدول العديد من سلاسل الرسائل من القراءة من جدول في وقت واحد، ولكن إذا أراد مؤشر ترابط الكتابة إلى الجدول، فيجب عليه أولاً الحصول على وصول خاص. أثناء التحديث، يجب على كافة سلاسل الرسائل الأخرى التي تريد الوصول إلى الجدول الانتظار حتى اكتمال التحديث.
تعتبر تحديثات الجدول بشكل عام أكثر أهمية من عمليات استرجاع الجدول، لذلك يتم إعطاؤها أولوية أعلى. يجب أن يضمن هذا أن نشاط تحديث الجدول لا يمكن أن يتضاءل، حتى لو كان هناك نشاط SELECT كثيف على الجدول.
يمكن أن يتسبب قفل الجدول في حدوث مشكلات في مواقف مثل انتظار مؤشر الترابط لأن القرص الثابت ممتلئ ويجب أن تكون هناك مساحة خالية قبل أن يتمكن مؤشر الترابط من المعالجة. في هذه الحالة، يتم أيضًا وضع كافة سلاسل الرسائل التي تريد الوصول إلى الجدول المعني في حالة انتظار حتى تتوفر مساحة أكبر على القرص الثابت.
يعد تأمين الجدول أيضًا مشكلة في المواقف التالية:
· يصدر العميل استعلامًا طويل الأمد.
· ثم يقوم عميل آخر بتحديث نفس الجدول. يجب على العميل الانتظار حتى اكتمال التحديد.
· يصدر عميل آخر عبارة SELECT أخرى على نفس الجدول. نظرًا لأن UPDATE له أولوية أعلى من SELECT، فإن عبارة SELECT تنتظر اكتمال UPDATE، وتنتظر اكتمال SELECT الأول.
بعض الطرق موضحة أدناه لتجنب أو تقليل التعارض الناتج عن أقفال الجدول:
· حاول تشغيل عبارة SELECT بشكل أسرع. قد يتعين عليك إنشاء بعض الجداول التلخيصية للقيام بذلك.
· ابدأ mysqld باستخدام --low-priority-updates. سيؤدي هذا إلى إعطاء كافة العبارات التي تقوم بتحديث (تعديل) جدول أولوية أقل من عبارات SELECT. في هذه الحالة، سيتم تنفيذ عبارة SELECT الثانية في الموقف السابق قبل عبارة UPDATE دون انتظار اكتمال SELECT الأول.
· يمكنك استخدام عبارة SET LOW_PRIORITY_UPDATES=1 لتحديد أن كافة التحديثات في اتصال معين يجب أن تكون ذات أولوية منخفضة.
· يمكنك استخدام الخاصية LOW_PRIORITY لإعطاء أولوية أقل لعبارة INSERT أو UPDATE أو DELETE المحددة.
· يمكنك استخدام السمة HIGH_PRIORITY لإعطاء عبارة SELECT محددة أولوية أعلى.
· ابدأ mysqld بتحديد قيمة منخفضة لمتغير النظام max_write_lock_count لإجبار MySQL على زيادة أولوية كافة عبارات SELECT بشكل مؤقت في انتظار الجدول بعد اكتمال عدد محدد من الإدخالات. يسمح هذا بإعطاء قفل القراءة بعد عدد معين من أقفال الكتابة.
· إذا كانت لديك مشكلات مع INSERT مع SELECT، فانتقل إلى استخدام جداول MyISAM الجديدة لأنها تدعم SELECT وINSERT المتزامنين.
· إذا قمت بخلط الإدخالات والمحذوفات في نفس الجدول، فإن الإدخال المتأخر سيكون ذا فائدة كبيرة.
· إذا كانت لديك مشاكل في خلط عبارات SELECT وDELETE في نفس الجدول، فيمكن أن يساعدك خيار LIMIT الخاص بـ DELETE.
· استخدام SQL_BUFFER_RESULT لعبارات التحديد يمكن أن يساعد في تقليل أوقات قفل الجدول.
· يمكن تغيير رمز القفل في mysys/thr_lock.c لاستخدام قائمة انتظار واحدة. في هذه الحالة، سيكون لأقفال الكتابة وأقفال القراءة نفس الأولوية، مما قد يكون مفيدًا لبعض التطبيقات.
فيما يلي بعض النصائح المتعلقة بقفل الجدول في MySQL:
إذا لم تقم بخلط التحديثات مع التحديدات التي تتطلب التحقق من العديد من الصفوف في نفس الجدول، فيمكنك القيام بعمليات متوازية.
· يمكنك استخدام جداول القفل لتحسين السرعة، لأن العديد من التحديثات في قفل واحد تكون أسرع بكثير من التحديثات بدون أقفال. يمكن أن يساعد أيضًا تقسيم محتويات الجدول إلى عدة جداول.
· إذا واجهت مشاكل في السرعة عند قفل الجداول في MySQL، فيمكنك تحويل الجدول إلى جدول InnoDB أو BDB لتحسين الأداء.