Weak isolation

Are strategies for prevent carrer condition in databases. In week isolation exists a few levels:

• Read Committed:
  • Guarantees: no dirty read, no dirty writes.
  • Use a exclusive lock for transactions, that's means that other transaction can't modify until the first transaction commit or abort.
  • This method allow Write Skew
• Snapshot Isolation (SI):
  • Guarantees: each transaction see a consistent snapshot of the whole database
  • Uses MVVC (Multi-Version Concurrency Control): the database never write data in-place, instead each write creates a new version of the row. Each version have a metadata: created_by and deleted_by
  • Garbage collection: Old versions can be cleaned up when no active transactions need them. This is why very long transactions can cause bloat.
  • This method allow Write Skew

Example of a Write Skew:

-- Invariant: there must always be at least 1 doctor on call
-- Initial state: alice=on_call, bob=on_call

=== SNAPSHOT ISOLATION (write skew occurs) ===

Tx1: BEGIN
Tx2: BEGIN
Tx1: SELECT count(*) FROM doctors WHERE on_call = true  → 2 (snapshot at Tx1 start)
Tx2: SELECT count(*) FROM doctors WHERE on_call = true  → 2 (snapshot at Tx2 start)
Tx1: "there are 2, I can leave" → UPDATE doctors SET on_call=false WHERE name='alice'
Tx2: "there are 2, I can leave" → UPDATE doctors SET on_call=false WHERE name='bob'
Tx1: COMMIT ✓
Tx2: COMMIT ✓  ← both commits succeed, but now count = 0!

No write-write conflict detected because they modify different rows.
Each transaction's premise (count >= 2) was invalidated by the other.


=== READ COMMITTED (write skew also occurs, but for a different reason) ===

Tx1: BEGIN
Tx2: BEGIN
Tx1: SELECT count(*) FROM doctors WHERE on_call = true  → 2
Tx2: SELECT count(*) FROM doctors WHERE on_call = true  → 2
Tx1: UPDATE doctors SET on_call=false WHERE name='alice'
Tx1: COMMIT ✓
Tx2: UPDATE doctors SET on_call=false WHERE name='bob'
     (if Tx2 did a SELECT now, it would see count=1, but it already decided)
Tx2: COMMIT ✓  ← same problem: count = 0!

Read Committed sees committed values at each statement, but Tx2's decision
was based on stale data from before Tx1 committed.


=== READ COMMITTED (different interleaving, same problem) ===

Tx1: BEGIN
Tx2: BEGIN
Tx1: SELECT count(*) FROM doctors WHERE on_call = true  → 2
Tx1: UPDATE doctors SET on_call=false WHERE name='alice'
Tx2: SELECT count(*) FROM doctors WHERE on_call = true  → 2 (Tx1 not committed yet!)
Tx1: COMMIT ✓
Tx2: "there are 2, I can leave" → UPDATE doctors SET on_call=false WHERE name='bob'
Tx2: COMMIT ✓  ← count = 0 again!

Under Read Committed, Tx2's SELECT doesn't see Tx1's uncommitted write.
The anomaly happens because the read and write aren't atomic.


=== SERIALIZABLE (anomaly prevented) ===

Tx1: BEGIN
Tx2: BEGIN
Tx1: SELECT count(*) FROM doctors WHERE on_call = true  → 2
Tx2: SELECT count(*) FROM doctors WHERE on_call = true  → 2
Tx1: UPDATE doctors SET on_call=false WHERE name='alice'
Tx2: UPDATE doctors SET on_call=false WHERE name='bob'
Tx1: COMMIT ✓
Tx2: COMMIT ✗ ROLLBACK (serialization failure detected)

SSI tracks that both transactions read the on_call=true predicate,
then both wrote to rows matching that predicate. It detects the
dependency cycle and aborts one transaction.