انتقل إلى المحتوى
أمين أبوزيد
Englishالعربية
MID-015

العزل والقفل

  • 7 درس
  • 3 ساعة تدريب (تقدير)
ابدأ الوحدة
في هذه الصفحة 8 موضوعات

نص الدروس والتمارين باللغة الإنجليزية، بينما تبقى واجهة الموقع وعناوين الدروس بالعربية.

نظرة عامة

الموضوعات

  • Read committed
  • repeatable read
  • serializable
  • الجمود المتبادل (deadlocks)
  • أقفال الصفوف

تعلّم أولًا

3 ساعة تدريب (تقدير)

مخرجات التعلم

  1. Describe the anomalies (dirty read, non-repeatable read, phantom, lost update, write skew) and which isolation levels prevent them in PostgreSQL.
  2. Use row locks (FOR UPDATE, FOR SHARE, SKIP LOCKED) correctly.
  3. Diagnose lock waits and deadlocks from pg_locks and logs.
  4. Handle serialization failures with retries.

الدروس

  1. 01شذوذات التزامن
  2. 02مستويات العزل في PostgreSQL
  3. 03الأقفال على مستوى الصف
  4. 04طوابير المهام بـ SKIP LOCKED
  5. 05الجمود المتبادل (Deadlocks)
  6. 06عرض عملي: تحديث مفقود في طرفيتين
  7. 07عرض عملي: انحراف الكتابة (write skew) تحت serializable

المختبر والتمارين

Lab

MID-015-LAB1 · Last item in stock

Outcomes: 1–4 · Time: ~3 h Reproduce overselling in Shop: 50 threads each try to buy the last 10 units. Fix it three ways: (1) atomic UPDATE ... WHERE qty >= n, (2) SELECT ... FOR UPDATE, (3) SERIALIZABLE with a bounded retry. Compare throughput and error rate. Acceptance: a concurrent test never oversells for all three fixes; a short comparison table of throughput.

Exercises (assessment: anomaly-identification scenarios)

IDOutcomeTask
MID-015-EX011For five short timelines, name the anomaly.
MID-015-EX021Fill a table: which anomalies each PostgreSQL level allows.
MID-015-EX032Write a transaction that reserves one unit using FOR UPDATE; explain what a concurrent session experiences.
MID-015-EX042Implement a job claim query with SKIP LOCKED and run two workers.
MID-015-EX053Create a deadlock with two sessions, read the log message, and fix it with ordering.
MID-015-EX063Find a blocked session and its blocker using pg_locks / pg_stat_activity (or pg_blocking_pids).
MID-015-EX074Write a retry wrapper (max 3 attempts, backoff) for SQLSTATE 40001 and 40P01.
أظهر الحلول
  • EX01 For example: stale re-read = non-repeatable; new rows in a repeated query = phantom; both write from the same read = lost update; two writers on different rows breaking a shared rule = write skew; seeing uncommitted data = dirty read.
  • EX02 READ COMMITTED allows all but dirty; REPEATABLE READ allows only write skew; SERIALIZABLE allows none.
  • EX03 SELECT ... FOR UPDATE; UPDATE ...; COMMIT; the other session blocks on its write or lock until commit.
  • EX04 ... FOR UPDATE SKIP LOCKED LIMIT 1 inside a CTE with UPDATE ... RETURNING; the workers get different jobs.
  • EX05 Two sessions update rows A,B and B,A; the log shows "deadlock detected"; both update in ascending id order.
  • EX06 SELECT pid, pg_blocking_pids(pid), query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;
  • EX07 Loop up to 3 times catching those SQLSTATEs, rerun the whole transaction, sleep with jitter, rethrow at the end.
  • The "retrying without limits" mistake is covered by EX07 and lab fix 3.

الاختبار

Outcomes: O1 anomalies and isolation levels · O2 row locks · O3 diagnosing locks and deadlocks · O4 retries.

اختبار ذاتي لك: لا توجد درجات، ولا يُحفظ أو يُرسل أي شيء. اختر إجابة ثم تحقق منها، ويمكنك المحاولة مرة أخرى كما تشاء.

  1. 1Two transactions read stock 5 and each write 4. This is a:O1
  2. 2PostgreSQL's default isolation level is:O1
  3. 3Which level prevents write skew in PostgreSQL?O1
  4. 4Does READ COMMITTED prevent a lost update in read-then-write application code?O1
  5. 5SELECT ... FOR UPDATE makes a concurrent FOR UPDATE on the same row:O2
  6. 6SKIP LOCKED is ideal for:O2
  7. 7The simplest oversell-safe stock decrement:O2
  8. 8PostgreSQL resolves a deadlock by:O3
  9. 9Best way to prevent most deadlocks:O3
  10. 10A serialization failure (40001) should be handled by:O4

الجزء العملي

Anomaly-identification scenarios (five timelines to classify and fix) plus exercises MID-015-EX01 to EX07 and the lab. Pass: quiz 8/10, four of five scenarios correct, and the lab concurrency test never overselling.

أخطاء شائعة

Assuming READ COMMITTED prevents lost updates; retrying without limits.

التقييم

Anomaly-identification scenarios.

الصلة بالمسار الوظيفي

Data integrity (SRC-014).

اكتب للبحث في المستويات والمراحل والوحدات والدروس.