DB2 for z/OS Locking for Application Developers Part 1

This DB2 for z/OS Locking blog series is mostly targeted at DB2 for z/OS application developers, but is also appropriate for application designers, database administrators, DB2 systems programmers and other DB2 professionals who are concerned with data integrity and application performance.

You might ask, what’s that got to do with locking? It’s all too easy to assume that locking just happens and that you don’t need to worry about it. Nothing could be farther from the truth: if you don’t understand the DB2 locking mechanism and the way it is affected by BIND options, then it is very easy for your application to compromise data integrity, or to deliver poor performance – or both. This series of articles is intended as a practical guide to how and why DB2 locks data that will help you avoid the programming pitfalls that can compromise the holy grail of data integrity. If you’re already a seasoned professional, then I encourage to read on as there might be some useful pieces of information for you.

The objective of this series is to help you understand:

• Why locking is important.
• The concepts of DB2 for z/OS locking.
• How DB2 locking behaviour is affected by BIND/REBIND options.
• How to code update applications to avoid data anomalies and guarantee data integrity.
• How you can improve performance by taking as few locks as possible.

There is a whole series of topics related to locking which I don’t cover:

• Drains and claims.
• Restrictive states.
• Utility compatibility.
• Latches.
• LOB locks and XML locks.

This first article lays the groundwork for the more practical discussions later in the series. There’s a fair bit of terminology to define and some first principles to outline before moving onto what they mean for DB2 itself and for application design and coding in practice. I’ll start by discussing at a high level why you should be concerned with locking, and then move on to cover in some detail some of the principles underlying locking for database managers in general, and the challenge of complying with these while delivering the performance demanded by today’s businesses.

There are two main reasons why you should be concerned with locking: ensuring data integrity; and application performance, including CPU consumption, transaction concurrency, and throughput. Without locking, it would be impossible to stop two (or more) applications updating the same data records at the same time, inevitably leading to data integrity problems. Without integrity, the value of data is severely downgraded, and could affect the viability of a business. Imagine a bank which was unable to provide an accurate ledger of transactions against an account – they probably wouldn’t stay in business for very long. I’ll keep coming back to the topic of data integrity throughout this series as it is so vitally important.

However, locking involves a CPU and elapsed time overhead. If one transaction wants to access some data that has been locked and updated by another transaction, then the first transaction has to wait until the lock taken by the second transaction is released. This is known as a lock suspension or lock wait. These add to elapsed times. Taking a lock requires a very small amount of CPU – taking many requires a potentially significant amount, adding to the cost and increasing elapsed times. Therefore, the ideal locking strategy is to lock all the data that needs locking to guarantee data integrity, but for performance reasons to take only the locks absolutely needed and for no longer than necessary.

We all instinctively know that locking is required for data integrity, but to understand the implications for application programs, we need to identify what are the characteristics required of database transactions to ensure that they guarantee data integrity.

Back in 1983, German computer scientists Andreas Reuter and Theo Härder wrote a landmark paper, “Principles of transaction-oriented database recovery”, for the Association for Computer Machinery (ACM) in which they set about establishing a framework for “transaction-oriented recovery schemes for database systems”. In doing so, they answered the question, what are the characteristics of a database transaction required to guarantee data integrity? Their paper defined a now-standard set of properties of database transactions or logical units of work (LUW) designed to guarantee data integrity and consistency even in the event of errors, power failures, and so on, known by the acronym ACID (you can find a copy of the article in the Harvard University archives).

Those ACID[1] properties are:

• Atomicity: either all of a transaction’s updates are made to the database, or none of them are made.
• Consistency: the database manager must enforce all defined consistency rules; the data must be changed from one consistent state at the start of a transaction to another consistent state at the end.
• Isolation: all transactions must be executed, from a data point of view, as though no other transactions are being executed at the same time.
• Durability: once a transaction is committed, then its updates are preserved, irrespective of any system failures.

You’ll notice straightaway, even if you don’t read the article by Reuter and Härder, that the primary focus of these four database transaction properties is firmly on data integrity. In fact, Reuter and Härder state that their objective is to provide a “clear methodological and terminological framework” for database recovery.  It’s clear that it’s largely the responsibility of the database management software (DBMS), such as DB2 for z/OS, to ensure compliance with these properties by providing logging, recovery, backout and crash restart capabilities.

Nevertheless, the principal theme of this series of articles is that the implications spill over into the application domain. Data integrity can only be guaranteed by collaboration between the application program and the DBMS. In this series of articles, I’m going to concentrate on the properties of atomicity and isolation. I’m not considering durability as being an application responsibility, at least for the purposes of this series. Nor do I discuss consistency from an application programming point of view in any detail, for two reasons. Firstly, some aspects of consistency are achieved as a consequence of database transactions having the properties of atomicity and isolation. Secondly, the other aspects arise from standard good programming practices; for example, when performing a funds transfer between two accounts, ensuring that the sum of the two account balances is identical at the start and at the end of the transaction.

In considering database transactions properties, the next question is, what is a database transaction? In somewhat theoretical terms, a database transaction can be regarded as being a set of interactions between the application and the database, where the database is changed from one consistent state to another. In more familiar terms, a transaction consists of a set of database reads and updates which form a logically consistent unit of work such as a bank balance transfer from one account to another. A database transaction ends when the application indicates to the DBMS that it wants to make its database changes permanent by issuing a COMMIT.

DB2 for z/OS locking is designed, at least in part, to comply with the ACID principles, but the challenge is to meet these objectives whilst at the same time providing a highly performant multi-user system which meets the demands of today’s enterprises. The isolation property states that all transactions must be executed, from a data point of view, as though no other transactions are being executed at the same time. Locking is a vital component in achieving this. Quite simply, if data isn’t locked while a transaction is in the process of updating it, then another transaction can update the data at the same time and the updates of one of the transactions will be lost.

To explain the transaction isolation/concurrency challenge simply, let’s take a couple of straightforward examples. Firstly, if a database table is locked for update, no-one else can even read the table while the lock is held. Readers have to wait until the update (exclusive) lock is released. Secondly, if a table is locked for read, no-one else can update the table while the lock is held. Other transactions can read the table, but update transactions have to wait until the read (share) lock is released.

It would be easy to meet the demands of transaction isolation by running database transactions one at a time, serially. Apart from the fact that this would represent extremely inefficient use of expensive computing resources, this would prevent enterprises from running the high-volume, high concurrency workloads required to conduct their business. The pragmatic solution to this conundrum is for the DBMS to compromise on the principle of transaction isolation and push some of the responsibility for this onto application program coding practices. DB2 does this by providing multiple transaction isolation levels[2] which allow the user to establish the desired balance between transaction isolation and concurrency. Which brings us back to the point made earlier – the application programmer must take responsibility for coding for data integrity (and performance), taking into account the level of transaction isolation selected.

There are three main factors that affect transaction isolation and concurrency:

• The size of the lock – whether it is held on a database, table, or row (for example). This is typically known as the lock size.
• The state of the lock – whether, for example, it is an exclusive lock or a share lock. This is known as the lock state or lock mode.
• How long the lock is held – whether it is held until the end of the transaction or for a shorter period. This is known as lock duration.

The locking techniques that are used by a DBMS are known as locking semantics, with each DBMS using its own set of semantics.

In the next article we move on to discuss DB2 for z/OS locking semantics and go into detail about DB2 lock sizes and the concept of hierarchical locking, and introduce the concept of DB2 lock modes.

[1] They built on a previous paper by Jim Gray, “The Transaction Concept: Virtues and Limitations” which included the properties of atomicity, consistency and durability, but not isolation.

[2] Isolation levels are discussed in detail later in the series.