Dennis Cote wrote:
[EMAIL PROTECTED] wrote:
It appears that my requirements are to be able to do the following:
BEGIN parent;
insert into t values ('a');
BEGIN child;
insert into t values ('b');
insert into t values ('c');
ROLLBACK child; // child aborts
insert into t values ('d');
COMMIT parent;
As a result of this sequence, the table should have two new rows
with values 'a' and 'd', but not 'b' and 'c'.
Can you explain why your application is rolling back the child transaction?
If the above is really how your application works (and I don't think it
is), then the exact same result can always be achieved with the simpler
sequence:
BEGIN;
insert into t values ('a');
insert into t values ('d');
COMMIT;
You don't need to bother inserting b and c if you are going to undo
those insertions with a static rollback.
I can give you the example of an application using sqlite as the on-disk
file format. As mentioned on the sqlite website [1], the traditional
File/Open operation does an sqlite3_open() and executes a BEGIN
TRANSACTION. File/Save does a COMMIT followed by another BEGIN
TRANSACTION. That would be the parent transaction.
Imagine now the application needs to execute a group of sql statements
that needs to be atomic. Some examples that come to my mind are
importing data, re-arranging existing data,... To guarantee the entire
operation is atomic, I want to group them in a child transaction. But
when this operation fails for some reason (because of invalid data, a
violated constraint,...), only the child transaction needs to rollback.
Because changes prior to this child transaction should remain intact and
the application can still continue because the database remains in a
clean state.
Without nested transactions, I have to make a compromise by:
(a) not using a parent transaction and loosing the File/Save feature.
(b) not using a child transaction and running into the risk of leaving
inconsistent data in the database after an error or having to throw away
all changes after an error.
[1] http://www.sqlite.org/whentouse.html
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
