Subject Re: lock conflict on no wait transaction - help please
Author jbeh2000
Hi Set

Thank you for your comment and advice. I wasn't aware of the IN /
EXISTS distinction and I am still not sure that I fully understand it
but I am grateful to you for pointing it out.. Nevertheless the
problem for both my original and your improvement is that neither of
them work at all.

They both throw the same error message

lock conflict on no wait transaction

As I understand it the update all occurs within the context of a
single transaction and I therefore don't understand what the lock
conflict is.

--- In ib-support@y..., Svein Erling Tysvær
<svein.erling.tysvaer@k...> wrote:
> Hi John.
> I'm not certain what your problem is and don't know whether my
> will help or not - but are you aware that your subselect is
executed once
> for every row in your table? Changing to
> update JOBS set JOB_IS_ACTIVE = 'N'
> where exists(select 1 from SP13
> and SP13.HAS_WIP='N'
> and SP13.MTHS_AGO_LAST_TRAN is null
> and SP13.MTHS_AGO_OPENED > 48)
> could improve execution speed drastically (in general, it is a bad
idea to
> use IN followed by a subselect - the more rows the subselect
returns, the
> slower the execution).
> Whether changing your update statement to what I suggest will help
you get
> rid of - or change - your error, I simply do not know. Try it and
> back to us the results.
> Set