DB2 for z/OS Locking for Application Developers Part 3

This is the third article in the series on locking for DB2 for z/OS developers. To recap, data integrity and application performance are dependent on application programs being designed and coded to take into account the locking strategy used and working in collaboration with the DBMS. The previous article concentrated on lock size and discussed gross locks and intent locks at the tablespace level; in this article I go into more detail about lock modes, and discuss incompatible locks.

Now, some lock modes are only available at the tablespace level, some are available at all levels in the hierarchy, but the implications for concurrency vary considerably depending on whether gross locks or intent locks are taken at the tablespace level. The design default for all tablespaces should be LOCKSIZE PAGE (or ROW where justified, of which more in a later article), as this causes DB2 to acquire intent locks rather than gross locks at the tablespace level, allowing much greater concurrency, as we’ll see in this article.

With the exception of the SQL LOCK TABLE statement and some other rarely used SQL clauses (e.g. USE AND KEEP UPDATE LOCKS), DB2 selects the lock mode for the object based on the LOCKSIZE attribute and the SQL DML statements that reference the object. I’ll go through the lock modes in some detail before explaining how the SQL DML statement determines the lock mode chosen.

The lock modes available at the partition, tablespace, and table levels only are:

• IS (Intent Share)
• IX (Intent Exclusive)
• SIX (Share with Intent Exclusive)

The lock modes available at the partition, tablespace, table, page and row levels (that is, all levels of the locking hierarchy) are:

• S (Share)
• X (Exclusive)
• U (Update)

There are some subtle differences between the way the lock modes operate at the top of the hierarchy and the bottom, so I’ll deal with lock modes at the tablespace level first and the lock modes at the page/row level afterwards.

The two main intent lock modes, IS and IX, provide the best transaction concurrency, as they are the least restrictive and rely on page/row level locks to ensure data integrity. Intent locks are only acquired when the tablespace is defined¹ with the LOCKSIZE PAGE or LOCKSIZE ROW attribute.

The IS (intent share) lock indicates that the application is accessing the tablespace with the intention of reading one or more rows. An application that acquires an IS lock can read but not change data in the tablespace. It might also acquire page or row locks. Concurrent processes with IS or IX locks can read and change the data using page or row locks – this is a key advantage of the IS lock. IS locks are chosen with LOCKSIZE PAGE/ROW if DB2 can detect at BIND time (or PREPARE time for dynamic SQL) that the table is only being accessed for read.

The IX (intent exclusive) lock indicates that the application is accessing the tablespace with the intention of reading and possibly updating one or more rows. An application that acquires an IX lock can both read and change data in the table and might also acquire page or row locks – a page or row lock is always required on any data changed. Concurrent processes with IS or IX locks can read and change the data, using page or row locks – as with IS locks, this is a key point of the IX lock. IX locks are chosen with LOCKSIZE PAGE/ROW if DB2 detects at BIND time (or PREPARE time for dynamic SQL) that there is a possible intent to update the table, for example: there is a cursor defined with FOR UPDATE OF; or there is an INSERT statement.

The two main gross lock modes, S and X, provide very limited concurrency, and are acquired with LOCKSIZE TABLE/TABLESPACE or the SQL DML statement LOCK TABLE IN SHARE/EXCLUSIVE MODE. Because page/row locks are not needed, applications acquiring S and X locks use less CPU resource.

The S lock (share) allows the application to read, but not change, data in the tablespace. Concurrent processes acquiring S, IS or U tablespace locks can read but not change the data.

The X (exclusive) lock allows the application to read and change data in the tablespace. No other concurrent processes can change or read the data.

There are two other locks which can be acquired at the tablespace level, but they are rare cases. These are the U and SIX locks.

The U (update) lock is relatively rare at the tablespace level, as it requires LOCKSIZE TABLE or TABLESPACE and a cursor-based select with a FOR UPDATE OF clause. There is no such SQL statement as LOCK TABLE IN UPDATE MODE which you can use to acquire a tablespace U lock. An application with a U tablespace lock can read but not change locked data: when the application tries to change the data, DB2 attempts to promote the U to an X tablespace lock. Like the S and X tablespace locks, the U tablespace lock does not need page or row locks. Concurrent processes can acquire S or IS locks and read the data, but they can’t acquire any kind of update lock.

The SIX (share with intent exclusive) lock is even rarer, and is acquired then the application already holds an IX lock, then issues the SQL statement LOCK TABLE IN SHARE MODE. The holder of a SIX lock can read and change data in the table, but only when data is changed are page or row locks acquired. Concurrent processes can read data in the tablespace but not change it.

You might ask, why does DB2 support intents locks, as locks are also acquired at the page or row level? One reason is that it allows DB2 to check which gross locks and/or intent locks held by two or more different transactions are compatible with each other, as in the following table:

Page and row-level locks are much simpler, but bear in mind that the holder of a page or row lock must also hold an IS, IX or SIX lock.

An application holding a page/row S lock can read but not change the locked page or row. Concurrent processes can also read but not change the locked page or row, and they can acquire S or U locks on the page/row or might read data without acquiring a page or row lock (this will be covered in the lock avoidance topic in a later article).

Similarly, an application holding a page/row U lock can read, but not change, the locked page or row; however, when the application attempts to change the page/row, DB2 attempts to promote² the U lock to an X lock. Concurrent processes can acquire S locks or read data without acquiring a page or row lock, but cannot acquire a U or X lock. U locks reduce the chance of deadlocks when reading a page or row to determine whether to change it

An application holding a page/row X lock can read or change the locked page or row, and concurrent processes cannot acquire S, U, or X locks on the page or row. Conversely, an application cannot acquire an X lock on a page/row if a concurrent process already holds an S or U lock on that page or row.

This table shows the compatibility matrix for page/row locks:

Bear in mind that there is no compatibility matrix for page/row and tablespace locks – page and row locks are only ever acquired when there is an intent lock on the tablespace.

Let’s briefly consider incompatible locks. What happens if two transactions want to access the same data with incompatible lock modes? For example, transactions A and B both want to update row Z, and transaction A is the first to issue an UPDATE statement and acquire an X lock on row Z. Now, transaction B must wait until transaction A has committed its update even before reading row Z. DB2 detects that transaction B needs a lock on the row, and suspends transaction B, forcing it to wait for the lock to be acquired before it can continue processing. Depending on how long a transaction continues processing between acquiring a lock on a row and releasing it, other transactions can be forced into long lock suspensions and elongated elapsed times, or be the victim of a timeout or deadlock.

If you don’t already understand the concept of a deadlock, consider the case where transaction A has acquired an exclusive lock on row Z and wants a share lock on row Y. However, transaction Tran B has acquired an exclusive lock on row Y and wants a share lock on row Z. We now have the situation where transaction A is waiting for a lock on row Y but transaction B holds an incompatible lock on that row, and transaction B is waiting for a lock on row Z but transaction A holds an incompatible lock on that row. Without intervention, the two transactions will wait forever³. DB2’s deadlock detection mechanism identifies deadlocks very quickly, and chooses one of the transactions as a “victim”. DB2 abnormally terminates that victim and rolls back any updates to the start of the transaction (or previous commit), meaning the other transaction can continue processing.

Timeout is much simpler. DB2 system parameter IRLMRWT determines how long a transaction should wait for a lock before DB2 abnormally terminates it and rolls it back. The longer a transaction holds a lock, the likelier it is that other transactions will timeout while waiting for a lock on that page or row.

Importantly, if an application is suspended because it is waiting for a lock, although performance is worse, it still looks functionally to the transaction as if it is the only transaction in the system, unless there is a deadlock or timeout.

In the next article, I’ll wrap up the discussion on lock size and lock mode by introducing the concept of lock escalation and provide some recommendations on lock size before moving on to describe the final component of the locking mechanism covered in this series of articles, lock duration.

Footnotes

^{1 Via the CREATE or ALTER SQL DDL statements.}

^{2 When DB2 tries to change the mode of lock to a more restrictive one, this is known as lock promotion.}

^{3 A deadlock also occurs if Transaction A has an S lock on row Z and wants an X lock on row Y, and Transaction B has an S lock on row Y and wants an X lock on row Z.}