Subject Re: [IBO] Nested Transactions?
Author Geoff Worboys
> Just thinking more... I'm surprised that IB doesn't have this
> support. A solution for this would be either nested transactions or
> a stack of savepoints within the same transaction with the ability
> to rollback to the last savepoint.
> Is there any future development in this area? Are there any RDBMSs
> which support nested transactions?

Check out the Firebird website - there is a list of open features that
may tell you if this has been requested.

This is something I have thought about at various times and it would
be a convenient feature BUT it has little to no impact on the genuine
transaction processing. That is; everything within the big
transaction (including all little transactions) will be committed or
rolled back. So as far as the system is concerned there is only one
actual transaction - anything inside is just developer convenience and
should not be referred to as a transaction but simply a save point
within the transaction.

AFAICT IB/FB does have some level of internal transaction capability,
but it is not made available to the API. When you try to post a
record various things may happen inside the triggers - and they are
generally rolled if the post fails.

As a final comment I will say that most instances I have come across
can be dealt with reasonably easily at the client...

> The examples that could use this are endless. A Customer/Order Entry
> system, for example. A user enters a new customer, then begins to
> enter orders, adds an order and commits, then adds another order but
> screws-up and deletes the order and re-enters it... all the while
> having the ability to rollback the entire customer.

In this example the commit should happen after entering a new
customer, and again after each order. To enforce this in a obvious
way to the user I give one form for creating the customer and then a
separate form for creating ONE order. Before creating the order the
customer must be created, a full order must be created and committed
before proceeding to the next.

It is modular and works very well (IMHO).

Geoff Worboys
Telesis Computing