CS411 Database Systems

1
CS411Database Systems
14 Concurrency Control

• Kazuhiro Minami

2
Announcements

• Homework 5 is out. The due date is Dec 2. Donna's
  office has changed to 2106
• Please NOT fill out ICES on-line evaluations
• Everything is recorded under Prof. Winslett name,
  which will cause confusion later

3
A transaction is a sequence of operations that
must be executed as a whole.
BUSEY SAVINGS Winslett 1000
BUSEY CHECKING Winslett 0
Transfer 500

• Debit savings
• Credit checking

Either both (1) and (2) happen or neither!

• Every DB action takes place inside a transaction.

4
We abstract away most of the application code
when thinking about transactions.
Users point of view
Transfer 500
Code writers point of view

• Debit savings
• Credit checking

Read Balance1 Write Balance1 Read Balance2 Write
Balance2
Concurrency control s recoverys point of view
5
Schedule The order of execution of operations of
two or more transactions.
Schedule S1
Transaction1
Transaction2
R(A) R(C) W(A) R(B) W(C) R(B)
W(B) W(B)
Read data item A (typically a tuple)
Time
6
Why do we need transactions?
Transaction 1 Add 100 to account A
Transaction 2 Add 200 to account A
R(A) W(A)
R(A) W(A)
Time
7
What will be the final account balance?
Transaction 1 Add 100 to account A
Transaction 2 Add 200 to account A
R(A) W(A)
R(A) W(A)
Time
The Lost Update Problem
8
What will be the final account balance?
Transaction 1 Add 100 to account A
Transaction 2 Add 200 to account A
R(A) W(A) F A I L
R(A) W(A)
Time
Dirty reads cause problems.
9
Abort or roll back are the official words for
fail.

• Commit
• All your writes will definitely absolutely be
  recorded and will not be undone, and all the
  values you read are committed too.
• Abort/rollback
• Undo all of your writes!

10

• The concurrent execution of transactions must be
  such that each transaction appears to execute in
  isolation.

11
The ACID properties

• Atomicity
• A transaction is performed entirely or not at all
• Consistency Preservation
• A transactions execution takes the database from
  one correct state to another
• Isolation
• The updates of a transaction must not be made
  visible to other transactions until it is
  committed
• Durability
• If a transaction changes the database and is
  committed, the changes must never be lost because
  of subsequent failure

12
Example
T1 T2
READ(A, t) t t 100 WRITE(A, t) READ(B, t) t
t 100 WRITE(B, t)
READ(A, s) s s 2 WRITE(A, s) READ(B, s) s
s 2 WRITE(B, s)
Concurrency constraint A B
13
Scheduler
Transaction Manager
Read/Write request
Scheduler
Reads and writes
Buffers
14
Schedule

• Time-ordered sequence of the important actions
  taken by one or more transactions
• Consider only the READ and WRITE actions, and
  their orders ignore the INPUT and OUTPUT
  actions
• An element in a buffer is accessed by multiple
  transactions

15
Serial Schedule

• If any action of transaction T1 precedes any
  action of T2, then all action of T1 precede all
  action of T2
• The correctness principle tells us that every
  serial schedule will preserve consistency of the
  database state

T1s actions
T2s actions
Time
16
Example 1 (T1, T2)
T1 T2 A
B

• 25
• 125
• 125
• 250
• 250

READ(A, t) t t 100 WRITE(A, t) READ(B, t) t
t 100 WRITE(B, t)
READ(A, s) s s 2 WRITE(A, s) READ(B, s) s
s 2 WRITE(B, s)
17
Example 2 (T2, T1)
T1 T2 A
B

• 25
• 50
• 50
• 150
• 150

READ(A, s) s s 2 WRITE(A, s) READ(B, s) s
s 2 WRITE(B, s)
READ(A, t) t t 100 WRITE(A, t) READ(B, t) t
t 100 WRITE(B, t)
18
Serial Schedule is Not Necessarily Desirable

• Improved throughput
• I/O activity can be done in parallel with
  processing at CPU
• Reduced average waiting time
• If transactions run serially, a short transaction
  may have to wait for a preceding long transaction
  to complete

19
A schedule is serializable if it is guaranteed to
give the same final result as some serial
schedule.

• Which of these are serializable?

Read(A) Read(A)
Write(A) Write(A) Read(B) Write(B)
Read(B)
Write(B)
Read(A) Read(A) Write(A)
Write(A) Read(B) Write(B)
Read(B)
Write(B)
Read(A) Write(A) Read(A)
Write(A) Read(B) Write(B)
Read(B)
Write(B)
20
Example s s1
T1 T2 A
B

• 25
• 125
• 125
• 25
• 125

READ(A, t) t t 100 WRITE(A, t)
READ(A, s) s s 1 WRITE(A, s) READ(B, s) s
s 1 WRITE(B, s)
READ(B, t) t t 100 WRITE(B, t)
21
Transaction Semantics

• Any database element A that a transaction T
  writes is given a value that depends on the
  database state in such a way that no arithmetic
  coincidences occur
• More informally, if there is something that T
  could have done to A that will make the database
  state inconsistent, then T will do that

22
Notation for Transactions and Schedules

• We do not consider the details of local
  computation steps such as t t 100
• Only the reads and writes matter
• Action ri(X) or wi(X)
• Transaction Ti a sequence of actions with
  subscript i
• Schedule S a sequence of actions from a set of
  transactions T

23
Examples

• T1 r1(A) w1(A) r1(B) w1(B)
• T2 r2(A) w2(A) r2(B) w2(B)
• S r1(A) w1(A) r2(A) w2(A) r1(B) w1(B)
  r2(B) w2(B)

24
Conflict-Serializability

• Commercial systems generally support
  conflict-serializability
• Stronger notion than serializability
• Based on the idea of a conflict
• Turn a given schedule to a serial one by make as
  many nonconflicting swaps as we wish

25
Conflicts

• A pair of consecutive actions in a schedule such
  that, if their order is interchanged, then the
  behavior of at least one of the transactions
  involved can change

26
Conflicting Swaps

• Two actions of the same transaction
• - E.g., ri(X) wi(Y)
• Two writes of the same database element
• - E.g., wi(X) wj(X)
• A read and a write of the same database element
• - E.g., ri(X) wj(X)

27
Nonconflicting swaps

• Any two actions of different transactions may be
  swapped unless
• They involve the same database element, and
• At least one is a write
• Examples
• ri(X) rj(Y)
• ri(X) wj(Y) if X! Y
• wi(X) rj(Y) if X ! Y
• wi(X) wj(Y) if X ! Y

28
Conflict-serializable

• Two schedules are conflict-equivalent if they can
  be turned one into the other by a sequence of
  nonconflicting swaps of adjacent actions
• A schedule is conflict-serializable if it is
  conflict-equivalent to a serial schedule
• Easy to check whether a schedule is
  conflict-serializable by examining a precedence
  graph

29
Example
r1(A) w1(A) r2(A) w2(A) r1(B) w1(B) r2(B)
w2(B) r1(A) w1(A) r2(A) r1(B) w2(A) w1(B)
r2(B) w2(B) r1(A) w1(A) r1(B) r2(A) w2(A)
w1(B) r2(B) w2(B) r1(A) w1(A) r1(B) r2(A)
w1(B) w2(A) r2(B) w2(B) r1(A) w1(A) r1(B)
w1(B) r2(A) w2(A) r2(B) w2(B)
30
Test for Conflict-Serializability

• Can decide whether or not a schedule S is
  conflict-serializable
• Ideas
• when there are conflicting actions that appear
  anywhere in S, the transactions performing those
  actions must appear in the same order in any
  conflict-equivalent serial schedule
• Summarize those conflicting actions in a
  precedence graph

31
Precedence Graphs

• T1 takes precedence over T2 (T1 ltS T2), if there
  are actions A1 of T1 and A2 of T2, s.t.
• A1 is ahead of A2 in S
• Both A1 and A2 involve the same database element
• At least one of A1 and A2 is a written action
• Construct a precedence graph and ask if there are
  any cycles

32
Certain pairs of operations conflict with each
other.
Write(A) Write(A)
Write(A) Read(A)
Write(A) Write(A)
Read(A) Write(A)
Read(A) Read(A)
Write(A) Write(A) Read(B) Write(B)
Read(B)
Write(B)
Read(A) Read(A) Write(A)
Write(A) Read(B) Write(B)
Read(B)
Write(B)
Read(A) Write(A) Read(A)
Write(A) Read(B) Write(B)
Read(B)
Write(B)
33
Lets clean up by collapsing all the nodes for a
transaction into one big node.
Read(A) Read(A)
Write(A) Write(A) Read(B) Write(B)
Read(B)
Write(B)
Read(A) Read(A) Write(A)
Write(A) Read(B) Write(B)
Read(B)
Write(B)
Read(A) Write(A) Read(A)
Write(A) Read(B) Write(B)
Read(B)
Write(B)
Blue Black
Blue Black
Blue Black
34
Lets clean up by collapsing all the nodes for a
transaction into one big node.
A schedule is conflict serializable iff its
dependency graph is acyclic.
Read(A) Read(A)
Write(A) Write(A) Read(B) Write(B)
Read(B)
Write(B)
Read(A) Read(A) Write(A)
Write(A) Read(B) Write(B)
Read(B)
Write(B)
Read(A) Write(A) Read(A)
Write(A) Read(B) Write(B)
Read(B)
Write(B)
Blue Black
Blue Black
Blue Black
35
Is this schedule conflict serializable?
T1 T2 T3
T1 T2 T3
R2(C) W2(C)
R2(B) W2(B)
R1(A) W1(A) R1(C) W1(C)
R3(C) W3(C)

R1(A) R2(C)
W2(C) W1(A) R1(C) W1(C)
R3(C) W3(C)
R2(B) W2(B)
Schedule S2
Schedule S1
36

• What serial schedule is S1 equivalent to, if any?

S2
S1
R(A) W(A)
R(C) W(C)
R)C( W(B) R(A) R(B) W(B)
W(B) R(A)
W(A) R(A) R(B) R(C) W(B) W(C)
R)C(
37

• What serial schedule is S1 equivalent to, if any?

S1
R(A)
W(A) R(A) R(C) R(C) W(A) W(C)
R)A(
Nothing
38
Example

• S r2(A) r1(B) w2(A) r3(A) w1(B) w3(A)
  r2(B) w2(B)

1
2
3
S r1(B) w1(B) r2(A) w2(A) r2(B) w2(B)
r3(A) w3(A)
39
Example

• S1 r2(A) r1(B) w2(A) r2(B) r3(A) w1(B)
  w3(A) w2(B)

1
2
3
40
Locks are the basis of most protocols to
guarantee serializability.

• Prevent orders of actions that lead to an
  unserializable schedule using locks
• Maintain a lock on each database element
• Transactions must obtain a lock on a database
  element if they want to perform any operation on
  that element

41
Locks

• A scheduler uses a lock table to guide decisions

requests from transactions
lock table
Scheduler
Locks(element, transaction)
Serializable schedule of action
42
Requirements for the use of locks

• Consistency of transactions
• A transaction can only read or write an element
  if it previously requested a lock on that element
  and hasnt yet released the lock
• If a transaction locks an element, it must later
  unlock that element
• Legality of schedulers
• No two transactions may have locked the same
  element without one having first released the lock

43
Notation for locks

• li(X) Transaction Ti requests a lock on
  database element X
• ui(X) Transaction Ti releases its lock on
  database element X

44
Example 1

• A legal, but not serializable schedule

T1 T2 A
B

• 25
• 125
• 250
• 50
• 150

l1(A) r1(A) A A100 w1(A)u1(A)
l2(A)r2(A) A A2 w2(A) u2(A) l2(B)
r2(B) B B2 w2(B) u2(B)
l1(B) r1(B) B B100 w1(B)u1(B)
45
Example 2

• T1 and T2 lock B before releasing the lock on A

T1 T2 A
B

• 25
• 125
• 250
• 125
• 250

l1(A) r1(A) A A100 w1(A) l1(B) u1(A)
l2(A)r2(A) A A2 w2(A) l2(B)
Denied
r1(B) B B100 w1(B) u1(B)
l2(B) u2(A) r2(B) B B2 w2(B) u2(B)
46
2-Phase Locking (2PL) no new locks once youve
given one up

• In every transaction, all lock requests precede
  all unlock requests
• Guaranteed that a legal schedule of consistent
  transactions is conflict-serializable

47
Why Two-Phase Locking Works

• Each two-phase-locked transaction may be thought
  to execute in its entirety at the instant it
  issues its first unlock request
• The conflict-equivalent serial schedule for a
  schedule S of 2PL transactions is the one in
  which the transactions are ordered in the same
  order as their first unlock

Instantaneously executes now
locks acquired
time
48
Locking system with several locks

• Shared lock read lock many transactions can
  hold a shared lock on the same item at the same
  time.
• Exclusive lock write lock only one transaction
  can hold an exclusive lock on an item at any
  given time.

49
The lock manager makes decisions based on its
compatibility matrix
X
S
X
N
N
S
N
Y
50
2PL doesnt solve every potential problem.
T1 T2
We should never have let T1 commit.
W(B) R(A)
W(A) R(A) R(B) W(B)
T1 commits
Cascading rollback
Now T2 aborts!
51
How do we deal with this?

• Commit trans T only after all transactions that
  wrote data that T read have committed
• Or only let a transaction read an item after the
  transaction that last wrote this item has
  committed
• Strict 2PL 2PL a transaction releases its
  locks only after it has committed.
• How does Strict 2PL prevent cascading rollback?

52
Summary

• Concurrency control is a scheduling problem of
  actions of different tranactions