Conversion Deadlock v9.1

[t***@momentum.co.za]

Hi

I'm experiencing conversion deadlocks in a highly concurrent java application which uses Hibernate with Cursor Stability isolation level.

One thread issues a "select * from myTable where pkey=? for update with cs" to acquire an update lock, followed by and "update myTable, set blah blah where pkey=?". The select for update is required both to acquire a lock and refresh the data since optimistic locking is used and multiple threads could both have read the same data.

Another thread uses exactly the same statements and occasionly I get a conversion deadlock (Thread 1 gets an NS->Update conversion lock and thread 2 an Update->Exclusive conversion lock).

All the reading I've done suggests that select for update issues an NS lock prior to the Update lock, so thread 2 can acquire an NS lock while thread 1 has an Update lock. If the X lock is now attempted by thread 1, the deadlock occurs.

How can I avoid this situation?
Is there any other way to get an update(or exclusive) lock without using "select for update" or changing my isolation level?

Regards
Terry

[j***@macehill.co.uk]

What happens if you do "Update mytable set pkey=pkey where pkey=?" and then do a select (doesn't really need the for update suffix in this case).

Also what happens if the 2nd statement was changed to a be "update mytable set <blah> where current of <cursor>"?

[t***@momentum.co.za]

Thanks for the response.

I think that solution would work because it creates an immediate exclusive lock.
However i tried this and it created some side effects and drastically reduced concurrency which made some web pages inaccessible.

IBM has suggested that I add "USE AND KEEP UPDATE LOCKS" to the select statement which appears to do the trick. The select now does not attempt an NS lock, but directly attempts the Update lock.

A possible side effect of this second solution is that not only predicate matches are locked if an index or table scan is used, so I need to be very careful where I use it.