Transactions and concurrency
Opening two sessions side by side, you watch ACID properties, the anomalies each isolation level allows, locks, MVCC and deadlocks live in PostgreSQL and MySQL.
Level 301 · 18 lessons · 15 practice labs
Lessons
ACID
- BEGIN, COMMIT and ROLLBACK
- Savepoints
- Atomicity and durability: a crash experiment
- Consistency and deferred constraints
- Short task: a safe money transfer
Isolation
- Anomalies: dirty, non-repeatable and phantom reads
- Isolation levels and how engines differ
- MVCC and snapshots
- Write skew and SERIALIZABLE
- Lost updates and how to prevent them
- Short task: the right isolation level for three scenarios
Locks
- Row and table locks
- SELECT … FOR UPDATE and SKIP LOCKED
- Deadlocks: causing, reading and preventing them
- Monitoring lock waits
- Advisory locks
- Optimistic locking: a version column
- Capstone: prevent overselling in ticket sales
Practice labs
- Roll back a faulty bulk update: Notice inside a transaction that an UPDATE changed thousands of rows by mistake and undo it with ROLLBACK.
- Return to a savepoint on partial failure: When only the coupon step fails in a multi-step order save, roll back that step instead of the whole transaction.
- Insert circular rows with a deferred constraint: Insert two rows that reference each other in one transaction with a deferrable foreign key and show why it is needed.
- Reproduce a non-repeatable read: Open two sessions and show the same query returning different results inside one READ COMMITTED transaction.
- Choose the right isolation level for a report: Stop a long month-end report from producing inconsistent totals by choosing the right isolation level.
- Prevent write skew in a duty roster: Reproduce the bug where two doctors can leave duty at once and fix it with SERIALIZABLE or a lock.
- Show a lost stock update: Show step by step how two sessions reading and writing the same stock lose one sale.
- Add optimistic locking with a version column: Add a version column to a product edit screen and reject saves made from stale data.
- Stop overselling concert tickets: Prove at Martı Bilet that 50 concurrent buyers exceed the seat count and fix it with an atomic update.
- Lock a row with FOR UPDATE: Lock an account with FOR UPDATE and watch the second session wait.
- Build a job queue with SKIP LOCKED: Let three workers pick Mavikuş Kargo's label printing jobs without clashing using SKIP LOCKED.
- Make a nightly job single-run with an advisory lock: Make the second copy of a billing job started twice exit immediately using an advisory lock.
- Cause a deadlock on purpose: Update rows in opposite order in two sessions to create a deadlock and read the error.
- Find who holds the blocking lock: Use system views to find which session a stuck update is waiting on and resolve it.
- Redesign a transfer that keeps deadlocking: Fix account-transfer code that deadlocks at peak time with a fixed lock order and retries.
About this course
Opening two sessions side by side, you watch ACID properties, the anomalies each isolation level allows, locks, MVCC and deadlocks live in PostgreSQL and MySQL.
What you will learn
Prerequisites
You should be comfortable with the level 101 and 201 topics of the same path.
Lab environment
Lessons run in your own Kali Linux terminal in the browser, connected to a private lab network. Nothing to install.
Frequently asked questions
- Is this course right for my level?
- The course is at level 301; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.