FranklinRaj22
asked on
Lock timeout/Deadlocks on a update query that updates the same column value
I am seeing a repeated deadlock scenario in an update query and that query seems to be updating the column with the same value . Loooks fishy , is this a reason for the deadlocks ?
UPDATE BAL SET VALUE = VALUE
WHERE ID IN (?) AND BAL_ID IN
( SELECT MIN(BAL_ID) FROM cl WHERE cl.ID=1 )
UPDATE BAL SET VALUE = VALUE
WHERE ID IN (?) AND BAL_ID IN
( SELECT MIN(BAL_ID) FROM cl WHERE cl.ID=1 )
ASKER
thats a question I have asked the vendor product team , no aliases , its typically trying to update the same column with the same value . But technically making updates to teh same column how much of an impact do they have on the deadlocks . Imagine below query is the right one
UPDATE BAL SET VALUE = VALUE *10
WHERE ID IN (?) AND BAL_ID IN
( SELECT MIN(BAL_ID) FROM cl WHERE cl.ID=1 )
UPDATE BAL SET VALUE = VALUE *10
WHERE ID IN (?) AND BAL_ID IN
( SELECT MIN(BAL_ID) FROM cl WHERE cl.ID=1 )
I would think that it would return an 'ambiguous column name' syntax error with the VALUE = VALUE, but setting that aside assuming the ? parameter returns only one BAL row, and cl is a smallish number of rows, I would expect this to execute quickly.
btw What exactly is the relationship between BAL and cl? WHERE .. BAL_ID IN
( SELECT MIN(BAL_ID) FROM cl looks pretty clunky.
btw What exactly is the relationship between BAL and cl? WHERE .. BAL_ID IN
( SELECT MIN(BAL_ID) FROM cl looks pretty clunky.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Since there are two tables involved here, BAL and cl, should there be table aliases in play? SET BAL.VALUE = cl.Value