--- In firebird-support@yahoogroups.com, Juan Pedro López Sáez
<jpls@a...> wrote:

> Alexander, thank you for the answer.
>
> The situation is the following:
>
> My PSQL statement (included in a SP) could be represented as two SQL
> statements:
>
> First statement: SELECT FROM TABLE1
> Second Statement: UPDATE TABLE2
>
> Let's immagine two transactions that call the SP.
>
> I must avoid the following execution schema in order to prevent
> inconsistent values in the field finally updated:
>
> Transaction1 performs SELECT FROM TABLE1
> Transaction2 performs SELECT FROM TABLE1
> Transaction1 performs UPDATE TABLE2
> Transaction2 performs UPDATE TABLE2
>
> The expected behaviour should be:
>
> Transaction1 performs SELECT FROM TABLE1
> Transaction1 performs UPDATE TABLE2
> Transaction2 performs SELECT FROM TABLE1
> Transaction2 performs UPDATE TABLE2
>
> To get it I'm thinking in adding a dummy update, to the same field
> updated in the UPDATE TABLE2 statement, in the beginning of the PSQL
> statement, just to assure the database will throw a lock conflict if

a

> transaction wants to execute the SP when there is another one in the
> process.
>
> Is threre any other possiblity to get the desired behaviour in my
> application?

Yes. If your transactions run in concurrency isolation level, any
attempt to update record which was changed since transaction's start
will cause lock conflict depending of lock resolution mode of this
transaction:

1. nowait - regardless of was changes made by another transaction
commited or not.
2. wait - attempt will wait finish of another transaction and raise
conflict in case of commit.

So, in concurrency messed data in table2 is impossible - one of
transactions will get exception. But your "right" scenario will be
successfull for transaction 2 only in case

Transaction 1 starts
Transaction1 performs SELECT FROM TABLE1
Transaction1 performs UPDATE TABLE2
Transaction 1 commits
Transaction 2 starts
Transaction2 performs SELECT FROM TABLE1
Transaction2 performs UPDATE TABLE2

In read_commited nowait successfull will be too:

Transaction 1 starts
Transaction 2 starts
Transaction1 performs SELECT FROM TABLE1
Transaction2 performs SELECT FROM TABLE1
Transaction1 performs UPDATE TABLE2
Transaction 1 commits
Transaction2 performs UPDATE TABLE2

and unsuccessfull

Transaction 1 starts
Transaction 2 starts
Transaction1 performs SELECT FROM TABLE1
Transaction2 performs SELECT FROM TABLE1
Transaction1 performs UPDATE TABLE2
Transaction2 performs UPDATE TABLE2

in nowait mode transaction2 will get confict on Update.

In read_commited rec_version wait it will wait for finish of
transaction1 and in case of commit will detect conflict, in case of
rollback perform update. Note conflict is detected not since
transaction start like in concurrency, but on conincidence of updates
itself.
In read_commited no_rec_version wait second update will be
successfull. In case of more than 2 transactions struggles for this
record no more than 2 of them can be successfull.
All this read_commited wait shamanism in FB seems illogical to me
and I'm trying to discuss this in 4 forums simultaneously. Last answer
about internals I got in firebird-devel is informative, I'm not ready
to answer there immediately, should think some time.

Best regards,
Alexander.