Since DB2 v9.7 was released on June 19, 2009 there has been a lot of really good content trickling out. If you are interested you will find much of it on ChannelDB2.com. However, much of it has focused on describing the key feature of v9.7 Oracle Compatibility i.e. ability to enable applications written for Oracle to work on DB2. Notice I did not say “migrate” and that is for a reason.
But what if you are not in to Oracle anyway; no thank you. What is in the new version of DB2 9.7 that you have to look forward to? It turns out there is quite a bit. In this post I will will talk about one such new feature – “read currently committed” mode of the Cursor Stability isolation level. That’s a mouthful. Let me translate that in to English. “Read Currently Committed” makes busy applications (those with many users) go really fast without you changing any application code or even database configuration. Sounds good? I thought so. Let me explain how it works. If
- you have an application with a lot of concurrent users, and
- you use DB2 with a release number that is lower than v9.7, and
- your transaction isolation is Cursor Stability (90% certainty because CS is the default)
you will likely run in to a scenario where your application slows down dramatically under heavy load. This happens not because your code is not efficient and not because DB2 can’t deal with the volume of requests. It happens because the work done by some users interferes with requests from others (when using Cursor Stability isolation level). In other words, writers block readers from retrieving their data. When this happens, your users reading data will get delays waiting for writers to release locks on the data readers want. Why is that so? Well, it is because Cursor Stability semantics were designed to provide the absolute latest data to the readers and to get the latest you need to wait. That is actually the best case scenario. You will often get lock timeouts and deadlocks. If your application is not written to handle these conditions gracefully you are not going to win any popularity contests with your users. Luckily, v9.7 has this new “Currently Committed” semantics which can make you a star without you lifting a finger. When you use these high volume transaction applications DB2 will no longer wait until some transaction completes an update of the record that another user wants to read. It will return whatever data is already committed in the database to the reader without waiting for any updates that may be in flight. Not waiting is good as it allows everybody’s work to proceed at the maximum speed and without contention.
Here are the things that I personally like about this new feature:
- this is now default behavior for any new database
- it can be turned on for an existing database (set cur_commit = ON using UPDATE DATABASE CONFIGURATION command)
- there is no impact on writers performance
However, the thing I like the most is that there is nothing to manage, administer, configure or optimize. It is not often that we get great things for free; there is often a catch. In this case, there is no catch. All you need to do is get the new DB2 Express-C v9.7 (also FREE) and go at it. If you need to learn a bit about DB2, get, you guessed it, a FREE DB2 book. I am sure there are a lot of questions that this short post did not answer. If you are going to be at the IOD conference in Las Vegas there is a session dedicated to this topic 2187 IBM DB2 9.7 Concurrency: Enhanced Implementation and Improved Performance. Be sure to attend. Last but not least, those of you who are familiar with Oracle will recognize that this behavior indeed similar (but better) to what Oracle implements. If you do want to learn more about this and other Oracle compatibility features set aside an hour to watch this webinar or wait for my future posts.