Interview Questions   Tutorials   Discussions   Programs   

DB2 - What are the various locks available?

asked mar August 21, 2014 12:30 PM  

What are the various locks available?


1 Answers

answered By Mswami   0  
DB2 determines its locking strategy for each transaction during the bind process in most cases.

Share, Update, and Exclusive Locks:

Locks can be taken in share (S), exclusive (X), or update (U) mode. Share locks allow two or more programs to read simultaneously but not change the locked unit, with each taking a share lock. DB2 uses share locks when it responds to SELECT statements. An exclusive lock bars all other users from the locked unit while an update takes place. After a program has taken an exclusive lock, no other transaction can read the data or take any kind of lock against it. (Uncommitted read is an exception.) It is necessary for DB2 to read a row before it can be updated or deleted, and during this read a less restrictive update lock can be taken.

Before changing a value, DB2 can take an S or U lock against the page or row and then promote that to an X to make the change. It could take an X lock initially, but that would limit concurrency unnecessarily, since there would be many cases in which a program could read data in anticipation of changing it but then not make the change at all. By using lock promotion, concurrent users can read the data until the last possible moment before the change - if the change is made at all.

However, this approach also increases the possibility for deadlocks. Between the time the update transaction takes the S lock and promotes it to an X lock, a second update transaction is also entitled to take an S lock against the same page. Each share lock would hold, waiting for the other to be released so it could take an exclusive lock and make its change. Neither will be released, because each is preventing the other from taking the X lock it needs to complete.

The U lock avoids these deadlocks in certain cases while still affording a high level of concurrency. A U lock is compatible with S locks but not with other U locks or X locks. Selecting rows and declaring an intent to update or delete rows results in a U lock being taken while looking for a page to change. Before the change is actually made, the lock is promoted to an X lock after the existing S locks held by other programs are released.

 If and when the program wants to make the change, it waits for preceding S locks to be released and promotes the U to an X lock. The S locks are likely to be released because none of them represent an update transaction vainly waiting its turn for an X lock. If one of the concurrent transactions were for an update, it would have had a U lock, barring the second update transaction and avoiding a deadlock.

Between the time a transaction acquires a U lock and promotes it, other transactions can acquire share locks, enabling them to read the data. The update transaction must wait for all previous S locks taken by other transactions to be released before promoting to an X lock for the change if the change is needed. If a change is not made, the U lock is released depending on the isolation level used and always when a commit work is issued. U lock processing is not used for row locking. An S lock is changed to X lock for the update.

Update locks are available at the page level and are used when searching for a row to be updated or deleted. They are usually used when a cursor is used to fetch the data and the user has specified an isolation level of cursor stability. The DECLARE {name} CURSOR statement with the FOR UPDATE OF clause invokes the use of U locks. DB2 also uses U locks while searching for data to be updated without a cursor, particularly for updates that affect multiple rows.
   add comment

Your answer

Join with account you already have



 Write A Tutorials
Online-Classroom Classes

  1 person following this question

  1 person following this tag

  Question tags

Asked 1 year and 1 month ago ago
Number of Views -160
Number of Answers -1
Last updated
2 years and 2 months ago ago

  Similar questions

Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!