1

I have some problem with the following statement, does anyone have any idea??

Any comments are appreciated!!

UPDATE tableTx 
SET tableTx.ResultCode = 0 
WHERE tableTx.id in (SELECT tableTxInfo.id 
                     FROM tableTxInfo 
                     WHERE tableTxInfo.messageId = 'john-to-bob' 
                     and tableTx.ResultCode != 1)

id of tableTx and tableTxInfo is the same (it's used as the INTEGER PRIMARY KEY) For a new transaction, a new record will be created for both tableTx and tableTxInfo with the same id.

I have around 40000 records with messageId = "john-to-bob".

When I tries to execute the above SQL statement from two different threads, one hangs for a quite long time and the other thread just get response:

Transaction detected a deadlock, transaction is rolled back.

I am not quite familiar with SQL and database concept. Is there any way to prevent from this kind of error?

my database is IBM soliddb

kai
  • 1,141
  • 3
  • 15
  • 25
  • have you tried this with a subset of the 40k? Might be that it is taking so long that the script thinks it has a dead lock going on. – PCoelho Feb 23 '13 at 09:15
  • 1
    Try setting a write lock on your table first. – Burhan Khalid Feb 23 '13 at 09:18
  • Thanks for the comments, PCoelho is right, the operation tooks so long so that another thread is timed out and get that deadlock error returned. The problem disappear after modifying the SQL statement~~ – kai Feb 23 '13 at 14:35

1 Answers1

2

You could try it like this:

UPDATE tableTx 
SET tableTx.ResultCode = 0 
WHERE tableTx.id in (SELECT tableTxInfo.id 
                     FROM tableTxInfo 
                     WHERE tableTxInfo.messageId = 'john-to-bob') 
      AND tableTx.ResultCode != 1;

This might prevent the cyclic dependency between the two queries.

Good luck!

Lodewijk Bogaards
  • 19,777
  • 3
  • 28
  • 52