Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Locking techniques in Concurrency control in SQL

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 342
    Comment on it

    Concurrency Control
    when multiple users accessing a same database at the same time, If any change made by any one of the user do not adversely affect work of the other users.
    Locking technique in concurrency control means use a lock variable with each data element in database with the aim to achieve a maximum concurrency and minimum delay in processing transactions.


    Basically we have two types of locking techniques are used:
    1. Binary Locks
    2. Shared and Exclusive Locks

     

    Binary Locks
    In binary lock is generally in two states locked(1) or unlocked(0) i.e if the value of the lock variable is 1 it means that the data item is locked and the requesting element is not allow to perform the operation  on database data item. If the value of the lock variable is 0 it means that the data item is unlocked and the requesting element get the access to perform the operation on database data item. These two binary locks make a part of the transactions.


    Each transaction must follow the some transaction rules:
    1. If transaction get the access then it must that before performing an operation on database it will set the value of the lock variable=1.
    2. If transaction complete its operation on database then it must that after completion it will set the value of the lock variable=0.
    3. A Transaction will get the lock operation on the data item only if it doesnot contain any other lock on another data item in database.  
    4.  A Transaction will get the unlock operation on the data item only if it doesnot contain the lock on that data item.


    One transaction can hold only one lock on a particular data item it means that no two transaction will  hold the lock on a same data item in database.

     

    Shared and Exclusive Locks
    Exclusive lock means acquiring a write operation on data item.
    Shared lock means acquiring a read operation on data item.


    In shared we are able to to read the value of a data item but not modify the value of the data item.
    In exclusive lock we are able to read the value of the data item as well as modify the value of the data item but only one transaction will get the exclusive lock on data item at time.
    If the transaction wants a exclusive lock on data item on which another transaction already holding a shared lock then second requesting transaction will wait until the first transaction will unlock the data item but if the second transaction requesting for a shared lock on data item then it will get the lock on the data item. If the first transaction contains a exclusive lock on data item then second transaction will wait until the first transaction will unlock the data item.
    It means that two transactions will hold the read operation simultaneously but not the holds the write operation simultaneously.

     

     

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: