Understanding Locking in DB2 - II

This is the second installment of locking concept ( Check the first part here ).

In first part, we saw how on various objects locks can be taken and the types of lock (Lock states) for those objects.

In this part we are going to discuss in detail what are these different lock states and their meaning.In the sense when i say that there is an Intent share lock on my table then what does it exactly mean, what kind of lock my particular program holds and more importantly what kind of lock it allows to other application on the same object(resource).

Let's first discuss for all the lock states for high level lock (Table space, partition, and table).

( If you have any problem in understanding the terms here such as high level and level locks or lock state , then just check out the first part).


As discuss , there are the six possible lock states for a high level lock :

• Intent share (IS)

The lock owner can read data in the table space, partition, or table, but not change it. The lock owner may acquire an S(Share) lock on the row or page containing the row. Concurrent processes may both read and change data.

• Intent exclusive (IX)

The lock owner and concurrent processes can read and change data in the table space,partition, or table. The lock owner acquires a U(Update) lock or S (Share) lock on a page or row. When the data is to be changed, the U lock or S lock is promoted to an X(Exclusive) lock.

• Share (S)

The lock owner and concurrent processes can read, but not change, data in the tablespace, partition, or table.There are no S locks on a page or row.

• Update (U)

The lock owner can read, but not change, the locked data. However, the owner can promote the U lock to an X lock and then change the data. Processes concurrent with the U lock can acquire an S lock and read the data, but no concurrent process can acquire a U lock. The lock owner does not need page or row locks.

• Share with intent exclusive (SIX)

The lock owner can read and change data in the table or table space. Concurrent processes can read the data in the table or table space, but not change it. Only when the lock owner changes data is the X lock acquired on a page or row.

• Exclusive (X)

The lock owner may read and change data in the table space or table. Only the concurrent processes using uncommitted read isolation may access the table space or table.

Now we will discuss the lock states for the low level locks ( Page and row).

There are the three possible lock states (S,U AND X) for the low level lock size of a page or row.

Remember (As mentioned in previous post) these LOCKS are applicable only if there is an intent lock on the table space or partition or the table).

• Share lock

The lock owner and any concurrent process can read, but not change, the locked DB2 object. Other concurrent processes may acquire share or update locks on the DB2 object. Also called an S lock.

• Update

The lock owner can read the DB2 object and has the intention to change it. Concurrent processes may acquire share locks and read the DB2 object, but no other process can acquire an update lock. Update locks are promoted to exclusive locks before DB2 actually changes the DB2 object. Promotion to exclusive lock may cause a suspension if other processes are holding share locks. Also called a U lock.

• Exclusive

Only the lock owner can read or change the locked data, with the following exceptions. Concurrent applications using uncommitted read isolation can read the exclusively locked data. Also, if lock avoidance techniques indicate that the exclusively locked data is require currency of data.

Fair enough, In next part we will discuss compatibility between various lock states.