Subject: Distributed Database

Topic: Distributed Transaction & Concurrency Control

Difficulty: Medium

Concurrency control

• Concurrency controlling techniques ensure that multiple transactions are executed simultaneously while maintaining the ACID properties of the transactions.

• Simultaneous execution of transactions overashared database can create several data integrity and consistency problems.

  • Lost Update
  • Dirty Read Problems
  • İncorrect Summary Problems
  • Unrepeatable Read Problem

• The various approaches for concurrency control,

  • Lock based concurrency control
  • Timestamp concurrency control
  • Optimistic concurrency control

Lock-based Protocols

• Database systems equipped with lock-based protocols usea mechanism by which any transaction cannot read or write data until it acquires an appropriate lock on it.
• Locks are of two kinds ,
  • Binary Locks-Alock onadata item can be in two states; it is either locked or unlocked.
  • Shared/exclusive–Ifalock is acquired onadata item to performawrite operation, it is an exclusive lock. Read locks are shared because no data value is being changed.

Types of lock protocols

• Single phase locking -Two phase locking- 2PL
  • Strict two phase locking
  • Rigorous 2PL

Single phase:

• Lock-based protocols allow transactions to obtainalock on every object beforeaoperation is performed.

• Transactions may unlock the data item after completing the operation.

• Two phase: This locking protocol divides the execution phase ofatransaction into two phase.

  • In the first part, the transaction acquires all the locks (Growing phase).
  • Second phase starts, the transaction cannot demand any new locks; it only releases the each acquired locks lock after use (Shrinking phase).

Strict Two-Phase Locking:

• The first phase of Strict-2PL is the same as 2PL.
• After acquiring all the locks in the first phase, the transaction continues to execute normally.
  • But in contrast to 2PL, Strict-2PL does not releasealock after using it.
  • Strict-2PL holds all the exclusive locks until the commit point and releases all the locks at a time.

Rigorous 2PL:

• The first phase of Rigorous 2PL is the same as 2PL
  • After acquiring all the locks in the first phase, the transaction continues to execute normally
  • In Rigorous 2PL all locks are released only after commit(both shared and exclusive.

Locking rules for nested transactions

• Every lock that is acquired byasuccessful sub transaction is inherited by its parent when it completes.
• Parent transactions are not allowed to run concurrently with their child transactions.
• Sub transactions at the same level are allowed to run concurrently.

Lock-based Protocols

• For a sub transaction to acquirearead lock on an object, no other active transaction can haveawrite lock on that object.
• For a sub transaction to acquireawrite lock on an object, no other active transaction can havearead or write lock on that object.
• When a sub transaction commits, its locks are inherited by its parent
• When a sub transaction aborts, its locks are discarded.