Transaction management in database systems

1
Transaction management in database systems

• Amr El Abbadi
• Department of Computer Science
• University of California
• Santa Barbara, CA 93106

2
Overview

• Serializability Theory and Concurrency Control.
• Recovery and Atomic commitment.
• Ref Concurrency Control and Recovery in Database
  Systems, Bernstein, Hadzilacos and Goodman.

3
Preliminaries

• A database consists of a set of objects x,y,z.
• Each object has a value.
• The values of all the objects form the state of
  the database, and these states must satisfy the
  database integrity constraints.
• Database objects support 2 atomic operations
  readx, writex.

4
Preliminaries

• A transaction is a set of operations executed in
  some order. We will assume total order.
• A transaction is assumed to be correct, i.e., if
  executed alone on a consistent database, it
  transforms it into another consistent state.
• Example r1x r1y w1x w1y is an example
  of a transaction t1 that transfers some amount of
  money from account x to account y.

5
Preliminaries

• Transaction execution must be atomic
• no interference among transactions.
• Either all its operations are executed or none.
• Each transaction ends with a commit or an abort
  operation.
• A concurrency control protocol ensures that
  concurrent transactions do not interfere with
  each other.
• A recovery protocol ensures the all or nothing
  property.

6
Preliminaries

• 2 operations conflict if the order of execution
  is important, i.e. if one of them is a write.
• Given a set of transactions T, a history H over T
  is a partial order over all transaction
  operations and the order reflects the operation
  execution order (transaction order and
  conflicting operations order).

7
Example of a history

• T1 r1x w1x c1
• T2 r2x w2y w2x c2
• T3 r3y w3x w3y w3z c3
• r1x w1x c1
• r3y w3x w3y
  w3z c3
• r2x w2y
  w2x c2

8
Correctness

• A history is serial if for every 2 transactions,
  either all operations of one appear before the
  other or vice-versa.
• Since every transaction is correct, a serial
  history must be correct, and if executed on a
  consistent database, will result in a consistent
  database.
• But we want to allow concurrent transactions

9
Example of concurrent execution transfer 100
from account x to y

• Serial execution Concurrent execution
• r1x returns 200 r1x
  returns 200
• w1x writes 100 w1x
  writes 100
• r1y returns 200 r2x
  returns 100
• w1y writes 300 r1y
  returns 200
• commit t1
  w1y writes 300
• r2x returns 100 commit
  t1
• r2y returns 300 r2y
  returns 300
• commit t2
  commit t2
• BOTH TRANSACTIONS OBSERVE AND WRITE
  
  SAME VALUES!

10
Serializability

• A history is serializable if it is equivalent to
  a serial history over the same set of
  transactions.
• 2 histories are view equivalent of they have the
  same effects, i.e. same values are written by all
  transactions. Since we do not know what
  transactions write, we require that transactions
  read from the same transactions and final written
  values are the same.

11
View Equivalence

• t2 reads-x-from t1 in history H if
• r2x and w1x are in H,
• w1x lt r2x,
• there is no w3x such that w1x lt w3x lt r2x
• We augment the history with 2 transactions
• Tinit, which writes all values initially
• Tfinal, which reads all values at the end.
• 2 histories, H1 and H2 are view equivalent
  if for all transactions ti and tj
  tj
  reads-x-from ti in H1 iff tj reads-x-from ti in
  H2.

12
View Serializability

• A history is view serializable if it is
  equivalent to a serial history.
• PROBLEM recognizing view serializable
  histories is NP-Complete!
• Basic intuition
• w1x
  r2x
• w3x

OR
13
Conflict Serializability

• Recall 2 operations conflict if one of them is a
  write operation.
• Two histories, H1 and H2, are conflict equivalent
  if the order of conflicting operations is the
  same in both histories, i.e., if o1 in t1 and
  o2 in t2 conflict, then
• o1 lt o2 in H1 iff o1 lt o2 in H2.
• H is conflict serializable if it is conflict
  equivalent to a serial history.

14
Serialization Graphs

• How do we prove a history H is (conflict)
  serializable?
• Serialization Graph SG(H)
• nodes are transactions,
• t1 -gt t2 if o1 in t1 and o2 in t2 conflict and o1
  lt o2 in H
• H w1xw1yc1r2xr3yw2xc2w3yc3
• t1 t2
  t3
• Serializability Theorem A history H is
  serializable if and only if (iff) SG(H) is
  acyclic.
• A concurrency control protocol ensures
  serializability.

15
Recoverable Histories

• When a transaction aborts, the system must wipe
  out all its effects
• on data use before images
• on transactions cascading aborts.
• Consider
• w1x,2 r2x w2y,3 c2 a1
• What do we do? Semantic dilemma!
• Solution Only allow recoverable histories.
• A history is recoverable if whenever tj
  reads-x-from ti, ci lt cj.

16
Cascading Aborts

• w1x,2 r2x w2y,3 a1
• Cascading aborts abort t2
• A history avoids cascading aborts (ACA) if
  whenever ti reads-x-from tj, cj lt rix, i.e.,a
  transaction only reads committed values.

17
Strict Executions

• w1x,2 w2x,3 a1
• For recovery we want to use before images.
• What value to restore?
• What if t2 aborts?
• Solution read and write committed data only.
• A history is strict if whenever wjx lt oix
  either ajltoix or cjltoix, where oi is read or
  write, i.e., no object is read or written until
  the transaction that last wrote its value
  terminated (commit or abort).

18
Relationships between Histories
SR
ST
Serial
ACA
RC
19
Two Phase Locking

• Locking Associate with each object a lock.
• A transaction must acquire a lock before
  executing an operation on the object, and
  releases it after execution.
• 2 types of locks read locks and write locks.
• Read write
• read X read and write conflict
• write X X write
  and write conflict
• Two Phase rule Once a transaction has released a
  lock it cannot acquire any locks.

20
Two Phase Locking

• Theorem Every 2PL history H is serializable.
• In SG(H), if ti tj, then for some object
  x, there are conflicting ops pix and qjx such
  that unlock of pi precedes the lock of qj.
• In SG(H), if there is a path t1 t2
  . tn then there are operations p1x and
  qny such that unlock of p1 precedes the lock of
  qn.
• Finally, if there is a cycle t1 t2
  . tn t1, then there are operations
  p1x and q1y such that unlock of p1 precedes
  the lock of q1, a contradiction.

21
Two Phase Locking

• Strict two phase locking Release all locks
  together at termination. This ensures that all
  executions are strict. Most commercial
  implementations are strict 2PL.
• Deadlock Two transactions t1 and t2.
• t1 acquires read lock on x
• t2 acquires read lock on y
• now t1 cannot acquire write lock on y and t2
  cannot acquire write lock on x.

22
Locking Performance

• In a multiprogramming system, resource contention
  arises over memory, processors, I/O channels,
  etc.
• In a locking system, data contention arises due
  to queues , which form due to conflicting
  operations.
• Locking can cause thrashing as number of
  transactions increases, throughput increases up
  to a point and then decreases. In DB, thrashing
  can be caused by data contention alone, ie even
  with infinite resources.
• Thruput
• trans/sec
• 
  transactions

23
Tree Locking KS 80

• Objects are structured as nodes in a tree.
• For simplicity, assume one type of operations,
  access.
• 2 access operations conflict.
• Lock coupling rule if x is not the root,
  transaction t can acquire a lock on x only if t
  is holding a lock on the parent of x.
• Once t releases a lock, it may not subsequently
  obtain that lock again.

24
Ordered Sharing AE90

• w1x r2x w3y w1y r2y (t1 t2 t3)
• Not allowed by 2PL, although serializable.
• 2PL solve the conflict problem by blocking.
• IDEA Instead of blocking allow sharing but
  restrict lock releases.
• Introduce a third mode of locking
• Ordered sharing
• Sharing
• Exclusive

25
Ordered Sharing

• Lock Acquisition RuleIf ti acquires a lock
  pl1x before t2 acquires ql2x then p1 x is
  executed before q2x.
• Two Phase locking rule.
• A transaction t2 is waiting for transaction t1 if
  t2 has acquired a lock after t1 and t1 has not
  released any of its locks.
• Lock Relinquish Rule t2 cannot release any of
  its locks if it is waiting for some transaction
  t1.
• Some transactions may have to delay their commit.
• If all locks are ordered sharing, the protocol
  accepts all (order-preserving) conflict
  serializable executions, i.e., the order of
  non-interleaving transactions is preserved.

26
Timestamp Ordering

• Associate with each transaction a timestamp.
• The CC protocol orders conflicting operations
  according to timestamp order.
• TO rule If pix and qjx are conflicting
  operations, then pi is executed before qj if
  time(ti) lt time(tj).
• Every object maintains max_read and max_write.
• Read if time(ti) lt max_write, then reject read
• Write if time(ti) lt max_read or time(ti) lt
  max_write, then reject write.
• Update max_read and max_write appropriately.
• Thomas Write Rule if time(ti) lt max_write then
  ignore write operation.

27
Serialization Graph Testing

• Maintain a serialization graph as part of CC
  protocol.
• When SGT receives an operation, it adds
  appropriate nodes and edges to graph.
• If no cycle, accept operation, else reject.
• Garbage collection when do we delete a
  transaction?
• When a transaction commits?
• r1x w2x w2y c2 r3y w3z c3w1z
• Safe rule delete a transaction t from SG if t
  committed and is a source in SG.

28
Certification-based CC

• Locking may block resources for long periods.
• Simple Certification Approach
• Immediately execute all operations of t1.
• At commit, check if any active transaction has
  executed a conflicting operation, if so, abort
  t1.
• Proof Idea if t1 t2 then t1 certified
  before t2.
• Several variations, e.g., SGT certification, most
  famous is optimistic concurrency control protocol
  by Kung and Robinson.

29
Optimistic Concurrency Control KR81

• Transactions execute in 3 phases
• Read phase unrestricted reading of any object
• Validation phase ensure that no conflicts
  occurred.
• Write phase after successful validation, write
  values in db.
• Validation of transaction t1
• Check all concurrent transactions t2, i.e., the
  write phase of t2 overlaps with read phase of t1
• if readset (t1) overlaps with writeset (t2) then
  abort t1.
• Further optimizations have been explored.

30
Centralized Recovery

• We need to recover disk failures during
  transaction execution so as to ensure the all or
  nothing property.
• 3 Approaches
• Shadow paging 2 copies of database.
• Before images store on disk log of before
  values and update database immediately. If
  failure occurs and transaction has not committed
  restore db based on log.
• After images Perform updates in a log of after
  images. If transaction commits, install values in
  db from log.

31
Distributed Recovery

• DBs reside on sites in a distributed system.
• Communication between sites by messages only.
• Each transaction has a home site or coordinator,
  and a number of participants.
• Goal Either all sites commit or all abort.
• When a transaction wants to commit, it must be
  sure that all sites agree to commit too.

32
Atomic Commitment

• At commit time, the coordinator requests votes
  from all participants.
• Atomic commitment requires
• All processes reach same decision
• Commit only if all processes vote Yes.
• If there are no failures and all processes vote
  Yes, decision will be commit.

33
Two Phase Commit (2PC)

• Coordinator
• send vote-request
• Collect votes. If all Yes, then Commit, else
  Abort.
• Send decision

• Participant
• receive vote-request
• send Yes or No
• receive decision

34
Failures and Blocking

• What does a process do if it does not receive a
  message it is expecting? I.e., on timeout?
• 3 cases
• participant waiting for vote-request
  abort
• coordinator waiting for vote
  abort
• participant waiting for decision
  uncertain
• Note coordinator never uncertain

35
Termination Protocol

• Can participant find help from other
  participants?
• Send to all participants Help! What is
  decision?
• if any participant has committed or aborted
• send commit or
  abort decision.
• If a participant has not yet voted
• abort and send
  abort decision.
• If all participants voted Yes
• all live
  participants uncertain
• Transaction BLOCKED!

36
Blocking of 2PC

• 2PC is a blocking protocol.
• Basic intuition When a participant is in wait
  (uncertain) state, some other participants may be
  in commit and others in abort states.
• Solution Introduce a buffer state so that if
  any operational site is uncertain, no process can
  have decided to Commit Skeen 82.
• 3 Phase commit protocol only assumes site
  failures.

37
Three Phase Commit (3PC)

• Coordinator
• send vote-request
• Collect votes. If all Yes, then send
  Pre-Commit, else send Abort.
• Collect all Acks, and send Commit

• Participant
• receive vote-request
• send Yes or No
• if receive abort, then Abort, else, send Ack
• If recive commit, then Commit.

38
Failure handling in 3PC

• 5 cases
• participant waiting for vote-request
  abort
• coordinator waiting for vote
  abort
• coordinator waiting for Ack
  commit
• participant waiting for decision
  elect new leader
• participant waiting for commit
  elect new leader
• Note In (5) a participant may still be waiting
  for decision.

39
Termination for 3PC

• Leader sends to all participants requesting
  state.
• if any participant has committed or aborted
• send commit or abort decision.
• If all participants are uncertain
• abort and send abort decision.
• If some participant has pre-committed
• leader sends Pre-commit to all
  and wait for acks
• send commit

40
Commit Protocols Summary

• 2 PC blocks with failures
• 3PC is non-blocking with site failures only.
• 3PC blocks with partitioning failures.
• Partition 1
  Partition 2
• Theorem Skeen82 There is no non-blocking
  atomic commitment protocol in the presence of
  partitioning failures.

41
Conclusion

• Concurrency Control
• Serializability Theory
• 2 phase locking
• Other locking and non-locking protocols
• Recovery
• Centralized.
• Distributed 2PC and 3PC