Understanding Locking in DB2 - I

Locking in DB2 is the least understood concept among the programmer's.
There are lots of chaos ranging from what is lock to why we need it to end up discussing what exactly is isolation level etc.and i don't blame them.

Some programmer's who have somewhat understanding about it ,they have there different set of questions to ask on it ,such as we have something called as LOCKSIZE parameter while creating tablespace, then we have bind parameter such as Isolation Level and Aquire and Release. Now what exactly the function of these parameter? do we need all of them for deciding the kind of lock DB2 will take on resource for my particular Application Program.


Well to answer these questions let me clarify first that , Locking is not as simple subject as to cover it into a single post , if one want to understand the detailing of it , it may take up reading fully dedicated book on it.

I will try to explain the overview of it in this post and will cover the various aspect of it in coming posts.

Well i will not dwell much into what is the definition of lock and why we need it . you can read it anywhere (better go for IBM manual )


So read on


There are three things which you need to understand first in order to get rid of the concept of Locking.


-- The object on which the LOCK to be taken (That is Table,Page,Row etc.).
    It can also be called as size of  the LOCK.

 -- The mode or type of Locking (Share,Update etc.).
     This controls the degree to which the resource can be accessed concurrently
     by multiple processes.


-- The duration of lock.This refers to the length of time that the lock is held.

Now lets discuss the above mentioned point one by one.


Lock Size

The size of the lock determines how much data is within the scope of a lock (hence the term lock size).

For example, a page lock includes all rows on a page, while a row lock just covers a single row.

The lock size is determined by the LOCKSIZE parameter of the CREATE and ALTER TABLESPACE data definition language statements. The options are ANY, TABLESPACE,TABLE, PAGE, ROW.
ANY is the default. TABLE should only be used for segmented table spaces.

DB2 uses locks of the following sizes:

– Table space

– Partition

– Table

– Page

– Row

Out of theses first three are high level locks(table space,partition,table) and the next two (page and row) are called as low level locks.The low level locks cannot be taken before taking the corresponding high level lock for them while high level locks can be taken directly ( except intent share, intent exclusive, and share with intent exclusive.) I know this statement is bit of confusing here but had to go with flow,don't worry will explain below. 

Lock state or lock mode

The state of a lock, also called the mode, determines the type of access to the locked object permitted to the lock owner and to any other concurrent application processes. The lock states available for an object depend on the lock size.

There are three possible states for a low level lock:

- Share
- Update
- Exclusive

Now these three modes of locks are available for low level lock( page and row) while high level lock size have these three modes of locking with the addition of three intent locks referred to as intent share, intent exclusive, and share with intent exclusive.

The primary difference is that whenever an intent lock is held,DB2 also has to request a lock at the page or row level, hence the reference to low level locks.

The share, update, and exclusive locks at the table space, partition, and table level are also
referred to as gross locks because they do not require low level locks to be taken.

Now, i think you got what i was trying to say previously.

When to take a lock ?

The low level locks, that is page and row are acquired when necessary while a process is being executed.
When the high level locks, that is, table space, partition, or table, are acquired depends on the ACQUIRE BIND option.

For ACQUIRE(ALLOCATE), the high level locks are acquired at the first SQL call.

For ACQUIRE(USE), the high level locks are acquired at the first actual use of the table.

The ACQUIRE bind options have no influence on when high level locks are acquired for dynamic SQL, which is always when the object is first accessed.


So until now we are clear with size and modes of lock. Lets revise it.

We can have high level locks (lock size) which covers table,tablespace and partition also we have low level locks which are on row and pages.Then we have various modes of locking that is intent share,intent exclusive,share intent exclusive,share,update and exclusive.Out of theses six modes of locking , all six are applicable for high level locking while for low level locking only three modes( share,update and exclusive) are applicable.

Now,I must stop this post here so you can grasp it. Don't worry if you don't get the concept in one reading , go through the post number of times till you get clear picture out of it.

In next post  i will discuss what all these six modes of locking exactly mean in detail and there compatibility with each other. Then we have the third attribute remaining to discuss that is duration of lock (May be one after the next post)

>> Read Part II