We have a job (say Process A) that updates Table T. The environment is configured so that lock escalation takes place when a large update is being done.
When lock escalation takes place, the locking is switched to TABLE LOCK in X mode from page level locking.
The program issues COMMIT frequently. However, the TABLE LOCK EXCLUSIVE is not getting released after the COMMIT.
It is only getting released if PROCESS A terminates.
However, in our situation, Process A calls Process B (which is a remote cobol).
Process B does some math and tries to update the same TABLE T and fails to get the necessary resources.
This problem does not happen if LOCK ESCALATION does not occur on TABLE T (i.e. the page locks are getting release appropriately after the commits).
1. Is there any sql command within DB2 that will force the TABLE LOCK to be released?
2. DB2 documentation says that the LOCK will be released based on the RELEASE option with the BIND. The RELEASE option is RELEASE(COMMIT). Anything else we need to check for? Is there any setting/flag for the TABLE that says when to release a TABLE LOCK
3. I tried to use LOCK TABLE EXCLUSIVE in Process A. This did not do lock escalation but the LOCK was not released after the COMMIT. Therefore, Process B could not get a lock on the TABLE T. Is there any SQL or DB2 specific command that I can use to release the TABLE LOCK on this table?
Please note that the sql that is being executed is dynamic sql. We know the table name ahead of time but the criteria changes. Not sure if this has an impact on the locking.
In addition, the program tries to compile the sql statement and reuse it (holding the cursor in memory?).
We are looking at separating Processes A and B into their own runs (which is a big pain .....).
However, if we can figure this locking stuff, it will be much more easier.
Let me get this straight. The program is the one that is issuing the TABLE LOCK EXCLUSIVE and DB2 is not dynamically escalating the lock to table level?
Here is some info from the DB2 version 7 manuals that was not contained in the version 6 manual. I don't know if the functionality changed, or if IBM just added this info for better clarification. But please note the part about dynamic plan caching.
From V7 SQL Reference manual on LOCK TABLE statement:
"Releasing locks: If LOCK TABLE is a static SQL statement, the RELEASE option of bind determines when DB2 releases a lock. For RELEASE(COMMIT), DB2 releases the lock at the next commit point. For RELEASE(DEALLOCATE), DB2 releases the lock when the plan is deallocated (the application ends).
If LOCK TABLE is a dynamic SQL statement, DB2 uses RELEASE(COMMIT) and releases the lock at the next commit point, unless the table or table space is referenced by cached dynamic statements. Caching allows DB2 to keep prepared statements in memory past commit points. In this case, DB2 holds the lock until deallocation or until the commit after the prepared statements are freed from memory. Under some conditions, if a lock is held past a commit point, DB2 demotes the lock state of a segmented table or a nonsegmented table space to an intent lock at the commit point."
Personally, I would consider using LOCKSIZE ANY on the table definition and let DB2 automatically escalate locks to table level as it sees fit.