Transaction Processing: Concurrency and Serializability

1
Transaction Processing Concurrency and
Serializability

• 10/4/05

2

• Interleave transactions to improve concurrency
  increasing concurrency can increase throughput
  (performance).
• Some interleaved transactions will never violate
  isolation because they act on different data.
• Some interleaved transactions MAY violate
  isolation.

3

• Concurrency control An algorithm to (hopefully)
  permit good interleaving and refuse bad
  interleaving.
• NB, Executing a concurrency control algorithm
  will increase overhead of the transaction
  manager.
• This will increase response time,
• and reduce throughput.

4
Concurrency control

• Input to the algorithm are the arriving requests
  for database reads/writes.
• The input is obtained from the various
  transactions.
• Output is a sequence of database read/write
  requests.
• The output is provided to the portion of the data
  manager actually accessing the disk.

5

• A serial schedule has no interleaving between
  transactions (a transaction completes before
  another begins).
• A schedule is correct if it is equivalent to a
  serial schedule.

6
Isolation levels

• By relaxing the isolation requirement, more
  interleaving is possible -- at a greater risk to
  data integrity.
• Isolation levels characterize the amount of
  isolation imposed.

7
Commuting operations

• Two operations, p1 and p2, commute if, for all
  possible initial database states,
• p1 returns the same value when executed in order
  ltp1, p2gt or ltp2, p1gt
• p2 returns the same value when executed in order
  ltp1, p2gt or ltp2, p1gt
• The database state produced by both sequences is
  the same.
• Note, commutativity is symmetric.
• NOTE! Two operations on different data items
  always commute.
• Note, Two operations on the same data item MAY
  commute.

8
Conflicting operations

• Two operations that do not commute are
  conflicting operations.
• E.g., S1 lts11, s12gt
• S1 lts12, s11gt
• If they are run on the same starting state, and
  end up in different states, then s11 and s12
  conflict.
• Look at the following from the aspect of two
  different transactions,
• A read and read on the same item always commute.
• A read and a write on the same item conflict
  because (though the final state is the same),
  value returned depends on order of ops.
• A write and a write on the same item conflict.

9

• If S2 can be obtained from S1 by swapping
  commuting operations, then S1 and S2 are
  equivalent.
• Equivalence of schedules is transitive!

10
Example schedules

• Two interleaved transactions T1 (t11, t12), T2
  (t21, t22)
• S1 s11, s12, s13, s14
• Suppose s12 and s13 commute, then
• S2 s11, s13, s12, s14
• Same start state Same end state

11

• Schedule equivalence (not the same as ENs
  complete schedule definition)
• Two schedules of the same set of ops are
  equivalent iff conflicting operations are ordered
  in the same way in both schedules.
• gt A schedule S2 can be derived from a schedule
  S1 by interchanging commuting operations iff
  conflicting operations are ordered in the same
  way in both schedules.

12
Restatement of Serializable Schedule

• A schedule is serializable if it is equivalent to
  a serial schedule
• Equivalent construction
• Commute commuting operators and use transitivity
  of equivalence, or
• Conflicting operations are in the same order in
  both schedules.

13
Try this is S1 serializable (what
commutations?), S2? S3?

• T1 ltr(a), r(b), b a, w(b)gt
• T2 ltr(a), a , w(a) gt
• S1 ltr1(a), r2(a), w2(a), r1(b) w1(b) gt
• S2 ltr1(a), r1(b), r2(a), w1(b), w2(a)gt
• S3 ltr2(a), r1(a), w2(a), r1(b), w1(b)gt

14
Try this is S4 serializable?

• S4 ltr1(a), r2(b), w2(a), w1(b)gt

15
More on schedule equivalence

• The preceding definition of equivalence (by
  commuting, AKA by maintaining order of
  conflicting ops) is called conflict equivalence.
• A different kind of equivalence is view
  equivalence, two schedules of the same set of ops
  are view equivalent if both the following are
  true
• Corresponding read ops in each schedule return
  the same values,
• Both schedules yield the same final state.

16
View equivalence

• If corresponding read ops in both schedules
  return the same values, then the transactions
  perform the same calculations and write the same
  results!
• I.e., transactions in both schedules have the
  same view of the database.
• Conflict equivalence implies view equivalence
• View equivalence does not imply conflict
  equivalence.
• I.e., Conflict equivalence is the stronger but
  it turns out that conflict equivalence is easier
  to use for concurrency control.

17
Serialization graphs

• A schedule, S, is represented as a directed
  graph.
• Nodes are (committed) transactions.
• Edge between Ti and Tj (Ti -gt Tj) if
• Some op in Ti, pi, conflicts with some op, pj, in
  Tj, and
• pi appears before pj in S.

18
Example

• S1 ltr1(a), r2(a), w2(a), r1(b) w2(b)gt
• T2 writes a after T1 reads a.
• The ops do not commute
• r1(a), w2(a)
• Graph of S1
• T1 T2

19
A schedule is conflict serializable iff its
serialization graph is acyclic.

• T2 T4
• T1
• T3 T5
• T6 T7
• Topological sorts give conflict equivalent serial
  schedules, e.g.
• T1, T3, T5, T2, T6, T7, T4.
• Others?

20
In class

• Using concurrent transactions, deposit to a,
  withdraw from a, make a (non-serial) schedule
• Give the serialization graph
• Is it acyclic? If so, give a conflict equivalent
  serial schedule.
• Identify commuting operations.
• Identify conflicting operations.
• Using the concurrent deposit, transfer and
  withdraw transactions (deposit to a, withdraw
  from b, transfer takes from b and puts in a),
  make a (non-serial) schedule
• Give the serialization graph
• Is it acyclic? Is there a serial schedule?
• How many total pairs of operations are there?
• Identify, at least some, commuting operations.
• Identify, at least some, conflicting operations.

21
A strict concurrency control

• A transaction is not allowed to read or write
  data that has been written by another still
  active transaction. (Recoverability topic later).
• Conflict avoidance
• If operation requests by T1 and T2 do not
  conflict, they are granted.
• Requests dont conflict if either
• Requests are to different data items, OR
• Requests are both reads.

22
In class

• Make the conflict table for the previous
  algorithm(put X for conflicting requests)
• Granted op
• Requested op read write
• read
• write

23
But if you make a transaction wait

• DEADLOCK
• (a cycle of k transactions waiting for each other)

24
Dealing with deadlock

• Prevention maintain a data structure that checks
  whether deadlock may result. If so, some
  transaction involved in the deadlock must be
  aborted.
• Timeout if time to execute exceeds a threshold,
  force an abort.
• TimestampTimestamp start of each transaction.
  Use timestamp to implement a conflict resolution
  policy
• Older transaction never waits for younger (e.g.,
  by aborting younger, even though younger has been
  waiting a long time),
• Younger transaction can only wait for an older
  (place younger on wait-list)

25
Manual locking an alternative to AUTOMATIC
locking

• A transaction explicitly requests concurrency
  control to grant a lock on a data item, then
  makes the read/write request.
• Concurrency control grants (or refuses) locks.

26
UNLOCKING

• Can be automatic -- when a transaction
  terminates, all locks held by it are released.
• Can be manual -- transaction explicitly releases
  a lock.

27
Two phase locking 2PL

• A transaction maintains 2PL protocol if it
  obtains all of its locks before making any
  unlocks
• lock phase, followed by unlock phase
• Automatic locking is 2PL.
• Automatic unlocking is 2PL.
• 2PL protocol produces serializable schedules.

28

• For next time, well discuss the paper in the
  RedBook Granularity of Locks
• How are the different lock modes used?
• What are the degrees of consistency?
• How does the locking protocol relate to degrees
  of consistency.
• What are the overhead costs of the different
  locking protocols?