Concurrency Control and Two-Phase Locking Practice Problems

  1. Consider the precedence graph below. Is the corresponding schedule conflict serializable? Explain your answer.1 precedence-graph.png
  2. List the ACID properties. Explain the usefulness of each.2
  3. Explain the distinction between the terms serial schedule and serializable schedule.3
  4. Why do database systems support concurrent execution of transactions, despite the extra effort needed to ensure that concurrent execution does not cause any problems?4

  5. Consider the following two transactions: 

    T1: BEGIN                               
    R(A)                                
    R(B)                                
    if A == 0 then B = B + 1            
    W(B)                                
    COMMIT

T2: BEGIN                    
    R(B)                     
    R(A)                     
    if B == 0 then A = A + 1 
    W(A)                     
    COMMIT

    Add lock and unlock instructions to transactions T1 and T2 so that they observe the two-phase locking protocol. Can the execution of these transactions result in a deadlock?5

  6. What benefit does strict two-phase locking provide? What disadvantages result?6

Footnotes:

1

There is a serializable schedule corresponding to the precedence graph since the graph is acylic. A possible schedule is obtained by doing a topological sort, that is, \(T_1\), \(T_2\), \(T_3\), \(T_4\), \(T_5\).

2
  • Consistency: Execution of a transaction in isolation (that is,with no other transaction executing concurrently) preserves the consistency of the database. This is typically the responsibility of the application programmer who codes the transactions.
  • Atomicity: Either all operations of the transaction are reflected properly in the database, or none are. Lack of atomicity will lead to inconsistency in the database.
  • Isolation: When multiple transactions execute concurrently, it should be the case that, for every pair of transactions \(T_i\) and \(T_j\), it appears to \(T_i\) that either \(T_j\) finished execution before \(T_i\) started, or \(T_j\) started execution after \(T_i\) finished. Thus, each transaction is unaware of other transactions executing concurrently with it. The user view of a transaction system requires the isolation property and the property that concurrent schedules take the system from one consistent state to another. These requirements are satisfied by ensuring that only serializable schedules of individually consistency-preserving transactions are allowed.
  • Durability: After a transaction completes successfully, the changes it has made to the database persist, even if there are system failures.
3

A schedule in which all the instructions belonging to one single transaction appear together is called a serial schedule. A serializable schedule has a weaker restriction that it should be equivalent to some serial schedule. The definition of schedule equivalence we examined was conflict equivalence.

4

Transaction-processing systems usually allow multiple transactions to run concurrently. It is far easier to insist that transactions run serially. However there are three good reasons for allowing concurrency:

  • Improved throughput and resource utilization. A transaction may involve I/O activity and CPU activity. The CPU and the disk in a computer system can operate in parallel. This can be exploited to run multiple transactions in parallel. For example, while a read or write on behalf of one transaction is in progress on one disk, another transaction can be running in the CPU. This increases the throughput of the system.
  • Reduced waiting time. If transactions run serially, a short transaction may have to wait for a preceding long transaction to complete. If the transactions are operating on different parts of the database, it is better to let them run concurrently, sharing the CPU cycles and disk accesses. It reduces unpredictable delays and the average response time.
  • The parallel architecture of virtually all modern computers.
5 
T1: BEGIN                                
    S-LOCK(A)                            
    R(A)                                 
    X-LOCK(B)                            
    R(B)                                 
    if A == 0 then B = B + 1             
    W(B)
    UNLOCK(A)
    UNLOCK(B)
    COMMIT                               

T2: BEGIN                      
    S-LOCK(B)                  
    R(B)                       
    X-LOCK(A)                  
    R(A)                       
    if B == 0 then A = A + 1   
    W(A)
    UNLOCK(B)
    UNLOCK(A)
    COMMIT

Execution of these transactions an result in deadlock. For example, consider the following partial schedule: 

T1: BEGIN                   T2:
    S-LOCK(A)
                                BEGIN
                                S-LOCK(B)
                                R(B)
    R(A)
    X-LOCK(B)
                                X-LOCK(A)

The transactions are now deadlocked.

6

Because it produces only cascadeless schedules, recovery is very easy. But the set of schedules obtainable is a subset of those obtainable from plain two-phase locking, thus concurrency is reduced.