هندسة البرمجيات والـ Backend الأدلة التقنية

المعاملات Transactions ومستويات العزل: ماذا يحمي كل مستوى؟

ما الذي يمنعه كل مستوى عزل فعلاً في PostgreSQL وMySQL، بسبع ظواهر شغلناها على جلستين وألف وستمئة حجز متزامن، وكيف تكتب كوداً لا يبيع المقعد مرتين.

المعاملات Transactions ومستويات العزل: ماذا يحمي كل مستوى؟

تكتب صفحة حجز التذاكر لفعالية فيها مئة مقعد، والكود بسيط وواضح: تقرأ عدد المقاعد المتبقية، فإذا كان أكبر من صفر أنقصته واحداً وأضفت الحجز، وكل ذلك داخل معاملة Transaction واحدة كما تعلمت. وتشغل الاختبارات فتنجح، وتجرب الصفحة بنفسك فتعمل. ثم يفتح الحجز في الساعة التاسعة مساءً ويدخل مئات الأشخاص في الدقيقة الأولى، وفي الصباح تجد في جدول الحجوزات 160 حجزاً لفعالية سعتها مئة، وعداد المقاعد ما زال يقول إن هناك مقاعد متبقية. لم يظهر أي خطأ في السجلات Logs، ولم ترفض قاعدة البيانات شيئاً، وكل معاملة نفذت كاملة أو لم تنفذ، أي أن الذرية Atomicity عملت كما وعدت. والمشكلة كانت في حرف آخر من ACID لا يفكر فيه أغلب المطورين حتى يحدث هذا، وهو العزل Isolation.

والسبب أن «داخل معاملة» لا تعني «وحدي في قاعدة البيانات»، فما تراه معاملتك وما يحدث عندما تعدل معاملتان نفس البيانات يحدده مستوى العزل Isolation Level، والمستوى الافتراضي في أغلب قواعد البيانات يسمح بحالات قد لا تتوقعها. وقد عرفنا المعاملة وACID ومستويات العزل الثلاثة بتجارب قصيرة في الجزء الثالث من سلسلة تعلم SQL، وشرحنا الأقفال والجمود Deadlock وانحراف القراءة في مقال الأقفال، وهذا المقال يأخذ السؤال من زاوية المطور الذي يكتب كود التطبيق: ما الذي يحميك منه كل مستوى بالضبط، وما الذي لا يحميك منه، وهل الإجابة نفسها في PostgreSQL وMySQL، وكيف تكتب الكود الذي يبقى صحيحاً عندما يدخل مئات المستخدمين في نفس اللحظة.

وسوف نناقش في هذا المقال ما يلي:

  • ما الذي يعد به العزل، ولماذا هو أضعف وعود ACID في الواقع.
  • الظواهر Anomalies التي تحدث عندما تعمل معاملتان معاً: القراءة القذرة، والقراءة غير المتكررة، والقراءة الوهمية Phantom، والتحديث المفقود Lost Update، وانحراف القراءة والكتابة، ولماذا انتقد الباحثون تعريف المعيار ANSI لها.
  • كيف ينفذ PostgreSQL العزل من الداخل باللقطات Snapshots وقاعدة «أول من يعدل يفوز» وتقنية SSI، وكيف يختلف عنه InnoDB في MySQL.
  • تجربة عملية تشغل سبع ظواهر على كل مستوى في PostgreSQL 18.6 وMySQL 8.4.13، والنتيجة في جدول واحد، مع خطأ تسلسل ظهر دون تعارض حقيقي.
  • كود التطبيق نفسه: ست طرق لحجز 1000 مقعد بست عشرة عملية متوازية، وكم مقعداً بيع مرتين، وكم مرة أعيدت المعاملة، وكم عميلاً رفض حجزه والمقاعد متاحة.
  • كيف تكتب إعادة المحاولة Retry بشكل صحيح، وجدول قرار تختار به المستوى والطريقة.

ما الذي يعد به العزل Isolation؟

المعاملة Transaction مجموعة عمليات تعاملها قاعدة البيانات كوحدة واحدة، والوعود الأربعة المعروفة باسم ACID هي: الذرية Atomicity، أي كل العمليات أو لا شيء، والاتساق Consistency، أي أن القيود التي كتبتها تبقى صحيحة، والعزل Isolation، والديمومة Durability، أي أن ما أكد يبقى بعد انقطاع الكهرباء. والوعد النظري للعزل في الكتب هو أن كل معاملة تعمل وكأنها وحدها، أي أن نتيجة تنفيذ معاملات كثيرة في نفس الوقت تساوي نتيجة تنفيذها واحدة بعد الأخرى بترتيب ما، وهذا ما يسمى القابلية للتسلسل Serializability.

ولكن هذا الوعد مكلف، لأن أبسط طريقة لتحقيقه أن تنتظر كل معاملة غيرها، ولذلك عرف معيار SQL مستويات أضعف تسمح ببعض التداخل مقابل أداء أفضل، وجعلت أغلب قواعد البيانات مستوى ضعيفاً هو الافتراضي: Read Committed في PostgreSQL وOracle وSQL Server، وRepeatable Read في MySQL. وهذا يعني أن العزل الذي تحصل عليه دون أن تطلب شيئاً ليس «كأنك وحدك»، وإنما مجموعة محددة من الضمانات، وما لا تضمنه هذه المجموعة هو مسؤولية كودك أنت. وقد يتساءل البعض: إذا كانت قاعدة البيانات تعرف أن المستوى الافتراضي يسمح بأخطاء، فلماذا تجعله افتراضياً؟ والإجابة أن أغلب المعاملات في التطبيقات العادية لا تتأثر بهذه الحالات، مثل قراءة صفحة وعرضها أو إضافة سجل مستقل، وأن رفع المستوى للجميع يعني أخطاء وإعادة محاولات وانتظاراً في كل مكان، فتركت القرار لك في المعاملات التي تحتاجه.

وفي سنة 2017 أظهرت ورقة ACIDRain من جامعة Stanford حجم المشكلة في الواقع، فقد فحص الباحثون Todd Warszawski وPeter Bailis اثني عشر تطبيقاً مشهوراً للتجارة الإلكترونية تستضيفها الشركات بنفسها، مكتوبة بأربع لغات ومنشورة على أكثر من مليوني موقع، ووجدوا 22 ثغرة حرجة تسمح للمهاجم بأن يفسد أرقام المخزون، وأن يصرف بطاقة الهدايا Gift Card أكثر من رصيدها، وأن يأخذ بضاعة لم يدفع ثمنها، وكلها بإرسال طلبات متزامنة إلى الواجهة البرمجية API نفسها التي يستخدمها أي عميل. وتذكر الورقة أن هجوماً من هذا النوع أفلس منصة لتبادل Bitcoin. ولم تكن المشكلة في قاعدة البيانات، وإنما في كود يقرأ ثم يقرر ثم يكتب، تحت مستوى عزل لا يمنع غيره من القراءة والكتابة بين الخطوتين.

الظواهر Anomalies: ماذا قد يحدث عندما تعمل معاملتان معاً؟

لكي تقول ما الذي يحميك منه كل مستوى، تحتاج أولاً قائمة بما قد يحدث، وهذه القائمة تسمى الظواهر Anomalies أو الظواهر الشاذة، وكل ظاهرة تسلسل محدد من القراءة والكتابة بين معاملتين ينتج حالة لا يمكن أن تحدث لو نفذتا واحدة بعد الأخرى. وسوف نستخدم في كل الأمثلة نفس النظام: جدول الفعاليات events وفيه عمود المقاعد المتبقية seats_left، وجدول الحجوزات bookings، والقاعدة التي يجب أن تبقى صحيحة دائماً أن السعة تساوي المقاعد المتبقية زائد عدد الحجوزات. ومعها جدول صغير للمهندسين المناوبين On-call، وقاعدته أن يبقى مهندس واحد على الأقل مناوباً.

  • الكتابة القذرة Dirty Write: معاملة تكتب فوق تعديل لم تؤكده معاملة أخرى بعد. وكل قواعد البيانات المعروفة تمنعها في كل المستويات، لأن الكتابة تأخذ قفلاً على الصف حتى نهاية المعاملة.
  • القراءة القذرة Dirty Read: معاملة تقرأ تعديلاً لم يؤكد بعد، ثم تلغيه صاحبته Rollback، فتكون الأولى قد اتخذت قراراً على قيمة لم توجد أبداً.
  • القراءة غير المتكررة Non-repeatable Read: تقرأ المعاملة نفس الصف مرتين فتحصل على قيمتين مختلفتين، لأن معاملة أخرى عدلته وأكدت بين القراءتين.
  • القراءة الوهمية Phantom Read: تنفذ المعاملة نفس الاستعلام بشرط مرتين، فتظهر في المرة الثانية صفوف جديدة أضافتها معاملة أخرى، مثل عد الحجوزات مرتين.
  • التحديث المفقود Lost Update: معاملتان تقرآن نفس القيمة، وتحسب كل منهما قيمة جديدة في الكود، وتكتبانها، فتضيع كتابة الأولى. وهذه هي قصة المقاعد في بداية المقال.
  • انحراف القراءة Read Skew: تقرأ المعاملة جزءاً من البيانات قبل تعديل معاملة أخرى وجزءاً بعده، فترى حالة لم توجد في أي لحظة، مثل تقرير يقرأ المقاعد قبل الحجز ويعد الحجوزات بعده.
  • انحراف الكتابة Write Skew: معاملتان تقرآن نفس البيانات، وكل منهما تتحقق من قاعدة ثم تعدل صفاً مختلفاً، فتبقى كل كتابة صحيحة وحدها وتصبح القاعدة مخالفة بعد الاثنتين، مثل مهندسين مناوبين يطلب كل منهما الخروج من المناوبة في نفس اللحظة لأن «الآخر ما زال مناوباً».

والصورة التالية تبين ثلاثاً من هذه الظواهر كخطوط زمنية، وهي الثلاث التي تسبب أغلب الأخطاء في التطبيقات:

ثلاثة خطوط زمنية لمعاملتين A وB: في التحديث المفقود تقرأ الاثنتان 98 وتكتبان 97 فتضيع كتابة، وفي القراءة الوهمية تعد A الحجوزات مرتين وتضيف B حجزاً بينهما، وفي انحراف الكتابة تقرأ كل منهما أن عدد المناوبين اثنان ثم تخرج كل منهما مهندساً مختلفاً فلا يبقى أحد
كل خطوة في الخطوط الثلاثة صحيحة وحدها، والخطأ يظهر فقط من ترتيبها بين معاملتين

تعريف المعيار ANSI ولماذا انتقده الباحثون؟

عرف معيار SQL سنة 1992 أربعة مستويات بثلاث ظواهر فقط: Read Uncommitted يسمح بالثلاث، وRead Committed يمنع القراءة القذرة، وRepeatable Read يمنع القراءة غير المتكررة أيضاً، وSerializable يمنع القراءة الوهمية كذلك. وفي سنة 1995 نشر باحثون من Microsoft ومن جامعة Massachusetts، منهم Hal Berenson وJim Gray، ورقة بعنوان A Critique of ANSI SQL Isolation Levels بينوا فيها أن هذا التعريف ناقص وغامض في نفس الوقت. فهو لا يذكر الكتابة القذرة ولا التحديث المفقود ولا انحراف القراءة والكتابة، وصياغته بالكلمات تسمح بتفسيرين، وأهم ما فيها أنها عرفت مستوى لم يكن في المعيار أصلاً، وهو عزل اللقطة Snapshot Isolation: كل معاملة تقرأ من لقطة ثابتة للبيانات المؤكدة لحظة بدايتها، وإذا عدلت معاملتان نفس الصف تفشل الثانية. وبينت الورقة أن هذا المستوى يمنع كل الظواهر في جدول ANSI، ومع ذلك ليس قابلاً للتسلسل، لأنه يسمح بانحراف الكتابة.

ولهذه الورقة أثر مباشر عليك اليوم، لأن قواعد البيانات الحديثة بنيت على اللقطات، ثم وضعت كل منها هذه اللقطات تحت أحد أسماء المعيار الأربعة كما رأت. فالاسم Repeatable Read في PostgreSQL يعني عزل اللقطة، وفي MySQL يعني شيئاً آخر كما سوف نرى في التجربة، وهذا هو سبب القاعدة التي سوف تتكرر في المقال: لا تعتمد على اسم المستوى، واعرف ما الذي ينفذه محركك تحت هذا الاسم.

كيف تنفذ قواعد البيانات العزل من الداخل؟

هناك طريقتان كبيرتان لمنع المعاملات من إفساد بعضها: الأقفال Locks، أي أن تنتظر المعاملة حتى تنتهي الأخرى، وتعدد النسخ MVCC، أي أن تحتفظ قاعدة البيانات بأكثر من نسخة من الصف فتقرأ كل معاملة النسخة المناسبة لها دون انتظار. وقد شرحنا كيف يحفظ PostgreSQL النسخ في الصفحات ولماذا يحتاج VACUUM في مقال التخزين من الداخل، وهنا نهتم بما يعنيه ذلك لمستويات العزل.

اللقطة Snapshot في PostgreSQL: ثلاثة أرقام

اللقطة في PostgreSQL ليست نسخة من البيانات، وإنما ثلاثة أشياء صغيرة: أصغر رقم معاملة ما زالت تعمل xmin، والرقم الذي بعد آخر معاملة بدأت xmax، وقائمة المعاملات التي كانت تعمل لحظة أخذ اللقطة. وكل صف يحمل رقم المعاملة التي أنشأته، فإذا كانت تلك المعاملة مؤكدة قبل اللقطة فالصف مرئي، وإذا لم تكن فهو غير مرئي، وبهذه المقارنة البسيطة ترى كل معاملة «عالمها» دون أن توقف غيرها. والفرق بين Read Committed وRepeatable Read في PostgreSQL هو متى تؤخذ اللقطة: في الأول مع كل جملة، وفي الثاني مرة واحدة عند أول جملة في المعاملة. والمخرج التالي من التجربة يبين ذلك بالدالة pg_current_snapshot() وعمود النظام xmin الذي يحمل رقم المعاملة التي كتبت النسخة الحالية من الصف، والجلسة B تعدل المقاعد بين القراءتين:

-- engine=pg level=READ COMMITTED
A> BEGIN ISOLATION LEVEL READ COMMITTED
   A| BEGIN
A> SELECT pg_current_snapshot(), seats_left, xmin FROM events WHERE id = 1
   A| 117007:117007:, 98, 117004
B> UPDATE events SET seats_left = 10 WHERE id = 1
   B| UPDATE 1
A> SELECT pg_current_snapshot(), seats_left, xmin FROM events WHERE id = 1
   A| 117008:117008:, 10, 117007
A> COMMIT
   A| COMMIT
-- engine=pg level=REPEATABLE READ
A> BEGIN ISOLATION LEVEL REPEATABLE READ
   A| BEGIN
A> SELECT pg_current_snapshot(), seats_left, xmin FROM events WHERE id = 1
   A| 117018:117018:, 98, 117015
B> UPDATE events SET seats_left = 10 WHERE id = 1
   B| UPDATE 1
A> SELECT pg_current_snapshot(), seats_left, xmin FROM events WHERE id = 1
   A| 117018:117018:, 98, 117015
A> COMMIT
   A| COMMIT

في المخرج أعلاه لاحظ التالي:

  • اللقطة تكتب بالشكل xmin:xmax:list، والقائمة فارغة هنا لأنه لم تكن هناك معاملة أخرى مفتوحة لحظة أخذها.
  • في Read Committed تغيرت اللقطة في الجملة الثانية من 117007 إلى 117008، لأن معاملة B انتهت بينهما، فرأت A القيمة الجديدة 10 والصف الجديد الذي كتبته المعاملة 117007.
  • في Repeatable Read بقيت اللقطة نفسها 117018:117018: في الجملتين، فرأت A القيمة القديمة 98 والنسخة القديمة من الصف، مع أن النسخة الجديدة موجودة في الجدول ومؤكدة.

قاعدة «أول من يعدل يفوز» First Updater Wins

اللقطة تحل مشكلة القراءة، ولكن ماذا يحدث عندما تحاول معاملة أن تعدل صفاً عدلته معاملة أخرى بعد لقطتها؟ في Read Committed ينتظر PostgreSQL حتى تنتهي الأخرى، ثم يعيد قراءة الصف بنسخته الجديدة ويطبق التعديل عليها، وهذا هو سلوك إعادة التقييم الذي رأيناه في مقال الأقفال. أما في Repeatable Read فلا يمكنه أن يفعل ذلك، لأن المعاملة وعدت بأن ترى لقطتها فقط، فإذا عدل الصف بعد لقطتها رفض التعديل بالخطأ could not serialize access due to concurrent update ورقمه SQLSTATE 40001، وهذا ما يمنع التحديث المفقود. ويسمي توثيق PostgreSQL هذا السلوك بوضوح في صفحة عزل المعاملات، ويقول إن التطبيق الذي يستقبل هذا الخطأ يجب أن يعيد المعاملة كلها من البداية.

كيف يكتشف PostgreSQL انحراف الكتابة؟ تقنية SSI

قاعدة «أول من يعدل يفوز» لا تكفي لانحراف الكتابة، لأن المعاملتين فيه تعدلان صفين مختلفين، فلا يوجد صف واحد يتعارضان عليه. ولذلك يستخدم PostgreSQL منذ الإصدار 9.1 في مستوى Serializable تقنية اسمها العزل التسلسلي باللقطات Serializable Snapshot Isolation واختصاراً SSI، وقد شرح Dan Ports وKevin Grittner تنفيذها في ورقة Serializable Snapshot Isolation in PostgreSQL سنة 2012. والفكرة أن كل معاملة تسجل ما قرأته بأقفال خاصة اسمها SIRead، وهذه الأقفال لا تمنع أحداً من شيء، وإنما تترك أثراً: إذا كتبت معاملة أخرى بعد ذلك شيئاً كانت هذه قد قرأته، سجل PostgreSQL علاقة بينهما اسمها اعتماد قراءة وكتابة rw-dependency.

وقد أثبت الباحثون أن كل حالة غير قابلة للتسلسل تحت عزل اللقطة يكون فيها نمط محدد: معاملة في الوسط لها اعتماد وارد من معاملة واعتماد صادر إلى أخرى، ويسمى هذا البناء الخطر Dangerous Structure. فإذا ظهر هذا النمط ألغى PostgreSQL إحدى المعاملات بنفس رمز الخطأ 40001 ولكن برسالة مختلفة: could not serialize access due to read/write dependencies among transactions. ولاحظ أن الاكتشاف حذر، أي أنه قد يلغي معاملة في حالة كانت ستنتهي بنتيجة صحيحة، وهذا ما يسمى الإيجابيات الكاذبة False Positives، وسوف نرى واحدة منها في التجربة. والصورة التالية تجمع الآليات الثلاث في PostgreSQL وما يقابلها في MySQL:

عمودان: في PostgreSQL لقطة لكل جملة في Read Committed، ولقطة لكل المعاملة مع رفض التعديل على نسخة قديمة في Repeatable Read، ثم أقفال SIRead وكشف الاعتماد الدائري في Serializable. وفي InnoDB لقطة للقراءة العادية وقراءة النسخة الأحدث مع أقفال Next-Key في التعديل وFOR UPDATE، وفي Serializable تتحول كل قراءة إلى FOR SHARE
PostgreSQL يرفض بعد التعارض، وInnoDB يجعل المعاملات تنتظر بالأقفال، والفرق يظهر في نوع الخطأ الذي يستقبله كودك

InnoDB في MySQL: لقطة للقراءة وأقفال للكتابة

يستخدم InnoDB أيضاً تعدد النسخ للقراءة العادية، ويسميها توثيقه القراءة المتسقة Consistent Read، ففي Repeatable Read، وهو المستوى الافتراضي، تؤخذ اللقطة عند أول قراءة في المعاملة وتبقى ثابتة. ولكن الفرق المهم أن جمل التعديل UPDATE وDELETE وجمل القراءة مع القفل SELECT ... FOR UPDATE لا تقرأ من اللقطة، وإنما من أحدث نسخة مؤكدة من الصف، وتأخذ عليه قفلاً. ويسمي البعض هذا القراءة الحالية Current Read. ولمنع القراءة الوهمية في القراءات التي تأخذ أقفالاً يستخدم InnoDB أقفال الفجوات Gap Locks وأقفال Next-Key، وهي أقفال على المسافة بين قيم الفهرس تمنع إضافة صفوف جديدة فيها، كما يشرح توثيق Phantom Rows.

وهذا التصميم له نتيجتان سوف تراهما في التجربة: الأولى أن InnoDB لا يطبق قاعدة «أول من يعدل يفوز»، فإذا عدلت معاملتك صفاً عدلته معاملة أخرى بعد لقطتك فلن تستقبل خطأ، وإنما يعدل التعديل النسخة الأحدث بصمت، أي أن Repeatable Read في MySQL لا يمنع التحديث المفقود. والثانية أن Serializable في InnoDB، كما يقول توثيق مستويات العزل، هو Repeatable Read مع تحويل كل قراءة عادية إلى SELECT ... FOR SHARE عندما يكون autocommit معطلاً، أي أنه يمنع الظواهر بالانتظار والجمود Deadlock وليس بالكشف بعد التعارض كما في PostgreSQL.

💡
في ديسمبر 2023 نشر Kyle Kingsbury وPeter Alvaro تحليلاً من سلسلة Jepsen بعنوان MySQL 8.0.34، اختبرا فيه مستويات العزل على سيرفر واحد سليم دون أي أعطال في الشبكة، ووجدا أن Repeatable Read في MySQL يسمح بالتحديث المفقود وبانحراف القراءة وبقراءات غير متكررة داخل المعاملة نفسها، وكتبا أنه لا يحقق عزل اللقطة Snapshot Isolation، مع أن بعض المراجع تصفه بذلك. والدرس أن نفس الاسم Repeatable Read يعني في PostgreSQL ضماناً أقوى بكثير منه في MySQL، فإذا نقلت تطبيقاً بين المحركين فراجع كل معاملة تقرأ ثم تكتب.

تجربة عملية Lab: سبع ظواهر على محركين

الكلام السابق كله يبقى ادعاءات حتى تراه، لذلك سوف نشغل كل ظاهرة على كل مستوى في المحركين، ونقرأ ما حدث. ونحتاج لذلك جلستين تعملان في نفس الوقت وترسل لكل منهما خطوتها بترتيب محدد. والطريقة اليدوية أن تفتح نافذتين، ولكن حتى تكون المخرجات قابلة للتكرار كتبنا سكربتاً صغيراً بلغة Python يفتح اتصالين، ويرسل كل خطوة إلى جلستها، وينتظر ثانية واحدة، فإذا لم ترد الجلسة كتب أنها تنتظر، ثم يطبع ردها عندما يصل. وكل الملفات في مجلد lab مع المقال.

التجهيز Setup

نشغل PostgreSQL 18.6 وMySQL 8.4 على شبكة Docker واحدة، وكلاهما فيه قاعدة بيانات اسمها tickets:

docker network create sw2-net
docker run -d --name sw2-pg --network sw2-net -e POSTGRES_PASSWORD=lab -e POSTGRES_DB=tickets postgres:18.6
docker run -d --name sw2-mysql --network sw2-net -e MYSQL_ROOT_PASSWORD=lab -e MYSQL_DATABASE=tickets mysql:8.4
docker build -t sw2-tx:lab .

والملف Dockerfile فيه مكتبتا الاتصال فقط:

FROM python:3.13.18-slim
RUN pip install --no-cache-dir "psycopg[binary]==3.3.6" "PyMySQL[rsa]==1.1.2"
WORKDIR /lab

والجداول نفسها في المحركين، والملف schema.sql يحذفها وينشئها من جديد قبل كل تجربة حتى تبدأ كل تجربة من نفس الحالة: فعالية سعتها 100 فيها حجزان و98 مقعداً متبقياً، ومهندسان مناوبان:

-- the same schema on PostgreSQL and MySQL (InnoDB)
DROP TABLE IF EXISTS bookings;
DROP TABLE IF EXISTS events;
DROP TABLE IF EXISTS oncall;

CREATE TABLE events (
  id         INT PRIMARY KEY,
  name       VARCHAR(80) NOT NULL,
  capacity   INT NOT NULL,
  seats_left INT NOT NULL,
  version    INT NOT NULL DEFAULT 0
);
CREATE TABLE bookings (
  id       INT PRIMARY KEY,
  event_id INT NOT NULL REFERENCES events(id),
  email    VARCHAR(120) NOT NULL
);
CREATE INDEX bookings_event ON bookings(event_id);
CREATE TABLE oncall (
  engineer VARCHAR(40) PRIMARY KEY,
  on_call  BOOLEAN NOT NULL
);

-- the invariant: capacity = seats_left + number of bookings
INSERT INTO events VALUES (1, 'DevOps Meetup', 100, 98, 0), (2, 'Data Night', 50, 50, 0);
INSERT INTO bookings VALUES (1, 1, '[email protected]'), (2, 1, '[email protected]');
-- the rule: at least one engineer is on call
INSERT INTO oncall VALUES ('alice', TRUE), ('bob', TRUE);

والسكربت two.py يقرأ ملف خطوات، كل سطر فيه اسم الجلسة ثم الجملة، ويستبدل الكلمة BEGIN بطريقة بدء المعاملة في كل محرك بالمستوى المطلوب، لأن PostgreSQL يقبل BEGIN ISOLATION LEVEL ... في جملة واحدة، أما MySQL فيحتاج SET TRANSACTION ISOLATION LEVEL ... ثم START TRANSACTION:

"""Two sessions, one script.
usage: python two.py pg|my LEVEL scenario.txt
Each scenario line is "A: SQL" or "B: SQL". "BEGIN" is replaced by the engine's way to start a
transaction at LEVEL. A step that does not answer within 1 s is shown as waiting; its answer is
printed later, when it arrives."""
import queue, sys, threading, time

ENGINE, LEVEL, SCRIPT = sys.argv[1], sys.argv[2].upper().replace("_", " "), sys.argv[3]


def connect():
    if ENGINE == "pg":
        import psycopg
        return psycopg.connect("host=sw2-pg dbname=tickets user=postgres password=lab", autocommit=True)
    import pymysql
    return pymysql.connect(host="sw2-mysql", user="root", password="lab", database="tickets", autocommit=True)


def begin_sql():
    if ENGINE == "pg":
        return [f"BEGIN ISOLATION LEVEL {LEVEL}"]
    return [f"SET TRANSACTION ISOLATION LEVEL {LEVEL}", "START TRANSACTION"]


def run(cur, sql):
    cur.execute(sql)
    if cur.description:
        rows = cur.fetchall()
        return " | ".join(", ".join(str(v) for v in r) for r in rows) or "(no rows)"
    if ENGINE == "pg":
        return cur.statusmessage          # what the server answered, e.g. ROLLBACK for a failed COMMIT
    verb = sql.split()[0].upper()
    return f"{verb} {cur.rowcount}" if verb in ("UPDATE", "INSERT", "DELETE") else verb


def error_text(e):
    code = getattr(e, "sqlstate", None) or (e.args[0] if e.args else "")
    msg = getattr(getattr(e, "diag", None), "message_primary", None) or (e.args[1] if len(e.args) > 1 else str(e))
    return f"ERROR {code}: {msg}"


class Session(threading.Thread):
    def __init__(self, name):
        super().__init__(daemon=True)
        self.name, self.inbox, self.outbox = name, queue.Queue(), queue.Queue()
        self.conn = connect()
        self.start()

    def run(self):
        cur = self.conn.cursor()
        while True:
            sqls = self.inbox.get()
            for sql in sqls:
                try:
                    self.outbox.put(run(cur, sql))
                except Exception as e:  # show the database error as the session saw it
                    self.outbox.put(error_text(e))
                    break


sessions = {"A": Session("A"), "B": Session("B")}
waiting = {"A": 0, "B": 0}


def drain(name, wait):
    deadline = time.time() + wait
    while waiting[name]:
        try:
            out = sessions[name].outbox.get(timeout=max(0.0, deadline - time.time()))
        except queue.Empty:
            return
        waiting[name] -= 1
        print(f"   {name}| {out}")


print(f"-- engine={ENGINE} level={LEVEL}")
for line in open(SCRIPT, encoding="utf-8"):
    line = line.strip()
    if not line or line.startswith("#"):
        continue
    name, sql = line.split(":", 1)
    sql = sql.strip().rstrip(";")
    sqls = begin_sql() if sql.upper() == "BEGIN" else [sql]
    for other in sessions:          # anything that arrived meanwhile from the other session
        if other != name:
            drain(other, 0.3)
    print(f"{name}> {'; '.join(sqls)}")
    waiting[name] += len(sqls)
    sessions[name].inbox.put(sqls)
    drain(name, 1.0)
    if waiting[name]:
        print(f"   {name}| ... waiting")
for name in sessions:
    drain(name, 3.0)
    if waiting[name]:
        print(f"   {name}| ... still waiting")

والسكربت run.sh يعيد الجداول ثم يشغل ملف خطوات واحداً، وmatrix.sh يشغل كل الملفات على كل المستويات ويحفظ كل مخرج في مجلد out:

#!/bin/sh
# usage: sh run.sh pg|my "LEVEL" scenario   (resets the tables, then plays the scenario)
if [ "$1" = pg ]; then docker exec -i sw2-pg psql -q -U postgres -d tickets < schema.sql > /dev/null
else docker exec -i sw2-mysql mysql -uroot -plab tickets < schema.sql 2> /dev/null; fi
docker run --rm --network sw2-net -v "$PWD:/lab" sw2-tx:lab python two.py "$1" "$2" "scenarios/$3.txt"
#!/bin/sh
# plays every scenario at every level on both engines; one output file per run in out/
mkdir -p out
for s in dirty-read nonrepeatable-read read-skew phantom phantom-write lost-update write-skew; do
  for l in "READ COMMITTED" "REPEATABLE READ" "SERIALIZABLE"; do
    sh run.sh pg "$l" $s > "out/pg-$s-$(echo $l | tr ' A-Z' '-a-z').txt" 2>&1
  done
  for l in "READ UNCOMMITTED" "READ COMMITTED" "REPEATABLE READ" "SERIALIZABLE"; do
    sh run.sh my "$l" $s > "out/my-$s-$(echo $l | tr ' A-Z' '-a-z').txt" 2>&1
  done
done

وعند أول تشغيل لهذه السكربتات على MySQL ظهرت المشكلة التالية في كل التجارب:

RuntimeError: 'cryptography' package is required for sha256_password or caching_sha2_password auth methods

والسبب أن MySQL 8.4 يستخدم افتراضياً طريقة المصادقة caching_sha2_password، وعندما يكون الاتصال دون TLS كما في شبكة Docker الداخلية يحتاج الكلاينت أن يشفر كلمة المرور بالمفتاح العام للسيرفر، ومكتبة PyMySQL لا تفعل ذلك إلا مع مكتبة cryptography، كما يذكر توثيق تثبيتها. والحل أن تثبتها بالشكل PyMySQL[rsa] كما في ملف Dockerfile أعلاه، ولا تقم بتغيير طريقة المصادقة في السيرفر إلى الطريقة القديمة لتتجاوز الخطأ، لأنها أضعف ومعطلة افتراضياً في الإصدارات الحديثة.

التحديث المفقود Lost Update: نفس الكود على أربعة مستويات

نبدأ بأهم تجربة، وهي الكود الذي يكتبه كل تطبيق تقريباً: يقرأ عدد المقاعد، ويحسب في الكود القيمة الجديدة، ويكتبها، ويضيف الحجز. وملف الخطوات lost-update.txt يجعل الجلستين تقرآن قبل أن تكتب أي منهما، وهذا يحدث في الإنتاج عندما يصل طلبان في نفس اللحظة:

# the read-modify-write that every app writes: read seats_left, compute in code, write it back
A: BEGIN
A: SELECT seats_left FROM events WHERE id = 1
B: BEGIN
B: SELECT seats_left FROM events WHERE id = 1
A: UPDATE events SET seats_left = 97 WHERE id = 1
A: INSERT INTO bookings VALUES (3, 1, '[email protected]')
A: COMMIT
B: UPDATE events SET seats_left = 97 WHERE id = 1
B: INSERT INTO bookings VALUES (4, 1, '[email protected]')
B: COMMIT
A: SELECT seats_left, (SELECT COUNT(*) FROM bookings WHERE event_id = 1) AS booked FROM events WHERE id = 1

والنتيجة في PostgreSQL بالمستوى الافتراضي Read Committed:

-- engine=pg level=READ COMMITTED
A> BEGIN ISOLATION LEVEL READ COMMITTED
   A| BEGIN
A> SELECT seats_left FROM events WHERE id = 1
   A| 98
B> BEGIN ISOLATION LEVEL READ COMMITTED
   B| BEGIN
B> SELECT seats_left FROM events WHERE id = 1
   B| 98
A> UPDATE events SET seats_left = 97 WHERE id = 1
   A| UPDATE 1
A> INSERT INTO bookings VALUES (3, 1, '[email protected]')
   A| INSERT 0 1
A> COMMIT
   A| COMMIT
B> UPDATE events SET seats_left = 97 WHERE id = 1
   B| UPDATE 1
B> INSERT INTO bookings VALUES (4, 1, '[email protected]')
   B| INSERT 0 1
B> COMMIT
   B| COMMIT
A> SELECT seats_left, (SELECT COUNT(*) FROM bookings WHERE event_id = 1) AS booked FROM events WHERE id = 1
   A| 97, 4

نجحت كل الجمل، والسطر الأخير يقول إن المقاعد المتبقية 97 والحجوزات 4، أي أن المجموع 101 لفعالية سعتها 100، فقد ضاع إنقاص B لأنها كتبت 97 التي حسبتها من قراءة قديمة فوق 97 التي كتبتها A. ولاحظ أن B لم تنتظر شيئاً، لأن A كانت قد أكدت قبل أن تعدل B. والآن نفس الخطوات في Repeatable Read:

-- engine=pg level=REPEATABLE READ
A> BEGIN ISOLATION LEVEL REPEATABLE READ
   A| BEGIN
A> SELECT seats_left FROM events WHERE id = 1
   A| 98
B> BEGIN ISOLATION LEVEL REPEATABLE READ
   B| BEGIN
B> SELECT seats_left FROM events WHERE id = 1
   B| 98
A> UPDATE events SET seats_left = 97 WHERE id = 1
   A| UPDATE 1
A> INSERT INTO bookings VALUES (3, 1, '[email protected]')
   A| INSERT 0 1
A> COMMIT
   A| COMMIT
B> UPDATE events SET seats_left = 97 WHERE id = 1
   B| ERROR 40001: could not serialize access due to concurrent update
B> INSERT INTO bookings VALUES (4, 1, '[email protected]')
   B| ERROR 25P02: current transaction is aborted, commands ignored until end of transaction block
B> COMMIT
   B| ROLLBACK
A> SELECT seats_left, (SELECT COUNT(*) FROM bookings WHERE event_id = 1) AS booked FROM events WHERE id = 1
   A| 97, 3

في المخرج أعلاه لاحظ التالي:

  • عندما حاولت B أن تعدل الصف رفض PostgreSQL بالخطأ 40001، لأن الصف تغير بعد لقطتها، وهذه قاعدة «أول من يعدل يفوز».
  • بعد الخطأ رفضت المعاملة أي جملة أخرى بالخطأ 25P02 حتى تنتهي، وكتبنا COMMIT فرد السيرفر بالكلمة ROLLBACK.
  • النتيجة النهائية 97 مقعداً و3 حجوزات، أي أن القاعدة صحيحة، وصاحب الطلب B يحتاج أن يعيد المحاولة، وعند إعادتها سوف يقرأ 97.

وكان المستوى Serializable في PostgreSQL بنفس النتيجة تماماً ونفس رسالة الخطأ. أما MySQL فبالمستوى الافتراضي فيه Repeatable Read:

-- engine=my level=REPEATABLE READ
A> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION
   A| SET
   A| START
A> SELECT seats_left FROM events WHERE id = 1
   A| 98
B> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION
   B| SET
   B| START
B> SELECT seats_left FROM events WHERE id = 1
   B| 98
A> UPDATE events SET seats_left = 97 WHERE id = 1
   A| UPDATE 1
A> INSERT INTO bookings VALUES (3, 1, '[email protected]')
   A| INSERT 1
A> COMMIT
   A| COMMIT
B> UPDATE events SET seats_left = 97 WHERE id = 1
   B| UPDATE 0
B> INSERT INTO bookings VALUES (4, 1, '[email protected]')
   B| INSERT 1
B> COMMIT
   B| COMMIT
A> SELECT seats_left, (SELECT COUNT(*) FROM bookings WHERE event_id = 1) AS booked FROM events WHERE id = 1
   A| 97, 4

لم يظهر أي خطأ، والنتيجة 97 مقعداً و4 حجوزات، أي أن التحديث المفقود حدث في المستوى الذي يسميه MySQL Repeatable Read، وهو ما وجده تحليل Jepsen. ولاحظ أيضاً الرد UPDATE 0 على تعديل B، وهذا ليس لأن الجملة لم تجد الصف، وإنما لأن MySQL يرد افتراضياً بعدد الصفوف التي تغيرت قيمتها فعلاً، والقيمة كانت 97 قبل التعديل فلم يتغير شيء، وإذا أردت عدد الصفوف التي طابقت الشرط فاتصل بالخيار CLIENT_FOUND_ROWS. وهذا تفصيل مهم إذا كان كودك يعتمد على هذا العدد ليعرف هل نجح التعديل. والآن MySQL بالمستوى Serializable:

-- engine=my level=SERIALIZABLE
A> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; START TRANSACTION
   A| SET
   A| START
A> SELECT seats_left FROM events WHERE id = 1
   A| 98
B> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; START TRANSACTION
   B| SET
   B| START
B> SELECT seats_left FROM events WHERE id = 1
   B| 98
A> UPDATE events SET seats_left = 97 WHERE id = 1
   A| ... waiting
A> INSERT INTO bookings VALUES (3, 1, '[email protected]')
   A| ... waiting
A> COMMIT
   A| ... waiting
B> UPDATE events SET seats_left = 97 WHERE id = 1
   B| ERROR 1213: Deadlock found when trying to get lock; try restarting transaction
   A| UPDATE 1
   A| INSERT 1
   A| COMMIT
B> INSERT INTO bookings VALUES (4, 1, '[email protected]')
   B| INSERT 1
B> COMMIT
   B| COMMIT
A> SELECT seats_left, (SELECT COUNT(*) FROM bookings WHERE event_id = 1) AS booked FROM events WHERE id = 1
   A| 97, 4

هنا تحولت القراءة العادية في الجلستين إلى قراءة مع قفل مشترك Shared Lock، فعندما حاولت A أن تعدل انتظرت قفل B، وعندما حاولت B أن تعدل أصبحت كل منهما تنتظر الأخرى، فاكتشف InnoDB الجمود وألغى معاملة B بالخطأ 1213، ثم أكملت A. والنتيجة صحيحة، ولكن لاحظ ما حدث بعد ذلك: أرسلت B الإضافة INSERT فنجحت، لأن MySQL بعد الجمود يلغي المعاملة كلها ويعيد الجلسة إلى وضع autocommit، فأضيف الحجز الرابع خارج أي معاملة والمجموع 101 مرة أخرى. وهذا ليس خطأ في المستوى وإنما في كودنا، فقد أكمل الخطوات بعد الخطأ كأن شيئاً لم يحدث، وهو نفس الخطأ الذي يقع فيه كود التطبيق عندما يلتقط الاستثناء Exception ويتابع. والقاعدة: بعد أي خطأ في معاملة توقف وأعد المعاملة كلها من البداية، ولا تكمل الخطوات الباقية أبداً.

القراءة الوهمية في MySQL: لا تراها، ولكنك تعدلها

في تجربة القراءة الوهمية العادية، أي العد مرتين وB تضيف حجزاً بينهما، منع Repeatable Read الظاهرة في المحركين، فرأت A العدد 2 في المرتين. ولكن ماذا لو عدلت A الحجوزات بعد إضافة B؟ ملف الخطوات phantom-write.txt:

# A reads the bookings, B adds one, then A updates "all the bookings of event 1"
A: BEGIN
A: SELECT id, email FROM bookings WHERE event_id = 1
B: INSERT INTO bookings VALUES (3, 1, '[email protected]')
A: UPDATE bookings SET email = UPPER(email) WHERE event_id = 1
A: SELECT id, email FROM bookings WHERE event_id = 1
A: COMMIT

والنتيجة في MySQL بالمستوى Repeatable Read:

-- engine=my level=REPEATABLE READ
A> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION
   A| SET
   A| START
A> SELECT id, email FROM bookings WHERE event_id = 1
   A| 1, [email protected] | 2, [email protected]
B> INSERT INTO bookings VALUES (3, 1, '[email protected]')
   B| INSERT 1
A> UPDATE bookings SET email = UPPER(email) WHERE event_id = 1
   A| UPDATE 3
A> SELECT id, email FROM bookings WHERE event_id = 1
   A| 1, [email protected] | 2, [email protected] | 3, [email protected]
A> COMMIT
   A| COMMIT

قرأت A حجزين، ثم عدلت «كل حجوزات الفعالية 1» فعدل التعديل ثلاثة صفوف، ثم قرأت مرة أخرى فظهر الحجز الثالث الذي لم يكن في لقطتها. والسبب أن التعديل يقرأ أحدث نسخة وليس اللقطة، وبعد أن عدلت A الصف أصبح الصف من كتابتها هي فظهر في لقطتها. أما PostgreSQL بنفس المستوى:

-- engine=pg level=REPEATABLE READ
A> BEGIN ISOLATION LEVEL REPEATABLE READ
   A| BEGIN
A> SELECT id, email FROM bookings WHERE event_id = 1
   A| 1, [email protected] | 2, [email protected]
B> INSERT INTO bookings VALUES (3, 1, '[email protected]')
   B| INSERT 0 1
A> UPDATE bookings SET email = UPPER(email) WHERE event_id = 1
   A| UPDATE 2
A> SELECT id, email FROM bookings WHERE event_id = 1
   A| 1, [email protected] | 2, [email protected]
A> COMMIT
   A| COMMIT

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

انحراف الكتابة Write Skew: المناوبة التي لم يبق فيها أحد

ملف الخطوات write-skew.txt: كل جلسة تعد المناوبين، فتجد اثنين، فتخرج مهندساً مختلفاً:

# rule: at least one engineer stays on call. Both check the rule, then each takes himself off
A: BEGIN
A: SELECT COUNT(*) FROM oncall WHERE on_call
B: BEGIN
B: SELECT COUNT(*) FROM oncall WHERE on_call
A: UPDATE oncall SET on_call = FALSE WHERE engineer = 'alice'
B: UPDATE oncall SET on_call = FALSE WHERE engineer = 'bob'
A: COMMIT
B: COMMIT
A: SELECT engineer, on_call FROM oncall ORDER BY engineer

في Read Committed وRepeatable Read في المحركين انتهت التجربة دون أي خطأ وكلا المهندسين خارج المناوبة. وفي PostgreSQL بالمستوى Serializable:

-- engine=pg level=SERIALIZABLE
A> BEGIN ISOLATION LEVEL SERIALIZABLE
   A| BEGIN
A> SELECT COUNT(*) FROM oncall WHERE on_call
   A| 2
B> BEGIN ISOLATION LEVEL SERIALIZABLE
   B| BEGIN
B> SELECT COUNT(*) FROM oncall WHERE on_call
   B| 2
A> UPDATE oncall SET on_call = FALSE WHERE engineer = 'alice'
   A| UPDATE 1
B> UPDATE oncall SET on_call = FALSE WHERE engineer = 'bob'
   B| UPDATE 1
A> COMMIT
   A| COMMIT
B> COMMIT
   B| ERROR 40001: could not serialize access due to read/write dependencies among transactions
A> SELECT engineer, on_call FROM oncall ORDER BY engineer
   A| alice, False | bob, True

نجحت كل الجمل حتى التأكيد، ثم رفض PostgreSQL تأكيد B بالخطأ 40001 مع الرسالة الخاصة بتقنية SSI، لأنه وجد أن A قرأت ما كتبته B وأن B قرأت ما كتبته A، ولا يوجد ترتيب تسلسلي يعطي هذه النتيجة. ولاحظ أن الخطأ جاء عند COMMIT وليس عند التعديل، لذلك يجب أن يكون التأكيد نفسه داخل منطقة إعادة المحاولة في كودك. أما MySQL بنفس المستوى:

-- engine=my level=SERIALIZABLE
A> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; START TRANSACTION
   A| SET
   A| START
A> SELECT COUNT(*) FROM oncall WHERE on_call
   A| 2
B> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; START TRANSACTION
   B| SET
   B| START
B> SELECT COUNT(*) FROM oncall WHERE on_call
   B| 2
A> UPDATE oncall SET on_call = FALSE WHERE engineer = 'alice'
   A| ... waiting
B> UPDATE oncall SET on_call = FALSE WHERE engineer = 'bob'
   B| ERROR 1213: Deadlock found when trying to get lock; try restarting transaction
A> COMMIT
   A| UPDATE 1
   A| COMMIT
B> COMMIT
   B| COMMIT
A> SELECT engineer, on_call FROM oncall ORDER BY engineer
   A| alice, 0 | bob, 1

تحولت القراءة إلى قفل مشترك على كل صفوف الجدول، لأن عمود on_call ليس عليه فهرس فقرأ InnoDB الجدول كله، فانتظرت A، ثم وقع الجمود عند تعديل B وألغيت. والنتيجة صحيحة في المحركين، ولكن الطريق مختلف: PostgreSQL ترك المعاملتين تعملان ثم رفض واحدة في النهاية، وMySQL أوقف إحداهما في الوسط ثم ألغى الأخرى بالجمود.

النتيجة في جدول واحد: ماذا يمنع كل مستوى فعلاً؟

شغلنا الملفات السبعة على ثلاثة مستويات في PostgreSQL وأربعة في MySQL، أي 49 تجربة، وكل مخرج موجود في مجلد lab/out، والجدول التالي يلخصها. والكلمة «يحدث» تعني أن الظاهرة حدثت دون أي خطأ، و«خطأ» تعني أن قاعدة البيانات منعتها برفض إحدى المعاملات، و«انتظار» تعني أنها منعتها بجعل إحدى الجلستين تنتظر الأخرى، و«ممنوع» تعني أنها لم تحدث أصلاً:

الظاهرةPG Read CommittedPG Repeatable ReadPG SerializableMySQL Read UncommittedMySQL Read CommittedMySQL Repeatable ReadMySQL Serializable
القراءة القذرة Dirty Readممنوعممنوعممنوعيحدثممنوعممنوعانتظار
القراءة غير المتكررةيحدثممنوعممنوعيحدثيحدثممنوعانتظار
انحراف القراءة Read Skewيحدثممنوعممنوعيحدثيحدثممنوعانتظار
القراءة الوهمية Phantomيحدثممنوعممنوعيحدثيحدثممنوعانتظار
التعديل يرى صفاً وهمياًيحدثممنوعممنوعيحدثيحدثيحدثانتظار
التحديث المفقود Lost Updateيحدثخطأ 40001خطأ 40001يحدثيحدثيحدثجمود 1213
انحراف الكتابة Write Skewيحدثيحدثخطأ 40001يحدثيحدثيحدثجمود 1213

ومن هذا الجدول ثلاث ملاحظات تستحق أن تحفظها. الأولى أن PostgreSQL لا يسمح بالقراءة القذرة في أي مستوى، فطلب Read Uncommitted فيه يعطيك Read Committed. والثانية أن Repeatable Read في PostgreSQL يمنع كل ظواهر جدول ANSI ومعها التحديث المفقود، ويبقى انحراف الكتابة وحده، وهذا هو عزل اللقطة كما عرفته ورقة 1995. والثالثة أن Repeatable Read في MySQL يحمي القراءة فقط، أما أي معاملة تقرأ ثم تكتب بناءً على ما قرأته فهي في MySQL بنفس الخطر سواءً كانت في Read Committed أو Repeatable Read. والصورة التالية ترسم الجدول نفسه:

مصفوفة ملونة: صفوف للظواهر السبع وأعمدة للمستويات في المحركين، الخلايا الحمراء حيث حدثت الظاهرة، والخضراء حيث منعت، والبرتقالية حيث منعت بخطأ أو جمود يحتاج إعادة محاولة، والرمادية حيث منعت بالانتظار
نتيجة 49 تجربة حقيقية: نفس الاسم Repeatable Read يمنع في PostgreSQL ما لا يمنعه في MySQL

مشكلة ظهرت في التجربة: خطأ تسلسل Serialization Failure دون أي تعارض

بعد أن رأينا Serializable يمنع كل شيء، جربنا ما يبدو أنه لا يحتاج حماية أصلاً: حجزان لفعاليتين مختلفتين، كل جلسة تعد حجوزات فعاليتها ثم تضيف حجزاً لها، أي أنه لا توجد قراءة ولا كتابة مشتركة بين الجلستين:

# two bookings for two DIFFERENT events, both SERIALIZABLE; there is no real conflict
A: BEGIN
A: SELECT COUNT(*) FROM bookings WHERE event_id = 1
B: BEGIN
B: SELECT COUNT(*) FROM bookings WHERE event_id = 2
A: SELECT locktype, relation::regclass, page, tuple, pid = pg_backend_pid() AS mine FROM pg_locks WHERE mode = 'SIReadLock' ORDER BY 5 DESC, 1
A: INSERT INTO bookings VALUES (3, 1, '[email protected]')
B: INSERT INTO bookings VALUES (4, 2, '[email protected]')
A: COMMIT
B: COMMIT
-- engine=pg level=SERIALIZABLE
A> BEGIN ISOLATION LEVEL SERIALIZABLE
   A| BEGIN
A> SELECT COUNT(*) FROM bookings WHERE event_id = 1
   A| 2
B> BEGIN ISOLATION LEVEL SERIALIZABLE
   B| BEGIN
B> SELECT COUNT(*) FROM bookings WHERE event_id = 2
   B| 0
A> SELECT locktype, relation::regclass, page, tuple, pid = pg_backend_pid() AS mine FROM pg_locks WHERE mode = 'SIReadLock' ORDER BY 5 DESC, 1
   A| page, bookings_event, 1, None, True | tuple, bookings, 0, 2, True | tuple, bookings, 0, 1, True | page, bookings_event, 1, None, False
A> INSERT INTO bookings VALUES (3, 1, '[email protected]')
   A| INSERT 0 1
B> INSERT INTO bookings VALUES (4, 2, '[email protected]')
   B| INSERT 0 1
A> COMMIT
   A| COMMIT
B> COMMIT
   B| ERROR 40001: could not serialize access due to read/write dependencies among transactions

رفض PostgreSQL تأكيد B مع أنه لا توجد ظاهرة حقيقية هنا، فلو نفذت المعاملتان واحدة بعد الأخرى لكانت النتيجة نفسها. والسبب في سطر pg_locks: أقفال SIRead لا تسجل «حجوزات الفعالية 1» كشرط منطقي، وإنما تسجل ما لمسه الاستعلام فعلاً من صفوف وصفحات، وكلا الاستعلامين قرأ نفس الصفحة رقم 1 من الفهرس bookings_event، لأن الجدول صغير وكل قيم الفهرس في صفحة واحدة. فعندما أضافت A حجزاً إلى تلك الصفحة أصبح لـ B اعتماد على A، وعندما أضافت B حجزها أصبح لـ A اعتماد على B، وظهرت الدائرة.

وماذا لو كان الجدول كبيراً؟ أضفنا 198 فعالية أخرى و10000 حجز بالملف ssi-big-table.sql، أي خمسين حجزاً لكل فعالية، وأعدنا نفس التجربة على الفعاليتين 100 و150:

-- engine=pg level=SERIALIZABLE
A> BEGIN ISOLATION LEVEL SERIALIZABLE
   A| BEGIN
A> SELECT COUNT(*) FROM bookings WHERE event_id = 100
   A| 50
B> BEGIN ISOLATION LEVEL SERIALIZABLE
   B| BEGIN
B> SELECT COUNT(*) FROM bookings WHERE event_id = 150
   B| 50
A> SELECT locktype, relation::regclass, page, tuple, pid = pg_backend_pid() AS mine FROM pg_locks WHERE mode = 'SIReadLock' ORDER BY 5 DESC, 1, 2, 3
   A| page, bookings, 36, None, True | page, bookings_event, 6, None, True | page, bookings, 54, None, False | page, bookings, 55, None, False | page, bookings_event, 8, None, False
A> INSERT INTO bookings VALUES (900001, 100, '[email protected]')
   A| INSERT 0 1
B> INSERT INTO bookings VALUES (900002, 150, '[email protected]')
   B| INSERT 0 1
A> COMMIT
   A| COMMIT
B> COMMIT
   B| COMMIT

هذه المرة قرأت كل جلسة صفحة مختلفة من الفهرس، 6 و8، وصفحات مختلفة من الجدول، والإضافة الجديدة ذهبت إلى صفحات لم تقرأها الجلسة الأخرى، فأكدت المعاملتان دون خطأ. والدرس العملي من التجربتين أن أخطاء التسلسل في Serializable ليست دليلاً على خطأ في منطقك، وإنما جزء طبيعي من عمل هذا المستوى، وتزيد مع الجداول الصغيرة والاستعلامات التي تقرأ الجدول كله Sequential Scan، لأن القفل حينها يرتفع إلى مستوى الجدول كله، ويذكر توثيق PostgreSQL أن الفهارس المناسبة تقلل هذه الأخطاء. لذلك لا تكتب كوداً يعامل الخطأ 40001 كحالة نادرة، فكل معاملة في Serializable يجب أن تكون داخل حلقة إعادة محاولة.

رسمان لفهرس bookings_event: في الأول صفحة واحدة تقرؤها الجلستان A وB وتكتبان فيها فيظهر سهمان متعاكسان ودائرة حمراء وخطأ 40001، وفي الثاني فهرس من عدة صفحات تقرأ A الصفحة 6 وB الصفحة 8 فلا يوجد تقاطع وتؤكد المعاملتان
أقفال SIRead تسجل الصفحات التي قرأتها المعاملة، فإذا تشاركت معاملتان صفحة واحدة ظهر تعارض لا يوجد في البيانات نفسها

من الجلستين إلى كود التطبيق: 1600 محاولة على 1000 مقعد

التجارب السابقة تبين ما يحدث بين جلستين بترتيب حددناه نحن، أما في الإنتاج فالترتيب يحدده التوقيت وعدد المستخدمين، والسؤال الذي يهم صاحب المشروع ليس «هل يمكن أن يحدث» وإنما «كم مرة يحدث، وكم يكلف منعه». لذلك كتبنا السكربت bench.py: فعالية فيها 1000 مقعد، و16 عملية متوازية Workers كل منها تحاول 100 حجز، أي 1600 محاولة على 1000 مقعد، والنتيجة الصحيحة الوحيدة أن ينتهي الاختبار بـ 1000 حجز بالضبط وصفر مقاعد. ويجرب السكربت ست طرق، وكلها تعمل على نفس الجداول:

  • الطريقة البسيطة في Read Committed naive-rc: قراءة المقاعد، ثم كتابة القيمة المحسوبة في الكود، كما في بداية المقال.
  • نفس الكود في Repeatable Read naive-rr وفي Serializable serializable: لم نغير سطراً في المنطق، فقط المستوى، مع إعادة المحاولة عند الخطأ.
  • القفل المتشائم Pessimistic Locking for-update: نفس الكود مع SELECT ... FOR UPDATE، فتنتظر كل معاملة التي قبلها على صف الفعالية.
  • القفل المتفائل Optimistic Locking optimistic: نقرأ المقاعد ورقم النسخة version، ونكتب بشرط أن الرقم لم يتغير، فإذا عدل التعديل صفر صفوف فقد سبقنا أحد ونعيد المحاولة.
  • التعديل الذري Atomic Update atomic: لا نقرأ شيئاً، وإنما نكتب SET seats_left = seats_left - 1 WHERE id = 1 AND seats_left > 0، فإذا عدل صفاً واحداً فلنا مقعد.

وهذا هو السكربت كاملاً، ولاحظ أن إعادة المحاولة تعيد الدالة كلها، أي المعاملة من أولها، وأن لها حداً أقصى 20 محاولة، وانتظاراً عشوائياً يزيد مع كل محاولة:

"""16 workers try to book 1600 tickets for an event with 1000 seats, using one strategy.
usage: python bench.py pg|my STRATEGY
A correct strategy ends with exactly 1000 bookings and seats_left = 0."""
import collections, os, random, sys, threading, time

ENGINE, STRATEGY = sys.argv[1], sys.argv[2]
WORKERS, SEATS = 16, 1000
ATTEMPTS = int(os.environ.get("ATTEMPTS", 100))      # per worker: 1600 attempts for 1000 seats

if ENGINE == "pg":
    import psycopg
    RETRYABLE = ("40001", "40P01")          # serialization failure, deadlock
    def connect():
        return psycopg.connect("host=sw2-pg dbname=tickets user=postgres password=lab", autocommit=True)
    def code(e): return getattr(e, "sqlstate", None)
    def begin(cur, level): cur.execute(f"BEGIN ISOLATION LEVEL {level}")
else:
    import pymysql
    RETRYABLE = (1213, 1205)                # deadlock, lock wait timeout
    def connect():
        return pymysql.connect(host="sw2-mysql", user="root", password="lab", database="tickets", autocommit=True)
    def code(e): return e.args[0] if e.args else None
    def begin(cur, level):
        cur.execute(f"SET TRANSACTION ISOLATION LEVEL {level}")
        cur.execute("START TRANSACTION")

P = "%s"   # both drivers use %s placeholders


def book_read_modify_write(cur, bid, level, lock=""):
    """read seats_left, decide in code, write the new value back"""
    begin(cur, level)
    cur.execute(f"SELECT seats_left FROM events WHERE id = 1 {lock}")
    left = cur.fetchone()[0]
    if left <= 0:
        cur.execute("COMMIT"); return False
    cur.execute(f"UPDATE events SET seats_left = {P} WHERE id = 1", (left - 1,))
    cur.execute(f"INSERT INTO bookings VALUES ({P}, 1, '[email protected]')", (bid,))
    cur.execute("COMMIT"); return True


def book_atomic(cur, bid):
    """let the database do the arithmetic and the check in one statement"""
    begin(cur, "READ COMMITTED")
    cur.execute("UPDATE events SET seats_left = seats_left - 1 WHERE id = 1 AND seats_left > 0")
    if cur.rowcount == 0:
        cur.execute("COMMIT"); return False
    cur.execute(f"INSERT INTO bookings VALUES ({P}, 1, '[email protected]')", (bid,))
    cur.execute("COMMIT"); return True


class Conflict(Exception):
    pass


def book_optimistic(cur, bid):
    """read with the version, write only if the version did not change"""
    begin(cur, "READ COMMITTED")
    cur.execute("SELECT seats_left, version FROM events WHERE id = 1")
    left, ver = cur.fetchone()
    if left <= 0:
        cur.execute("COMMIT"); return False
    cur.execute(f"UPDATE events SET seats_left = {P}, version = version + 1 WHERE id = 1 AND version = {P}", (left - 1, ver))
    if cur.rowcount == 0:
        cur.execute("ROLLBACK"); raise Conflict()
    cur.execute(f"INSERT INTO bookings VALUES ({P}, 1, '[email protected]')", (bid,))
    cur.execute("COMMIT"); return True


STRATS = {
    "naive-rc":     lambda cur, bid: book_read_modify_write(cur, bid, "READ COMMITTED"),
    "naive-rr":     lambda cur, bid: book_read_modify_write(cur, bid, "REPEATABLE READ"),
    "serializable": lambda cur, bid: book_read_modify_write(cur, bid, "SERIALIZABLE"),
    "for-update":   lambda cur, bid: book_read_modify_write(cur, bid, "READ COMMITTED", "FOR UPDATE"),
    "atomic":       book_atomic,
    "optimistic":   book_optimistic,
}

stats = {"booked": 0, "retries": 0, "gave_up": 0}
errors = collections.Counter()
lock = threading.Lock()


def worker(w):
    conn = connect(); cur = conn.cursor()
    for i in range(ATTEMPTS):
        bid = w * 1000 + i
        for attempt in range(1, 21):                       # retry the WHOLE transaction
            try:
                ok = STRATS[STRATEGY](cur, bid)
                with lock:
                    stats["booked"] += ok
                break
            except Conflict:
                with lock:
                    errors["version"] += 1
            except Exception as e:
                if code(e) not in RETRYABLE:
                    raise
                with lock:
                    errors[str(code(e))] += 1
                cur.execute("ROLLBACK")
            with lock:
                stats["retries"] += 1
            time.sleep(random.uniform(0, 0.002 * attempt))  # backoff with jitter
        else:
            with lock:
                stats["gave_up"] += 1


c = connect(); cur = c.cursor()
cur.execute("DELETE FROM bookings"); cur.execute(f"UPDATE events SET seats_left = {SEATS}, capacity = {SEATS}, version = 0 WHERE id = 1")
t0 = time.perf_counter()
threads = [threading.Thread(target=worker, args=(w,)) for w in range(WORKERS)]
for t in threads: t.start()
for t in threads: t.join()
dt = time.perf_counter() - t0
cur.execute("SELECT seats_left FROM events WHERE id = 1"); left = cur.fetchone()[0]
cur.execute("SELECT COUNT(*) FROM bookings"); rows = cur.fetchone()[0]
print(f"{ENGINE:2} {STRATEGY:13} bookings={rows:5} seats_left={left:4} sold_twice={rows - (SEATS - left):4} "
      f"retries={stats['retries']:5} gave_up={stats['gave_up']:3} time={dt:6.2f}s {dict(errors)}")

وتشغل كل الطرق على المحركين بالسكربت bench.sh:

#!/bin/sh
# every strategy on both engines; then the "what if" run with fewer attempts than seats
docker exec -i sw2-pg psql -q -U postgres -d tickets < schema.sql
docker exec -i sw2-mysql mysql -uroot -plab tickets < schema.sql 2> /dev/null
for e in pg my; do
  for s in naive-rc naive-rr serializable for-update optimistic atomic; do
    docker run --rm --network sw2-net -v "$PWD:/lab" sw2-tx:lab python bench.py $e $s
  done
done
echo "== what if: 16 x 50 = 800 attempts for 1000 seats"
for s in serializable optimistic for-update; do
  docker run --rm --network sw2-net -v "$PWD:/lab" -e ATTEMPTS=50 sw2-tx:lab python bench.py pg $s
done

والمخرج سوف يكون كما يلي:

pg naive-rc      bookings= 1600 seats_left= 900 sold_twice=1500 retries=    0 gave_up=  0 time= 19.40s {}
pg naive-rr      bookings= 1000 seats_left=   0 sold_twice=   0 retries= 9087 gave_up=210 time= 33.25s {'40001': 9087}
pg serializable  bookings= 1000 seats_left=   0 sold_twice=   0 retries= 8707 gave_up=233 time= 47.74s {'40001': 8707}
pg for-update    bookings= 1000 seats_left=   0 sold_twice=   0 retries=    0 gave_up=  0 time= 33.68s {}
pg optimistic    bookings= 1000 seats_left=   0 sold_twice=   0 retries= 7966 gave_up=204 time= 22.38s {'version': 7966}
pg atomic        bookings= 1000 seats_left=   0 sold_twice=   0 retries=    0 gave_up=  0 time=  5.15s {}
my naive-rc      bookings= 1600 seats_left= 899 sold_twice=1499 retries=    0 gave_up=  0 time= 12.74s {}
my naive-rr      bookings= 1600 seats_left= 899 sold_twice=1499 retries=    0 gave_up=  0 time= 23.25s {}
my serializable  bookings= 1000 seats_left=   0 sold_twice=   0 retries=11603 gave_up=206 time= 42.82s {'1213': 11603}
my for-update    bookings= 1000 seats_left=   0 sold_twice=   0 retries=    0 gave_up=  0 time=  8.52s {}
my optimistic    bookings= 1000 seats_left=   0 sold_twice=   0 retries=10966 gave_up=298 time= 27.43s {'version': 10966}
my atomic        bookings= 1000 seats_left=   0 sold_twice=   0 retries=    0 gave_up=  0 time= 12.49s {}
== what if: 16 x 50 = 800 attempts for 1000 seats
pg serializable  bookings=  729 seats_left= 271 sold_twice=   0 retries= 5048 gave_up= 71 time= 10.59s {'40001': 5048}
pg optimistic    bookings=  708 seats_left= 292 sold_twice=   0 retries= 4405 gave_up= 92 time=  7.44s {'version': 4405}
pg for-update    bookings=  800 seats_left= 200 sold_twice=   0 retries=    0 gave_up=  0 time=  2.18s {}

في المخرج أعلاه لاحظ التالي:

  • الطريقة البسيطة باعت 1600 تذكرة لألف مقعد في المحركين، والعداد يقول إن 900 مقعد ما زالت متاحة. والعمود sold_twice يقول إن 1500 حجز لم تنقص العداد، لأن كل مجموعة من المعاملات قرأت نفس القيمة وكتبت نفس النتيجة، والتحقق left <= 0 لم يوقف أحداً لأن العداد لم يقترب من الصفر أصلاً.
  • رفع المستوى إلى Repeatable Read أصلح المشكلة في PostgreSQL ولم يصلحها في MySQL: في الأول 1000 حجز بالضبط مع 9087 خطأ 40001، وفي الثاني 1600 حجز كما في Read Committed، وهذه هي نتيجة جدول الظواهر نفسها، ولكن هذه المرة على كود حقيقي.
  • Serializable صحيح في المحركين، ولكن PostgreSQL وصل إليه بـ 8707 خطأ تسلسل، وMySQL بـ 11603 جمود، أي أن كل حجز ناجح احتاج في المتوسط ما بين ثماني وإحدى عشرة محاولة فاشلة.
  • القفل المتفائل صحيح أيضاً ولكن بنفس النمط: 7966 تعارضاً في PostgreSQL و10966 في MySQL، لأن كل المعاملات تتنافس على صف واحد، وهذه هي الحالة التي لا يناسبها القفل المتفائل أبداً.
  • FOR UPDATE والتعديل الذري صحيحان دون أي إعادة محاولة، لأن المعاملات تنتظر بعضها بدلاً من أن تفشل.
  • العمود gave_up هو عدد الحجوزات التي استنفدت عشرين محاولة. وفي هذا الاختبار لا يظهر أثره في النتيجة، لأن المحاولات أكثر من المقاعد، فبقي من يأخذ المقاعد الباقية.

أما الزمن فتغير كثيراً بين التشغيلات المتكررة، لأن الكلاينت وقاعدة البيانات على نفس الجهاز ويتنافسان على نفس المعالج، فرأينا التعديل الذري في PostgreSQL بين 4 و18 ثانية في أربعة تشغيلات، لذلك لا تبن قراراً على عمود الزمن هنا، واعتمد على أعمدة الصحة وعدد المحاولات، فهي ثابتة تقريباً بين التشغيلات: الطريقة البسيطة باعت بين 1486 و1500 حجز زائد في كل مرة، والطرق التي تعتمد على إعادة المحاولة احتاجت بين 7900 و11600 محاولة في كل مرة. والصورة التالية تلخص النتيجة:

أعمدة لست طرق في المحركين: عدد الحجوزات مقابل خط السعة 1000، الطريقة البسيطة تصل إلى 1600 في المحركين، وRepeatable Read تصل إلى 1600 في MySQL و1000 في PostgreSQL، وبجانبها عدد إعادات المحاولة: بين 8000 و11600 لـ Serializable والقفل المتفائل وRepeatable Read في PostgreSQL، وصفر لـ FOR UPDATE والتعديل الذري
كل الطرق الصحيحة وصلت إلى 1000 حجز، والفرق بينها في عدد المرات التي أعادت فيها المعاملة

ماذا لو كانت المقاعد أكثر من الطلبات؟ What If

في الاختبار السابق كانت المحاولات أكثر من المقاعد، فلم يظهر من رفض حجزه ظلماً. والآن نعيد ثلاث طرق صحيحة في PostgreSQL مع 50 محاولة لكل عملية، أي 800 محاولة على 1000 مقعد، والنتيجة الصحيحة أن ينجح الجميع، وهي آخر ثلاثة أسطر في المخرج أعلاه:

  • FOR UPDATE: 800 حجز من 800 محاولة، دون أي إعادة.
  • Serializable: 729 حجزاً فقط، و71 عميلاً استنفدوا عشرين محاولة فرجعت لهم رسالة خطأ، مع أن 271 مقعداً بقيت فارغة.
  • القفل المتفائل: 708 حجوزات، و92 عميلاً رفضوا والمقاعد متاحة.

وهذه هي النقطة التي لا تظهر في الشرح النظري: الطريقة «صحيحة» بمعنى أنها لا تفسد البيانات، ولكنها تحت التنافس الشديد على صف واحد قد ترفض طلبات كان يجب أن تنجح. وإذا رفعت الحد إلى خمسين محاولة فسوف ينخفض العدد، ولكن كل محاولة إضافية وقت يقضيه العميل منتظراً واتصال مشغول في قاعدة البيانات. ولذلك القاعدة العملية: Serializable والقفل المتفائل للحالات التي يكون التعارض فيها نادراً، أما الصف المزدحم الذي يتنافس عليه الجميع، مثل عداد المقاعد أو رصيد حساب واحد، فاجعل قاعدة البيانات ترتب الطلبات بالتعديل الذري أو بالقفل.

كيف تكتب إعادة المحاولة Retry بشكل صحيح؟

إذا اخترت Repeatable Read أو Serializable أو القفل المتفائل فإعادة المحاولة جزء من الكود وليست إضافة، والحلقة في bench.py تبين القواعد الخمس التي تحتاجها:

  1. أعد المعاملة كلها من أول جملة، لأن الخطأ يعني أن قراراتك بنيت على بيانات قديمة، فلا معنى لإعادة الجملة التي فشلت وحدها.
  2. التقط رموز الأخطاء القابلة للإعادة فقط: في PostgreSQL الرمز 40001 لخطأ التسلسل و40P01 للجمود كما في جدول رموز الأخطاء، وفي MySQL الرقم 1213 للجمود و1205 لانتهاء مهلة انتظار القفل. أما أي خطأ آخر، مثل مخالفة قيد، فهو خطأ حقيقي يجب أن يصل للمستخدم.
  3. ضع حداً أقصى للمحاولات، ثم أرجع للمستخدم رسالة واضحة، مثل الرمز 503 أو 409 في الواجهة البرمجية، بدلاً من طلب يدور إلى الأبد.
  4. انتظر وقتاً عشوائياً يزيد مع المحاولات Exponential Backoff with Jitter، لأن المعاملات التي تصادمت مرة سوف تتصادم مرة أخرى إذا أعيدت في نفس اللحظة.
  5. لا تضع داخل المعاملة ما لا يمكن إلغاؤه: البريد والرسائل واستدعاء خدمة الدفع تخرج بعد التأكيد، أو تكتب في جدول Outbox داخل المعاملة نفسها كما شرحنا في مقال Saga وOutbox، وإذا كان الطلب نفسه قد يتكرر من الكلاينت فاجعله آمناً عند التكرار كما في مقال Idempotency.

ومكتبة psycopg لا تعيد المعاملات تلقائياً، وتوثيقها في صفحة المعاملات يشرح أن الكتلة with conn.transaction() تؤكد عند الخروج الطبيعي وتلغي عند الاستثناء، فتضع الكتلة كلها داخل الحلقة. وإذا كان المبلغ مالاً حقيقياً فقد بنينا في مقال المحفظة الرقمية دفتر قيود يتحمل عشرين اتصالاً متزامناً بهذه القواعد نفسها.

كيف تختار المستوى والطريقة؟ Decision Table

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

الحالةالطريقة الأنسبوالسبب
إنقاص أو زيادة عداد: مقاعد، مخزون، رصيدتعديل ذري واحد بشرط SET x = x - 1 WHERE x > 0قاعدة البيانات تقرأ وتقرر وتكتب في جملة واحدة، فلا توجد فجوة بين القراءة والكتابة
قرار معقد على صف واحد معروف قبل التعديلSELECT ... FOR UPDATE في Read Committedالجلسات تنتظر بعضها في مكان تحدده أنت، دون أخطاء ودون إعادة محاولة
تعديل يحرره المستخدم لدقائق في الواجهةالقفل المتفائل Optimistic Locking بعمود versionلا يمكن أن تبقى المعاملة مفتوحة أثناء تفكير المستخدم، والتعارض نادر
قاعدة على مجموعة صفوف: مناوب واحد على الأقل، لا يتجاوز مجموع شيء حداًSerializable مع إعادة المحاولة في PostgreSQL، أو قفل صف «أب» يمثل المجموعةالمعاملات تعدل صفوفاً مختلفة، فلا تحميها أقفال الصفوف ولا عزل اللقطة
تقرير أو تصدير يقرأ أكثر من جدولRepeatable Read، ومعه READ ONLYلقطة واحدة لكل التقرير فلا يظهر انحراف القراءة، ودون أخطاء تسلسل لأنه لا يكتب
قاعدة تستطيع قاعدة البيانات أن تعبر عنهاقيد Constraint: CHECK أو UNIQUE أو Exclusionالقيد يعمل في كل المستويات وكل الطرق، حتى في الكود الذي نسي المعاملة

والملاحظة الأخيرة في الجدول أهم مما تبدو، فلو كان في جدول الفعاليات القيد CHECK (seats_left >= 0) لما بيع أكثر من السعة حتى مع الطريقة البسيطة، ولو كان في جدول الحجوزات قيد UNIQUE (event_id, email) لما تكرر حجز نفس الشخص. والقيود لا تغني عن الطريقة الصحيحة لأنها ترفض ولا تصحح، ولكنها شبكة الأمان الأخيرة عندما يخطئ الكود. والصورة التالية تحول الجدول إلى أسئلة متتالية:

مخطط قرار: هل تقرأ المعاملة ثم تكتب بناءً على ما قرأت؟ لا: المستوى الافتراضي. نعم: هل يمكن التعبير عن التعديل بجملة واحدة؟ نعم: تعديل ذري. لا: هل القرار على صف واحد؟ نعم: FOR UPDATE أو عمود version. لا: Serializable مع إعادة المحاولة أو قفل صف أب، ومع كل الحالات قيد CHECK أو UNIQUE كشبكة أمان
ابدأ بالسؤال عن القراءة ثم الكتابة، وارفع الحماية فقط حيث تحتاجها

أخطاء شائعة Common Mistakes

  • الاعتقاد أن المعاملة تعني العزل الكامل: المعاملة تضمن الذرية، أما ما تراه من تعديلات غيرك فيحدده المستوى، والافتراضي يسمح بالتحديث المفقود في المحركين.
  • القراءة في الكود ثم الكتابة بقيمة محسوبة: النمط x = read(); write(x - 1) هو مصدر أغلب الأخطاء، فاكتب التعديل بالشكل x = x - 1 كلما أمكن.
  • نقل الكود من PostgreSQL إلى MySQL أو العكس دون مراجعة: نفس الاسم Repeatable Read يرفض التحديث المفقود في الأول ويسمح به في الثاني.
  • إعادة جملة واحدة بدلاً من المعاملة كلها: بعد الخطأ 40001 أو 1213 تكون المعاملة قد ألغيت، والقراءات التي بني عليها القرار قديمة، فأعد من أول جملة.
  • المتابعة بعد الخطأ: رأيت في التجربة كيف أضاف MySQL الحجز بعد الجمود خارج أي معاملة، لأن الكود أكمل الخطوات.
  • إعادة المحاولة دون حد ودون انتظار عشوائي: بدون حد تتحول المشكلة إلى طلب لا ينتهي، وبدون Jitter تتصادم المحاولات من جديد في نفس اللحظة.
  • إرسال بريد أو استدعاء خدمة خارجية داخل المعاملة: إذا أعيدت المعاملة فسوف يرسل البريد مرتين، وإذا ألغيت فسوف يرسل لحجز لم يحدث.
  • رفع كل شيء إلى Serializable لتكون آمناً: على صف واحد مزدحم رأيت أن هذا يعني آلاف الإعادات ورفض عملاء والمقاعد متاحة، والطريقة الأنسب كانت أبسط.
  • معاملات طويلة: المعاملة المفتوحة تحتفظ بلقطتها وأقفالها، فتزيد التعارضات وتمنع التنظيف، وقد رأينا أثرها في مقال التخزين.

قائمة تحقق Checklist

  • أعرف المستوى الافتراضي في قاعدة بياناتي، وما الذي يعنيه اسمه فيها تحديداً.
  • راجعت كل معاملة تقرأ ثم تكتب بناءً على ما قرأت، وحددت لها طريقة من جدول القرار.
  • العدادات تتغير بتعديل ذري بشرط، وليس بقيمة محسوبة في الكود.
  • القواعد التي تستطيع قاعدة البيانات أن تعبر عنها مكتوبة كقيود CHECK وUNIQUE.
  • كل معاملة في Repeatable Read أو Serializable أو مع قفل متفائل داخل دالة إعادة محاولة تعيدها كلها.
  • إعادة المحاولة لها حد أقصى وانتظار متزايد عشوائي، وتلتقط فقط رموز الأخطاء القابلة للإعادة.
  • لا يوجد داخل المعاملة إرسال بريد أو استدعاء API خارجي، والأحداث تخرج عبر جدول Outbox.
  • أقيس عدد أخطاء 40001 أو 1213 وعدد من استنفدوا المحاولات في المراقبة Monitoring.
  • المعاملات قصيرة، ولا تنتظر داخلها مدخلات من المستخدم أو من الشبكة.
  • عندي اختبار يشغل الطلبات المتزامنة على الكود الحساس، وليس فقط اختبارات تعمل طلباً بعد طلب.

حذف المختبر Cleanup

docker rm -f sw2-pg sw2-mysql
docker network rm sw2-net
docker rmi sw2-tx:lab

ماذا تقرأ بعد ذلك؟ Next Steps

هذا المقال في مرحلة قواعد البيانات بعد مقال التصميم والتطبيع ومقال الفهارس والأقفال، والمقالات التالية تكمل الصورة: كيف يتكلم تطبيقك مع قاعدة البيانات عبر ORM ومتى تكتب SQL بيدك، ولماذا الاتصال نفسه مكلف، وكيف تبقى المعاملة صحيحة عندما تمتد على أكثر من خدمة.

دليلالأقفال Locks والجمود Deadlock في قواعد البياناتلماذا يوقف أمر ALTER TABLE صغير كل الاستعلامات خلفه، وكيف تقرأ pg_locks وتتجنب الجمود وظواهر العزل في PostgreSQL.دليلتعلم SQL (3): CTE ودوال النوافذ والمعاملاتكيف تكتب استعلامات أوضح بجمل WITH ودوال النوافذ، وتحمي بياناتك بالمعاملات ومستويات العزل ومن حقن SQL.دليلORM أم SQL مباشرة؟ كيف يتحدث تطبيقك مع قاعدة البياناتما الذي يرسله ال ORM إلى قاعدة البيانات نيابة عنك، بصفحة واحدة أرسلت 251 استعلاماً ثم استعلاماً واحداً مع SQLAlchemy وPostgreSQL، ومتى تكسب SQL المكتوبة يدوياً.دليلIdempotency: كيف تجعل العملية آمنة عند تكرارهالماذا تصل الرسالة نفسها أكثر من مرة، وكيف تكتب مستهلكاً يطبقها مرة واحدة فقط مهما تكررت.دليلالمعاملات الموزعة: Saga ونمط Outbox بدل 2PCكيف تنفذ عملية تمتد على عدة خدمات دون 2PC، بخطوات محلية وتعويضات وجدول Outbox لا يضيع فيه أي حدث.دليلالمحفظة الرقمية من الداخل: دفتر القيد المزدوج Ledgerكيف تبني محفظة رقمية لا يضيع فيها ريال ولا يخصم مرتين، بالقيد المزدوج والمعاملات والمطابقة مع مزود الدفع.

الخلاصة Summary

وصلنا لنهاية الموضوع، وأهم ما فيه:

  • العزل هو الوعد الذي يحدد ما تراه معاملتك من تعديلات غيرها، والمستوى الافتراضي في PostgreSQL وMySQL يسمح بالتحديث المفقود وانحراف الكتابة.
  • تعريف المعيار ANSI بثلاث ظواهر ناقص، والقائمة الكاملة تشمل الكتابة القذرة والتحديث المفقود وانحراف القراءة والكتابة، كما بينت ورقة Berenson وGray سنة 1995.
  • PostgreSQL يأخذ لقطة لكل جملة في Read Committed ولكل المعاملة في Repeatable Read، ويرفض التعديل على نسخة قديمة بالخطأ 40001، ويكشف انحراف الكتابة في Serializable بتقنية SSI.
  • InnoDB يقرأ من لقطة ويعدل أحدث نسخة، فيسمح Repeatable Read فيه بالتحديث المفقود، ويمنع Serializable الظواهر بالأقفال والجمود 1213.
  • في 49 تجربة حقيقية رأيت ذلك كله، ومعه خطأ تسلسل دون تعارض حقيقي بسبب صفحة فهرس مشتركة، لذلك إعادة المحاولة جزء من كود Serializable وليست حالة نادرة.
  • في كود التطبيق كانت الطريقة البسيطة تبيع المقاعد مرتين، وكان التعديل الذري وFOR UPDATE الأسرع والأصح على الصف المزدحم، وكانت الطرق التي تعتمد على إعادة المحاولة صحيحة ولكنها رفضت عملاء والمقاعد متاحة.
  • اختر لكل معاملة طريقتها: الافتراضي لما لا يقرر، والتعديل الذري للعدادات، والقفل للصف الواحد، وSerializable للقواعد على مجموعات، والقيود في كل الحالات.

سجل التحديثات Changelog

  • 18 نوفمبر 2026: كتابة المقال، وتنفيذ التجارب على Docker Engine 29.8.0 مع PostgreSQL 18.6 (postgres:18.6) وMySQL 8.4.13 (mysql:8.4) وPython 3.13.18 مع psycopg 3.3.6 وPyMySQL 1.1.2.
نشرة عرب رووت | ArabRoot

معرفة تستحق مكاناً في بريدك.

مقالات مختارة وأدوات مفيدة وأفكار لمشروعك القادم، في رسالة واحدة كل أسبوع.

يمكنك إلغاء الاشتراك متى شئت. الخصوصية

تم استلام طلبك. افتح بريدك واضغط رابط التأكيد لإتمام الاشتراك.