Skip to main content

4 posts tagged with "Transactions"

Transactions

View All Tags

DB-010: Dual Anomalies

· One min read
Abhishek Tripathi
Abhishek Tripathi
Curiosity brings awareness.

Combined Anomaly: Dirty Read Leads to Lost Update

A more complex scenario with 3 transactions on a non-MVCC system. It shows how T2 performing a 'Dirty Read' on T1's uncommitted data directly enables the 'Lost Update' anomaly when T1 later aborts.

Step: 0/10 | Time: 0
Next TxID: 1

Database State (No Versions)

Current Operation

None

Transaction States & Undo Logs

No active transactions.

Transaction Timelines

0
10
20
30
40
50
60
70
80
90
100

T1

B
W
A

T2

B
R
W
C

T3

B
R
C

DB-008: Phantom Read

· One min read
Abhishek Tripathi
Abhishek Tripathi
Curiosity brings awareness.

Transaction Timeline: Phantom Read Anomaly

T1 scans for 'Engineering' employees. T2 inserts and commits a new 'Engineering' employee. T1's subsequent identical scan finds this new 'phantom' row.

Step: 0 / 7
Time: 0

Committed Database State

employees

idnamedepartmentsalary
1AliceEngineering90000
2BobSales75000
4DavidEngineering85000

Uncommitted Inserts

None

Scan Log

No scans yet

Current Operation

None

Transaction Timelines

0
10
20
30
40
50
60

T1

B
S
S
C

T2

B
I
C

Legend

B
Begin
S
Scan (Range Query)
I
Insert
C
Commit
A
Abort

Understanding Phantom Reads

A Phantom Read occurs when a transaction executes a range query (e.g., "find all employees in Engineering") twice, and the set of rows returned by the second query is different from the first. This happens because another transaction has inserted or deleted rows that match the query's criteria and committed those changes in between the two queries of the first transaction. The newly appeared (or disappeared) rows are called "phantoms."

Key Observation Points:

  • Step 2: T1's first scan of 'Engineering' employees finds Alice and David.
  • Step 4 & 5: T2 inserts 'Charlie' into 'Engineering' and commits this change.
  • Step 6: T1's second scan of 'Engineering' employees now finds Alice, David, AND 'Charlie'. 'Charlie' is the phantom row.
  • This anomaly can lead to inconsistencies if T1 was performing calculations or making decisions based on the assumption that the set of rows matching its criteria would not change during its execution.
  • Phantom reads are typically prevented by higher isolation levels like Serializable, or by specific locking strategies (e.g., predicate locks, though not widely implemented, or careful range locking).

DB-007: MVCC Visibility Rules

· One min read
Abhishek Tripathi
Abhishek Tripathi
Curiosity brings awareness.

MVCC Visibility Rules Demonstration

Illustrates how transactions see data based on snapshot isolation. Focus on T3's reads and how its snapshot (taken at t=40) affects what it sees from T1 and T2, even after they commit.

Step: 0/14 | Time: 0
Next TxID: 1 | Committed: [0]

Data Item Versions (Global State)

Current Operation

None

Transaction States & Snapshots

No active transactions.

Transaction Timelines

0
10
20
30
40
50
60
70

T1

B
R
W
R
C

T2

B
R
W
R
C

T3

B
R
R
C

Legend

B
Begin
R
Read
W
Write
C
Commit
A
Abort

DB-001: Database Isolation (dirty reads)

· 2 min read
Abhishek Tripathi
Abhishek Tripathi
Curiosity brings awareness.

Understanding Dirty Reads: A Database Isolation Problem​

What is a Dirty Read?​

A dirty read occurs when a transaction reads data that has been written by another transaction but not yet committed. If the writing transaction rolls back, the data read by the first transaction becomes invalid—hence, "dirty."

This can lead to inconsistent or incorrect results in applications, especially in systems where multiple users or processes are accessing and modifying the data concurrently.