DB2 Read committed without locking?

Question

We have a transaction that is modifying a record. The transaction must call a web service, rolling back the transaction if the service fails (so it can't commit it before hand). Because the record is modified, the client app has a lock on it. However, the web service must retrieve that record to get information from it as part of it's processing. Bam, deadlock.

We use websphere, which, for reasons that boggle my mind, defaults to repeatable read isolation level. We knocked it down to read_committed, thinking that this would retrieve the row without seeking a lock. In our dev environment, it seemed to work, but in staging we're getting deadlocks.

I'm not asking why it behaved differently, we probably made a mistake somewhere. Nor am I asking about the specifics of the web service example above, because obviously this same thing could happen elsewhere.

But based on reading the docs, it seems like read_committed DOES acquire a shared lock during read, and as a result will wait for an exclusive lock held by another transaction (in this case the client app). But I don't want to go to read_uncommitted isolation level because I don't want dirty reads. Is there a less extreme solution? I need some middle ground where I can perform reads without any lock-waiting, and retrieve only committed data.

Is there such a goldilocks solution? Not too deadlock-y, not too dirty-read-y? If not in siolation level, maybe some modifier I can tack onto my SQL? Anything?

Answer 1

I assume you are talking jdbc isolation levels, and not db2. The difference between read_committed (cursor stability in db2) and repeatable_read (read stability) is how long the share locks are kept. repeatable_read keeps every lock that satisfied the predicates, read_committed on the other hand only keeps the lock until another row that matches the predicate is found.

Have you compared the plans? If the plans are different you may end up with different behaviour.

Are there any escalations occurring?

Have you tried CURRENTLY_COMMITTED (assuming you are on 9.7+)?

Pre currently_committed there where the following settings, DB2_SKIPINSERTED, DB2_EVALUNCOMMITTED and DB2_SKIPDELETED

Answer 2

The lowest isolation level that reads committed rows is read committed.

Usually, you process rows in a DB2 database like this:

1. Read database row with no read locks (ordinary SELECT with read committed).
2. Process data so you have a row with changed values.
3. Read database row again, with a read lock. (SELECT for UPDATE)
4. Check to see the database row in 1. matches the database row in 3.
5. If rows match, update database row.
6. If rows don't match, release update lock and go back to 2.