الـ Isolation Levels في MySQL و PostgreSQL

1. الافتراضي وإزاي تغيّره

خد بالك من الفرق في آخر صف: في MySQL، SET TRANSACTION من غير SESSION بيأثر على الترانزاكشن الجاية بس، ولو اتنفذ جوه ترانزاكشن مفتوحة بيرمي Error. في PostgreSQL العكس: لازم يبقى جوه الترانزاكشن وقبل أي استعلام.

2. PostgreSQL

READ UNCOMMITTED = READ COMMITTED

PostgreSQL مش بيدّيك Dirty Reads أبداً. لو طلبت READ UNCOMMITTED هيشتغل READ COMMITTED. ده مسموح في المعيار، لأن المعيار بيحدد الحد الأدنى من الحماية، مش الحد الأقصى.

READ COMMITTED (الافتراضي)

• كل جملة بتاخد Snapshot جديدة لحظة ما تبدأ. فجملتين ورا بعض ممكن يشوفوا داتا مختلفة (Non-repeatable Reads و Phantoms و Read Skew كلهم ممكنين).
• جوه الجملة الواحدة، الصورة ثابتة.
• الكتابة: لو UPDATE أو DELETE لقى صف بيتعدّل من ترانزاكشن تانية، بيستنى. ولو التانية عملت COMMIT، بياخد النسخة الجديدة من الصف ده، ويعيد التحقق من الـ WHERE عليها، ولو لسه مطابقة يكمل عليها. ده اسمه Re-check (داخلياً EvalPlanQual).

الـ Re-check ده بيخلّي UPDATE ... SET x = x + 1 صح دايماً (مفيش Lost Update في الـ UPDATE الذري). بس عنده حالات غريبة، والمثال ده من الـ Documentation الرسمي:

DELETE مامسحش حاجة رغم إن فيه صف مطابقجرّب بنفسك

في الداتابيزrow A hits9row B hits10

T1⏱T2

1. BEGIN; UPDATE website SET hits = hits + 1;2 rows1
2. 2DELETE FROM website WHERE hits = 10;⏳ مستنية
3. COMMIT;تم3
4. 4-- Re-check على النسخة الجديدة من B: hits = 110 rows

▶

دوس ▶ شغّل، أو امشي خطوة خطوة بالأسهم.

▶ شغّل→←↺ من الأول

REPEATABLE READ = Snapshot Isolation

• Snapshot واحدة من أول جملة في الترانزاكشن لحد آخرها. فمفيش Non-repeatable Reads ولا Phantoms ولا Read Skew.
• لو حاولت تعدّل (أو تقفل بـ FOR UPDATE) صف اتعدّل واتعمله COMMIT بعد الـ Snapshot بتاعتك: ERROR: could not serialize access due to concurrent update (SQLSTATE 40001). ده بيمنع الـ Lost Update، بس الكود لازم يعمل Retry.
• Write Skew بتعدّي.

SERIALIZABLE = SSI

• كل ضمانات REPEATABLE READ + مراقبة SIREAD، فحتى الـ Write Skew بيتمنع بـ 40001.
• أداؤه قريب من REPEATABLE READ لأن القراية مش بتقفل. بس Aborts أكتر، ومحتاج Retry دايماً.

3. MySQL (InnoDB)

في InnoDB لازم تفرّق بين نوعين قراية:

• Consistent Read (Nonlocking): أي SELECT عادي. بيقرا من الـ Read View (MVCC)، مش بيقفل ومش بيستنى.
• Locking Read / Current Read: SELECT ... FOR UPDATE، و FOR SHARE، و UPDATE، و DELETE. بيقرا آخر نسخة متثبتة (مش الـ Snapshot)، وبياخد Locks.

READ UNCOMMITTED

القراية العادية بتشوف آخر نسخة حتى لو مش متثبتة (Dirty Reads). نادراً ما له استخدام صح.

READ COMMITTED

• كل Consistent Read بتاخد Read View جديدة.
• الـ Locking Reads و UPDATE و DELETE بياخدوا Record Locks بس، من غير Gap Locks (إلا في فحص الـ Foreign Keys والـ Duplicate Keys). فالـ Phantoms ممكنة، بس الـ Deadlocks أقل بكتير.
• Semi-consistent Read: في UPDATE، لو صف مقفول، InnoDB بيقرا آخر نسخة متثبتة منه ويشوف هل مطابق للـ WHERE أصلاً. لو مش مطابق، بيعدّيه من غير ما يستنى. والصفوف اللي اتمسحت ومش مطابقة بيفك الـ Lock بتاعها فوراً.

REPEATABLE READ (الافتراضي)

• كل Consistent Reads في الترانزاكشن من Read View واحدة (من أول قراية). فالـ SELECT العادي مش بيشوف Phantoms.
• الـ Locking Reads و UPDATE و DELETE بيعملوا Current Read وبياخدوا Next-Key Locks على الـ Range اللي اتمسح (أو Record Lock بس لو بحث بـ Unique Index عن صف واحد).
• مفيش First-Updater-Wins: الـ Read-Modify-Write بـ SELECT عادي بيعمل Lost Update من غير أي Error (شفناها في درس الـ MVCC).
• Write Skew بتعدّي برضه.

SERIALIZABLE

• زي REPEATABLE READ، بس InnoDB بيحوّل كل SELECT عادي لـ SELECT ... FOR SHARE لو الـ autocommit مقفول (يعني جوه ترانزاكشن). ولو الـ autocommit شغال، كل SELECT بيبقى ترانزاكشن لوحدها بـ Consistent Read.
• يعني 2PL كلاسيك: القراية بتقفل وبتستنى، والـ Deadlocks بتزيد (خصوصاً الـ Lock Upgrade من S لـ X).

4. جنب بعض

5. تختار إيه؟

قاعدة عملية: ابدأ بالافتراضي، واحمي الأماكن الحساسة بالأداة المناسبة، بدل ما ترفع مستوى الداتابيز كلها.

• أغلب الشغل (CRUD عادي): READ COMMITTED في الاتنين كفاية، بشرط إن أي Read-Modify-Write يبقى UPDATE ذري أو FOR UPDATE.
• تقرير أو Export لازم يبقى متسق: ترانزاكشن على REPEATABLE READ، وفي PostgreSQL ممكن SERIALIZABLE READ ONLY DEFERRABLE.
• قواعد بتشمل كذا صف ومش ممكن تتعمل Constraint: في PostgreSQL SERIALIZABLE مع Retry (أنضف حل). في MySQL FOR UPDATE على كل الصفوف اللي القرار اتبنى عليها، أو صف تقفل عليه.
• منطق فلوس حساس: Constraints في الداتابيز (CHECK (balance >= 0)) + UPDATE ذري بشرط + Ledger (هتشوفه في درس الـ Patterns).

اسم المستوى مش كفاية تعرف إنت محمي من إيه. REPEATABLE READ في PostgreSQL بيرمي Error على الـ Lost Update، وفي MySQL بيعدّيه بصمت. اعرف داتابيزك، واحمي الأماكن الحساسة بشكل صريح.

6. أسئلة الإنترفيو

فكّر في إجابتك الأول، وبعدين افتح السؤال.

01إيه الـ Isolation Level الافتراضي في MySQL و PostgreSQL؟

MySQL (InnoDB): REPEATABLE READ. PostgreSQL: READ COMMITTED.

02في PostgreSQL على READ COMMITTED، إيه اللي بيحصل لو UPDATE لقى صف اتعدّل من ترانزاكشن تانية؟

بيستنى لحد ما التانية تخلص. لو عملت COMMIT، بياخد النسخة الجديدة من الصف ويعيد التحقق من الـ WHERE عليها، ولو مطابقة يكمل عليها، ولو لأ يعدّيه. علشان كده UPDATE x = x + 1 آمن، بس منطق الشروط المعقدة ممكن يطلع نتايج غريبة.

03ليه الافتراضي في MySQL هو REPEATABLE READ؟ وليه شركات كتير بتغيّره؟

تاريخياً عشان Statement-based Replication: الـ Gap Locks كانت بتضمن إن الـ Replica تنفّذ الجمل بنفس النتيجة. مع Row-based Binlog المشكلة اتحلت، فشركات كتير بتنزل لـ READ COMMITTED عشان Gap Locks و Deadlocks أقل وتوازي أعلى.

04الفرق بين Consistent Read و Locking Read في InnoDB؟

Consistent Read هو SELECT عادي بيقرا من الـ Read View بالـ MVCC من غير Locks. Locking Read هو FOR UPDATE و FOR SHARE وكمان UPDATE و DELETE: بيقرا آخر نسخة متثبتة وبياخد Locks. علشان كده في REPEATABLE READ ممكن SELECT عادي يشوف قيمة و UPDATE في نفس الترانزاكشن يشتغل على قيمة تانية.

05MySQL SERIALIZABLE بيعمل إيه بالظبط؟

زي REPEATABLE READ، بس كل SELECT عادي بيتحول لـ SELECT ... FOR SHARE لو الـ autocommit مقفول. يعني القراية بتاخد Shared Locks وبتستنى الكتابات، وده 2PL بتوازي أقل و Deadlocks أكتر. ومفيش SSI زي PostgreSQL.