DB2 for z/OS Locking for Application Developers Part 5

Welcome to this, the fifth in the series on DB2 Locking for Application Developers. So far, we’ve covered a lot of ground, including the ACID properties of database transaction, the locking semantics implemented by DB2 – lock size, lock mode and lock duration – and issues such as lock contention and lock escalation, all with a view to understanding how data integrity and application performance depend on an effective partnership between DB2 and the application.

The next stage in this journey takes in the four isolation levels supported by DB2 (RR, RS, CS and UR), and the way they provide differing degrees of compromise between isolation (remember, the isolation property specifies that “all transactions must be executed, from a data point of view, as though no other transactions are being executed at the same time”) and concurrency – multiple transactions potentially accessing the same data running alongside each other.

The four DB2 isolation levels are determined at the package level at BIND time for both static and dynamic SQL, and control the trade-off between isolation and concurrency. The isolation level specified at the package level can be overridden for a SELECT statement by specifying the WITH UR/CS/RS/RR clause. Moreover, if you specify WITH RS/RR, you can specify which lock mode DB2 should use by additionally specifying USE AND KEEP EXCLUSIVE/UPDATE/SHARE LOCKS. Although we’re getting slightly ahead of ourselves, it’s worth noting that the “AND KEEP” syntax doesn’t affect lock duration for SELECT … WITH RS/RR statements.

The isolation level determines how long locks are held – lock duration – and to some extent which locks are held. This is also affected by the CURRENDATA BIND option, which will be covered later.

The four isolation levels are:

• Repeatable Read (RR)
• Read Stability (RS)
• Cursor Stability (CS)
• Uncommitted Read (UR)

I’ve given the DB2 names for these, which is important because these are the terms used when binding a package or specifying the isolation level for a SELECT statement. Confusingly, ANSI¹ isolation level names are different from DB2’s. As we go through the isolation levels in order of greatest isolation/least concurrency to least isolation/maximum concurrency, I’ll also give you the ANSI isolation level names which may well be familiar to Java developers, together with some explanatory history². These isolation levels became the industry standard in the ANSI/ISO SQL-92 specification.

The following discussion assumes that you are using LOCKSIZE PAGE or ROW for your tablespaces and that therefore DB2 is using intent locks rather than gross locks at the tablespace or partition level.

We’ll start off with RR – Repeatable Read, which is one of the two original DB2 isolation levels. If a single RR transaction runs the same query twice, it is certain to see the same set of values (no more and no less).  An INSERT, UPDATE or DELETE by another concurrent transaction which would change the result set is not allowed (the current transaction is able to do so, however, as this quality of the transaction is maintained by locking all the rows read until the next commit, plus all rows evaluated). In DB2, all accessed rows or pages are locked until the next commit point, even if they do not satisfy the predicate. For page level locking, if a row on the locked page is not even evaluated, it remains locked until the next commit. Not surprisingly, the RR isolation level is more likely to lead to lock escalation.

The standard name for this isolation level is Serializable, which makes sense if you remember that it appears to a repeatable read transaction that it’s being executed, from a data point of view, as if no other transactions are being executed at the same time – that is, it appears as if the transactions are being executed serially.

The next isolation level is RS – Read Stability. This makes sure that, if a transaction reads the same row twice, it will have the same value, but does not prevent new rows from appearing during the course of the transaction. That is, DELETE by another transaction is prohibited, but INSERT is allowed, as is an UPDATE which changes an existing, non-qualifying row such that it now qualifies for and appears as a new row in the result set³. All rows or pages satisfying any stage 1 predicates are locked until the application commits, plus all rows or pages evaluated during stage 2 processing, whether or not they qualify. Like RR, the RS isolation level can cause many locks to be held and therefore is more likely to lead to lock escalation than other isolation levels (apart from RR). Read Stability was introduced into DB2 after the SQL-92 standard was published. However, the SQL-92 name for this isolation level is Repeatable Read.⁴

The factor that most inhibits concurrency with both RR and RS, therefore, is the fact that both of them hold all page and row locks – S, U and X – from the time they are acquired to the next commit point, with the small detail that U locks are, of course, promoted to X locks if a row or page is actually updated.

The other original DB2 isolation level, CS or Cursor Stability also differs from the SQL-92 name, Read Committed⁵. With CS, DB2 locks the row on which the cursor is positioned but keeps the lock for the minimum time necessary, that is, until the next row is fetched or a commit point is reached. This applies to both U locks and S locks; U locks are released when the application fetches the next row but are promoted to an X lock if the row/page is updated, and the X lock is retained until the application reaches a commit point.

Now, there are some implications with Cursor Stability. It ensures your transaction doesn’t read a row that’s been changed by another uncommitted unit of work, but it does allow other concurrent transactions to change a row that’s already been read by your application before you reach a commit point. The consequence is that, if the same query is executed more than once in a CS transaction, it might get a different result set. Provided you update rows read via a cursor using a positioned update (WHERE CURRENT OF the cursor) combined with a cursor defined with FOR UPDATE OF, then you can update the row with confidence because DB2 uses a U lock. We’ll discuss the pitfalls of different programming techniques and their remedies later in the series.

Another consequence of Cursor Stability is that it is less likely to lead to lock escalation, certainly in the case of read-intensive applications, which is better for concurrency – gross locks are much worse for concurrency than intent locks. And as S and U locks are typically held for a much shorter period with CS, there are fewer and shorter lock waits, and reduced application contention. This should result in improved performance – faster transactions and higher throughput.

Before we finish the discussion on Cursor Stability, there is one more topic to introduce. I previously said that, with CS, “DB2 locks the row on which the cursor is positioned but keeps the lock for the minimum time necessary”. That is not completely true. Depending on the setting of the CURRENTDATA bind option, DB2 might lock the row on which the cursor is positioned or might avoid taking a lock altogether. We’ll discuss this in detail in a later article, along with the topic of DB2 lock avoidance.

The final DB2 isolation level is Uncommitted Read (UR), whose name is almost the same as that of the corresponding SQL-92 isolation level, Read Uncommitted. Known also by the unflattering name Dirty Read, it allows a transaction to see rows that have been updated by another concurrent transaction which has not yet committed its updates. This can lead to inconsistent data anomalies, as it is possible that the other transaction terminates abnormally and its updates are rolled back by DB2.

However, Uncommitted Read avoids the CPU and elapsed time cost of locking as it takes no row or page locks. However, it does acquire a special lock called a mass-delete lock. This prevents any other transaction executing an SQL DELETE with no WHERE clause, which of course empties the table out. Obviously, an Uncommitted Read transaction will not drive lock escalation.

Uncommitted Read is only recommended where the application can tolerate inconsistent data. I mentioned above the case where one transaction reads another transaction’s uncommitted updates are subsequently rolled back because the second transaction terminates abnormally. Consider another case where the second (concurrent transaction) is partway through a number of updates which form a logically consistent unit of work. The UR transaction risks seeing some rows which have been updated, and some which haven’t, meaning its view of the data is logically inconsistent. This is often referred to as transactionally inconsistent data – all the other isolation levels provided transactionally consistent data. Having said that, some applications can tolerate inconsistent data and for these UR is highly recommended because of the reduced CPU cost and better performance benefits.

In concluding this discussion on transaction isolation levels, it’s hopefully clear by now that for OLTP (online transaction processing) or for high concurrency read-write applications, the default design decision should be to use Cursor Stability, including any batch programs. Use the other isolation levels where necessary but be very wary of the severe impact on concurrency with isolation RR or RS, and the potential to see uncommitted updates with isolation UR.

We’ve gone through a lot of essential information: the ACID properties of database transactions; lock size, lock mode and lock duration in DB2 for z/OS; compatible and incompatible locks; and isolation levels. We’re now ready in the next article to discuss the four main data anomalies that database transactions could encounter if transaction isolation is not effective. A later article will discuss which isolation levels are susceptible to these data anomalies.

Footnotes

^{1 Strictly speaking, the current standard is not an ANSI standard, but a standard written by an ANSI-approved committee, and the current standard is “The SQL Standard – ISO/IEC 9075:2016”. You can read more about it in this ANSI blog entry. Sadly, the content of the SQL Standard is only available to subscribers.}

^{2 This topic is more complicated and controversial than presented here; the topic of database transaction isolation levels and potential data anomalies is widely debated in industry and academic circles. This series of articles is intended as a practical guide for maintaining data integrity and maximising performance with DB2 for z/OS alone, hence the presentation of the subject matter here is necessarily somewhat simplified. Probably the most quoted paper on the topic is the 1995 ACM article “A Critique of ANSI SQL Isolation Levels”}

^{3 This is only true if the row failed stage 1 processing – if it was eliminated from the result set by stage 2 processing, then it is locked until commit and therefore cannot be updated by a concurrent transaction.}

^{4 We can only guess at IBM’s decision-making process here. They could either change the name of the DB2 RR isolation level to serializable and upset the user community by forcing wholesale changes to production jobs and processes, or come up with a new name for the SQL-92 isolation level, Repeatable Read. We can presume that they understandably decided to do the latter, and the name Read Stability was adopted.}

^{5 Some theorists argue that Read Committed and Cursor Stability are in fact two different isolation levels. This debate is beyond the scope of this series of articles.}