DB2 deadlocks - simple scenario 2

[maxtardiveau]

We seem to get an awful lot of deadlocks using DB2 7.2 on AIX or Win2K. Here's a scenario to reproduce a simple deadlock. First create a table :

create table t1 (a int not null primary key)
insert into t1 (a) values (1)
insert into t1 (a) values (2)
insert into t1 (a) values (3)
commit

Now open two clients (make sure autocommit is turned off) :


Client 1

delete from t1 where a = 3

Client 2

delete from t1 where a = 2

-- this blocks

Client 1

delete from t1 where a = 1

-- deadlock


I'm very afraid that this is in fact normal and that's just the way DB2 works. Maybe someone can tell me otherwise ? It would be a great relief.

I don't understand why DB2 wants to acquire a lock on row n+1 when it deletes row n.

Any insights greatly appreciated.

Thanks,

-- Max

 

[Guest_imported]

It looks like you are using repeatable read as an isolation level, or you are not using row locking... I find the locking mechanism in DB2 a bit of a nightmare myself.

Basically, access things in the same order and use a less harsh isolation level

Then again, I did these things on a more complicated problem and I can't work out why I'm still getting a deadlock at the moment...!

(Sorry if you are doing this already, it just looks like that!)

 

[legacy370]

Even with page-level locking, if you explicitly commit the update, DB2 should release the locks.

Also, as implied in the first response, it might help to specify "cursor stability" rather than "repeatable read" as your isolation level. This is something the DBA would most likely declare.

 

[maxtardiveau]

I finally stumbled across the DB2_RR_TO_RS registry setting. We turned it on and voila ! Almost all our deadlocks are gone. Oy ve.

-- Max

 

[blom0344]

Well, these are the 4 levels of isolation:

RS (Read Stability)
RR (Repeatable Read)
CS (Cursor stability)
UR (Uncommited read)

The whole business of concurrency is nightmarishly complex with DB2 compared with , for instance ORACLE, indeed.

If anyone wants my DBA notes (IBM) on this, let me know.


CS is recommended for most applications, with UR you can run into data integrity risks. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com