unordained wrote:

>Ann, thanks for taking an interest.
>
>The semantics I would expect from nested transactions are as follows:
>
>(At the global database level)
>- A transaction is truly committed if it has committed and all parent transactions have committed.
>- A transaction is truly rolled back if it has rolled back or any of its parent transactions have
>rolled back.
>
>(If parent transactions are still active, then sub-transactions may have some of these properties,
>but aren't finalized.)
>
>

...snip...

>If I'm wrong about the linearity issue, then ... many many apologies?
>
>-Philip
>
>

Hi,

My English is very poor to write descriptive text, so try hard to
understand what I meant to say :-)

I don't usually post on this list, but I have sometimes wished nested
transactions.

Why ?

All my systems use OOD/OOP, So I have a bunch o Business Objects, that
maps to relational objects, and has methods for get/set values
(select/insert/update/delete) on database tables, and some business
process, like subtract from stock, etc.

I have an object called boStockMovement, that add a record on
StockMovement Table, subtract/add the amount movemented on table
Products.PhysicalQty, and a lot more...
the method will be something similar to this:

begin
Start Transaction
try
insert in Table Movement
update table Product
etc.
commit
except
rollback
end
end

Then I have another object boInvoice that maps to Invoice Table

When a Sales Order become an Invoice, I should movement the Stock (to
reduce the physical quantity)

The Method to transform the Sales Order in a Invoice is like this:

begin
StartTransaction
try
insert Invoice from Sales Order
insert Bill (the Customer must pay)
Movement the Stock (using boStockMoviment)
Update Sales Order item to reflect the products sent to the costumer
Commit
except
Rollback
end
end

The Method for StockMoviment could be used alone, or inside other
methods, so this method will try to create a transaction inside a
transaction, what is not allowed.

The solution we made for this situation is:
We have a StartTransaction, Commit, Rollback Methods on your boObject
(the base class that all business objects are derived).
The StartTransaction Method has a Stack of Transactions, the transaction
is put on the stack and if the stack was empty then create a new
transaction,
The Commit Method commits the transaction, and remove that from stack,
and if that is the last one on the stack, then perform a "real commit"
The Rollback Method, removes all transactions from the stack, and
performs a real rollback.

The Commit are just made when the last "virtual transaction" performs a
commit
The Rollback, rollbacks all transactions, doesn't mater in what level
the situation that needs a Rollback was

an example:
boInvoice.StartTransaction (performs a real start transaction)
try
boInvoice.Insert;
boInvoice.AddBill;
for each Invoice Item do
boStockMoviment.Movement(boInvoiceItem.ProductID,
boInvoiceItemQuantity)
(this occurs insed boStockMoviment.Movement)
boStockMoviment.StartTransaction (there was another transaction
on the stack, so do nothing)
try
insert in Table Movement
update table Product
etc.
boStockMoviment.Commit (there are another transaction on the
stack, so just remove it from the stack)
except
rollback (if this occur, generated an exception that will be
catch on the upper level, and remove the transaction from stack)
end
end
except
rollback (if some exception occurs, this will be the last transaction
on the stack, so performs a real rollback)
end

So I just perform Commit/Rollback on the last transaction on the stack
If some rollback on the sub-transactions occurs, an exception will be
generated, and catched by the upper level, that will peform another
rollback, and so on until it reachs the last transaction on the stack
that will perform real rollback

The commit on the last transaction commits everything, and a rollback on
any level rollbacks everything.

All are running on the same transaction context, so evething is visible
accorss sub-transactions, no dead-lock... It's not possible to just
rollback a portion, if something goes wrong, everything should be redone...

It's a simple approach, but solves my problem...

Hope I could explain how I done it...

see you !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br