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.
Read 15.2--3.
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
performed serially.
- Durability --- Once committed, remembered.
-
BEGIN
--- Begin a transaction in chained mode.
Ordinarily, each SQL statement is considered its own transaction (unchained
mode).
-
COMMIT
--- End a transaction, applying all modifications.
-
ROLLBACK
--- End a transaction, rolling back all
modifications.
-
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
,
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
--- Set isolation
level. Default is read committed. Should be set immediately following
BEGIN
.
-
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
immediately following 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).
Why incorrect?
Note that R2(x) and W1(x) were commuted, relative to the first serial
schedule.
- 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
locks.
A read lock request will wait for the release of a write lock on the
same item.
- Write lock: Item may be read or written. Not shareable with other
locks.
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.
- Deadlock!!!
- Table locks: coarse.
- Row or field locks: fine.
- Impact upon concurrency?
- DB isolation levels:
- Read uncommitted: Read without obtaining a read lock. Dirty reads
possible.
- Read committed: Short term read lock acquired. Nonrepeatable
reads possible.
- Repeatable read: Long term tuple read locks acquired. Phantoms
possible.
- Serializable: Long term table read locks acquired. Concurrency
reduced.
Write locks are always of long duration.
- Phantoms: an anomaly of row locking.
- T1 read locks all tuples satisfying
SELECT *
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
granularities.
Thomas P. Kelliher
Fri Apr 26 17:56:25 EDT 2002
Tom Kelliher