Lock timeout/Deadlocks on a update query that updates the same column value

FranklinRaj22
FranklinRaj22 used Ask the Experts™
on
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 )
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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

Author

Commented:
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 HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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.
Senior Oracle DBA
Commented:
The purpose of updating without changing a value is to get a row level lock.

It isn't causing a deadlock by itself.  However, if the application is poorly designed, it can cause a deadlock situation in combination with something else.

Typically, you wouldn't be using any type of an in list or subquery with this type of lock attempt.  Because if 2 sessions run the update at nearly the same time, the order of rows locked isn't guaranteed and that could cause a deadlock as well.
nociSoftware Engineer
Distinguished Expert 2018
Commented:
And it can be used to activate a trigger. which may update another column with a last_update date f.e.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial