Recovery and Concurrency Control Motivating Questions How does

Recovery and Concurrency Control • Motivating Questions – How does Recovery and Concurrency control work ? – Why is it important for DBMS ? – How to use it ? C. Claramunt 1

Concurrency Control • Concurrency control is needed to handle problems that can occur when concurrent transactions execute – Lost Update: an update to a data item by some transaction is overwritten by another interleaved transaction without knowledge of the initial update – Temporary Update (Dirty Read): a transaction reads a data item updated by another transaction that later falls – Incorrect Summary: a transaction calculating an aggregate function uses some but not all updated data items of another transaction C. Claramunt 2

Lost Update Example T 1 T 2 read (X) X <- X - N read (X) X <- X + M write (X) read (Y) write (X) Y <- Y + N write (Y) C. Claramunt 3

Temporary Update Example T 1 T 2 read (X) X <- X - N write (X) read (X) X <- X + M write (X) read (Y) C. Claramunt 4

Incorrect Summary Example T 1 T 2 read (X) X <- X - N write (X) read (X) sum <- sum + X read (Y) sum <- sum + Y read (Y) Y <- Y + N write (Y) C. Claramunt 5

Serial Model • Basic Assumption: Each transaction preserves database consistency • Serial execution of a set of transactions preserves database consistency • In a concurrent execution, steps of a set of transactions may be interleaved • The sequence of steps in an execution is called a schedule C. Claramunt 6

Schedule • Only interested in access to the database • Write previous schedule as: T 1 T 2 read (X) write (X) read (Y) write (Y) C. Claramunt 7

Schedules and Recoverability • A schedule S is recoverable if no transaction T in S commits until all other transactions that have written an item that T reads have committed • In a recoverable schedule, no committed transaction ever needs to be rolled back • Can have cascading abort when an uncommitted transaction is aborted because it read an item from another transaction that failed • In a strict schedule a transaction can neither read nor write an item X until the last transaction that wrote X has committed (or aborted) -- avoids cascading abort C. Claramunt 8

Conflict between Transactions • Two transactions Ti and Tj conflict if and only if there exists some item X accessed by both Ti and Tj and at least one of these transactions wrote X • Possible conflicting operations: – T 1: read (X), T 2: write (X) – T 1: write (X). T 2: read (X) – T 1: write (X), T 2: write (X) • Intuitively, a conflict between two transactions forces a temporal order between them C. Claramunt 9

Conflict Serializability • A schedule is conflict serializable if it is conflict equivalent to some serial schedule • A schedule that is conflict serializable is correct C. Claramunt 10

Serializability of Schedules (Example 1) T 1 T 2 T 3 read (Z) read (Y) write (Y) read (Z) read (X) write (Y) write (Z) read (X) read (Y) write (X) C. Claramunt 11

Serializability of Schedules (Example 2) T 1 T 2 T 3 read (Y) read (Z) read (X) write (Y) write (Z) read (Y) write (Y) read (X) write (X) C. Claramunt 12

Locking Techniques • One technique use for concurrency control is based on the concept of locking data items • A lock is a variable associated with a data item that describes the status of that item with respect to possible operations on the item • Locks are uses as a means of synchronizing the access by concurrent transactions to the data items C. Claramunt 13

Multimode Locks • Two types of lock can be set on each item – X-lock (exclusive lock) – S-lock (shared lock) • Locks can be set only by adhering to a compatibility matrix old lock X S new lock X N N S N Y • A transaction may set a lock on an item if this lock is compatible with locks already head on the item by other transactions C. Claramunt 14

Multimode Locks • Any number of transactions can hold S-locks on an item • If any transaction holds an X-lock on an item, no other transaction may hold any lock on the item • A transaction holding an X-lock may issue a write or a read request on the data item • A transaction holding an S-lock may issue a read request on the data item C. Claramunt 15

Two-Phase Locking Protocol • Phase 1 (expanding phase) – transaction may request locks – transaction may not release locks • Phase 2 (shrinking phase) – transaction may not request locks – transaction may release locks • The protocol assures serializability C. Claramunt 16

Example 2 PL T 1 S_lock (Y) read (Y) X_lock (X) unlock (Y) read (X) write (X unlock (X) C. Claramunt T 2 X_lock (Y) read (Y) write (Y) S_lock (X) read (X) unlock (Y) 17

2 PL with Lock Conversion • Phase 1 – can acquire an S-lock on item – can acquire an X-lock on item – can convert an S-lock to an X-lock • Phase 2 – can release an S-lock – can release an X-lock – can convert an X-lock to an S-lock • Assures serializability • These protocols rely on the application programmer to insert the various locking instructions C. Claramunt 18

Deadlock • Deadlock occurs when two transactions are each waiting for locks held by the other to be released • Example T 1 T 2 S_lock (Y) read (Y) request X_lock(X) wait C. Claramunt S_lock (X) read (X) request X_lock(Y) wait 19

Deadlock Prevention • Lock all items a transaction needs in advance; limits concurrency • Timestamp transactions: – wait-die: allow a transaction to wait on a lock if the transaction is older than the transaction holding the lock – wound-wait: a older transaction can abort a younger transaction holding a lock needed by the older one • Waiting: – no waiting: a transaction is aborted if it can’t get a lock – cautious waiting: abort a transaction if it is blocked by another transaction that is also blocked • Timeouts: abort a transaction that has waited for a lock for some system-defined timeout C. Claramunt 20

Timestamp Protocol • Each transaction is issued a timestamp when it enters the system. Timestamps may be drawn from an increasing sequence of integers. • The protocol will manage the concurrent execution so that it will be equivalent to a predetermined serial execution, defined by the increasing order of timestamps • If two transactions conflict in the schedule, then the one with the lower timestamp accessed the item first C. Claramunt 21

Recovery and Concurrency Control: Summary • The effects of a transaction become permanent and known to other database users after the transaction executes a COMMIT, or are removed when it executes a ROLLBACK Locks are used to safeguard against interference between transactions • Shared locks are used to stop transactions updating data that is being read, and exclusive locks stop data being read while it is updated • When a database fails, it is recovered from back-up copies, and a transaction log which includes checkpoint records. C. Claramunt 22

Exercises • Give an example of how an update may be lost when two updates occur simultaneously • Give an example of how concurrent transactions can – cause inconsistent data to be retrieved – cause consistent but incorrect data to be retrieved C. Claramunt 23