--- In firebird-support@yahoogroups.com, Johannes Pretorius
<johannes@...> wrote:

>
> Good day
> 09--0-0-0-0
>
> This is related to the question I had. : "Rec Ver / Updates and

Deletes".

>
> Taking transaction into account. How does the stored procedures fit

into this ?

A call to a stored procedure (SP) is just like any other DML
(insert/update/delete). A SP but rather a sequence of statements and
control mechanisms that is executed as a single operation.

Unlike some other DBMS, a stored procedure has no control over the
starting or ending of a transaction.

> If we
> take the scenario as follows.
>
> Client A starts a transaction and does some updates and inserts and

then calls a stored proc and then commits.

> Now A gets a dead lock thanks to a other clients updates, but linked

to an update made by client a and not the stored procedure.

>
> DOES the changes that the stored procedure make (Lets say the stored

procedure inserted data into a table based on some rules)

> also roll back ? or is it outside of the transaction ? .. basically

is the stored procedure in its OWN transaction and not part of the
clients transaction ?

The SP runs in the same transactions your updates and inserts. If you
rollback that transaction, all the changes made by your SP are undone.

Using a SP allows you to implement a basic form of exception handling
within the SP, so you can catch and react to particular exceptions as
you see fit. For example, you may catch a lock conflict. Below is a
simple example of attempting to delete a record, but if you get a lock
conflict leave it untouched.

BEGIN
DELETE FROM SOMETABLE
WHERE ID = :ID

WHEN GDSCODE LOCK_CONFLICT DO
BEGIN
-- IGNORE
END
END

You would still receive any other exception. Any exception you do not
handle internally is passed back to the client application, and ALL
work done by the stored procedure is undone.

There are only three ways to end a transaction in Firebird.

1. Explicitly commit the transaction
2. Explicitly rollback the transaction
3. Break the connection (Firebird will rollback after it detects this
has occurred)

Firebird will never automatically start a transaction for you, you
must do that (although some tools may start one for you if you issue a
SQL command without an active transaction, the engine itself will
requires a transaction to do ANYTHING.)

Adam