January 18, 2010

Understanding DB2 Locks

Over the years I've seen many developers fail to consider DB2 locking when writing their applications. As a result, some of these applications are burdened with high CPU usage, long wait times or even high abort rates due to deadlock or timeouts.

DB2 locking is just one area covered in the just-published IBM Redbook, "DB2 9 for z/OS: Resource Serialization and Concurrency Control."

This publication can be valuable for anyone who designs and/or develops applications that access DB2 for z/OS. Because of the fantastic job the authors did in presenting this material, even those new to DB2 for z/OS can get up to speed and learn in a few hours what it took me years to learn.

While I can't begin to summarize all of the information in this Redbook, I'll highlight part two, "Application concurrency and lock optimization." I believe this is a must-read for any DBA or application developer working on a new system. It's so important to think not only about the database design, but to also design with concurrency in mind.

Reading this information brought back memories of a new system I worked on in 1994. I was the DBA working with development on the design of this new application. Well into the process, I discovered that the batch processing portion of the application was being designed as if the online system would be offline when the batch was running. The developers didn't realize that the CICS online portion of these system would be up 24-7. I had to explain to them that they needed to design the batch programs to be fail-proof and assume the online systems may be up and running simultaneously. Had I not stepped in, this system would have had some real locking problems. Ultimately, the online transactions as well as the batch jobs were implemented with locking and concurrency in mind. The implementation was successful and we didn't have locking problems.

What about you? Do you have stories related to designing for high availability systems? Have you encountered and fixed locking issues? Please leave a comment so everyone can benefit from your experiences.