CS 728 Advanced Database Systems Chapter 21 Introduction to Protocols for Concurrency Control in Databases

1
CS 728 Advanced Database Systems Chapter 21
Introduction to Protocols for Concurrency Control
in Databases
2
Concurrency Control

• The main aim of any Database Management System is
  to control requests for the same data, at the
  same time, from multiple users.
• Concurrency control algorithms try to coordinate
  the operations of concurrent transactions to
  prevent interference among concurrently executing
  transactions in order to achieve transaction
  consistency.

3
Concurrency Control

• Purpose of Concurrency Control
• To enforce Isolation (through mutual exclusion)
  among conflicting transactions.
• To preserve database consistency through
  consistency preserving execution of transactions.
• To resolve read-write and write-write conflicts.
• Example
• In concurrent execution environment if T1
  conflicts with T2 over a data item A, then the
  existing concurrency control decides if T1 or T2
  should get the A and if the other transaction is
  rolled-back or waits.

4
Lock-Based Protocols

• Concurrency control ensures that transactions are
  updated in the correct order, i.e. it ensures the
  serializability of transactions in a multi-user
  database environment.
• One way to insure serializability is to allow a
  transaction to access a data item only if it is
  currently holding a lock on that item.
• A lock is a variable associated with a data item
  that describes the status of the item with
  respect to possible operations that can be
  applied to it.
• Generally, there is one lock for each data item
  in the database.
• Lock requests are made to concurrency-control
  manager
• Transaction can proceed only after request is
  granted

5
Lock-Based Protocols

• Locking is an operation which secures permission
  to Read and Write a data item for a transaction.
  
• Example
• Lock (X) Data item X is locked in behalf of the
  requesting transaction.
• Unlocking is an operation which removes these
  permissions from the data item.
• Example
• Unlock (X) Data item X is made available to all
  other transactions.
• Lock and Unlock are Atomic operations.

6
Shared/Exclusive Locks

• Data items can be locked in two modes
• Shared (read) mode read_lock(Q)/lock-S(Q)
• More than one transaction can apply share lock on
  Q for reading its value but no write lock can be
  applied on Q by any other transaction.
• Exclusive (write) mode write_lock(Q)/lock-X(Q)
• Only one write lock on Q can exist at any time
  and no shared lock can be applied by any other
  transaction on Q.
• Conflict matrix
• Lock-compatibility matrix

7
Shared/Exclusive Locks

• A transaction may be granted a lock on an item if
• the requested lock is compatible with locks
  already held on the item by other transactions
• Any number of transactions can hold shared locks
  on an item, but
• if any transaction holds an exclusive on the item
  no other transaction may hold any lock on the
  item
• If a lock cannot be granted,
• the requesting transaction is made to wait till
  all incompatible locks held by other transactions
  have been released.
• The lock is then granted.

8
Shared/Exclusive Locks

• In shared/exclusive locking system, every
  transaction must obey the following rules
• 1. Issue the operation lock-S(Q) or lock-X(Q)
  before any read(Q) operation,
• 2. Issue the operation lock-X(Q) before any
  write(Q) operation is performed,
• 3. Issue the operation unlock(Q) after all
  read(Q) and write(Q) operations,
• 4. Not issue a lock-S(Q) operation if it already
  holds an exclusive lock on item Q,
• 5. Not issue a lock-X(Q) operation if it already
  holds a shared lock or exclusive lock on item Q,
• 6. Not issue an unlock(Q) operation unless it
  already holds a read lock or write lock on item Q.

9
Lock-Based Protocols

• The following code performs the read_lock(X)
  operation
• B if LOCK(X) unlocked then
• begin LOCK(X) read-locked
• no_of_reads(X) 1
• end
• else if LOCK(X) read-locked then
• no_of_reads(X)
• else
• begin
• wait(until LOCK(X)unlocked
• and the lock manager wakes up
• the transaction)
• go to B
• end

10
Lock-Based Protocols

• The following code performs the write_lock(X)
  operation
• B if LOCK(X) unlocked then
• LOCK(X) write-locked
• else
• begin
• wait (until LOCK(X)unlocked
• and the lock manager wakes up
• the transaction)
• go to B
• end

11
Lock-Based Protocols

• The following code performs the unlock operation
• if LOCK (X) write-locked then
• begin
• LOCK (X) ? unlocked
• wakes up one of the transactions, if any
• end
• else if LOCK (X) ? read-locked then
• begin
• no_of_reads(X) ? no_of_reads(X)-1
• if no_of_reads (X) 0 then
• begin
• LOCK (X) unlocked
• wake up one of the transactions, if any
• end
• end

12
Pitfalls of Lock-Based Protocols

• Consider the partial schedule
• Neither T3 nor T4 can make progress
• executing lock-S(B) causes T4 to wait for T3 to
  release its lock on B, while executing lock-X(A)
  causes T3 to wait for T4 to release its lock on
  A.

13
Pitfalls of Lock-Based Protocols

• Such a situation is called a deadlock.
• To handle a deadlock,
• one of T3 or T4 must be rolled back and its
  locks released.
• The potential for deadlock exists in most locking
  protocols.
• Deadlocks are a necessary evil.

14
Dealing with Deadlock

• A partial schedule of T1? and T2 ? that is in a
  state of deadlock.
• A wait-for graph for the partial schedule in (a).

15
Dealing with Starvation

• Starvation is the situation in which a
  transaction cannot proceed for an indefinite
  period of time while other transactions in the
  system continue normally.
• For example
• A transaction may be waiting for an X-lock on an
  item, while a sequence of other transactions
  request and are granted an S-lock on the same
  item.
• The same transaction is repeatedly rolled back
  due to deadlocks.
• One solution for starvation is to have a fair
  scheme, such as using a first-come-first-served.

16
The Two-Phase Locking Protocol

• This protocol ensures conflict-serializable
  schedules
• If all transactions obey the 2PL then all
  possible interleaved schedules are serializable.
• This protocol requires that each transaction
  issues lock and unlock requests in two phases
• Phase 1 Growing Phase
• transaction may obtain locks but may not release
  locks
• Phase 2 Shrinking Phase
• transaction may release locks but may not obtain
  locks

17
The Two-Phase Locking Protocol

• No transaction should request a lock after it
  releases one of its locks.

18
The Two-Phase Locking Protocol

• It can be proved that the transactions can be
  serialized in the order of their lock points
• a point where a transaction acquired its final
  lock
• end of its growing phase
• 2PL does not ensure freedom from deadlocks

19
Two-Phase Locking

• Transactions that do not obey 2PL
• Two transactions T1 and T2.
• Results of possible serial schedules of T1 and
  T2.

T1
T2
20
Two-Phase Locking

• Transactions that do not obey 2PL
• (c) A nonserializable schedule S that uses locks.

21
Two-Phase Locking

• Transactions T1? T2 ?, which are the same as T1
  T2 but which follow 2PL.

22
Strict Two-Phase Locking (S2PL)

• To avoid cascading rollback, follow a modified
  protocol called Strict 2PL (S2PL).
• 2PL
• a transaction must hold all its exclusive locks
  till it commits/aborts.
• S2PL does not prevent deadlock.

locks
time
T commits
23
Rigorous two-phase locking (R2PL)

• R2PL is even stricter
• all locks are held till commit/abort.
• In this protocol, transactions can be serialized
  in the order in which they commit.
• S2PL permits higher degree of concurrency than
  R2PL but less than 2PL.

24
Lock Conversions

• 2PL with lock conversions
• First Phase (Growing)
• can acquire a lock-S/lock-X on item
• can convert a lock-S to a lock-X (upgrade)
• if Ti has a read-lock (X) and Tj has no read-lock
  (X) (i ? j) then
• convert read-lock(X) to write-lock(X)
• Else force Ti to wait until Tj unlocks X
• Second Phase (Shrinking)
• can release a lock-S/lock-X
• can convert a lock-X to a lock-S (downgrade)
• Ti has a write-lock(X) (no transaction can
  have any lock on X)
• convert write-lock(X) to read-lock(X)
• This protocol assures serializability

25
Timestamp-Based Protocols

• Each transaction is issued a timestamp when it
  starts
• CC techniques based on timestamp ordering do no
  use locks, and thus deadlocks cannot occur (no
  transaction ever waits)
• may not be (cascadeless and recoverable)
• The protocol manages concurrent execution such
  that the time-stamps determine the
  serializability order.
• If an old transaction Ti has time-stamp TS(Ti), a
  new transaction Tj is assigned time-stamp TS(Tj)
  such that TS(Ti) ? TS(Tj)

26
Timestamp-Ordering Protocol

• In order to assure such behavior, the protocol
  maintains for each data Q two timestamp values
• W-timestamp(Q) (W-TS(Q))
• is the largest time-stamp of any transaction that
  executed write(Q) successfully.
• If W-TS(Q) TS(T), then T is the youngest
  transaction that has written Q successfully.
• R-timestamp(Q) (R-TS(Q))
• is the largest time-stamp of any transaction that
  executed read(Q) successfully.
• If R-TS(Q) TS(T), then T is the youngest
  transaction that has read Q successfully.
• These TSs are updated whenever a new read(Q) or
  write(Q) is executed

27
Timestamp-Ordering Protocol

• The timestamp ordering protocol ensures that any
  conflicting read and write operations are
  executed in timestamp order
• Suppose a transaction T issues read(Q)
• If TS(T) ? W-TS(Q) then T needs to read a value
  of Q that was already overwritten. Hence, the
  read operation is rejected, and T is rolled back.
• If TS(T) ? W-TS(Q), then the read operation is
  executed, and
• R-TS(Q) max(R-TS(Q), TS(T))

28
Timestamp-Ordering Protocol

• Suppose that transaction T issues write(Q).
• If TS(T) ? R-TS(Q), then the value of Q that T is
  producing was needed previously, and the system
  assumed that the value would never be produced.
  Hence, the write operation is rejected, and T is
  rolled back.
• If TS(T) ? W-TS(Q), then T is attempting to write
  an obsolete value of Q. Hence, this write
  operation is rejected, and T is rolled back.
• Otherwise, the write operation is executed, and
  W-TS(Q) TS(T)

29
Example Use of the Protocol

• TS(T1) 1, TS(T2) 2, TS(T3) 3

30
Example Use of the Protocol

• Transactions timestamps are 1, 2, 3, 4, 5

R-TS (X, Y, Z) W-TS (X, Y, Z)
(0, 0, 0) (0, 0, 0)
(5, 0, 0) (0, 0, 0)
(5, 2, 0) (0, 0, 0)
(5, 2, 0) (0, 0, 0)
(5, 2, 0) (0, 3, 0)
(5, 2, 0) (0, 3, 3)
(5, 2, 5) (0, 3, 3)
(5, 2, 5) (0, 3, 3)
(5, 2, 5) (0, 3, 3)
(5, 2, 5) (0, 3, 3)

T1
T2
T3
T4
T5
read(X)
read(Y)
read(Y)
write(Y)
write(Z)
read(Z)
read(X)
read(X)
write(Z)
reject roll back
write(Y)
write(Z)
31
Correctness of Timestamp-Ordering Protocol

• The timestamp-ordering protocol guarantees
  serializability since all the arcs in the
  precedence graph are of the form
• Thus, there will be no cycles in the precedence
  graph

32
Recoverability and Cascade Freedom

• Problem with timestamp-ordering protocol
• Suppose Ti aborts, but Tj has read a data item
  written by Ti, then Tj must abort
• If Tj had been allowed to commit earlier, the
  schedule is not recoverable.
• Further, any transaction that has read a data
  item written by Tj must abort
• This can lead to cascading rollback
• a chain of rollbacks

33
Recoverability and Cascade Freedom

• Solution
• A transaction is structured such that its writes
  are all performed at the end of its processing
• All writes of a transaction form an atomic
  action no transaction may execute while a
  transaction is being written
• A transaction that aborts is restarted with a new
  timestamp

34
Thomas Write Rule

• Modified version of the timestamp-ordering
  protocol in which obsolete (outdated) write (the
  value that will never need to be read) operations
  may be ignored under certain circumstances.
• When T attempts to write data item Q, if TS(T) ?
  W-TS(Q), then T is attempting to write an
  obsolete value of Q. Hence, rather than rolling
  back T as the timestamp ordering protocol would
  have done, this write operation can be ignored.
• Otherwise this protocol is the same as the
  timestamp ordering protocol.
• Thomas' Write Rule allows greater potential
  concurrency.

35
Deadlock Handling

• Consider the following two transactions
• T1 write (A) T2
  write(B)
• write(B)
  write(A)
• Schedule with deadlock

36
Deadlock Prevention

• Deadlock prevention protocols ensure that the
  system will never enter into a deadlock state.
• Some prevention strategies
• Requires that each transaction locks all its data
  items before it begins execution.
• Low degree of concurrency

37
Deadlock Prevention

• Conservative 2 PL (static deadlock-free)
• requires a transaction T to pre-declare all the
  read write set of items and lock all these
  items before T begins execution.
• If any of the pre-declared items can not be
  locked, T does not lock any item at all. Instead,
  T waits and tries again until all the items are
  available for locking.

38
Deadlock Prevention

• Assume that Ti requests a data item currently
  held by Tj.
• wait-die scheme
• If Ti is older than Tj (i.e., TS(Ti) ? TS(Tj))
• Then wait(Ti)
• Else die(Ti)
• Ti is aborted and restarted with its old starting
  time.
• Younger transactions never wait for older ones
  they are rolled back instead.
• A transaction may die several times before
  acquiring needed data item

39
Wait-Die Example

• T1 (ts 10)
• T2
• (ts 20)
• T3
• (ts 25)

wait
wait
40
Deadlock Prevention

• wound-wait scheme
• If Ti is older than Tj (i.e., TS(Ti) ? TS(Tj))
• then wound(Tj) // Tj is wounded by Ti
• Tj is aborted and restart it with its old
  starting time.
• else (Ti is younger than Tj) wait(Ti)
• Older transaction wounds (forces rollback) of
  younger transaction instead of waiting for it.
• Younger transactions may wait for older ones.
• May be fewer rollbacks than wait-die scheme.

41
Deadlock Prevention

• Older transactions thus have precedence over
  newer ones, and starvation is hence avoided.
• Example
• T1 W(X) W(Y)
• T2 W(Y) W(X)
• T1 is older.
• wait-die
• X-Lock1(X) X-Lock2(Y) wait(T1,Y)
• wound-wait
• X-Lock1(X) X-Lock2(Y) abort(T2)
  X-Lock1(Y)

42
Wound-Wait Example

• T1 (ts 25)
• T2
• (ts 20)
• T3
• (ts 10)

wait
wait
43
Timeout-Based Schemes

• If a transaction waits for a lock more than a
  specified amount of time, the transaction is
  rolled back.
• deadlocks are not possible
• simple to implement
• starvation is possible
• difficult to select a good timeout value

44
Deadlock Detection

• Deadlocks can be described as a wait-for graph,
  which consists of a pair G (V, E),
• V is a set of vertices (all the transactions)
• E is a set of edges
• each element is an ordered pair Ti ?Tj.
• If Ti ? Tj is in E, then Ti is waiting for Tj to
  release a data item.
• When Ti requests a data item currently being held
  by Tj, then the edge Ti ? Tj is inserted in the
  wait-for graph.
• This edge is removed only when Tj is no longer
  holding a data item needed by Ti.

45
Deadlock Detection

• The system is in a deadlock state if and only if
  the wait-for graph has a cycle.
• Must invoke a deadlock-detection algorithm
  periodically to look for cycles.

Wait-for graph without a cycle
Wait-for graph with a cycle
46
Deadlock Recovery

• When deadlock is detected
• Some transaction will have to rolled back (made a
  victim) to break deadlock.
• Select that transaction as victim that will incur
  minimum cost.
• Factors in selecting a victim transaction
• The amount of effort already made in the
  transaction.
• The cost of aborting the transaction.
• It may cause cascading aborts.
• How close the transaction is to complete?
• The number of deadlocks that can be broken when
  the transaction is aborted.

47
Deadlock Recovery

• When deadlock is detected
• Rollback determine how far to rollback
  transaction
• Total rollback Abort the transaction and then
  restart it.
• Partial rollback More effective to roll back
  transaction only as far as necessary to break
  deadlock.
• Starvation happens if same transaction is always
  chosen as victim.
• Include the number of rollbacks in the cost
  factor to avoid starvation