Subject Re: [Firebird-Architect] SQL Update and Delete
Author Jim Starkey
Samofatov, Nickolay wrote:

>I cannot be so sure about that.
>Standard syntax for UPDATE and DELETE statement was designed to work
>correctly and without update conflicts with READ COMMITTED isolation
Nonsense. I designed it to work with consistent transactions, which
were the only one we supported. It works just as well with broken
transactions, i.e. READ COMMITTED, however.

>If record changes in between you read and update it you can re-read the
>record, re-evaluate boolean condition for this record and re-execute
>expressions in SET clause.
>This is the approach how pessimistic locking (WITH LOCK) works now (it
>only doesn't have SET clause).
This doesn't following any conceivable definition of transactions, but
it is need what it does.

>Normal Firebird updates and deletes inherited GDML design which doesn't
>follow this pattern and tends to raise unnecessary "update conflict"
>errors, but this is a bug which has to be fixed eventually. Now this is
>one of the major problems you have to address when migrating serious
>applications from Oracle to Firebird.
Could you explain this rather brash assertion? A transaction by most
definition requires repeatable reads. Attempting to modify a record
that you could not read is an error in almost everybody's book.

Things are different in transactions without consistency, however.
Nothing like adding up a column of numbers 10 times and getting 10
different numbers...

>In short, the MS extension for using joins in UPDATE and DELETE
>statement contradicts with proper MGA design.
>For Firebird it would be possible to implement it "properly" either via
>migrating to mandatory record locking via lock manager which sounds like
>a step backwards or adding join syntax limitations so those joins may be
>rewritten as a single boolean by the optimizer which sounds and looks
>really ugly.
If you don't care about the right answer, worrying about what is proper
doesn't even qualify as an academic exercise.