Concurrency Control Issues

• Pessimistic Concurrency Control
  
  • The transactions are been checking for violating the serialization consistency before letting it execute is called pessimistic concurrency control
    
    • Two phase locking Scheme
      
      • Read /Write lock as a phase
        
      • Unlock – another phase
        
      • Both the above phases wont interleave, because of this, there may be deadlock, which can be detected by means of deadlock detection algorithm and if deadlock is there, one of the transactions is aborted with the nearer timestamp.
        
    • Multiversion Scheme
      
      • There are three locks Read, Write and Certify
        
      • Read lock – Read the needed data from the database
        
      • Write lock – Writing to its own private space
        
      • Certify lock – Updates to the database, this stage is the committed stage.
        

    General locking Rules
    

Lock Already Set	Lock Requested
	Read	Write	Certify
Read	Granted	Granted	Blocked
Write	Granted	Granted	Granted
Certify	Blocked	Granted	Blocked

 

Locking rules for priority Inversion

Lock Already Set by a Low Priority Transaction		Lock Requested by a High Priority Transaction
		Read		Write	Certify
Read	Granted	L-Aborted	Can't Occur*
Write	Granted/Blocked#	Granted	Granted
Certify	Conversion	Granted	Conversion

In the above table,

L-Aborted - Low Priority Transaction Aborted

Conversion – Low Priority Transaction is converted to write lock

* - already the transaction is aborted, so no reading lock set.

 

If we reduce transaction abortion, then in the above table, L-aborted may be when the HPT requesting the certify lock, while the HPT requesting Write lock which will be granted.

 

Lock Already Set by a High Priority Transaction	Lock Requested by a Low Priority Transaction
	Read	Write	Certify
Read	Granted	Granted	Blocked
Write	Blocked	Granted	Granted/Blocked#
Certify	Blocked	Granted	Blocked

# - depending on the implementaion

 

• Optimistic Concurrency Control
  
  • The transactions are first allowed to run, after that they are checked for violating the Serialization Consistency
    
    • Read Phase
      
      • Reads from the database and writes to its own private address space
        
    • Validation Phase
      
      • Checked for violating the serialization consistency.
        
    • Write Phase (if needed)
      
      • If the Serialization consistency is not violated in the above phase, then this phase is needed to update to the database
        
  • So this method is optimistic in the sense that, the transaction are execute and put into the private address space where it is checked for violating the serialization consistency.
    
  • A and T are two transactions where A's timestamp precedes T's timestamp. The serialization consistency is not violated due to T if the following conditions are true.
    
    • A has completed its write phase before T starts its read phase.
      
    • The write set of A is distinct from both the read and write set of T.
      
  • If the above conditions are not satisfied, then T will be aborted as its timestamp is nearer.