3

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?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Entropy
  • 1,219
  • 6
  • 21
  • 45
  • The point of the lock is to prevent something else from changing the row, right? Not so that you can update it later? Or would it be possible to allow other processes to update the row, your process just has to handle that afterwards? Can you call the webservice "locally"? I know db2 supports sharing locks for the same session, but this probably isn't happening if you route off-box first. What's the lock level of the other webservice? – Clockwork-Muse Jun 08 '14 at 22:31
  • @Clockwork-Muse - No, the client is indeed locking in preparation for update. The webservice implements a custom workflow notification scheme which must query the related record to build the notification (although it is reading different fields than are being updated, that doesn't really change anything). The web service and client currently run at read committed. I fear that the easiest fix (supported by our DB lead) is to change to read uncommitted for the service, but I am trying to find an alternative b/c I hate that answer. – Entropy Jun 09 '14 at 01:04
  • Do you care if anybody else updates the row in the meantime? Are the values such that you could update them with the difference afterwards, regardless of whatever other manipulations happened? Or just not care if somebody else updated the records in the meantime, and overwrite changes? Are you sure you can't run this on the same box as the service, possibly getting it to be part of the same session? – Clockwork-Muse Jun 09 '14 at 04:10
  • @Clockwork-Muse Same box, though as a web service, that won't always be true. Different connection pool. I want the web service to NOT seek a shared lock, and return the COMMITTED data when it queries. My current understanding is that the former can only happen in read uncommitted, which brings dirty reads into the fold. The web service is pure read only. But because read committed seeks shared locks, it waits for the exclusive lock to be released...which it won't be since the client has it and is waiting for the web service. I don't want uncommitted data, which is why I don't like UR. – Entropy Jun 09 '14 at 12:06

2 Answers2

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

Lennart - Slava Ukraini
  • 6,936
  • 1
  • 20
  • 32
  • No, this deadlock isn't escalation based, though we've had those in the past. I've never heard of CURRENTLY_COMMITTED. I will go read about it. – Entropy Jun 09 '14 at 12:09
  • I read up on this, and it is EXACTLY what we want. Cur_commit is the goldilocks zone of not deadlocking and not dirty reading. Unfortunately, we are on v9.5, but we already had a conversion effort underway to roll out v10.5, so we only have to wait a short while. Thanks. – Entropy Jun 09 '14 at 14:05
0

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.
Gilbert Le Blanc
  • 50,182
  • 6
  • 67
  • 111
  • Except, I think the OP wants to prevent any changes to the row while the processing is taking place. This would allow for reporting, but wouldn't prevent changes. – Clockwork-Muse Jun 08 '14 at 22:26
  • @GilbertLeBlanc I think you misunderstood the question slightly. The problem is that a read operation at read committed DOES lock the record, albeit briefly and non-exclusively. But because it wants to acquire a lock, if a different transaction has the row locked, the read will WAIT instead of finishing. This isn't a big deal normally, but in scenarios such as described in my first paragraph, this generates deadlocks b/c the service and client are on different connections, even though they are working together. – Entropy Jun 09 '14 at 01:00