Transactions and Isolation
Tom Kelliher, CS 318
Apr. 29, 2002
Normal form analyses due Wednesday. Toolboxes and projects due Friday.
Review for final on Friday. Course evaluation on Friday.
Access path considerations for various DB operations.
- Why transactions?
- PostgreSQL transaction features.
- Isolated transactions, serializability, lock granularity
Atomicity, durability, and distributed transactions.
- Consistency need not be maintained during a transaction.
Example: Holdings and Balance tables. For consistency, Balance should
reflect all Holdings.
- ACID properties:
- Atomicity --- All or nothing.
- Consistency --- If consistent before, consistent after.
- Isolation --- Concurrent transactions behave as is they were
- Durability --- Once committed, remembered.
BEGIN --- Begin a transaction in chained mode.
Ordinarily, each SQL statement is considered its own transaction (unchained
COMMIT --- End a transaction, applying all modifications.
ROLLBACK --- End a transaction, rolling back all
SET TRANSACTION ISOLATION LEVEL READ COMMITTED,
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE --- Set isolation
level. Default is read committed. Should be set immediately following
LOCK --- Explicitly lock a table or row. Several options
available. PostgreSQL always locks at the least restrictive level.
LOCK allows the designer to override this behavior. Should be given
BEGIN. Refer to online documentation.
- Consider two transactions in a banking system.
- T1 is a deposit of $100.
Operations: R1(x), W1(x).
- T2 is a withdrawal of $50 from the same account.
Operations: R2(x), w2(x).
- Two correct serial execution schedules:
- R1(x), W1(x), R2(x), W2(x).
- R2(x), W2(x), R1(x), W1(x).
- An incorrect concurrent schedule: R1(x), R2(x), W1(x), W2(x).
Note that R2(x) and W1(x) were commuted, relative to the first serial
- Which operations are safe to commute?
- Serializable schedule --- a concurrent schedule which corresponds to
some serial schedule.
Examples: R1(y), R2(x), R1(x), R2(y). R1(x), R2(y), W1(x), W2(y).
Two reads always commute. A write and another operation commute if they
reference different objects.
- Concurrency control: The part of the transaction processing system
that enforces isolation.
- Use strict two-phase locking protocol:
- Phase 1: Obtain locks.
- Phase 2: Release locks.
- Strict: all locks are held until transaction commits or rollbacks.
Nonstrict: some locks are released before others.
- Types of locks:
- Read lock: Item may be read. May be shared with other read
A read lock request will wait for the release of a write lock on the
- Write lock: Item may be read or written. Not shareable with other
A write lock request will wait for the release of a write lock or all
read locks on the same item.
- Possible problems with nonstrict protocols: dirty read, nonrepeatable
read, lost update.
Example of a dirty read: W1(x), Rel1(x), R2(x), Rollb1(x).
A nonrepeatable read: R1(x) Rel1(x), W2(x), Commit2(x), R1(x).
Banking example illustrated lost update.
- Table locks: coarse.
- Row or field locks: fine.
- Impact upon concurrency?
- DB isolation levels:
Write locks are always of long duration.
- Read uncommitted: Read without obtaining a read lock. Dirty reads
- Read committed: Short term read lock acquired. Nonrepeatable
- Repeatable read: Long term tuple read locks acquired. Phantoms
- Serializable: Long term table read locks acquired. Concurrency
- Phantoms: an anomaly of row locking.
- T1 read locks all tuples satisfying
FROM Transcript T
WHERE T.Studid = '123456';
- Subsequently, T2 inserts a new Transcript record for 123456,
before T1 finishes.
- The new record won't be seen by T1 --- it is a phantom.
- Intention locks necessary in presence of various lock
Thomas P. Kelliher
Fri Apr 26 17:56:25 EDT 2002