Link to home
Create AccountLog in
Avatar of FranklinRaj22
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 )
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

An even better question is what's the purpose of an UPDATE statement where values do not change?
Since there are two tables involved here, BAL and cl, should there be table aliases in play?  SET BAL.VALUE = cl.Value
Avatar of FranklinRaj22
FranklinRaj22

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 )
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.
ASKER CERTIFIED SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.