Thank you, Alexander.

I will test this isolation level behaviours to decide what's the best
choice for my application.

Juan Pedro Lopez

> --- 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.
>
>
> Yahoo! Groups Sponsor
> ADVERTISEMENT
> click here
>
> To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>
>
>
> Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.

--
Juan Pedro López Sáez
ALTIRIA TIC, S.L.L.
www.altiria.com