-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy path05-transactions.html
More file actions
586 lines (547 loc) · 53.8 KB
/
Copy path05-transactions.html
File metadata and controls
586 lines (547 loc) · 53.8 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8"><meta name="viewport" content="width=device-width, initial-scale=1.0">
<script>try{var t=localStorage.getItem("dbms-theme")||"dark";document.documentElement.setAttribute("data-theme",t)}catch(e){document.documentElement.setAttribute("data-theme","dark")}</script>
<title>Transactions & ACID — DBMS Illustrated</title>
<link rel="stylesheet" href="../css/style.css">
<style>:root{--topic-color:#EF4444}</style>
</head>
<body>
<div class="reading-progress" id="reading-progress"></div>
<nav class="navbar">
<div class="navbar-inner">
<a class="navbar-brand" href="../index.html"><span class="brand-icon" style="color:var(--primary-soft)">◆</span>DBMS Illustrated</a>
<div class="navbar-links"><a href="../index.html#topics">All Topics</a></div>
<div class="navbar-actions"><button class="btn-icon" id="theme-toggle" title="Toggle theme">☼</button></div>
</div>
</nav>
<div class="container">
<div class="breadcrumb">
<a href="../index.html">Home</a><span class="breadcrumb-sep">›</span>
<a href="../index.html#topics">Course</a><span class="breadcrumb-sep">›</span>
<span>Transactions & ACID</span>
</div>
<div class="topic-header">
<div class="topic-badge">Topic 05</div>
<h1>Transactions & <span class="accent">ACID</span></h1>
<p class="subtitle">Imagine transferring $100 from your bank account to a friend's. If the system crashes right after taking the money from your account but before adding it to theirs, that $100 disappears. Transactions are the database's way of preventing that. This topic explains the four guarantees — ACID — that make databases trustworthy.</p>
<div class="company-badges">
<span class="badge">InnoDB (MySQL)</span><span class="badge">PostgreSQL</span>
<span class="badge">CockroachDB</span><span class="badge">Spanner</span><span class="badge">Oracle</span>
</div>
</div>
<div class="at-a-glance reveal">
<h3>At a Glance</h3>
<ul>
<li><strong>Isolation levels</strong> — a dial from "trust everything" to "be completely safe." From weakest to strongest: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. Most apps run at READ COMMITTED.</li>
<li><strong>Dirty read</strong> — reading data from a transaction that has not finished yet. Like seeing a score update that might be reversed. Prevented at READ COMMITTED and above.</li>
<li><strong>Non-repeatable read</strong> — you read a row, then read it again and it has changed. Someone else updated it between your two reads. Prevented at REPEATABLE READ.</li>
<li><strong>Phantom read</strong> — you query for rows matching a condition, then query again and new rows have appeared. Prevented at SERIALIZABLE.</li>
<li><strong>MVCC</strong> — Multi-Version Concurrency Control. Instead of forcing readers to wait for writers, the database keeps multiple versions of each row and shows each transaction the version that existed when it started. Readers never block writers. Writers never block readers.</li>
</ul>
</div>
<!-- ── ACID Properties ──────────────────────────────────────── -->
<div class="section-label">The Four Guarantees</div>
<h2 class="section-title">What ACID Actually Means</h2>
<p class="section-desc">ACID is not a monolithic feature — it is four separate, independently enforceable guarantees. A database engine may implement them using completely different mechanisms. Understanding each one individually is key to reasoning about what can go wrong when you weaken or bypass them.</p>
<div class="mini-cards">
<div class="mini-card">
<div class="mini-card-icon"></div>
<h4>Atomicity</h4>
<p>Think of the bank transfer: debit one account, credit another. Atomicity means both happen, or neither happens — there is no middle state. If anything goes wrong between the debit and the credit, the debit is automatically reversed. The database keeps an "undo log" — a record of how to reverse each change — so that even a mid-transaction crash can be cleaned up on restart.</p>
</div>
<div class="mini-card">
<div class="mini-card-icon"></div>
<h4>Consistency</h4>
<p>The database has rules — like "every order must reference a real customer" or "account balance cannot go below zero." Consistency means the database is never left in a state that breaks those rules. A transaction starts with a valid database and must end with a valid database. If a transaction would break a rule, the database rejects it entirely. Some rules are enforced by the database automatically (foreign keys, NOT NULL). Others are up to the application to enforce.</p>
</div>
<div class="mini-card">
<div class="mini-card-icon"></div>
<h4>Isolation</h4>
<p>Imagine two people both booking the last seat on a flight at the same moment. Isolation ensures they cannot both succeed. Each transaction behaves as if it is the only one running — they cannot see each other's half-finished work. The database achieves this by either placing locks (like a "do not disturb" sign — called Two-Phase Locking or 2PL) or by keeping multiple versions of data so readers see a consistent snapshot (called MVCC). Higher isolation means fewer problems but can slow things down.</p>
</div>
<div class="mini-card">
<div class="mini-card-icon"></div>
<h4>Durability</h4>
<p>Once the database says "transaction complete," that data is permanent — even if the power cuts out a second later. The database achieves this by writing a record to a journal on disk (the Write-Ahead Log, or WAL) before confirming the commit. If the server crashes, it replays the journal on restart and recovers every committed transaction. Data you committed before the crash is never lost.</p>
</div>
</div>
<!-- ── Atomicity Deep Dive ─────────────────────────────────── -->
<div class="section-label">Atomicity</div>
<h2 class="section-title">How Undo Logging Works</h2>
<p class="section-desc">Atomicity is implemented through an undo log — a record of how to reverse every change made during a transaction. Before any page is modified, the old value is written to the undo log. If the transaction aborts or the server crashes, the recovery process reads the undo log and reverses the changes in reverse order.</p>
<div class="steps">
<div class="step-item">
<div class="step-num">1</div>
<div>
<h4>Write undo record</h4>
<p>Before the database changes anything, it first writes a note describing how to undo the change. Think of it as saving a "before" photo. For a row update, this is the original row value before the change. For a new row being inserted, the undo record simply says "delete this row if we need to roll back." This note goes into the undo log, which is written to disk first — before any actual data changes.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">2</div>
<div>
<h4>Modify the page</h4>
<p>Now the actual data is updated — but only in memory (the buffer pool), not on disk yet. This is intentional: writing to RAM is fast, writing to disk is slow. These in-memory changes are called "dirty pages." If a crash happens here, it is fine — the undo log on disk still has the "before" photos, so the database can revert everything on restart.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">3</div>
<div>
<h4>On COMMIT: flush redo log</h4>
<p>When you run COMMIT, the database writes a "this transaction is complete" record to its journal (the redo log, part of the WAL) and forces that journal to physical storage. Only after this is safely on disk does the database confirm success to you. The data pages in memory might still be dirty (not yet written to disk) — that is fine, because the redo log has everything needed to reconstruct those changes if a crash occurs.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">4</div>
<div>
<h4>On ABORT or CRASH: apply undo log</h4>
<p>If something goes wrong — the transaction is cancelled, or the server crashes — the database reads the undo log backwards (most recent change first) and reverses each change. Each step re-applies the "before" photo, restoring the data to exactly what it was before the transaction started. No partial changes survive. Old undo log entries are cleaned up periodically once no running transaction could need them anymore.</p>
</div>
</div>
</div>
<div class="code-block">
<div class="cb-header"><span class="cb-lang">SQL — Transaction Control</span></div>
<pre><span class="cmt">-- Basic transfer: atomic debit + credit</span>
<span class="kw">BEGIN</span>;
<span class="kw">UPDATE</span> accounts <span class="kw">SET</span> balance = balance - <span class="num">100</span> <span class="kw">WHERE</span> id = <span class="num">1</span>;
<span class="kw">UPDATE</span> accounts <span class="kw">SET</span> balance = balance + <span class="num">100</span> <span class="kw">WHERE</span> id = <span class="num">2</span>;
<span class="kw">COMMIT</span>;
<span class="cmt">-- Pessimistic lock: read-then-update without lost update</span>
<span class="kw">BEGIN TRANSACTION ISOLATION LEVEL</span> REPEATABLE READ;
<span class="kw">SELECT</span> balance <span class="kw">FROM</span> accounts <span class="kw">WHERE</span> id = <span class="num">1</span> <span class="kw">FOR UPDATE</span>; <span class="cmt">-- exclusive lock</span>
<span class="kw">UPDATE</span> accounts <span class="kw">SET</span> balance = balance - <span class="num">50</span> <span class="kw">WHERE</span> id = <span class="num">1</span>;
<span class="kw">COMMIT</span>;
<span class="cmt">-- Savepoints: partial rollback within a transaction</span>
<span class="kw">BEGIN</span>;
<span class="kw">INSERT INTO</span> orders (user_id, total) <span class="kw">VALUES</span> (<span class="num">42</span>, <span class="num">199.99</span>);
<span class="kw">SAVEPOINT</span> after_order;
<span class="kw">INSERT INTO</span> payments (order_id, amount) <span class="kw">VALUES</span> (<span class="num">1001</span>, <span class="num">199.99</span>);
<span class="cmt">-- payment insert failed validation</span>
<span class="kw">ROLLBACK TO SAVEPOINT</span> after_order; <span class="cmt">-- undo payment only, keep order</span>
<span class="kw">INSERT INTO</span> payments (order_id, amount, method) <span class="kw">VALUES</span> (<span class="num">1001</span>, <span class="num">199.99</span>, <span class="str">'card'</span>);
<span class="kw">COMMIT</span>;
<span class="cmt">-- Optimistic locking with version column (no DB-level lock)</span>
<span class="kw">BEGIN</span>;
<span class="kw">SELECT</span> balance, version <span class="kw">FROM</span> accounts <span class="kw">WHERE</span> id = <span class="num">1</span>;
<span class="cmt">-- application reads version=7, computes new balance</span>
<span class="kw">UPDATE</span> accounts
<span class="kw">SET</span> balance = <span class="num">850</span>, version = <span class="num">8</span>
<span class="kw">WHERE</span> id = <span class="num">1</span> <span class="kw">AND</span> version = <span class="num">7</span>; <span class="cmt">-- fails if another tx already updated</span>
<span class="cmt">-- check affected rows; if 0, someone else won, retry</span>
<span class="kw">COMMIT</span>;</pre>
</div>
<!-- ── MVCC ───────────────────────────────────────────────── -->
<div class="section-label">MVCC</div>
<h2 class="section-title">Multi-Version Concurrency Control</h2>
<p class="section-desc">MVCC is the key insight behind modern databases: instead of making readers wait for writers (or vice versa), keep multiple versions of each row and give each transaction a consistent snapshot of the data at the moment it began.</p>
<div class="steps">
<div class="step-item">
<div class="step-num">1</div>
<div>
<h4>Transaction IDs and row versions</h4>
<p>Every transaction gets a unique ID number (like a timestamp). Every row in the database secretly has two hidden fields: when it was created (xmin — the ID of the transaction that created it) and when it was deleted or replaced (xmax — the ID of the transaction that replaced it, or empty if it is still the current version). When a row is updated, the database does not overwrite it — it marks the old version as "replaced" and creates a new version. Both versions exist on disk simultaneously.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">2</div>
<div>
<h4>Snapshot at transaction start</h4>
<p>When your transaction begins, the database takes a "snapshot" — it records which transactions were completed at that moment. From that point on, your reads only see row versions that were committed before your snapshot. If another transaction updates a row while you are running, you continue seeing the old version — not the new one. It is like viewing a photo of the database from the moment you started, even as others are still making changes. No locks needed for reads.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">3</div>
<div>
<h4>Readers never block writers</h4>
<p>Because every reader sees their own consistent snapshot, a writer updating a row at the same time does not interfere. The reader just sees the old version from before the write started. Writers only block other writers — two people cannot update the same row simultaneously. This is what makes MVCC powerful for high-traffic applications: reads and writes can happen in parallel on the same rows without either having to wait for the other.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">4</div>
<div>
<h4>VACUUM cleans up dead versions</h4>
<p>Over time, the old row versions pile up — every update creates a new version and leaves the old one behind. This is called table bloat. PostgreSQL periodically runs a process called VACUUM that scans for old versions no active transaction could possibly be reading anymore, and frees that space for reuse. Without regular VACUUM, the table grows indefinitely on disk. Most production databases are configured to run VACUUM automatically in the background, but monitoring it is important on busy tables.</p>
</div>
</div>
</div>
<!-- ── Isolation Levels ───────────────────────────────────── -->
<div class="section-label">Isolation Levels</div>
<h2 class="section-title">Anomalies vs. Isolation</h2>
<p class="section-desc">The SQL standard defines four isolation levels in order of increasing strictness. Higher isolation prevents more anomalies but typically reduces concurrency (more blocking, more aborts). The right choice depends on what anomalies your application can tolerate.</p>
<div class="table-wrap">
<table class="compare-table">
<thead>
<tr><th>Isolation Level</th><th>Dirty Read</th><th>Non-Repeatable Read</th><th>Phantom Read</th><th>Lost Update</th><th>Typical Use</th></tr>
</thead>
<tbody>
<tr><td>READ UNCOMMITTED</td><td style="color:#EF4444">possible</td><td style="color:#EF4444">possible</td><td style="color:#EF4444">possible</td><td style="color:#EF4444">possible</td><td>Approximate analytics</td></tr>
<tr><td>READ COMMITTED</td><td style="color:#10B981">prevented</td><td style="color:#EF4444">possible</td><td style="color:#EF4444">possible</td><td style="color:#EF4444">possible</td><td>PostgreSQL default; most OLTP apps</td></tr>
<tr><td>REPEATABLE READ</td><td style="color:#10B981">prevented</td><td style="color:#10B981">prevented</td><td style="color:#EF4444">possible*</td><td style="color:#10B981">prevented</td><td>MySQL default; financial reads</td></tr>
<tr><td>SERIALIZABLE</td><td style="color:#10B981">prevented</td><td style="color:#10B981">prevented</td><td style="color:#10B981">prevented</td><td style="color:#10B981">prevented</td><td>Accounting, inventory, compliance</td></tr>
</tbody>
</table>
</div>
<p style="font-size:.78rem;color:var(--text-muted);margin-top:-8px;margin-bottom:28px;">*PostgreSQL's REPEATABLE READ also prevents phantom reads via snapshot isolation. MySQL InnoDB uses next-key locks to prevent phantoms at REPEATABLE READ.</p>
<!-- ── Anomalies Explained ────────────────────────────────── -->
<div class="section-label">Anomalies</div>
<h2 class="section-title">Read Anomalies Explained</h2>
<div class="steps">
<div class="step-item">
<div class="step-num">1</div>
<div>
<h4>Dirty Read</h4>
<p>T1 reads a row modified by T2 before T2 has committed. If T2 rolls back, T1 has read data that never officially existed. Example: T2 updates a user's balance from 100 to 50, T1 reads 50, T2 aborts — T1 acted on a false balance. Only occurs at READ UNCOMMITTED.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">2</div>
<div>
<h4>Non-Repeatable Read</h4>
<p>T1 reads a row, T2 commits an UPDATE to that row, T1 reads the same row again and gets a different value. The same query produced two different results within one transaction. Prevented at REPEATABLE READ and above, where T1 sees a snapshot frozen at its start time.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">3</div>
<div>
<h4>Phantom Read</h4>
<p>T1 executes <code>SELECT * FROM orders WHERE amount > 100</code> and gets 5 rows. T2 commits an INSERT of an order with amount=200. T1 re-runs the same query and now gets 6 rows — the "phantom" row appeared. Prevented only at SERIALIZABLE, which either uses predicate locks or detects the dependency (SSI in PostgreSQL).</p>
</div>
</div>
<div class="step-item">
<div class="step-num">4</div>
<div>
<h4>Lost Update</h4>
<p>T1 and T2 both read balance=100. T1 writes 150 (100+50). T2 writes 150 (100+50). T2's write overwrites T1's — one update is lost; the balance should be 200. Fixed with <code>SELECT FOR UPDATE</code>, atomic updates (<code>SET balance = balance + 50</code>), or optimistic locking with a version column.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">5</div>
<div>
<h4>Write Skew</h4>
<p>Two transactions each read overlapping data and each write non-overlapping data, but their combined effect violates an invariant. Example: hospital on-call system requires at least 1 doctor on duty. T1 and T2 each see 2 doctors on duty, each assumes the other doctor stays, each removes themselves — both leave, violating the constraint. Only SERIALIZABLE prevents write skew.</p>
</div>
</div>
</div>
<!-- ── WAL ────────────────────────────────────────────────── -->
<div class="section-label">Durability Mechanism</div>
<h2 class="section-title">Write-Ahead Logging (WAL)</h2>
<p class="section-desc">WAL is the mechanism behind both atomicity and durability. The rule: before any data page is written to disk, the log record describing that change must already be on disk. This "write-ahead" constraint means the log is always ahead of the data — so recovery after a crash can always reconstruct the correct state.</p>
<div class="steps">
<div class="step-item">
<div class="step-num">1</div>
<div>
<h4>Log record types</h4>
<p>The WAL contains two types of records. Redo records describe the change that was made — enough information to replay the change if needed. Undo records describe how to reverse a change — used when a transaction is cancelled or the database is recovering from a crash and needs to clean up incomplete work. There is also a special type of log entry (called a Compensation Log Record) that records when an undo step has already been applied, preventing any step from being applied twice.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">2</div>
<div>
<h4>Group commit</h4>
<p>Writing to disk is slow — it can take several milliseconds per write. If the database had to write to disk for every single transaction before confirming it, performance would collapse under load. Instead, databases wait just a moment and batch up multiple transactions' journal entries into one disk write. This "group commit" trick means one disk write confirms dozens of transactions at once. It is a major reason high-traffic databases can handle thousands of transactions per second.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">3</div>
<div>
<h4>Checkpoints</h4>
<p>Periodically, the database takes a "checkpoint" — it writes all in-memory changes to disk and marks a safe point in the journal. Think of it like saving your progress in a video game. If a crash happens, recovery does not need to replay the entire journal from the very beginning — only from the last checkpoint. More frequent checkpoints mean faster recovery after a crash, but they also mean more work during normal operation. The interval is configurable.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">4</div>
<div>
<h4>ARIES recovery algorithm</h4>
<p>When the database crashes, it replays a journal (called the WAL) to rebuild exactly what happened, then undoes any incomplete work. This process is called ARIES recovery. It happens in three steps: first, scan the journal to figure out which transactions were in-progress when the crash happened (Analysis). Second, replay all the journal entries from the last checkpoint to reconstruct the exact state at the moment of the crash (Redo). Third, reverse any transactions that were still in-progress — they were never committed, so their changes should not survive (Undo). After these three steps, the database is in a clean, consistent state.</p>
</div>
</div>
</div>
<!-- ── Two-Phase Locking ──────────────────────────────────── -->
<div class="section-label">Locking Protocol</div>
<h2 class="section-title">Two-Phase Locking (2PL)</h2>
<p class="section-desc">2PL is the classic locking protocol for achieving serializability. It has two strict rules: (1) before reading, acquire a shared lock; before writing, acquire an exclusive lock. (2) Once a lock is released, no new locks can be acquired. This creates a "growing phase" and a "shrinking phase."</p>
<div class="table-wrap">
<table class="compare-table">
<thead>
<tr><th>Lock Type</th><th>Compatible With Shared?</th><th>Compatible With Exclusive?</th><th>Use Case</th></tr>
</thead>
<tbody>
<tr><td>Shared (S)</td><td style="color:#10B981">Yes — multiple readers</td><td style="color:#EF4444">No — blocks writers</td><td>SELECT, SELECT FOR SHARE</td></tr>
<tr><td>Exclusive (X)</td><td style="color:#EF4444">No — blocks readers</td><td style="color:#EF4444">No — only one writer</td><td>UPDATE, DELETE, SELECT FOR UPDATE</td></tr>
<tr><td>Intent Shared (IS)</td><td style="color:#10B981">Yes</td><td style="color:#EF4444">No</td><td>Signals intent to lock rows; allows table-level checks</td></tr>
<tr><td>Intent Exclusive (IX)</td><td style="color:#10B981">Yes</td><td style="color:#EF4444">No</td><td>Table-level marker for row-level exclusive locks</td></tr>
</tbody>
</table>
</div>
<div class="steps">
<div class="step-item">
<div class="step-num">1</div>
<div>
<h4>Strict 2PL</h4>
<p>Two-Phase Locking (2PL) is a classic strategy for preventing transactions from interfering. It has two rules: first, acquire all locks you need before making any changes. Second, release all locks at the end (when you commit or rollback). In the strict version used in practice, all locks are held until the very end. This prevents a chain reaction where one transaction's rollback forces another's — called cascading aborts. The downside is that it can cause contention when many transactions compete for the same rows.</p>
</div>
</div>
<div class="step-item">
<div class="step-num">2</div>
<div>
<h4>Deadlock detection</h4>
<p>Imagine two people each holding one key and waiting for the other's key. Neither can move forward — this is a deadlock. In databases, it happens when Transaction A is waiting for a row locked by Transaction B, and Transaction B is waiting for a row locked by Transaction A. Both wait forever. The database detects this by periodically checking whether any transactions are caught in a circular wait. When a deadlock is found, the database picks one transaction as the "victim" and rolls it back, letting the other proceed. The application must catch this error and retry.</p>
</div>
</div>
</div>
<!-- ── Interactive Demo ───────────────────────────────────── -->
<div class="section-label">Interactive Demo</div>
<h2 class="section-title">Transaction Timeline</h2>
<p class="section-desc">Drag the progress slider to advance time through two concurrent transactions. Change the isolation level to see which anomalies appear or disappear.</p>
<div class="diagram-box">
<svg viewBox="0 0 700 220" role="img" aria-label="ACID transaction properties and two-phase commit timeline">
<!-- ACID boxes -->
<rect x="20" y="20" width="140" height="72" rx="8" class="svg-box-c"/>
<text x="90" y="43" text-anchor="middle" class="svg-label">Atomicity</text>
<text x="90" y="62" text-anchor="middle" class="svg-soft">All-or-nothing.</text>
<text x="90" y="78" text-anchor="middle" class="svg-soft">Rollback on failure.</text>
<rect x="180" y="20" width="140" height="72" rx="8" class="svg-box-p"/>
<text x="250" y="43" text-anchor="middle" class="svg-label">Consistency</text>
<text x="250" y="62" text-anchor="middle" class="svg-soft">Constraints always</text>
<text x="250" y="78" text-anchor="middle" class="svg-soft">hold before & after.</text>
<rect x="340" y="20" width="140" height="72" rx="8" class="svg-box-a"/>
<text x="410" y="43" text-anchor="middle" class="svg-label">Isolation</text>
<text x="410" y="62" text-anchor="middle" class="svg-soft">Concurrent txns</text>
<text x="410" y="78" text-anchor="middle" class="svg-soft">appear sequential.</text>
<rect x="500" y="20" width="180" height="72" rx="8" class="svg-box-g"/>
<text x="590" y="43" text-anchor="middle" class="svg-label">Durability</text>
<text x="590" y="62" text-anchor="middle" class="svg-soft">Committed writes</text>
<text x="590" y="78" text-anchor="middle" class="svg-soft">survive any crash.</text>
<!-- Timeline -->
<text x="20" y="125" class="svg-soft">T1</text>
<line x1="40" y1="130" x2="440" y2="130" class="svg-line" style="stroke:#7C3AED"/>
<rect x="40" y="120" width="80" height="20" rx="4" class="svg-box-p"/>
<text x="80" y="134" text-anchor="middle" class="svg-mono" style="font-size:10px">BEGIN</text>
<rect x="140" y="120" width="100" height="20" rx="4" class="svg-box-c"/>
<text x="190" y="134" text-anchor="middle" class="svg-mono" style="font-size:10px">READ/WRITE</text>
<rect x="260" y="120" width="80" height="20" rx="4" class="svg-box-g"/>
<text x="300" y="134" text-anchor="middle" class="svg-mono" style="font-size:10px">COMMIT</text>
<line x1="300" y1="140" x2="300" y2="168" class="svg-line" style="stroke:#10B981"/>
<text x="20" y="165" class="svg-soft">T2</text>
<line x1="40" y1="170" x2="660" y2="170" class="svg-line" style="stroke:#0EA5E9"/>
<rect x="160" y="160" width="80" height="20" rx="4" class="svg-box-c"/>
<text x="200" y="174" text-anchor="middle" class="svg-mono" style="font-size:10px">BEGIN</text>
<rect x="260" y="160" width="100" height="20" rx="4" class="svg-box-c"/>
<text x="310" y="174" text-anchor="middle" class="svg-mono" style="font-size:10px">READ/WRITE</text>
<rect x="380" y="160" width="80" height="20" rx="4" class="svg-box-r"/>
<text x="420" y="174" text-anchor="middle" class="svg-mono" style="font-size:10px">ROLLBACK</text>
<!-- WAL note -->
<text x="540" y="160" class="svg-soft" style="fill:#F59E0B">WAL flush</text>
<text x="540" y="177" class="svg-soft" style="fill:#F59E0B">before COMMIT</text>
<!-- Packets -->
<circle class="pkt" cx="140" cy="130" r="5" fill="#7C3AED"/>
<circle class="pkt" cx="260" cy="130" r="5" fill="#10B981"/>
<circle class="pkt" cx="380" cy="170" r="5" fill="#EF4444"/>
</svg>
<p class="diagram-caption">ACID is a contract between the application and the DB engine. Isolation is the expensive property — weaker levels trade anomaly risk for throughput. Durability is free after the WAL write; the actual page flush can happen lazily.</p>
</div>
<div class="demo-section" id="demo-transaction">
<div class="demo-header">
<h3>Concurrent Transaction Simulator</h3>
<div class="demo-controls">
<button class="demo-btn" data-isolation="READ_UNCOMMITTED">READ UNCOMMITTED</button>
<button class="demo-btn active" data-isolation="READ_COMMITTED">READ COMMITTED</button>
<button class="demo-btn" data-isolation="REPEATABLE_READ">REPEATABLE READ</button>
<button class="demo-btn" data-isolation="SERIALIZABLE">SERIALIZABLE</button>
</div>
</div>
<div class="demo-canvas-wrap"></div>
<div class="demo-hint">Drag the blue progress dot left/right to travel through time. Anomalies appear in red.</div>
</div>
<!-- ── Implementation Comparison ────────────────────────── -->
<div class="section-label">Database Comparison</div>
<h2 class="section-title">How Major Databases Implement Transactions</h2>
<div class="table-wrap">
<table class="compare-table">
<thead>
<tr><th>Database</th><th>Default Isolation</th><th>Concurrency Strategy</th><th>WAL Format</th><th>Notable Behavior</th></tr>
</thead>
<tbody>
<tr><td>PostgreSQL</td><td>READ COMMITTED</td><td>MVCC (heap + xmin/xmax)</td><td>WAL (redo only)</td><td>SSI at SERIALIZABLE; VACUUM needed for dead tuple cleanup</td></tr>
<tr><td>MySQL InnoDB</td><td>REPEATABLE READ</td><td>MVCC + 2PL</td><td>Redo log + undo log (separate)</td><td>Gap locks prevent phantoms; next-key locking</td></tr>
<tr><td>Oracle</td><td>READ COMMITTED</td><td>MVCC (undo segments)</td><td>Redo log (online + archived)</td><td>Undo tablespace holds old versions; autonomous transactions</td></tr>
<tr><td>SQL Server</td><td>READ COMMITTED</td><td>2PL (default) or RCSI/SI</td><td>Transaction log</td><td>RCSI (Read Committed Snapshot Isolation) opt-in; TempDB for versions</td></tr>
<tr><td>CockroachDB</td><td>SERIALIZABLE</td><td>MVCC + SSI</td><td>Raft log per range</td><td>Distributed transactions via 2PC over Raft; automatic retries on serialization failures</td></tr>
<tr><td>Google Spanner</td><td>SERIALIZABLE</td><td>MVCC + True Time</td><td>Distributed Paxos log</td><td>External consistency via TrueTime atomic clocks; linearizable reads globally</td></tr>
</tbody>
</table>
</div>
<!-- ── Practical Patterns ────────────────────────────────── -->
<div class="section-label">Application Patterns</div>
<h2 class="section-title">Safe Transaction Patterns</h2>
<div class="code-block">
<div class="cb-header"><span class="cb-lang">SQL — Patterns for Correctness</span></div>
<pre><span class="cmt">-- Pattern 1: Atomic increment (safe under any isolation level)</span>
<span class="kw">UPDATE</span> counters <span class="kw">SET</span> value = value + <span class="num">1</span> <span class="kw">WHERE</span> id = <span class="num">1</span>;
<span class="cmt">-- The DB evaluates value+1 atomically — no read-then-write race</span>
<span class="cmt">-- Pattern 2: Upsert (INSERT ... ON CONFLICT)</span>
<span class="kw">INSERT INTO</span> user_sessions (user_id, token, created_at)
<span class="kw">VALUES</span> (<span class="num">42</span>, <span class="str">'abc123'</span>, now())
<span class="kw">ON CONFLICT</span> (user_id) <span class="kw">DO UPDATE SET</span>
token = <span class="kw">EXCLUDED</span>.token,
created_at = <span class="kw">EXCLUDED</span>.created_at;
<span class="cmt">-- Atomic: no TOCTOU race between checking and inserting</span>
<span class="cmt">-- Pattern 3: Conditional update (optimistic concurrency)</span>
<span class="kw">UPDATE</span> inventory
<span class="kw">SET</span> quantity = quantity - <span class="num">1</span>, version = version + <span class="num">1</span>
<span class="kw">WHERE</span> product_id = <span class="num">99</span>
<span class="kw">AND</span> version = <span class="num">14</span> <span class="cmt">-- stale version check</span>
<span class="kw">AND</span> quantity >= <span class="num">1</span>; <span class="cmt">-- prevent negative stock</span>
<span class="cmt">-- If 0 rows affected, someone else won — retry at application level</span>
<span class="cmt">-- Pattern 4: SELECT FOR UPDATE SKIP LOCKED (work queue)</span>
<span class="kw">BEGIN</span>;
<span class="kw">SELECT</span> id, payload <span class="kw">FROM</span> job_queue
<span class="kw">WHERE</span> status = <span class="str">'pending'</span>
<span class="kw">ORDER BY</span> priority <span class="kw">DESC</span>
<span class="kw">LIMIT</span> <span class="num">1</span>
<span class="kw">FOR UPDATE SKIP LOCKED</span>; <span class="cmt">-- skip rows locked by other workers</span>
<span class="kw">UPDATE</span> job_queue <span class="kw">SET</span> status = <span class="str">'processing'</span> <span class="kw">WHERE</span> id = :id;
<span class="kw">COMMIT</span>;
<span class="cmt">-- Each worker atomically claims a different job — no double-processing</span></pre>
</div>
<!-- ── Anti-patterns ──────────────────────────────────────── -->
<div class="section-label">Anti-patterns</div>
<div class="antipatterns">
<div class="antipattern">
<h4>Long-running transactions</h4>
<p>Transactions held open for seconds or minutes accumulate MVCC versions (bloating the database and preventing VACUUM from reclaiming space), block DDL operations, and increase the blast radius of failures. Keep transactions short. Never hold a transaction open waiting for user input. Prepare data, then open the transaction and commit immediately.</p>
</div>
<div class="antipattern">
<h4>HTTP calls inside a transaction</h4>
<p>If the HTTP call hangs (30s timeout), your transaction holds locks for 30 seconds. Every concurrent transaction touching those rows is blocked. Never call external services inside BEGIN...COMMIT. Prepare and validate all data, commit, then make external calls. Use the outbox pattern for reliable event publishing.</p>
</div>
<div class="antipattern">
<h4>Using READ UNCOMMITTED in application code</h4>
<p>READ UNCOMMITTED is almost never the right choice. The only defensible use is approximate analytics dashboards where you genuinely need the fastest possible read and can tolerate seeing in-progress writes. PostgreSQL doesn't even support true dirty reads at READ UNCOMMITTED — it silently uses READ COMMITTED behavior. In MySQL/SQL Server, the risk is real.</p>
</div>
<div class="antipattern">
<h4>Read-modify-write outside a transaction</h4>
<p>Fetching a value in application code, modifying it, then writing it back is a race condition under any isolation level. Two requests reading the same value concurrently will both write based on the stale read. Use atomic SQL updates (<code>SET x = x + 1</code>), SELECT FOR UPDATE, or optimistic locking with a version column instead.</p>
</div>
<div class="antipattern">
<h4>Ignoring serialization failures</h4>
<p>At SERIALIZABLE isolation (especially PostgreSQL's SSI), the DB can abort a transaction with a <code>40001: could not serialize access</code> error to prevent a write skew anomaly. Applications must retry on this error. Silently propagating it to the user as a 500 error is wrong — wrap transaction code in a retry loop.</p>
</div>
</div>
<!-- ── Quiz ──────────────────────────────────────────────── -->
<div class="section-label">Quiz</div>
<div class="quiz-section">
<div class="quiz-question">
<div class="quiz-q-num">Question 1 of 5</div>
<div class="quiz-q-text">Which isolation level prevents dirty reads but still allows non-repeatable reads?</div>
<div class="quiz-options">
<div class="quiz-option" data-correct="false" data-explanation="READ UNCOMMITTED allows dirty reads — the weakest isolation level."><span class="opt-letter">A</span>READ UNCOMMITTED</div>
<div class="quiz-option" data-correct="true" data-explanation="READ COMMITTED prevents dirty reads (only reads committed data) but another transaction can commit an update between two reads within your transaction — that is a non-repeatable read."><span class="opt-letter">B</span>READ COMMITTED</div>
<div class="quiz-option" data-correct="false" data-explanation="REPEATABLE READ also prevents non-repeatable reads — it is stricter than READ COMMITTED."><span class="opt-letter">C</span>REPEATABLE READ</div>
<div class="quiz-option" data-correct="false" data-explanation="SERIALIZABLE is the strictest level and prevents all standard anomalies including phantom reads."><span class="opt-letter">D</span>SERIALIZABLE</div>
</div>
<div class="quiz-feedback"></div>
</div>
<div class="quiz-question">
<div class="quiz-q-num">Question 2 of 5</div>
<div class="quiz-q-text">In MVCC, a row is visible to your transaction if:</div>
<div class="quiz-options">
<div class="quiz-option" data-correct="false" data-explanation="MVCC does not use locks for reads — that is 2PL's approach."><span class="opt-letter">A</span>You hold a shared lock on the row</div>
<div class="quiz-option" data-correct="true" data-explanation="MVCC visibility check: xmin committed before your snapshot AND (xmax is 0 or xmax not yet committed or xmax greater than your snapshot XID). This means you see a snapshot frozen at your transaction start time."><span class="opt-letter">B</span>Its xmin committed before your snapshot and its xmax is 0 or not yet committed</div>
<div class="quiz-option" data-correct="false" data-explanation="The latest version is not necessarily the one visible to your snapshot — an older version may be correct for your snapshot timestamp."><span class="opt-letter">C</span>It is the most recently written version of the row</div>
<div class="quiz-option" data-correct="false" data-explanation="MVCC explicitly keeps old row versions around for readers — it does not require exclusive access to read."><span class="opt-letter">D</span>No other transaction has a pending write to that row</div>
</div>
<div class="quiz-feedback"></div>
</div>
<div class="quiz-question">
<div class="quiz-q-num">Question 3 of 5</div>
<div class="quiz-q-text">A transaction that aborts must:</div>
<div class="quiz-options">
<div class="quiz-option" data-correct="false" data-explanation="Committing partial changes violates Atomicity — the A in ACID."><span class="opt-letter">A</span>Commit any completed partial changes</div>
<div class="quiz-option" data-correct="false" data-explanation="Locks must be released on abort so other transactions can proceed."><span class="opt-letter">B</span>Retain all acquired locks indefinitely</div>
<div class="quiz-option" data-correct="true" data-explanation="Atomicity requires that an aborted transaction has NO visible effect. All changes must be rolled back via the undo log, restoring the database to its pre-transaction state."><span class="opt-letter">C</span>Roll back all changes, leaving no visible effect</div>
<div class="quiz-option" data-correct="false" data-explanation="Automatic retry is an application concern, not a DBMS guarantee. The DBMS only ensures rollback."><span class="opt-letter">D</span>Retry automatically until it succeeds</div>
</div>
<div class="quiz-feedback"></div>
</div>
<div class="quiz-question">
<div class="quiz-q-num">Question 4 of 5</div>
<div class="quiz-q-text">Which anomaly can occur even at REPEATABLE READ isolation level (according to the SQL standard)?</div>
<div class="quiz-options">
<div class="quiz-option" data-correct="false" data-explanation="Dirty reads are prevented at READ COMMITTED and above — they cannot occur at REPEATABLE READ."><span class="opt-letter">A</span>Dirty read</div>
<div class="quiz-option" data-correct="false" data-explanation="Non-repeatable reads are prevented by REPEATABLE READ — that is the whole point of that level."><span class="opt-letter">B</span>Non-repeatable read</div>
<div class="quiz-option" data-correct="true" data-explanation="Phantom reads — where a range query returns new rows on a second execution — are only fully prevented at SERIALIZABLE. The SQL standard allows phantoms at REPEATABLE READ. Note: PostgreSQL's implementation also prevents phantoms at REPEATABLE READ via snapshot isolation."><span class="opt-letter">C</span>Phantom read</div>
<div class="quiz-option" data-correct="false" data-explanation="Lost updates are prevented at REPEATABLE READ because the transaction's snapshot will detect a write conflict."><span class="opt-letter">D</span>Lost update</div>
</div>
<div class="quiz-feedback"></div>
</div>
<div class="quiz-question">
<div class="quiz-q-num">Question 5 of 5</div>
<div class="quiz-q-text">What does WAL's "write-ahead" rule mean?</div>
<div class="quiz-options">
<div class="quiz-option" data-correct="false" data-explanation="Data pages flushed before log records is the opposite of write-ahead — it would break recovery."><span class="opt-letter">A</span>Data pages must be flushed to disk before the log record is written</div>
<div class="quiz-option" data-correct="true" data-explanation="The log record describing a change must be on durable storage before the data page is written to disk. This ensures that if a crash occurs after the data write but before commit, recovery can undo the change using the log."><span class="opt-letter">B</span>The log record must be on durable storage before the data page is written to disk</div>
<div class="quiz-option" data-correct="false" data-explanation="Transactions don't need to wait for the previous transaction's log record — each transaction's log records are independent."><span class="opt-letter">C</span>Each transaction must wait for the previous transaction's log record to flush</div>
<div class="quiz-option" data-correct="false" data-explanation="Checkpoints are periodic, not per-commit. Waiting for a full checkpoint on every commit would be extremely slow."><span class="opt-letter">D</span>A checkpoint must complete before each commit</div>
</div>
<div class="quiz-feedback"></div>
</div>
</div>
<!-- ── Interview Q&A ──────────────────────────────────────── -->
<div class="section-label">Interview Q&A</div>
<div class="qa-section">
<div class="qa-item">
<div class="qa-q">How does MVCC work in PostgreSQL? Walk me through the xmin/xmax mechanism.<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">PostgreSQL stores multiple versions of each row in the heap. Every row version has two hidden system columns: <code>xmin</code> (XID of the transaction that created this version) and <code>xmax</code> (XID of the transaction that deleted/updated it, or 0 if still live). When a transaction begins, it acquires a snapshot: the current XID high-water mark and the set of all in-progress XIDs. A row version is visible if its xmin is committed and below the snapshot's XID (and not in the in-progress set), and its xmax is either 0, uncommitted, or above the snapshot's XID. Writes create a new row version with a new xmin; they set the old version's xmax to the writing XID but do not physically delete it. VACUUM later reclaims dead versions. This means readers never block writers and writers never block readers — only writer-writer conflicts cause blocking.</div></div>
</div>
<div class="qa-item">
<div class="qa-q">What is a dirty read, and when can it actually be useful?<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">A dirty read is when transaction T1 reads data written by T2 that has not committed yet. If T2 rolls back, T1 read data that never officially existed. In most application code, this is never acceptable. Legitimate uses: (1) approximate analytics dashboards — you want the freshest in-progress data for a live view counter and a slightly stale read is fine; (2) monitoring queries where seeing in-progress state is the intent; (3) debugging — seeing what an in-progress transaction is doing. Important caveat: PostgreSQL does not actually implement dirty reads even at READ UNCOMMITTED — it silently uses READ COMMITTED semantics. True dirty reads require SQL Server, MySQL, or Oracle with READ UNCOMMITTED specified.</div></div>
</div>
<div class="qa-item">
<div class="qa-q">Explain the lost update problem. What are the different solutions?<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">Lost update: T1 reads balance=100, T2 reads balance=100, T1 writes 150 (100+50), T2 writes 150 (100+50) — T1's update is overwritten by T2. The correct result is 200. Three solutions: (1) Atomic SQL update: <code>UPDATE SET balance = balance + 50</code> — the DB evaluates this atomically, serializing concurrent increments correctly. Best when you just need to increment/decrement. (2) SELECT FOR UPDATE: acquire an exclusive row lock at read time. T2 blocks until T1 commits, then reads the updated value 150 and writes 200. Correct but creates contention. (3) Optimistic locking with version column: read version=5, write WHERE version=5, if 0 rows affected someone else won, retry. Best for low-contention workloads — no lock held between read and write.</div></div>
</div>
<div class="qa-item">
<div class="qa-q">What is write skew and why does it require SERIALIZABLE to prevent?<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">Write skew is a subtle anomaly where two transactions each read an overlapping set of rows and each write to non-overlapping rows, but their combined effect violates an application-level invariant that neither transaction individually violated. Classic example: a hospital requires at least 1 doctor on duty. Two doctors each check: "are there 2+ doctors on duty?" — yes. Each assumes the other stays, each marks themselves off duty. Result: 0 doctors on duty. Both transactions were individually valid; together they broke the invariant. REPEATABLE READ cannot prevent this because the writes don't touch overlapping rows — there is no write-write conflict to detect. SERIALIZABLE (specifically SSI in PostgreSQL) detects the anti-dependency: T1's decision depended on the rows T2 modified, creating a cycle, and aborts one of them.</div></div>
</div>
<div class="qa-item">
<div class="qa-q">How does PostgreSQL's Serializable Snapshot Isolation (SSI) work?<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">SSI (introduced in PostgreSQL 9.1) achieves true SERIALIZABLE isolation without the blocking of traditional 2PL. It works by detecting dangerous anti-dependency cycles in the transaction dependency graph. Transactions are tracked in a "SIREAD lock" structure: every read acquires a predicate lock recording what was read. When a write happens, the engine checks whether any concurrent transaction read data that this write affects — recording an "rw-anti-dependency." If a cycle of anti-dependencies is detected (T1 depends on T2 which depends on T1), one transaction is aborted with error code 40001. Applications must retry. SSI has lower throughput than READ COMMITTED and can cause false positives (aborting transactions that actually would have been fine), but it is the gold standard for correctness under concurrency.</div></div>
</div>
<div class="qa-item">
<div class="qa-q">What are savepoints and when should you use them?<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">A savepoint marks a point within a transaction to which you can roll back without aborting the entire transaction. Syntax: <code>SAVEPOINT name</code>, <code>ROLLBACK TO SAVEPOINT name</code>, <code>RELEASE SAVEPOINT name</code>. Use cases: (1) Bulk imports with partial error tolerance — insert a row, SAVEPOINT, if it fails roll back to savepoint and skip that row, continue with the next. (2) Multi-step workflows where early steps must survive even if later steps fail. (3) Testing — wrap the test body in a transaction, use savepoints to reset between sub-tests, roll back the outer transaction at the end to leave no test data. Important: ROLLBACK TO SAVEPOINT does not release the savepoint — you must RELEASE it explicitly or it persists until end of transaction. Do not use savepoints as a substitute for proper error handling design.</div></div>
</div>
<div class="qa-item">
<div class="qa-q">When would you choose optimistic locking over SELECT FOR UPDATE?<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">Choose optimistic locking when contention is low and the cost of occasional retries is acceptable. SELECT FOR UPDATE holds an exclusive lock from read to commit — under high contention this creates a queue and throughput collapses. Optimistic locking with a version column holds no DB lock between read and write; retries only happen on actual conflicts. It scales better horizontally and works across microservices (you can pass the version to a different service that does the write). Use SELECT FOR UPDATE when: (1) conflicts are frequent and you need guaranteed forward progress rather than retry-based progress; (2) you need to read-then-write atomically and cannot use an atomic SQL expression; (3) you are implementing a queue (FOR UPDATE SKIP LOCKED is excellent for job queues). The key question: is the read-write window measured in milliseconds (optimistic fine) or seconds (use FOR UPDATE or redesign the flow)?</div></div>
</div>
<div class="qa-item">
<div class="qa-q">What happens when a transaction deadlocks? How does the database handle it?<span class="qa-chevron">▾</span></div>
<div class="qa-a"><div class="qa-a-inner">A deadlock occurs when T1 holds lock A and waits for lock B, while T2 holds lock B and waits for lock A — both block forever. Detection: the DBMS maintains a waits-for graph. Each lock wait is an edge: "T1 waits for T2". A cycle in this graph is a deadlock. Most databases run deadlock detection periodically (every 1 second in PostgreSQL) or on every lock acquisition attempt. When a deadlock is found, one transaction is chosen as the "victim" and rolled back (typically the cheapest to abort — fewest rows modified). The victim receives an error (PostgreSQL: <code>40P01: deadlock detected</code>). The application must catch this error and retry. Prevention strategies: always acquire locks in a consistent order across transactions (e.g., always lock lower account ID before higher); use lock timeouts (<code>SET lock_timeout = '2s'</code>); keep transactions short to reduce the window for deadlocks.</div></div>
</div>
</div>
<div class="section-label">Further Reading</div>
<div class="reading-links">
<a class="reading-link" href="https://www.postgresql.org/docs/current/transaction-iso.html" target="_blank">PostgreSQL Isolation Levels</a>
<a class="reading-link" href="https://jepsen.io/analyses" target="_blank">Jepsen Distributed Systems Analysis</a>
<a class="reading-link" href="https://martin.kleppmann.com/2015/09/26/transactions-at-various-isolation-levels.html" target="_blank">Kleppmann: Isolation Levels</a>
<a class="reading-link" href="https://www.cs.cmu.edu/~natassa/courses/15-721/papers/p1-mohan.pdf" target="_blank">ARIES: A Transaction Recovery Method (Mohan et al.)</a>
<a class="reading-link" href="https://postgrespro.com/blog/pgsql/5967948" target="_blank">PostgreSQL MVCC Internals Deep Dive</a>
</div>
<div class="topic-nav">
<a href="04-normalization.html" class="topic-nav-link"><div class="topic-nav-arrow">←</div><div><div class="topic-nav-label">Previous</div><div class="topic-nav-title">Normalization</div></div></a>
<a href="06-indexing.html" class="topic-nav-link next"><div class="topic-nav-arrow">→</div><div><div class="topic-nav-label">Next</div><div class="topic-nav-title">Indexing</div></div></a>
</div>
</div>
<footer class="site-footer">
<p class="footer-sub"><a href="../index.html">Back to Course</a> — DBMS Illustrated</p>
</footer>
<script src="../js/main.js"></script>
<script src="../js/demos.js"></script>
</body>
</html>