DB2 for z/OS Locking for Application Developers Part 9

In this ninth article in the series on DB2 Locking for Application Developers, which provides information about coding your application not only for data integrity, which is the principle focus of this series of articles, but also for performance and concurrency, taking into account the transaction isolation levels in effect at run time. Background information about DB2 for z/OS locking semantics and mechanisms, transaction isolation levels, data anomalies and more are discussed in previous articles. This article concentrates on coding techniques, mostly for combining read-only cursors with searched update statements, that will provide protection against data anomalies, most specifically the lost update anomaly.

Let’s start with a restatement of why this is important. In DB2 for z/OS, the recommended programming technique for reading rows via a cursor and then updating some or all of those rows is to specify the FOR UPDATE clause on the cursor declaration and use positioned updates – UPDATE WHERE CURRENT OF. This has the advantage that, when you read a row, DB2 takes a U lock on the row or page. This allows concurrent readers with an S lock, but any concurrent transactions requesting U or X locks will have to wait until the U lock is released. When the transaction issues UPDATE WHERE CURRENT OF <cursor-name>, DB2 attempts to promote the U lock to an X lock. This ensures that no other transaction can have updated the row between the SELECT, which protects the row with a U lock, and the UPDATE.

However, it’s not always possible to use FOR UPDATE cursors. Find out why. Click here to continue reading. 

Views: 10

Add a Comment

You need to be a member of The World of DB2 to add comments!

Join The World of DB2

Bringing Db2 enthusiasts together virtually. Expert or novice, distributed or mainframe, this is the place for everything DB2.

Forum

Register Now IBM Data Tech Summit virtual event October 6th!

Started by Surekha in What's hot ? Sep 24. 0 Replies

Data Tech Summit Virtual Event - The Latest News on Enterprise Data & AIDate:  Tuesday, October 06, 2020Time: 11:00 AM Eastern Daylight TimeRegister NowBusiness cycles are…Continue

Introducing IBM Db2 for z/OS Developer Extension for Microsoft Visual Studio Code

Started by Calene Janacek in Application Development and DB2 Jul 30. 0 Replies

We are excited to announce that the first iteration of IBM Db2 for z/OS Developer Extension is available now as a free downloadable extension in the…Continue

© 2020   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service