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 )

Jim Horn

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

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 )

Jim Horn

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

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

noci

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial