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 )
FranklinRaj22Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
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
FranklinRaj22Author 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 DudeCommented:
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.
johnsoneSenior Oracle DBACommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nociSoftware EngineerCommented:
And it can be used to activate a trigger. which may update another column with a last_update date f.e.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.