Transaction Processing

1
Transaction Processing
2
Transactions

• Many enterprises use databases to store
  information about their state
• e.g., Balances of all depositors at a bank
• When an event occurs in the real world that
  changes the state of the enterprise, a program is
  executed to change the database state in a
  corresponding way
• e.g., Bank balance must be updated when deposit
  is made
• Such a program is called a transaction

3
What Does a Transaction Do?

• Update the database to reflect the occurrence of
  a real world event
• Deposit transaction Update customers balance in
  database
• Cause the occurrence of a real world event
• Withdraw transaction Dispense cash (and update
  customers balance in database)
• Return information from the database
• RequestBalance transaction Outputs customers
  balance

4
Transactions

• The execution of each transaction must maintain
  the relationship between the database state and
  the enterprise state
• Therefore additional requirements are placed on
  the execution of transactions beyond those placed
  on ordinary programs
• Atomicity
• Consistency
• Isolation
• Durability

ACID properties
5
ACID Properties

• Atomic - Transaction should either complete or
  have no effect at all
• Responsibility of transaction processing system
• Consistent - Transaction should correctly
  transform the database state to reflect the
  effect of a real world event
• Responsibility of transaction designer
• Isolation - The effect of concurrently executing
  a set of transactions is the same as if they had
  executed serially (serializable)
• Responsibility of transaction processing system
• Durable - The effect of a transaction on the
  database state should not be lost once the
  transaction has committed
• Responsibility of transaction processing system

6
Database Consistency

• Enterprise (Business) Rules limit the occurrence
  of certain real-world events
• Student cannot register for a course if the
  current number of registrants equals the maximum
  allowed
• These limitations are (static) integrity
  constraints assertions that must be satisfied by
  the database state
• Database is consistent if all static integrity
  constraints are satisfied

7
Transaction Consistency

• A consistent database state does not necessarily
  model the actual state of the enterprise
• A deposit transaction that increments the balance
  by the wrong amount maintains the integrity
  constraint balance ? 0, but does not maintain the
  relation between the enterprise and database
  states
• A consistent transaction maintains database
  consistency and the correspondence between the
  database state and the enterprise state
  (implements its specification)
• Specification of deposit transaction includes
  balance balance? amt_deposit ,
  (balance? is the
  initial value of balance)

8
Atomicity

• A real-world event either happens or does not
  happen
• Student either registers or does not register
• Similarly, the system must ensure that either the
  corresponding transaction runs to completion or,
  if not, it has no effect at all
• Not true of ordinary programs. A crash could
  leave files partially updated on recovery

9
Commit and Abort

• If the transaction successfully completes it is
  said to commit
• The system is responsible for preserving the
  transactions results in spite of subsequent
  failures
• If the transaction does not successfully
  complete, it is said to abort
• The system is responsible for undoing, or rolling
  back, any changes the transaction has made

10
Reasons for Abort

• System crash
• Transaction aborted by system
• Execution cannot be made atomic (a site is down)
• Execution did not maintain database consistency
  (integrity constraint is violated)
• Execution was not isolated
• Resources not available (deadlock)
• Transaction requests to roll back

11
API for Transactions

• DBMS provide commands for setting transaction
  boundaries. For example
• begin transaction
• commit
• rollback
• The commit command is a request
• The system might commit the transaction, or it
  might abort it for one of the reasons on the
  previous slide
• The rollback command is always satisfied

12
Durability

• Durability deals with failure
• Media failure
• The system must ensure that once a transaction
  commits, its effect on the database state is not
  lost in spite of subsequent failures
• Not true of ordinary programs. A media failure
  after a program successfully terminates could
  cause the file system to be restored to a state
  that preceded the programs execution
• Mechanism for dealing with failures is the log

13
Isolation

• Serial Execution The transactions execute one
  after the other
• Each one starts after the previous one completes.
• The execution of each transaction is isolated
  from all others.
• Serial execution is inadequate from a performance
  perspective
• Concurrent execution offers performance benefits
• A computer system has multiple resources capable
  of executing independently (e.g., cpus, I/O
  devices),
• only concurrently executing transactions can
  make effective use of the system

14
Concurrent Execution
15
Isolation

• Concurrent (interleaved) execution of a set of
  consistent transactions offers performance
  benefits, but might not be correct
• Example course registration cur_reg is number
  of current registrants

T1 r(cur_reg 29)
w(cur_reg 30) T2 r(cur_reg
29) w(cur_reg 30)
time ?
Result Database state no longer corresponds
to real-world state, integrity constraint
violated (cur_reg ltgt list_of_registered_students
)
16
Transaction Schedule
T1 begin_transaction() .
p1,1 . p1,2
. p1,3 commit()

Transaction schedule p1,3 p1,2 p1,1
To db server
local variables

• Consistent - performs correctly when executed in
  isolation starting in a consistent database state
• Preserves database consistency
• Moves database to a new state that corresponds to
  new real-world state

17
Schedule
Arriving schedule (merge of transaction schedules)
Schedule in which requests are serviced
T1 T2 T3
Concurrency Control
To database
transaction schedules
Database server
18
Example Schedules

• Let T1 transfer 50 from A to B, and T2 transfer
  10 of the balance from A to B. The following is
  a serial schedule, in which T1 is followed by T2.

Schedule 1
19
Example Schedule (Cont.)

• Let T1 and T2 be the transactions defined
  previously. The following schedule is not a
  serial schedule, but it is equivalent to the
  previous Schedule.

Schedule 2
In both Schedule 1 and 2, the sum A B is
preserved.
20
Example Schedules (Cont.)

• The following concurrent schedule does not
  preserve the value of the sum A B.

Schedule 3
21
Correct Schedules

• Interleaved schedules equivalent to serial
  schedules are the only ones guaranteed to be
  correct for all applications
• Equivalence based on commutativity of operations
• Definition Database operations p1 and p2 commute
  if, for all initial database states, they return
  the same results and leave the database in the
  same final state when executed in either order.

22
Commutativity of Read and Write Operations

• p1 commutes with p2 if
• They operate on different data items
• w1(x) commutes with w2(y) and r2(y)
• Both are reads
• r1(x) commutes with r2(x)
• Operations that do not commute conflict
• w1(x) conflicts with w2(x)
• w1(x) conflicts with r2(x)

23
Equivalence of Schedules

• An interchange of adjacent operations of
  different transactions in a schedule creates an
  equivalent schedule if the operations commute
• S1 S1,1, pi,j, pk,l, S1,2 where i lt
  gt k
• S2 S1,1, pk,l, pi,j, S1,2
• Equivalence is transitive If S1 can be derived
  from S2 by a series of such interchanges, S1 is
  equivalent to S2

24
Example of Equivalence
conflict
S1 r1(x) r2(x) w2(x) r1(y) w1(y) S2
r1(x) r2(x) r1(y) w2(x) w1(y) S3 r1(x)
r1(y) r2(x) w2(x) w1(y) S4 r1(x) r1(y)
r2(x) w1(y) w2(x) S5 r1(x) r1(y) w1(y)
r2(x) w2(x)
conflicting operations ordered in same way
S1 is equivalent to S5 S5 is the serial
schedule T1, T2 S1 is serializable S1 is not
equivalent to the serial schedule T2, T1
25
Example of Equivalence
T1 begin transaction read (x, X)
X X4 write (x, X) commit
T2 begin transaction read (x,Y)
write (y,Y) commit
r1(x) r2(x) w2(y)
w1(x) x1, y3
x5, y1
x5, y1
r2(x) w2(y) r1(x) w1(x)
T2 T1
Interchange commuting operations
r1(x) r2(x) w2(y)
w1(x) x1, y3
x5, y1
x5, y5
r1(x) w1(x) r2(x) w2(y)
T1 T2
Interchange conflicting operations
26
Serializable Schedules

• S is serializable if it is equivalent to a serial
  schedule
• Transactions are totally isolated in a
  serializable schedule
• A schedule is correct for any application if it
  is a serializable schedule of consistent
  transactions
• The schedule r1(x) r2(y) w2(x)
  w1(y) is not serializable

27
Serializability

• Different forms of schedule equivalence give rise
  to the notions of
• 1. Conflict serializability
• 2. View serializability

28
Conflict Serializability

• Two 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!

29
Conflict Serializability (Cont.)

• Schedule 2 below can be transformed into Schedule
  1, a serial schedule where T2 follows T1, by
  series of swaps of non-conflicting instructions.
  Therefore Schedule 2 is conflict serializable.

30
Conflict Serializability (Cont.)

• Example of a schedule that is not conflict
  serializable
• T3 T4 read(Q) write(Q) write(Q)W
  e are unable to swap instructions in the above
  schedule to obtain either the serial schedule lt
  T3, T4 gt, or the serial schedule lt T4, T3 gt.

31
Precedence Graph

• A Precedence (or Serializability) graph
• Node for each Xact.
• 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
• R1(A), W1(A), R2(A), W2(A), R2(B), W2(B), R1(B),
  W1(B)

32
Precedence Graph of a Schedule, S

• Theorem - A schedule is conflict serializable if
  and only if its precedence graph has no cycles
• If precedence graph is acyclic, the
  serializability order can be obtained by a
  topological sorting of the graph. This is a
  linear order consistent with the partial order of
  the graph.

33
Example
Conflict ()
S p1,i, , p2,j, ...
T2
T4
S is serializable in order T1 T2 T3 T4 T5 T6 T7

T1
T5
T6
T7
T3
S is not serializable due to cycle T2 T6 T7 T2
T2
T4
T1
T5
T6
T7
T3
34
Example Schedule (Schedule A)

• T1 T2 T3 T4 T5 read(X)read(Y)read(Z)
  read(V) read(W) read(W)
  read(Y) write(Y) write(Z)read(U) read
  (Y) write(Y) read(Z) write(Z)
• read(U)write(U)

35
Precedence Graph for Schedule A
T1
T2
T4
T3
A serializability order for Schedule A would
beT5 ? T1 ? T3 ? T2 ? T4 .
36
Concurrency Control vs. Serializability Tests

• Testing a schedule for serializability after it
  has executed is a little too late!
• Goal to develop concurrency control protocols
  that will assure serializability. They will
  generally not examine the precedence graph as it
  is being created instead a protocol will impose
  a discipline that avoids nonseralizable
  schedules.
• Tests for serializability help understand why a
  concurrency control protocol is correct.

37
Concurrency Control
Serializable schedule
Arriving schedule
Concurrency Control
(from transactions)
(to processing engine)

• Concurrency control cannot see entire schedule
• It sees one request at a time and must decide
  whether to allow it to be serviced
• Strategy Do not service a request if
• It violates strictness or serializability, or
• There is a possibility that a subsequent arrival
  might cause a violation of serializability

38
Locking A Technique for C. C.

• A transaction can read a database item if it
  holds a read (shared) lock on the item
• It can read or update the item if it holds a
  write (exclusive) lock
• If the transaction does not already hold the
  required lock, a lock request is automatically
  made as part of the access

39
Locking

• Concurrency control usually done via locking.
• Lock info maintained by a lock manager
• Stores (XID, RID, Mode) triples.
• This is a simplistic view suffices for now.
• Mode Î S,X
• Lock compatibility table
• If a Xact cant get a lock, it is
• suspended on a wait queue.

40
Two-Phase Locking (2PL)

• 2PL
• If T wants to read an object, first obtains an S
  lock.
• If T wants to modify an object, first obtains X
  lock.
• If T releases any lock, it can acquire no new
  locks!
• Locks are automatically obtained by DBMS.
• Guarantees serializability!
• Why?

41
Strict 2PL

• Strict 2PL
• If T wants to read an object, first obtains an S
  lock.
• If T wants to modify an object, first obtains X
  lock.
• Hold all locks until end of transaction.
• Guarantees serializability, and recoverable
  schedule, too!

42
Lock Management

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

43
Lock Manager Implementation

• Question 1 What are we locking?
• Tuples, pages, or tables?
• Finer granularity increases concurrency, but also
  increases locking overhead.
• Question 2 How do you lock something??
• Lock Table A hash table of Lock Entries.
• Lock Entry
• OID
• Mode
• List Xacts holding lock
• List Wait Queue

44
Handling a Lock Request
Lock Request (XID, OID, Mode)
ModeS
ModeX
Currently Locked?
Empty Wait Queue?
Yes
No
Yes
Currently X-locked?
Yes
No
Put on Queue
No
Grant Lock
45
More Lock Manager Logic

• On lock release (OID, XID)
• Update list of Xacts holding lock.
• Examine head of wait queue.
• If Xact there can run, add it to list of Xacts
  holding lock (change mode as needed).
• Repeat until head of wait queue cannot be run.
• Note Lock request handled atomically!
• via latches (i.e. semaphores/mutex OS stuff).

46
Lock Upgrades

• Think about this scenario
• T1 locks A in S mode, T2 requests X lock on A, T3
  requests S lock on A. What should we do?
• In contrast
• T1 locks A in S mode, T2 requests X lock on A, T1
  requests X lock on A. What should we do?
• Allow such upgrades to supersede lock requests.
• Consider this scenario
• S1(A), X2(A), X1(A) DEADLOCK!
• BTW Deadlock can occur even w/o upgrades
• X1(A), X2(B), S1(B), S2(A)

47
Deadlocks

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

48
Deadlock Prevention
X1(A), X2(B), S1(B), S2(A)

• Assign a timestamp to each Xact as it enters the
  system. Older Xacts have priority.
• Assume Ti requests a lock, but Tj holds a
  conflicting lock.
• Wait-Die If Ti has higher priority, it waits
  else Ti aborts. (non-preemptive)
• Wound-Wait If Ti has higher priority, abort Tj
  else Ti waits. (preemptive)
• Note After abort, restart with original
  timestamp!
• Both guarantee deadlock-free behavior! Pros and
  cons of each?

49
Deadlock Detection

• 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
• Periodically check for cycles in the waits-for
  graph.
• Shoot some Xact to break the cycle.
• Simpler hack time-outs.
• T1 made no progress for a while? Shoot it.

50
Deadlock Detection (Continued)

• 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
T1
T2
T4
T3
T3
T3
51
Prevention vs. Detection

• Prevention might abort too many Xacts.
• 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!

52
Exercise

• Determine whether each of following executions is
  serializable or not. For each serializable
  execution, give the serial schedule which is
  equivalent to the given schedule.
• R1(X), W2(X), W1(X), R2(Y)
• R1(X), W2(X), W3(X), R1(X)
• R1(X), W2(X), W3(Y), W1(Y)

53
Exercise

• Transactions T1, T2, T3 are to be run
  concurrently. The following gives details of the
  proposed interleaving of read/write operations
  and the time when each such operation is to be
  scheduled.
• Time T1 T2 T3
• 1 read(A)
• 2 read(A)
• 3
  read(D)
• 4
  write(D)
• 5
  write(A)
• 6 read(C)
• 7 write(B)
• 8 write(B)
• Determine whether the operations can be executed
  in this order if concurrency is to be controlled
  using two-phase locking