Q and A

Asked and Answered

In " Tuning Data Sharing Performance ," you recommended using RELEASE(DEALLOCATE) on the bind parameters to reduce global lock contention. The article said that "RELEASE(DEALLOCATE) causes tablespace locks, once acquired, to be retained until thread deallocation".

We rebound with RELEASE(DEALLOCATE) on some of our CICS/DB2 plans/packages executing in a three-way CICS/DB2 data sharing environment, and the elapsed times increased by more than 30 seconds. The traces noted that the next execution of the transaction would begin once the thread terminated. We rebound them back to RELEASE(COMMIT) and the transaction elapsed times returned to normal. However, on some of our other transactions for this same application (bound with RELEASE(COMMIT)), we're seeing global lock contention.

Assuming plans/packages are bound with RELEASE(DEALLOCATE)), here are my questions:

a. With CICS thread reuse, are the parent L-locks not released until after the CICS thread terminates via the purge cycle?

b. What happens if the CICS thread is reused by another execution of the same transaction?

c. What happens when a transaction that updates specific table spaces/partitions/page sets ends, the CICS protected thread sits idle waiting for another transaction to reuse it, and another execution of the same transaction comes in on another thread from another data sharing DB2 member and has updates to the same table space/partition/page or page set that the previous transaction updated?

I'm not sure when and what locks (both logical and physical) are released at the end of a package execution and at the end of the entire plan when using RELEASE(DEALLOCATE) vs. RELEASE(COMMIT). I'm re-reading your articles on data sharing and CICS/DB2, but I am still perplexed.

Robert Catterall responds:

Your perplexity is understandable ý data sharing locking is a complex topic. First, let's distinguish between physical and logical locks (also known as P-locks and L-locks). Physical locks obtained on page sets and partitions are acquired by a data sharing group member when the associated data set is opened, and held until the data set is physically closed - that can be a long time (days, perhaps). The long duration of these locks is not cause for worry, however, because if a member of the DB2 data sharing group requests a type of physical lock on an object that conflicts with a P-lock held on the object by another member, the two members negotiate and end up holding compatible P-locks on the page set or partition.

P-locks are acquired on individual pages if 1) said pages belong to a data set that is the object of inter-DB2 read-write interest, 2) they have to be updated, and 3) they can't be L-locked at the page level. Examples include space map pages, type 2 index leaf pages, and pages of tablespaces defined with row-level locking. Page P-locks are acquired at the time of a page update operation and are released either at commit time or when another member requests the P-lock on the page. Generally speaking, P-lock activity is not affected by the specification of the RELEASE parameter of the BIND command.

L-locks, as you've noted, are divided into two categories: parent and child. Parent locks are basically tablespace locks, and child locks are basically page or row locks. The specification of the RELEASE parameter on the BIND command does not affect child L-lock activity.

But the RELEASE specification does affect parent L-lock duration. If a program is bound with RELEASE(COMMIT), tablespace locks will be acquired when needed and released when the program issues a commit (or a synch point, in the case of a CICS-DB2 transaction). If the same program is bound with RELEASE(COMMIT), the tablespace locks will again be acquired when needed, but they will be retained until the thread is deallocated. In the case of a batch job, that will occur at end of job. In the case of a CICS transaction that does not use protected threads, thread deallocation will typically occur when the transaction completes. If a CICS transaction uses protected threads, the thread used by a transaction might stick around for hours (or even days, if the transaction arrival rate is pretty high around the clock) before being deallocated. If the transaction program was bound with RELEASE(DEALLOCATE), the tablespace locks acquired by the program can be retained for hours (or even days, as noted).

Let me now turn to the questions you asked:

1) If a CICS-DB2 program is bound with RELEASE (DEALLOCATE) and threads used by the transaction are reused, when will the tablespace locks acquired by the program be released? The answer is that they will be released when the thread terminates. A non-protected thread will probably terminate when the transaction using it completes (unless you have transactions queuing up waiting for threads, and I don't like that situation). A protected thread will terminate if it goes unused for the remainder of the current purge cycle plus the entirety of the next purge cycle (an average of about 45 seconds, if the default 30-second purge cycle is in effect). Thread reuse itself does not cause tablespace locks to be released. Instead, thread reuse increases tablespace lock retention time when programs using the thread are bound with RELEASE(DEALLOCATE).

2) What happens if a CICS-DB2 thread is reused by another execution of the same transaction? The answer is that the page and/or locks acquired by the first execution of the transaction will have been released at commit (i.e., synch point) time - generally speaking, at the end of the transaction for online programs (asynchronous transactions can commit multiple times before completing). The tablespace locks acquired by the first transaction are still held by the thread, so they don't need to be re-acquired by the second execution of the transaction (this is the source of most of the CPU savings associated with the combination of RELEASE(DEALLOCATE) and thread reuse).

3) When a program bound with RELEASE(DEALLOCATE) uses a protected CICS-DB2 thread and completes, what happens when another execution of the same program comes from another member of the data sharing group? If the thread used by the first transaction is still there, will the tablespace locks held by that thread conflict with those that the second transaction needs in order to accomplish its updates? The answer is that such contention should not occur. Why not? Because tablespace locks are almost always of the intent variety, e.g., IX or IS. A transaction should get an IX lock on a tablespace or partition that it is updating. If an IX lock is acquired and held, there is not a problem if another transaction also needs an IX lock on the same tablespace or partition, because IX locks on an object are compatible with each other. If, on the other hand, a transaction acquires an exclusive (e.g., X) lock on a tablespace, that lock will block other transactions as long as it is held. Page- and row-level locks are always exclusive, but these locks are released at commit REGARDLESS OF WHETHER THE PROGRAM WAS BOUND WITH RELEASE(COMMIT) OR RELEASE(DEALLOCATE). When you're thinking about RELEASE(DEALLOCATE), you only need to be concerned about tablespace or partition locks.

So, why did your transaction elapsed time go up when you tried RELEASE(DEALLOCATE)? First, you want to make sure that response time actually went up. How do you know that elapsed time went up by more than 30 seconds? If you looked at your DB2 monitor data and saw an increase in class 1 elapsed time (usually labeled total elapsed time), note that this figure was misleading. Class 1 elapsed time is measured from first SQL statement issued to thread termination (or thread reuse by another transaction). So, if you use protected treads, you will almost always see elongated class 1 elapsed times when looking at DB2 monitor data. This is normal. Class 2 (i.e., in-DB2) elapsed time is what's important (note that this is a protected thread thing, not affected by the specification of the RELEASE parameter on the BIND command). If, on the other hand, your CICS monitor showed a 30+ second increase in transaction elapsed time, and/or you saw a big increase in class 2 (in-DB2) elapsed time for the transactions, how could this have been caused by going with RELEASE(DEALLOCATE)? Possibly, some exclusive tablespace locks were held by the threads. That could happen if transactions use the SQL statement LOCK TABLE, or if lock escalation occurs. It's easy to check for the occurrence of either LOCK TABLE or lock escalation via DB2 monitor accounting and statistics reports (or online displays of thread detail and statistics data). If exclusive locks are not the culprit, it could be that EDM pool space is not sufficient, causing waits when package table sections have to be loaded. Check the percentage of free pages in the EDM pool. If it's in the low single digits, you may have lacked sufficient free space (can be addressed through EDM pool enlargement). Another possibility is that protected threads caused the number of active threads to bump up against the CTHREAD limit specified in ZPARMs. Finally, it is possible for RELEASE(DEALLOCATE) and thread reuse to increase data sharing lock propagation activity, this because it may be that more page L-lock requests will be propagated to the lock structure in the coupling facility. If you were getting pretty poor coupling facility performance, the increase in lock structure activity could slow things down. This is highly unlikely, as you almost certainly would have already noted the coupling facility performance problem.

Bottom line: RELEASE(DEALLOCATE) and thread reuse should not have increased your transaction response times. We use RELEASE(DEALLOCATE) and protected threads for our highest-volume transactions, and we get sub-second response times in our three-way CICS-DB2 data sharing group.

See a complete archive of reader/author Q&As .