Handling Simultaneous Access
When multiple transactions run at the same time, they can interfere with each other. Concurrency control mechanisms ensure that concurrent transactions produce correct results without sacrificing performance.
Isolation Levels
Level │ Dirty Read │ Non-Repeatable │ Phantom
─────────────────────┼────────────┼────────────────┼─────────
Read Uncommitted │ Yes │ Yes │ Yes
Read Committed │ No │ Yes │ Yes
Repeatable Read │ No │ No │ Yes
Serializable │ No │ No │ No
Higher isolation = more safety but less concurrency. Choose based on your needs.
Locks
Shared Lock (S): Multiple transactions can READ simultaneously
Exclusive Lock (X): Only ONE transaction can WRITE at a time
Transaction A reads row: Shared lock on row
Transaction B reads row: Shared lock on row (OK — both read)
Transaction C writes row: Exclusive lock — must wait for A & B
Deadlock
Transaction A: Transaction B:
Locks Row 1 Locks Row 2
Wants Row 2 (waits for B) Wants Row 1 (waits for A)
...waiting forever... ...waiting forever...
Solution: Database detects deadlock and rolls back one transaction