Subject Re: master updation using trigger
Author Adam
--- In, Milan Babuskov <milanb@...>
> Adam wrote:
> > If this occurs, you have two
> > transactions trying to update the *same* record within product, which
> > if the second attempts it from a transaction started before the first
> > commits would cause a lock conflict.
> It causes deadlock. My applications alert users about this, rollback
> their transaction(s), and they can click on "Save" button again when
> "coast is clear".
> I still fail to see why would that be unsafe: AFAIU the data in
> remains consistent?

Yes it remains consistent because the second insert will be undone
upon receiving the lock conflict/deadlock. Is it safe is probably a
bad way to word the question (but I did not ask the question). It is
indeed safe in the sense that your data will be consistent at all
times. It is not safe in the sense that your inserts become
serialised. Certainly you must be careful if you are using a WAIT
transaction, as the exception your application is waiting to handle
may take a long time to arrive. It also requires your application to
have some sort of retry logic. The method Alan suggested allows both
users to click save at the same time, and both will succeed.