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?

Thank you.

Juan Pedro Lopez

> --- In firebird-support@yahoogroups.com, Juan Pedro López Sáez
> <jpls@a...> wrote:
> > Hello all,
> >
> > Is this SQL statement atomically executed?
> >
> > IF (EXISTS (SELECT C_STATE
> > FROM SETUP_PUSH_PROMOTION
> > WHERE ((K_PROMOTION =:PROMOTION)
> > AND (C_STATE = 'PENDIENTE'))
> > )
> > )
> > THEN UPDATE PROMOTIONS SET C_STATE = 'ACTIVO'
> > WHERE K_PROMOTION = :PROMOTION;
> > ELSE UPDATE PROMOTIONS SET C_STATE = 'INACTIVO'
> > WHERE K_PROMOTION = :PROMOTION;
> >
> > It will be part of a Stored Procedure, but I suppose this has
> nothing to
> > do with atomic execution.
> >
> > Is there any rule to know whether a SQL statement is atomic or not?
> >
>
> Juan, firstly this is not SQL statement but PSQL statement
> constructed from three SQL statements - Select and two Updates.
> Secondly, what do you mean "atomic"? SQL statements are atomic in the
> sense they are entirely successfull or entirely not. If condition of
> update statement cause update of 2 records and updating one server
> will encounter exception, no one record will be updated. In this sense
> entirely SP execution is atomic too since call is SQL statement - in
> case of any exception all changes made by SP will be undone (if there
> is not explicit exceptions handling code within this SP). If you mean
> - will result of Select is still valid when you perform Update - no,
> data can be changed between this statements execution.
>
> 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