Explain Concurrency Control with Locking Methods ?

A lock guarantees exclusive use of data item to a current transaction.

In other words transaction T2 doesn’t have access to a data item that is currently being used by transaction T1.

A transaction acquires a lock prior to data access the lock is released (unlocked ) when the transaction is completed so that another transaction can lock the data item for its exclusive use.

Lock granularity :lock granularity indicated the level of lock use. Locking can take place at the following levels data base, table, page, row, (or)even attribute(field).

Database level : In a data base – level lock, the entire data base is locked, thus preventing the use of any tables in the data base by transaction T2 while transaction T1 is executed.

this level of locking is good for batch process but it is unsuitable for multi-user DBMS.

following figure explains data base-level-lock, transactions, T1 &T2 cannot access the same data base concurrently even when they use different tables.

Table level : In a table level lock the entire table locked, preventing access to any row by transaction T2 while transaction T1 is using the table.

If a transaction requires access to several tables, each table may be locked.

However two transactions can access the same data base as long as they access different tables.

table – level locks while less restrictive than database level locks causes traffic jams when many transactions are waiting to access the same table.

consequently table level locks are not suitable for multi user DBMS.

fig. explains transactions T1 & T2 can not access the same table even when they try to use different rows T2 must wait until T1 unlocks the table.

Page level locks :In a page – level lock the DBMS will lock an entire disk page.

A disk page or page is equal of a disk block.

A page has fixed size such as 4k, 5k, or 16k.

A table can spend several pages and a page can contain several rows of one or more tables.

Page level locks are currently the most frequently used multi user DBMS locking methods.

Row level lock :The DBMS allows current transactions to access different rows of the same table even when the rows are locked on the same page.

Row level locking improves the availability of data.

Field level lock : It allows concurrent transactions to access the same row as long as they require the use of different fields within that row.

field– level locking are most flexible for multi user data access. Types of locks : Binary locks Shared / exclusive locks

Binary locks : A binary locks has 2 states. Locked Unlocked

If an object that is a data base, table, page or row is locked by a transaction no other transaction can use that object.

If an object is unlocked any transaction can lock the object for its use. Ex of binary lock Transaction Step Stored value T1 Lock product T1 Read value 15 T1 Update value +20 35 T1 Write product 35 T1 Unlock product T2 Lock product T2 Read value 35 T2 Update=value +30 65 T2 Write product 65 T2 Unlock product

Shared / exclusive locks :

The tables ‘shared’ & exclusive indicate the nature of the lock.

An exclusive lock exists when access is reserved specifically for the transaction that locked the object.

Exclusive lock give permission to read & write operations.

shared lock exists when concurrent transaction are granted read access on the basis of a common lock.

shared lock means the concurrent transactions are read only.

shared lock is issued when a transaction wants to read data from the data base.

exclusive lock  is issued when a transaction wants to update.

Using shared/ exclusive concept a lock can have 3 states. Unlocked Shared(read) Exclusive(write)

shared /exclusive lock schema increases the lock manager’s overhead for several reasons. v  The type of lock held must be known before a lock can be granted. v  Three lock operations Read-lock, write lock, unlock v  The schema has been enhanced to allow Lock upgrade(shared to exclusive) Lock downgrade(exclusive to shared)

Two phase locking :the two phases are Growing phase Shrinking phase

Growing phase :  In which a transaction acquire all required locks without unlocking any data.

Shrinking phase :  In which a transaction  releases all locks & can not obtain any new lock.

Two – phase locking follow the below rules :

Two transactions cannot have conflicting locks.

No locks operations can proceed a lock operations.

no data are affected until all locks are obtained.

Dead locks : Resource locking gives a new problem dead lock.

when two transaction wait indefinitely for each other to unlock data.

for ex. When two transactions T1 & T2 resources R1 & R2

T1 transaction lock the resource R1 & wait for the resource R2.

T2 transaction lock the resource R2 & wait for the resource R1.

T1 does not release the lock on resource R1 until the resource R2 was unlocked.

T2 does not release the lock on resource R2 until the resource R1 was unlocked.

So they are in waiting state.

Dead lock means infinite locking of two resources.

Three basic techniques to control dead locks are

1.Dead lock prevention :A transaction requesting a new lock is aborted when there is possibility that a dead lock can occur.

If the transaction is aborted, all changes made by this transaction are rolled back & all locks obtained by the transaction are released.

Dead lock prevention works because it avoids the conditions that lead to dead locking.

2.Dead lock detection : DBMS periodically, tests the data base for dead locks. If a dead lock is found one of the transaction is aborted & rolled back & restarted & the other transaction continues.

3.Dead lock avoidance : The transaction must obtain all of the locks it needs before it can be executed.

The choice of the best dead lock control method, to use depends on the data base environment. For ex : The probability of dead lock is low ® dead lock detection is recommended.

The probability of dead lock is high®dead lock prevention is recommended.