Hi everyone,

I am currently encountering concurrency issues with the following statement. I am using JDBC with autocommit on.

Update table1 t1
Set t1.column1 = 'something'
Where t1.column2 = (Select t2.column1 from table2 t2, table1 ttt1 where
ttt1.column1 is null and t2.column1 = ttt1.column2 fetch first 1 rows only) WITH CS;

Let me try to explain the logic:
1. The select statement retrieves 1 row that satisfies some criteria (in this case, just joining the 2 tables) and the table1.column1 is empty.
2. The update statement updates the table1.column1 to 'something'.

This statement works fine when there is only 1 user but the problem occurs when I test it with multiple threads. The problem is that I get back a whole lot of DeadLock exceptions. I don't quite understand this part. Doesn't the transaction lock up the rows until it completely finishes?

If I change WITH CS to WITH UR, then I don't get any DeadLocks, but some rows dont get updated because of the dirty reads.

Does anyone have a solution this ? ie. No DeadLock and no dirty reads...

You could try using RR or RS isolation level. Also, make sure you have the necessary indexes for your join, otherwise it could be doing a tablescan, whcih is more likely cause contention problems. Do a runststats on the tables and all indexes.

I think that the join in subquery is not neccesary.

Why not use simpler statement, like this...

Wouldn't RR or RS lead to more deadlocks? I have actually tested it and it resulted in more deadlocks then CS.

The sql I posted was just a sample to clarify the point.

I guess what I want to achieve is more of a QUEUE, hence its not what a relational database is best for....

It can lead to more deadlocks on some SQL, and less deadlocks on others. Using RR and RS would tend to single thread things more, but as you noted it could itself cause deadlocks, especially with different kinds of SQL running at the same time.

One of the most important things to improve concurrency is to make sure the SQL is not doing a table scan. If table is too small, you may have to set it to volatile to encourage index usuage.

That was my suspicion. If you have some real SQL that had a concurency problem, it would be easier to offer some advice once it became clear what you are trying to do. Usually, poorly designed databases and applications have more concurency problems that well designed ones.

Also, I am not sure about your statement:

"If I change WITH CS to WITH UR, then I don't get any DeadLocks, but some rows dont get updated because of the dirty reads."

Not sure how that could adversely affect the subselect unless you are deleting a lot of rows while the update is taking place.

Even if you used another database or keyed(or indexed) files,
you can design and write inefficient, and/or error prone programs.
You should make properly designed programs with understanding of the characteristics of the system you are using,
before complining the isuues/problems in your poorly designed program.

Back to your original SQL statement,
if there were some records of ttt1 satisfying the conditions, one of them must be t1.
It is not necessary to access table1 twice.