DB2 Locking, Part 17: In Conclusion

Today's blog post concludes our multi-part series on DB2 for z/OS locking and concurrency. We have touched on a great many aspects of locking in this series. Such an in-depth, technical topic as DB2 locking can be difficult to master, but doing so can deliver a more clear understanding of how DB2 operates and how your programming and design decisions impact not only your application, but also the entire DB2 subsystem. 

A Couple Additional Locking Ideas and Thoughts

Before concluding this series, here are a few more guidelines and thoughts on DB2 locking and concurrency:

• It is a good idea to use clustering to encourage DB2 to maintain data that is accessed together on the same page. If you use page locking, fewer locks are required to access multiple rows if the rows are clustered on the same page or pages. You can also use larger page sizes to control the amount of data locked for page locking.
  
  
• Consider using the free space parameters to influence locking. If you increase free space, fewer rows are stored on a single page. Therefore, fewer rows are locked by a single page lock. This approach can decrease contention. However, it consumes additional disk storage, and it can also decrease the performance of table space scans (because more pages with fewer rows must be read). Additionally, keep in mind that as data is added to the table the free space will decrease (because the new data is using it). As such, locking issues may become more prevalent.

• You can also decrease the number of rows per page using the MAXROWS option of the CREATE TABLESPACE statement. The fewer rows per page, the less intrusive page locking will be because fewer rows will be impacted by a page lock. This approach is probably better than the free space approach (previous bullet) because new data will not impact number of rows per page.

• Design your application programs with locking considerations in mind. THis is the Number One thing to remember to increase concurrency and minimize the impact of locking on DB2 application and system performance. You can minimize the effect of locking through proper application program design. This means:

• Limiting the number of rows that are accessed by coding predicates to filter unwanted rows

• Requesting only the data (rows and columns) that your actually need to perform your business processes

• Perform modifications as close to the end of the unit of work as possible

• And remember to avoid bachelor programming syndrome (see Part 9).

Summary

Of course, there are probably many more hints, tips, and guidelines for developing DB2 databases and applications with concurrency in mind, but I think a 17 part series is sufficient for my blog. If you want more details on concurrency (or any other aspect of DB2 for z/OS) might I recommend the latest edition of my book -- DB2 Developer's Guide, 6th edition.

And be sure to come back and review this series on locking if you get confused as you work to maximize the concurrency of your DB2 queries, transactions, and programs. 

Finally, as a service to my readers, this post includes a directory/index to the 16 separate posts that make up this series.

Index of Blog Posts on DB2 Locking

• DB2 Locking, Part 1: An Overview
• DB2 Locking, Part 2: Table Space and Table Locks
• DB2 Locking, Part 3: Locks Versus Latches
• DB2 Locking, Part 4: Page and Row Locks
• DB2 Locking, Part 5: Lock Suspensions, Timeouts, and Deadlocks
• DB2 Locking, Part 6: Claims, Drains, and Partition Independence
• DB2 Locking Part 7: Lock Avoidance, Related Issues, and Stuff
• DB2 Locking, Part 8: LOBs and Locking
• DB2 Locking, Part 9: Avoid Bachelor Programming Syndrome
• DB2 Locking, Part 10: Know Your ISOLATION Levels
• DB2 Locking, Part 11: Data Sharing Global Lock Management
• DB2 Locking, Part 12: Lock Promotion and Escalation
• DB2 Locking, Part 13: Optimistic Locking
• DB2 Locking, Part 14: Using the LOCK TABLE Statement
• DB2 Locking, Part 15: Tackling Timeout Troubles
• DB2 Locking, Part 16: Skipping Uncommitted Inserts