--- In firebird-support@yahoogroups.com, Vlad Orlovsky
<vlad.orlovsky@...> wrote:

>
> Hi Ivan,
>
> Just to clarify. All CRUD operations inside SP are executed in the

same transaction?

>
> What if I have:
>
> SELECT
> ID
> FROM SOME_TABLE WHERE .... INTO :CUSTOMER_ID
>
> IF (:CUSTOMER_ID IS NULL) THEN
> BEGIN
> INSERT INTO SOME_TABLE(...) VALUES(...);
> END
>
>
> How do I ensure that no other process inserts the data into

SOME_TABLE between SELECT & IF statements?

>
> In my case, it is very critical that INSERT only happens if SELECT

found no records...

I don't understand the difference here between what you are trying to
do from the stored procedure and if you executed it from the client
application.

The likelihood is reduced because the round trips between your
application and the database are minimising the delay, but the effect
is the same. Your design has not allowed for concurrency nor has it
allowed for transaction isolation.

Of concern, your select statement can *NOT* see:

1) *Any* other transaction which has already performed the insert but
is yet to commit.

2) In some common isolation modes, *Committed* transactions which have
already performed the insert and committed can *NOT* be seen because
they were *started* after your transaction was started.

So you have a choice here.

You can use a declared unique constraint on the field(s) of importance.

Alternatively, you can use some other transaction independent method
to make sure it is serialised, such as using a generator to flag when
it is safe to enter, or work something in your application logic to
ensure it can't happen.

Adam