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

MVCC

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

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

نظرة عامة

الموضوعات

  • اللقطات (Snapshots)
  • إصدارات الصفوف
  • vacuum
  • التضخم (bloat)
  • الرؤية (visibility)

تعلّم أولًا

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

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

  1. Explain MVCC: row versions, snapshots, and visibility.
  2. Explain why UPDATE creates new row versions and what vacuum does.
  3. Detect table and index bloat and long-running transactions that prevent cleanup.

الدروس

  1. 01القرّاء لا يحجبون الكتّاب
  2. 02إصدارات الصفوف واللقطات
  3. 03Vacuum وautovacuum
  4. 04التضخم والمعاملات الطويلة
  5. 05عرض عملي: معاملة خاملة تعطّل vacuum

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

Lab

MID-014-LAB1 · Observe MVCC

Outcomes: 1–3 · Time: ~2 h With two psql sessions, watch xmin/xmax/ctid change across INSERT, UPDATE, DELETE, and see what each session can see under READ COMMITTED and REPEATABLE READ. Then create bloat with an idle-in-transaction session and a hot update loop, measure it, and fix it. Acceptance: annotated session transcript covering visibility, bloat creation, detection, and the fix.

Exercises (assessment: quiz)

IDOutcomeTask
MID-014-EX011Explain in three sentences why a long read does not block a write.
MID-014-EX021Predict what session B sees in each of the two isolation levels after session A commits an update mid-transaction, then verify.
MID-014-EX032Update one row three times and show the ctid and xmin each time.
MID-014-EX042State what VACUUM does that VACUUM FULL does differently, and when each is appropriate.
MID-014-EX053Query pg_stat_user_tables to find the top three tables by dead tuples and their last autovacuum.
MID-014-EX063Find the oldest open transaction in pg_stat_activity and set idle_in_transaction_session_timeout for a role.
أظهر الحلول
  • EX01 Readers use a snapshot of old versions; writers create new versions; no row lock is needed to read.
  • EX02 READ COMMITTED: sees the new value on the next statement; REPEATABLE READ: keeps the old value until the transaction ends.
  • EX03 ctid changes with each update (new physical location); xmin is the updating transaction id.
  • EX04 VACUUM reclaims space for reuse without blocking reads/writes; VACUUM FULL rewrites the table to return space to the OS under an exclusive lock, so use it rarely and in a window.
  • EX05 SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 3;
  • EX06 ORDER BY xact_start on pg_stat_activity; ALTER ROLE app SET idle_in_transaction_session_timeout = '30s'; (applies to new sessions).
  • Disabling autovacuum and open transactions in application code are demonstrated in the lab and the lecture demo.

الاختبار

Outcomes: O1 MVCC and visibility · O2 row versions and vacuum · O3 bloat and long transactions.

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

  1. 1In MVCC a reader:O1
  2. 2xmin on a row version is:O1
  3. 3REPEATABLE READ takes its snapshot:O1
  4. 4An UPDATE in PostgreSQL:O2
  5. 5Plain VACUUM mainly:O2
  6. 6Autovacuum should be:O2
  7. 7The most common cause of vacuum being unable to clean:O3
  8. 8Where do you find idle transactions?O3
  9. 9A good safeguard for application mistakes:O3
  10. 10Dead tuples counted high and last_autovacuum very old suggest:O3

الجزء العملي

Quiz is the assessment, plus the annotated session transcript from MID-014-LAB1 and exercises MID-014-EX01 to EX06. Pass: quiz 8/10 and a transcript that shows visibility changes, bloat creation, and the fix.

أخطاء شائعة

Disabling autovacuum; leaving transactions open in application code.

التقييم

Quiz.

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

Isolation (MID-015) and database operations (MID-018).

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