Transactions

1

• Transactions

2
What is it?

• Transaction - a logical unit of database
  processing
• Motivation - want consistent change of state in
  data
• Transactions developed in 1950's  e.g. banking
  activities - allow multiple bank tellers to read
  and make changes
• Idea of transaction formalized in 1970's

3
Why?

• Why interleave operations?
• increases throughput of the system (number of
  transactions that can finish in any given
  period)
• interleave I/Os and CPU time

4
Problems

• 1) Can create inconsistent result if crash in
  middle of transaction
• 2) Can have error if concurrent execution
• 3) Uncertainty as to when changes become
  permanent.  Write to disk every time?
• Concurrency control and Recovery from failures
  are needed to solve these problems

5
ACID properties Jim Gray

• ACID properties
• Atomicity - transaction is indivisible,
• Consistency - correct execution takes DB from one
  consistent state to another
• Isolation transactions affect each other as if
  not concurrent
• Durability - once a transaction completes
  (commits), transaction is recoverable

6
Systems guarantees 4 properties

• ACID properties
• Atomicity
• all or nothing, performs transaction in entirety
  - solves 1)
• Consistency
• a logical property based on some consistency
  rule, implied by isolation
• If isolation is implemented properly - solves 2)
• Isolation
• equivalent to serial schedule (serializability)
• T2 -gt T1 or T1 -gt T2
• takes care of 2)
• Durability
• changes are never lost due to subsequent
  failures - solves 3)

7
ACID properties

• Atomicity ensures recovery
• Consistency ensures programmer and DBMS enforce
  consistency
• Isolation ensures concurrency control is utilized
  
• Durability ensures recovery

8
Operations of transactions

• Read and Write of data items
• Granularity can be  rows of a table or a table
  (Granularity can affect concurrency)
• Each transaction has a number i assigned to it
  (Ti)
• Transaction commit statement - causes transaction
  to end (commit) successfully (Ci)
• Transaction abort (rollback) statement - all
  writes undone (Ai)
• System or User can specify commit and abort

9
Rs and Ws

• Notation
• R1(X) - transaction 1 reads data item X    
  W2(Y) - transaction 2 writes to data item Y    
  C1 - transaction 1 commits     A2 - transaction
  2 aborts, rollback
• A series of R's and W's is a schedule (history)
• Allow multiple users to execute simultaneously to
  access tables in a common DB
• Concurrent access - concurrency

10
Lost Update Problem Dirty Write

• Dirty write - some value of DB is incorrect
                      T1                T2    
  where A10                     R1(A)
                      AA-10                      
                     R2(A) (A10)
                      W1(A)                       
                    AA20                         
                  W2(A)  
• R1(A)R2(A)W1(A)C1W2(A)C2
• The value of A is 30 when T1 and T2 are done, but
  it should be 20                  

11
Dirty Read Problem

• Transaction updates DB item, then fails
                     T1                T2    
  where A10                     R1(A)
                      AA-10                    
  W1(A)                     R1(C)
                                         R2(A)    
  (A0, reads value T1 wrote)
•                     A1 - T1 fails
                                          R2(B)
                                          BBA
                                          W2(B)
• R1(A)W1(A)R1(C)R2(A)W2(A)A1 R2(B)W2(B)C2
• When T1 is aborted, A is reset to value of 10,
  values B                         written by T2
  are incorrect  

12
Unrepeatable Read

• Transaction reads data item twice, in between
  values change                     
  T1                T2     where A10
                      R1(A)                    
  R1(B)                     BBA
                      W1(B)                       
                   R2(A)                           
               AA20                              
            W2(A)                     R1(A)
                      R1(C)                    
  CCA                     W1(C)  
•    R1(A)R1(B)W1(B)R2(A)W2(A)C2R1(A)R1(C)W1(C)C1
• When T1 first reads A it has a value of 10, then
  a value of 30
•                   

13
Degrees of Isolation

• degree 0 - doesn't overwrite data updated (dirty
  data) by other transactions with degree at least
  1
• degree 1 - no lost updates (no dirty writes)
• degree 2 - no lost updates and no dirty reads
• degree 3 - degree 2 plus repeatable reads

14
Serializable

• A transaction history is serializable if it is
  equivalent to some serial schedule         (does
  not mean it is a serial schedule)    The
  important word here is some
•         Example of two serial schedules
• R1(X)W1(X)C1  R2(X)W2(X)R2(Y)W2(Y)C2      
• T1ltlt T2
• R2(X)W2(X)R2(Y)W2(Y)C2  R1(X)W1(X)C1      
• T2 ltlt T1

15
Equivalence

• Is a given schedule equivalent to some serial
  schedule? R2(X)W2(X)R1(X)W1(X)R2(Y)W2(Y)C1C2
• How do we answer this question?
• What is equivalence?
• Need same number of operations
• How to define equivalence
•  

16
Result equivalence

• Result equivalent if produce same final state
• R1(X) (X2)W1(X)C1 R2(X)(XXmod10)W2(X)C2
          if X10,  X20 try                     
        
• R1(X)R2(X)(XXmod10)W2(X)C2(X2)W1(X)C1
                             if X10, X20
• Same results if X10, but not if X 7
• (results are 18 and 14)                     
                      

17
Conflict equivalence

•   First define conflicting operations
• R and W conflict if    1.  from 2 different
  transactions    2.  reference same data item   
  3.  at least one is a write

18
Conflicting operations

• The conflicting operations are    Ri(A) ltlt
  Wj(A)   read followed by a write    Wi(A) ltlt
  Rj(A)   write followed by a read    Wi(A) ltlt
  Wj(A)  write followed by a write
• Ri(A) ltlt Rj(A)  don't conflict    Ri(A) ltlt
  Wj(B)  don't conflict

19
Conflict equivalence

• Conflict equivalent if order of any 2 conflicting
  operations is the same in both schedules
• R1(A)W1(A)C1  R2(A)W2(A)C2 
• or in reverse order R1(A)ltltW2(A)          
        R2(A)ltltW1(A)  W1(A)ltltW2(A)              
  W2(A)ltltW1(A)  W1(A)ltltR2(A)               
  W2(A)ltltR1(A)

20
Conflict equivalence

• R1(A)R2(A)W1(A)C1W2(A)C2
•                     R1(A)ltltW2(A)
                      W1(A)ltltW2(A)
                      R2(A)ltltW1(A)
• NOT serializable - example of lost update

21
Example

• Is this schedule serializable?
•          R2(X)W2(X)R1(X)W1(X)R2(Y)W2(Y)C1C2
•               W2(X)ltltR1(X)          W2(X)ltltW1(X)
           R2(X)ltltW1(X)                
• T2ltltT1
• Is this schedule serializable?   
• R2(X)R1(X)W2(X)R2(Y)W1(X)C1W2(Y)C2
• R2(X)ltltW1(X)          W2(X)ltltW1(X)         
  R1(X)ltltW2(X)         
• not equivalent

22
Strategy

• There is a simple algorithm for determining
  conflict serializability of a schedule
• What is the algorithm?

23
Testing for Equivalence

• Construct a precedence graph (aka serialization
  graph)
•      1.  For each Ti in S, create node Ti in
  graph   2.  For all Rj(X) after Wi(X) create an
  edge Ti-gtTj   3.  For all Wj(X) after Ri(X)
  create an edge Ti-gtTj   4.  For all Wj(X) after
  Wi(X) create an edge Ti-gt Tj    5.  Serializable
  iff no cycles
•     If a cycle in the graph, no equivalent serial
  history

24
Example

• Is this the following schedule serializable?
• R1(A)R2(A)W1(A)W2(A)C1C2  
• T1 ? T2
• ?

25
Serializability

• If a schedule is serializable, we can say it is
  correct
• Serializable does not mean it is serial
• It is difficult to test for conflict
  serializability because the interleaving of
  operations is determined by the operating system
  scheduler
• Concurrency control methods don't test for it.
• Instead, protocols developed that guarantee a
  schedule is serializable.

26
Concurrency Control Techniques

• Protocols - set of rules that guarantee
  serializability      1.  locking      2. 
  timestamps      3. multiversion      4. 
  validation or certification