March 2008 - Table Spaces And Locking Levels, Part 3

Table Spaces And Locking Levels, Part 3
by Bonnie Baker

In Part 1, I described a database and three types of table spaces that can be defined within a database. In Part 2, I discussed how page-level locking is handled in those different types of table spaces. You might want to review those columns before reading this one.

In the third part of this series, we'll look at how DSNZPARMs are used to control the maximum number of locks we may acquire, the length of time we'll wait for a lock, and other system-level issues. We will also explore timeouts, lock escalation vs. lock upgrades, and deadlocks. And, we'll find out how an index-only job can time out even though we don't get locks on indexes.

DSNZPARMs

Because each lock requires about 250 bytes of memory (540 in V8 and later), system-level controls (DB2 DSNZPARMs) limit the number of locks each user can acquire.

One DSNZPARM specifies the maximum number of locks a user may acquire while his program or transaction is running. If a user tries to exceed that number, the lock request will receive a -904 SQLCODE (resource unavailable). The unavailable resource is another lock.

Another DSNZPARM limits the number of locks a user is allowed to acquire on any one table space. If a user attempts to exceed that number, DB2 will try to allow the job to continue (and attempt to avoid a long ROLLBACK) by taking over the next higher lock level (TABLE or PARTITION, depending upon the type of table space). This process is called "lock escalation." If the escalation succeeds, the lower-level page locks can be released.

Let's say the user is doing UPDATEs to a table in a segmented table space. This user first acquired an IX lock on the table space and on the table before acquiring an X lock on each page on which an UPDATE has occurred. The X locks on the pages have accumulated and reached the DSNZPARM maximum. Instead of rolling back the statement, DB2 recognizes that all the X page locks can be released if this user can acquire an X table lock to replace the existing IX table lock. In order for the request for the X lock on the entire table to be successful, no other user can have any kind of lock on the table. If another user does, the attempt to escalate will fail and the greedy user will receive a -911 (TIMEOUT) SQLCODE. If the escalation is successful, the user will have an IX lock on the table space and an X lock on the table; all the other locks will have been released.

Lock escalation rarely succeeds. When it does, it usually causes problems because the X lock on the table causes all other users to time out.

The system value for the maximum number of locks per table space may not be appropriate for some table spaces. Therefore, you can override it in the table space DDL with the LOCKSMAX parameter.

When lock limits are being reached and users are receiving -911s and -904s caused by asking for too many locks or by a failed attempt to escalate, don't increase the limits. Doing so rewards "the bad guy" — and could eventually bring DB2 down. Instead, examine the greedy jobs to see if appropriate, lock-releasing COMMIT logic is in use.

A third DSNZPARM sets a limit on the amount of time users will wait for an unavailable lock. Suppose the parameter is set to 60 seconds (the default). When a user requests a lock on a resource and an incompatible lock exists (our user needs an X lock on a page and another user has an S lock on that page), the user will wait...but not forever. The user will wait under a clock. If the S lock is released, the user will immediately acquire the needed X lock. If the S-lock isn't released, our user will time out in the following manner: The clock has an alarm that goes off every 60 seconds (per the DSNZPARM). Each time the alarm goes off, any user that was waiting the last time the alarm went off (in other words, has waited at least 60 seconds) will time out. If the user has waited less than 60 seconds, he will continue to wait until the next time the alarm goes off. Our user times out the second time the alarm goes off and receives a -911 SQLCODE.

Lock Upgrades

Suppose our user begins by reading a page using a CURSOR that includes the FOR UPDATE OF... syntax. On the initial read, the user will acquire an IS lock on the table space, an IS lock on the table, and a U lock on the page. If the user then executes an UPDATE WHERE CURRENT OF CURSOR statement, the existing locks must be upgraded to show that our user is no longer just reading. The U lock must be upgraded to an X lock, and both IS locks must be upgraded to IX locks.

Lock upgrades occur when a user begins as a reader with the intent to share and then changes to a writer with the intent to do something exclusive. Upgrades can fail with a -911 if another user has an incompatible lock on the page. For example, assume our user has a U lock on the page and another user has an S lock on the same page. When attempting to upgrade to an X lock, our user will be told to wait under the clock. If the user with the S lock lingers too long, our user will time out.

Deadlocks

A deadlock isn't the same as a timeout. While a user who times out did have a chance to succeed (if the incompatible user had released his lock and gotten out of the way), a deadlock victim had zero chance of success.

Here's a typical example of a deadlock. User 1 acquires an X lock on page 1 and needs an X lock on page 2 to complete his unit of work. However, User 2 has acquired an X lock on page 2 and needs an X lock on page 1 to complete her unit of work. Neither has a chance of success because each needs what the other has before a COMMIT can be executed to release the locks.

Because deadlocks are hopeless situations, DB2 has a deadlock detector. The detector will determine which user has done the least work (written the fewest log records) and issue that user a -911, rolling back the unit of work.

The example I mentioned involved two pages. However, two users can deadlock on a single page. Suppose User 1 has a U lock on page 1 because of a read FOR UPDATE OF... and wants to upgrade that lock to an X lock with an UPDATE WHERE CURRENT OF CURSOR... statement. User 2 has an S lock on page 1 and issues a stand-alone, searched update to update a row on page 1. She must upgrade the S lock to an X lock. Neither can succeed because of the other. The deadlock detector must come to the rescue.

Index-only Locking Contention

Since V4 and the Type 2 index, DB2 no longer acquires locks on indexes. So, how can an index-only job time out?

Suppose an index-only job is running concurrently with a job that isn't index-only. The index-only job uses INDEX2 (on LASTNAME, FIRSTNAME, MIDDLEINITIAL) on the EMPTABLE and executes this SQL:

SELECT FIRSTNAME, MIDDLEINITIAL FROM EMPTABLE WHERE LASTNAME = :HVLN

This SQL doesn't read the table, because everything it needs is in INDEX2. The other job uses INDEX1 (on EMPLOYEE_NUMBER) and executes this SQL:

UPDATE EMPTABLE SET SALARY = SALARY + :HVMORE WHERE EMPLOYEE_NUMBER = :HVEMPNO

It reads both the table and INDEX1.

For the second job, DB2 can easily ensure that updates are being done only to committed data. DB2 can check as the table page is read and locks are requested. However, the first job was bound with ISOLATION CS — it isn't willing to read uncommitted data. On this job, table pages aren't read and locks aren't requested. So how can DB2 be sure that none of the index rows are uncommitted?

Using some lock avoidance techniques that use information in the index-page and index-row headers, DB2 determines whether the index data being read is possibly uncommitted. If there's a possibility that the index data is uncommitted, DB2 must protect the CS reader. DB2 does this by taking the row ID (RID) associated with the current index row to the IRLM (DB2's Lock Manager) and determining whether another user has an X lock on that RID. If User 2 happens to be updating the salary of the employee whose name we're trying to read, User 1 will be told to wait under the clock and might time out. The name column information is perfectly clean but the row is uncommitted. DB2 doesn't do column-level checking for uncommitted data. If any part of a row is uncommitted, the entire row's data is uncommitted.

What We Know Now

We've learned that our most common lock size (page) is multilevel and includes table space and table/partition locks as well as page locks. We've learned about intent locks and that a lock's mode can change because of upgrades or successful escalation. DSNZPARMs police our locking, and a deadlock detector provides relief from irreconcilable differences. We've also learned that an index-only job can (on very rare occasions) time out.

Bonnie Baker is a consultant and guest trainer at many companies. She specializes in applications performance issues on the DB2 for z/OS platform. She is an IBM DB2 Gold Consultant, a five-time winner of the IDUG Best Speaker award, and a member of the IDUG Speakers’ Hall of Fame. She is best known for her series of seminars entitled “Things I Wish They’d Told Me 8 Years Ago” and the "Programmers Only" column in DB2 Magazine. To schedule classes or consulting, contact her by email at bkbaker@bonniebaker.com, through Bonnie Baker Corporation at 813-837-3393, or via her website at www.bonniebaker.com.

This article was originally published by DB2 Magazine.