Concurrency Control

1
Concurrency Control

• One key objective of a database system is data
  sharing.
• Simultaneous accesses to the database by multipe
  users should be allowed.
• Anomalies that may occur with concurrent
  transaction processing should be prevented.

2
Transaction

• A transaction is a sequence of operations
  accessing data.
• A logical unit of work.
• A unit of concurrency control.
• A unit of recovery.
• An execution of a transaction is atomic if its
  net effect is as though transactions were
  executed instantaneously.

3
Overview of Concurrency Control

• Concurrency Anomalies
• Lost updates, uncommitted changes, inconsistent
  analysis results, etc.
• Serializability
• Although transactions are executed concurrently,
  their net effects should be as if they were
  executed one at a time.
• Locking
• Exclusive locks and shared locks.
• Two-Phase Locking
• 2PL achieves a serializable execution of
  transactions.
• Deadlocks
• 2PL may introduce deadlocks.
• Wait-For Graph
• The wait-for graph of transactions is useful to
  find the transactions in deadlocks.

4
An update created is overwritten without being
read.
Concurrency Anomaly Lost Updates
T1 T2 (X 0)
t1 lt-- X t2 lt-- X
X lt-- t1 100 X lt-- t2
200 (X 200)
time
5
An update read is rolled back.
Concurrency Anomaly Dependency on Uncommitted
Updates
T1 T2
(X 0) X lt-- X 100 X
lt-- X - 100 pay 100
rollback (X 0)
time
6
Data at different logical times are read.
Concurrency Anomaly Inconsistent Analysis
T1 T2 (X
100) (Y 0) X lt-- X - 100
print(X Y) Y lt--
Y 100
time
7
Conflict Serializable Schedules

• Two actions of different transactions conflict
  with each other if they access the same data item
  and one of the actions is a write operation.
• Two schedules are conflict equivalent
• If they involve the same set of actions of the
  same set of transactions, and
• If conflicting actions are ordered the same way
  in these two scheduels.
• Schedule S is conflict serializable if S is
  conflict equivalent to some serial schedule.

8
Dependency Graph

• A dependency graph is useful to determine
  conflict
• serializability of a schedule.
• Provide a node for each transaction.
• For each pair of adjacent conflicting actions in
  transactions Ti and Tj, provide a directed edge
  from the node for Ti to the one for Tj .
• Theorem A schedule is conflict serializable if
  and only if its dependency graph is acyclic.

9
Serializability Dependency Graph

• Schedule

T1 R(A) W(A) R(B) W(B) T2
R(A) W(B) W(C) T3
R(C)
Dependency graph
A
C
T1
T3
T2
B
The cycle in the graph reveals a problem. T1
should precede T2, and T2 should precede T1.
10
Dependency Graph Lost Updates
T1 T2 (X 0)
t1 lt-- X t2 lt-- X
X lt-- t1 100 X lt-- t2
100 (X 100)
11
Dependency Graph Inconsistent Analysis
T1 T2 (X
100) (Y 0) X lt-- X - 100
print(X Y) Y lt--
Y 100
X
T1
T2
Y
12
Dependency Graph Exercise
T1 T2 T3 T4
R(V) W(V) R(V) R(W) W(X)
R(V)
R(X) R(Y)
W(Y) R(Y)
W(Z) R(Z)
time
13
View Serializability

• When schedules S1 and S2 are view equivalent ,
  transactions in them read and write the same
  values.
• If Ti reads initial value of A in S1, then Ti
  also reads initial value of A in S2
• If Ti reads value of A written by Tj in S1, then
  Ti also reads value of A written by Tj in S2
• If Ti writes final value of A in S1, then Ti also
  writes final value of A in S2

T1 R(A) W(A) T2 W(A) T3 W(A)
T1 R(A) W(A) T2 W(A) T3
W(A)
14
Locking

• Locking can be used to enforce a serializable
  execution of transactions.
• The simplest locking mechanism can implement
  mutual exclusion.
• A database system uses a more complex locking
  mechanism.
• A shared lock is used for read-only access.
• An exclusive lock is used for read/write access.
• Locks are applied in two-phases.

15
Exclusive locks (X-locks) prevent dirty data from
being read or updated by other transactions
Exclusive Locks
Other Transactions
Transaction holding the X-lock
Read
Write
Read
Write
X-Locked Data Item
16
Shared locks (S-locks) prevent data read from
being updated.
Shared Locks
Other Transactions
Transactions holding the S-lock
Read
Write
Read
Write
S-Locked Data Item
17
Compatibility Among Lock Types

• An X-locked item cannot be locked by another
  transaction in neither X- nor S- mode.
• An S-locked item can be locked by another
  transaction in S-mode.

18
Two-Phase Locking

• No locks are released before all locks are
  seized.
• A lock point is a point in time where all locks
  are seized.
• The net effect of each transaction is as if all
  the operations of that transaction were executed
  at the lock point.

time
S-Lock ------------------------ X-Lock
---------------- S-Lock
------ X-Lock ----------------
Lock Point
19
Two-Phase Locking (contd)
S-Lock --R1------------------- X-Lock
--W1------------- S-Lock
-------R2- X-Lock
-------------W2--
Lock Point

• The data read by R1 and R2 are identical to those
  that exist at the lock point since other
  transactions cannot update those data while the
  S-locks are set.
• The data written by W1 and W2 could be considered
  as written at the lock point since other
  transactions cannot read those data while the
  X-locks are set.

20
Two-Phase Locking (2PL)

• Two-Phase Locking Protocol
• Each Xact must obtain a S (shared) lock on object
  before reading, and an X (exclusive) lock on
  object before writing.
• A transaction can not request additional locks
  once it releases any locks.
• If an Xact holds an X lock on an object, no
  other Xact can get a lock (S or X) on that object.

21
Preventing Inconsistent Analysis by 2PL
T1 T2 X-lock on
X a1 X lt-- X - 100
b1 print(X Y) a2 Y lt--
Y 100 X-lock on Y
S-lock on X and Y

• Action b1 is blocked until the completion of a2
  because X is first S-locked by a1 and then Y is
  S-locked by a2.
• The S-lock on X can be released once the S-lock
  on Y is set.

22
Strict 2PL

• In strict 2PL, all locks held by a transaction
  are released when the transaction completes.
• Strict 2PL can prevent uncommitted updates from
  being read by other transactions.

23
Strict 2PL Prevents Uncommitted Updates to Be
Accessed
T1 T2
X-lock on X X lt-- X 100
b1 X lt-- X - 100
pay 100 rollback

• Action b1 is blocked until T1 is rolled back.

24
Lock Management

• Lock and unlock requests are handled by the lock
  manager.
• Lock table entry for each data item
• Number of transactions currently holding the lock
• Type of lock held (shared or exclusive)
• Pointer to the queue of lock requests
• Locking and unlocking have to be atomic
  operations.
• Transaction that holds a shared lock can be
  upgraded to hold an exclusive lock.

25
Deadlocks

• 2PL can produce deadlocks.
• Deadlock Cycle of transactions waiting for locks
  to be released by each other.
• Two ways of dealing with deadlocks
• Deadlock prevention
• Deadlock detection

26
Deadlock Caused by 2PL
T1 T2 a1 t1
lt-- X b1 t2 lt-- X
a2 X lt-- t1 100
b2 X lt-- t2 100

• Action a2 is blocked because of S-lock set by b1.
  
• Action b2 is blocked because of S-lock set by a1.

X
T1
T2
X
27
Wait-For Graph

• Create a waits-for graph
• Nodes are transactions
• There is an edge from Ti to Tj if Ti is waiting
  for Tj to release a lock
• If a cycle is formed in the waits-for graph, a
  deadlock has occurred.

28
Wait-For Graph Example

• T1 S(A) R(A) S(B)---------------------------
  -
• T2 X(B) W(B) X(C)------------------
• T3 S(C) R(C) X(A)----
• T4 X(B)------------

T1
T2
T4
T3
29
Wait-For Graph Exercise
T1 T2 T3 T4
R(V) W(V) ltR(V)gt R(W) commit
(R(V)) W(X)
R(V) R(Y)
W(Y) ltR(X)gt
W(Z)
ltR(Y)gt
ltR(Z)gt
T1
T2
T3
T4
time
30
Deadlock Prevention

• A cycle is not formed in a wait-for graph if a
  transaction with a higher priority does not wait
  for a lock held by a transaction with a lower
  priority.
• Priorities may be assigned based on timestamps.
• Assume Ti wants a lock that Tj holds.
• Wait-Die It Ti has a higher priority, Ti waits
  for Tj. Otherwise Ti aborts.
• Wound-wait If Ti has a higher priority, Tj is
  rolled back. Otherwise Ti waits.
• If a transaction re-starts, make sure it has its
  original timestamp

31
Summary

• When multiple transactions are executed
  simultaneously, concurrency anomalies may occur.
• An execution of transactions is considered
  correct if the schedule is serializable.
• A non-serializable schedule have cycles in its
  dependency graph
• There are several locking-based concurrency
  control schemes (Strict 2PL, 2PL).
• A concurrency control scheme based on locking may
  cause deadlocks.
• Deadlocks can be prevented or resolved with a
  wait-for graph.