Did you know about P, B or L locks in DB2? They are internal locks in DB2. As a DBA, it is good to know about them so that we know how to deal with them or avoid them.
P Locks
These are package level locks and you will see them when any application is executing a package. This internal P lock will obtain a S (shared) lock on the package when that package is being executed.
[Note: Generally DBAs have a tendency to think that locks are bad and whenever there is a discussion on lock, the first thing that comes to anybody’s mind is that someone is waiting. This is not always true. When you have a shared lock, you are not waiting to execute on that resource. So, please do not think in terms of wait time when you hear about lock.]
If you issue a REBIND command on any package, it will use a P lock on that package and will try to obtain ‘X’ exclusive lock on that package. Now, this REBIND will wait unless ‘S’ lock is removed from that package.
B Locks
These are ONLINE BACKUP locks that are obtained on objects when backup operation is underway on those objects. Please remember that there are some commands or utilities that can hinder ONLINE backup. Those commands or utilities are:
- REORG
- LOAD
- IMPORT WITH replace option
- ALTER TABLE
- CREATE INDEX
Why ONLINE BACKUP does not like above commands or utilities? Because, online backup will obtain a B lock on the objects enforcing that the LifeLSN on that object is not changed. But, above commands or utilities will change the LifeLSN of those objects. So one of them has to wait for another to finish.
As a best practice, one should try to avoid the use of above commands during online backup window.
L Locks
These are internal locks obtained by DB2 on tables. Normally, applications use CS isolation level so that row level lock is only obtained on the row on which you have cursor pointer. But, if you see that the locklist usage is very high even for CS isolation level, you can be 100% sure that your SELECT clause is having LONG VARCHAR or CLOBS columns with an ORDER BY clause.
The reason for high locks usage is due to the fact that row only contains pointer to the LONG VARCHAR or CLOBS column and there is not a parallel tracking of row pointer at the LOB storage. So, in order to protect that data, the L locks will be held on the LOBS result set even though only a row level lock will be held at that row if CS isolation level is used.
The L locks on LOBS is not blocking so you could still do INSERTS and read the long fields. A commit will release the locks.
As a best practice, always use unambiguous cursors in DB2 and if your intent is not UPDATE, always use FOR FETCH ONLY or READ ONLY in your SELECT clause.