Monday, December 24, 2018

Happy Holidays 2018

It is hard to believe that yet another year has come and gone (well, almost) and that the holiday season is once again upon us. And that means it is time to reflect on the past year -- including all that we have accomplished and what is yet to be done.

And importantly, it is also time to wind down and relax with friends, family and loved ones.  A time to put down the work that consumes us most of the year and to celebrate and enjoy... 

So whatever holiday tradition you celebrate, be sure to celebrate well, wave goodbye to 2018 and ring in the New Year with happiness and anticipation...

...and I'll see you back here on the blog in the New Year, 2019!

Monday, December 17, 2018

Dirty Reads... Done Dirt Cheap

Let's talk about dirty reads (with apologies to the AC/DC pun in the title of this blog post).

Application programmers must understand how concurrency problems impact the access and modification of Db2 data. When one program attempts to read data that’s in the process of being changed by another, the DBMS must forbid access until the modification is complete to ensure data integrity. Most DBMS products, including Db2, use a locking mechanism for all data items being changed. Therefore, when one task is updating data on a page, another task can’t access data (i.e., read or update) on that same page until the data modification is complete and committed.

If you are interested, I wrote a 17-part series of blog post on Db2 locking back in 2013... that last part, found here, contains an index to all 17 posts. But back to today's topic... the dirty read.

Before discussing what a “dirty read” is, we should first talk a bit about transactions and the importance of ACID. With the advent of NoSQL database systems that do not always support ACID, it is important that developers and DBAs understand what ACID is and why it is important to the integrity of your data.

Transactions and ACID

A transaction is an atomic unit of work with respect to recovery and consistency. A logical transaction performs a complete business process typically on behalf of an online user. It may consist of several steps and may comprise more than one physical transaction. The results of running a transaction will record the effects of a business process—a complete business process. The data in the database must be correct and proper after the transaction executes.

When all the steps that make up a specific transaction have been accomplished, a COMMIT is issued. The COMMIT signals that all work since the last COMMIT is correct and should be externalized to the database. At any point within the transaction, the decision can be made to stop and roll back the effects of all changes since the last COMMIT. When a transaction is rolled back, the data in the database will be restored to the original state before the transaction was started. The DBMS maintains a transaction log (or journal) to track database changes.

In other words, transactions exhibit ACID properties. ACID is an acronym for atomicity, consistency, isolation, and durability. Each of these four qualities is necessary for a transaction to be designed correctly.

  • ·        Atomicity means that a transaction must exhibit “all or nothing” behavior. Either all of the instructions within the transaction happen, or none of them happen. Atomicity preserves the “completeness” of the business process.
  • ·        Consistency refers to the state of the data both before and after the transaction is executed. A transaction maintains the consistency of the state of the data. In other words, after running a transaction, all data in the database is “correct.”
  • ·        Isolation means that transactions can run at the same time. Any transactions running in parallel have the illusion that there is no concurrency. In other words, it appears that the system is running only a single transaction at a time. No other concurrent transaction has visibility to the uncommitted database modifications made by any other transactions. To achieve isolation, a locking mechanism is required.
  • ·        Durability refers to the impact of an outage or failure on a running transaction. A durable transaction will not impact the state of data if the transaction ends abnormally. The data will survive any failures.

Let’s use an example to better understand the importance of transactions to database applications. Consider a banking application. Assume that you wish to withdraw $50 from your account with Mega Bank. This “business process” requires a transaction to be executed. You request the money either in person by handing a slip to a bank teller or by using an ATM (Automated Teller Machine). When the bank receives the request, it performs the following tasks, which make up the complete business process. The bank will:

  1. Check your account to make sure you have the necessary funds to withdraw the requested amount.
  2. If you do not, deny the request and stop; otherwise continue processing.
  3. Debit the requested amount from your checking account.
  4. Produce a receipt for the transaction.
  5. Deliver the requested amount and the receipt to you.

The transaction performing the withdrawal must complete all of these steps, or none of these steps, or else one of the parties in the transaction will be dissatisfied. If the bank debits your account but does not give you your money, then you will not be satisfied. If the bank gives you the money but does not debit the account, the bank will be unhappy. Only the completion of every one of these steps results in a “complete business process.” Database developers must understand the requisite business processes and design transactions that ensure ACID properties.

To summarize, a transaction—when executed alone, on a consistent database—will either complete, producing correct results, or terminate, with no effect. In either case the resulting condition of the database will be a consistent state.

Now Let’s Get Back to Dirty Reads

Programs that read Db2 data typically access numerous rows during their execution and are susceptible to concurrency problems. But when writing your application programs you can use read-through locks, also known as “dirty read” or “uncommitted read,” to help overcome concurrency problems. When using uncommitted reads, an application program can read data that has been changed, but not yet committed.

Dirty read capability is implemented using the UR isolation level (for uncommitted read). If the application program is using the UR isolation level, it will read data without taking locks. This lets the application program read data contained in the table as it’s being manipulated. Consider the following sequence of events:

1.     At 9 a.m., a transaction containing the following SQL to change a specific value is executed:

   WHERE  EMPNO = 10020;

2.     The transaction is long-running and continues to execute without issuing a COMMIT.
3.     At 9:01 a.m., a second transaction attempts to SELECT the data that was changed, but not committed.

If the UR isolation level was specified for the second transaction, it would read the changed data even though it had yet to be committed. Because the program simply reads the data in whatever state it happens to be at that moment, it can execute faster than if it had to wait for locks to be taken and resources to be freed before processing.

However, the implications of reading uncommitted data must be carefully examined before being implemented, as several problems can occur. A dirty read can cause duplicate rows to be returned where none exist. Alternately, a dirty read can cause no rows to be returned when one (or more) actually exists.

Some Practical Advice

So, when is it a good idea to implement dirty reads using the UR isolation level? If the data is read only, a dirty read is fine because there are no changes being made to the data. In "real life," though, true read only data is rare.

A general rule of thumb is to avoid dirty reads whenever the results of your queries must be 100 percent accurate. For example, avoid UR if calculations must balance, data is being retrieved from one source to modify another, or for any production, mission-critical work that can’t tolerate data integrity problems.

In other words: If my bank deployed dirty reads on its core banking applications I would definitely find myself another bank!

One of the more concerning things that I’ve witnessed as a Db2 consultant out “in the real world” is a tendency for dirty read to be used as a quick and dirty way to improve performance. By appending a WITH UR to a statement a developer can remove the overhead of locking and improve performance. But often this is done without a thorough investigation of the possible implications. Even worse, some organizations have implemented a standard that says SELECT statements should always be coded using WITH UR. That can wreak havoc on data integrity... and it goes against my core mantra - almost never say always or never.

Most Db2 applications aren’t viable candidates for dirty reads, but there are a few situations where dirty reads can be beneficial. Examples include access to a reference, code, or look-up table (where the data is non-volatile), statistical processing on large amounts of data, analytical queries in data warehousing and Business Intelligence (BI) applications, or when a table (or set of tables) is used by a single user only (which is rare). Additionally, if the data being accessed is already questionable, little harm can be done using a dirty read to access the information.

Because of the data integrity issues associated with dirty reads, DBAs should keep track of the programs that specify an isolation level of UR. This information can be found in the Db2 Catalog. The following two queries can be used to find the applications using uncommitted reads.

Issue the following SQL for a listing of plans that were bound with ISOLATION(UR) or contain at least one statement specifying the WITH UR clause:

         P.ISOLATION = ˈUˈ
        OR S.ISOLATION = ˈUˈ

Issue the following SQL for a listing of packages that were bound with ISOLATION(UR) or contain at least one statement specifying the WITH UR clause:

         P.LOCATION = ˈ ˈ        AND
         P.COLLID = S.COLLID     AND
         P.NAME = S.NAME         AND
         P.ISOLATION = ˈUˈ
        OR S.ISOLATION = ˈUˈ

The dirty read capability can provide relief to concurrency problems and deliver faster performance in specific situations. Understand the implications of the UR isolation level and the “problems” it can cause before diving headlong into implementing it in your production applications.