Understanding Locking in DB2 - III

 Read Part-I 

 Read Part-II

In this third installment ,we will discuss the Compatibility rules for lock states.


The major effect of the lock state is to determine whether one lock state is compatible with another. Locks of some states do not exclude all other users. Assume that process A holds a lock on a table space that process B also wants to access. DB2 requests, on behalf of process B, a lock of some particular state. If the lock mode of process A permits a request from process B, then the two lock states are said to be compatible.

If the two locks are not compatible, process B cannot proceed. It must wait until process A releases the lock.

Compatibility of page and row lock states.

  
I will just draw the table which will make you better understand it.


 
This table shows whether page or row locks of any two states are compatible (Yes) or not (No).


No question of compatibility of page lock with a row lock can arise, because a table space cannot use both page and row locks.


Two or more processes can concurrently access data by using a page or row share lock (S lock). However, two or more processes cannot concurrently access data using a page or row update-intent lock (U lock). That is, only one process can acquire a U lock on a specific page or row at any instant. Even though a page or row may be U locked, other processes can access that page or row (but for read-only) by acquiring an S lock. If the process holding the U lock wants to do an update or delete, the U lock must be promoted to X lock before this can be
done.

However, the promotion to an X lock does not occur if any other concurrent process holds an S lock on that page or row. Eventually, assuming the S locks are released before the timeout interval, the U lock is promoted to an X lock. While a page or row is U locked, if any other concurrent process wants to do an update or delete, it must first acquire a U or X lock, but has to wait and eventually either the U or X lock is acquired or a timeout occurs.


Compatibility of table space, partition, and table lock states

Compatibility for table space, partition, and table locks is slightly more complex.

Below table shows whether or not the high level locks of table space, partition, or table of any two modes are compatible.







The next and the final post on this topic will be coming soon, which will be on Lock duration.

3 comments:

Anonymous said...

Hi Nitin,

Reason for having two levels of locking is not very clear. Can you please help.

Yash said...

I have gone through the series on locking. But am unable to understand, how DB2 decides whether the process should be granted a lock on the table or on row?

ADA said...

You are my DB2 god sir . Because of you I found DB2 very interesting . Please keep going !!!!! thanks a ton !!!

Post a Comment