CSE 599c Hot Topics in Data Management Systems

Lecture 12: Transactions

Magdalena Balazinska

Winter 2006

Today's Paper

Michael J. Franklin. Concurrency Control and Recovery. The Handbook of Computer Science and Engineering, A. Tucker, ed., CRC Press, 1997.

Outline

Transactions

Transactions

More transactions

Still talking about transactions

Finally, transactions

Motivating Example

Would like to treat this group of instructions as a unit

UPDATE Budget

SET money=money-100

WHERE pid = 1

UPDATE Budget

SET money=money+60

WHERE pid = 2

UPDATE Budget

SET money=money+40

WHERE pid = 3

SELECT sum(money)FROM Budget

Transaction Example

START TRANSACTION

UPDATE Budget SET money = money - 100

WHERE pid = 1

UPDATE Budget SET money = money + 60

WHERE pid = 2

UPDATE Budget SET money = money + 40

WHERE pid = 3

COMMIT

ACID Properties

Atomicity: Either all changes performed by transaction occur or none occurs

Consistency: A transaction as a whole does not violate integrity constraints

Isolation: Transactions appear to execute one after the other in sequence

Durability: If a transaction commits, its changes will survive failures

What could go wrong?

Concurrent operations

Failures can occur at any time

Transaction may need to abort

How to Handle Concurrency

Serializability: interleaved execution has same effect as some serial execution

Schedule of two transactions (Figure 1)

r0[A]  w0[A]  r1[A]  r1[B]  c1  r0[B]  w0[B]  c0

Serializable schedule: equiv. to serial schedule

r0[A]  w0[A]  r1[A]  r0[B]  w0[B]  c0  r1[B]  c1

Implementation: Locking

Can serve to enforce serializability

Two types of locks: Shared and Exclusive

Also need two-phase locking (2PL)

Rule: once transaction releases lock, cannot aquire any additional locks!

So two phases: growing then shrinking

Also need to deal with deadlock

Avoidance or detection

Degrees of Isolation

Four levels of isolation

All levels use long-duration exclusive locks

READ UNCOMMITTED: no read locks

READ COMMITTED: short duration read locks

REPEATABLE READ:

long duration read locks on individual items

SERIALIZABLE:

all locks long duration and lock predicates

Trade-off: consistency vs concurrency

How to Handle Failures

Types of failures

Transaction failure

System failure

Required capability: undo and redo

Challenge: buffer manager

Changes performed in memory

Changes written to disk only from time to time

STEAL/NO-STEAL and FORCE/NO-FORCE

Solution Write Ahead Log (WAL)

For every update, commit, or abort operation

Write physical or logical log record

WAL protocol ensures that

All log records pertaining to a page are written to disk before the page is overwritten on disk

All log records for transaction are written to disk before the transaction is considered committed

ARIES

[From Franklin97]

ARIES

[From Franklin97]

Summary

A transaction is an ACID unit of work

Transactions are committed or rolledback

Transaction processing relies on

Locks

Write ahead logging

Serializability: interleaved execution has same effects as a serial execution

2006-01-01T23:16:26