Concurrency Control and Recovery

1
Concurrency Control and Recovery

• In real life
• users access the database concurrently, and
• systems crash.
• Concurrent access to the database also improves
  performance,
• yields better utilization of resources.
• BUT if not careful, concurrent access can lead
  to incorrect database
• states. Crashes can also leave the database in
  incoherent states.
• Basic concurrency/recovery concept
  transaction
• executed atomically. All or nothing.
• We cover
• transactions in SQL
• implementation of transactions and recovery.

2
Flight Reservation
get values for flight, date, seat EXEC SQL
SELECT occupied INTO occ FROM
Flight WHERE fltNum flight AND
fltdt date AND fltSeatseat if (!occ)
EXEC SQL UPDATE Flights SET
occupied true WHERE fltNum
flight AND fltdt date AND fltSeatseat
/ more code missing / else /
notify customer that seat is not available /
3
Problem 1
Customer 1 - finds a seat empty Customer 2 -
finds the same seat empty Customer 1 - reserves
the seat. Customer 2 - reserves the
seat. Customer 1 will not be happy.
serializability
4
Bank Transfers
Transfer amount from account1 to
account2 EXEC SQL SELECT balance INTO
balance1 FROM Accounts
WHERE accNo account1 if (balance1 gt
amount) EXEC SQL UPDATE Accounts
SET balance balance amount
WHERE acctNo account2 EXEC SQL UPDATE
Accounts SET balance balance -
amount WHERE acctNo account1
Crash...
5
Transactions

• The user/programmer can group a sequence of
  commands so that
• they are executed atomically and in a
  serializable fashion
• Transaction commit all the operations should be
  done and recorded.
• Transaction abort none of the operations should
  be done.
• In SQL
• EXEC SQL COMMIT
• EXEC SQL ROLLBACK
• Easier said than done...

6
ACID Properties
Atomicity all actions of a transaction happen,
or none happen. Consistency if a transaction
is consistent, and the database starts
from a consistent state, then it will
end in a consistent
state. Isolation the execution of one
transaction is isolated from other
transactions. Durability if a transaction
commits, its effects persist in the
database.
7
How Do We Assure ACID?
Concurrency control Guarantees
consistency and isolation, given
atomicity. Logging and Recovery
Guarantees atomicity and durability.
If you are going to be in the logging business,
one of the things that youll have to do is learn
about heavy equipment.
-- Robert VanNatta
Logging History of Columbia County
8
More on SQL and Transactions

• Read only transactions
• if the transaction is only reading, we can
  allow more operations
• in parallel.
• EXEC SQL SET TRANSACTION READ ONLY
• The default is
• SET TRANSACTION READ WRITE

9
Dirty Data
Data that has been written by a transaction that
has not committed yet is called dirty data. Do
we allow our transaction to read dirty data? It
may go away In SQL SET TRANSACTION
ISOLATION LEVEL READ UNCOMMITTED Note
default for READ UNCOMMITTED transactions is that
they are READ ONLY.
10
Problems with Dirty Data

• Transfer program 1. Add N to account 2.
• 2. If account 1
  has enough for the transfer,
• then subtract N
  from account 1, and commit
• else Subtract N
  from account 2, and commit
• Bad scenario A1 100, A2 200, A3 300
• T1 transfer 150 from
  A1 to A2
• T2 transfer 250 from
  A2 to A3.
• Events
• T2 does step 1, -gt A3 has 550
• T1 does step 1, -gt A2 has 350
• T2 does step 2 (then), all is ok (A2 now has
  100)
• T1 does step 2 and finds that A1 doesnt have
  enough funds
• so A2 ends up with -50.

11
Concurrency Control Methods

• Schedules
• Serial schedules
• Serializable schedules
• Locking
• Lock manager
• 2 Phase Locking
• Deadlocks
• Prevention
• Detection

12
Schedules

• A schedule is an interleaving of a set of actions
• of different transactions, such that the actions
  of
• any single transaction are in order.
• A schedule represents some actual sequence of
• database actions.
• In a complete schedule, every transaction either
• commits or aborts.
• Initial state Schedule -gt Final state.

13
Acceptable Schedules

• Serial schedules
• The transactions run one at a time from
  beginning to completion.
• Note there are many possible serial schedules.
  Each one is OK. The
• DBMS does not provide any guarantee in which
  order concurrently
• submitted transactions are executed.
• Serializable schedules
• Final state is what some serial schedule would
  have produced.

14
Aborted Transactions

• Slight modification to the definition
• A schedule is serializable if it is equivalent to
  a serial schedule
• of committed transactions.
• As if the aborted transactions never happened.
• Two issues to consider w.r.t. aborted
  transactions
• how does one undo the effect of a transaction?
• What if another transaction sees the effects of
  an aborted one?

15
Locks

• Concurrency control is usually done via locking.
• The lock manager maintains a list of entries
• object identifier (can be page, record, etc.)
• number of objects holding lock on the object
• nature of the lock (shared or exclusive)
• pointer to a list of lock requests.
• Lock compatibility table
• If a transaction cannot get a lock, it is
• suspended on a wait queue.

16
Handling Lock Requests
Lock Request (OID, Mode)
ModeS
ModeX
Currently Locked?
Empty Wait Queue?
Yes
No
Yes
Exclusive lock on OID?
Yes
No
Put on Queue
No
Grant Lock
17
Two-Phase Locking (2PL)

• 2 phase locking
• if T wants to read an object, it first obtains
  an S lock.
• If T wants to write an object, it first
  obtains an X lock.
• If T releases any lock, it can acquire no new
  locks.
• Recall all this is done transparently to the
  user by the DBMS.
• 2PL guarantees serializability!
• Why??

of locks
Time
18
Serializability Graphs

• Two actions conflict if they access the same data
  item.
• The precedence graph contains
• A node for every committed transaction
• An arc from Ti to Tj if an action of Ti
  precedes and conflicts
• with an action of Tj.
• T1 transfers 100 from A to B, T2 adds 6 to
  both
• R1(A), W1(A), R2(A), W2(A), R2(B), W2(B), R1(B),
  W1(B)

19
Conflict Serializability

• 2 schedules are conflict equivalent if
• they have the same sets of actions, and
• each pair of conflicting actions is ordered in
  the same way.
• A schedule is conflict serializable if it is
  conflict equivalent to a serial schedule.
• Note Some serializable schedules are not
  conflict serializable!
• Theorem A schedule is conflict serializable iff
  its precedence graph is acyclic.
• Theorem 2PL ensures that the precedence graph
  will be acyclic!

20
Deadlocks

• Suppose we have the following scenario
• T1 asks for an exclusive lock on A
• T2 asks for an exclusive lock on B
• T1 asks for a shared lock on B
• T2 asks for a shared lock on A
• Both T1 and T2 are waiting! We have a DEADLOCK.
• Possible solutions
• Prevent deadlocks to start with, or
• Detect when they happen and do something about
  it.

21
Deadlock Prevention

• Give each transaction a timestamp. Older
  transactions have
• higher priority.
• Assume Ti requests a lock, but Tj holds a
  conflicting lock.
• We can follow two strategies
• Wait-die if Ti has higher priority, it waits
  else Ti aborts.
• Wound-wait if Ti has higher priority, abort Tj
  else Ti waits.
• Note after aborting, restart with original
  timestamp!

Both strategies guarantee deadlock-free behavior!
22
An Alternative to Prevention

• In theory, deadlock can involve many
  transactions
• T1 waits-for T2 waits-for T3 ...waits-for T1
• In practice, most deadlock cycles involve
  only 2
• transactions.
• Dont need to prevent deadlock!
• Whats the problem with prevention?
• Allow it to happen, then notice it and fix it.
• Deadlock detection.

23
Deadlock Detection

• Lock Manager maintains a Waits-for graph
• Node for each transaction.
• Arc from Ti to Tj if Tj holds a lock and Ti
• is waiting for it.
• Periodically check graph for cycles.
• Shoot some transaction to break the cycle.
• Simpler hack time-outs.
• T1 made no progress for a while? Shoot it.

24
Detection Versus Prevention

• Prevention might abort too many transactions.
• Detection might allow deadlocks to tie up
  resources for a while.
• Can detect more often, but its time-consuming.
• The usual answer
• Detection is the winner.
• Deadlocks are pretty rare.
• If you get a lot of deadlocks, reconsider your
  schema/workload!

25

Review ACID Properties
Atomicity all actions of a transaction happen,
or none happen. Consistency if a transaction is
consistent, and the database starts
from a consistent state, then it will end
in a consistent
state. Isolation the execution of one
transaction is isolated from other
transactions. Durability if a transaction
commits, its effects persist in the
database.
The Recovery Manager guarantees Atomicity
Durability.