I think I've talked about this before. I see that the best generic interface
for savepoints is to make them look like normal sub-transactions, that are scoped:
1. The basic idea is that we have nested transactions, and starting a child is
defining a subunit that needs to succeed or be a no-op as a whole.
2. DBI is always in autocommit mode by default, because that treats each SQL
statement as an innermost nested transaction of its own. There should not be an
autocommit=0 in the interest of consistency.
3. A slightly higher level of abstraction would provide the greatest
user-friendliness, and I strongly prefer the idea of sub-transactions being tied
to a lexical scope or block, such as a try-block. So for example, entering a
sub-transaction block starts a child transaction, exiting one normally commits
that child, and exiting abnormally due to a thrown exception rolls back the
child. Making things scope-tied is the safest and easiest to use because users
don't have to explicitly call commit/rollback for every begin, similar to how
automatic memory management helps us not need to remember to do a 'free' for
each 'malloc' in spite of the many ways a code block might be exited. In this
situation, there would not be any explicit begin()/commit()/rollback() methods,
and also the SQL itself can't call those unpaired. As for implementation, well
I think there is a Perl module that implements sentinel objects or whatever
they're called, which could be looked at for ideas.
4. Less ideal for users, but perhaps closer to bare metal or what people are
used to, DBI can keep its existing start/begin()/commit()/rollback() methods,
and they just get reused for child transactions. There should be a transaction
nesting level counter which DBI exposes with a getter method. When a connection
starts, the level is 0. Starting a transaction increments this by 1, and ending
(commit or rollback) decrements it; decrementing it below zero is an error. The
start/begin() method starts a new child transaction, or a first transaction if
there are none, and commit()/rollback() ends the innermost transaction.
This all said, if you still want to have actual named savepoints, well David's
proposal sounds fairly decent.
-- Darren Duncan
David E. Wheeler wrote:
Tim et al.,
Anyone given any thought to an interface for savepoints? They're a part
of the SQL standard, and basically look like named subtransactions. The
SQL looks like this:
BEGIN;
INSERT INTO table1 VALUES (1);
SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (2);
ROLLBACK TO SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (3);
RELEASE SAVEPOINT my_savepoint;
COMMIT;
Compared to transactions, the statements look like this:
TRANSACTIONS | SAVEPOINTS
-------------------------------------
BEGIN; | SAVEPOINT :name;
COMMIT; | RELEASE :name;
ROLLBACK; | ROLLBACK TO :name;
Given these terms, I think that DBD::Pg takes the correct approach,
offering these functions:
pg_savepoint($name)
pg_release($name)
pg_rollback_to($name)
All you have to do is pass a name to them. I'd therefore propose that
the DBI adopt this API, offering these functions:
savepoint($name)
release($name)
rollback_to($name)
The would essentially work just like transactions in terms of error
handling and whatnot. The example might look like this:
$dbh−>{RaiseError} = 1;
$dbh->begin_work;
eval {
foo(...) # do lots of work here
$dbh->savepoint('point1');
eval {
bar(...) # including inserts
baz(...) # and updates
};
if ($@) {
warn "bar() and baz() failed because $@";
}
$dbh−>commit; # commit the changes if we get this far
};
if ($@) {
warn "Transaction aborted because $@";
# now rollback to undo the incomplete changes
# but do it in an eval{} as it may also fail
eval { $dbh−>rollback };
# add other application on−error−clean−up code here
}
If the transaction succeeds but the savepoint fails, the foo() code will
be committed, but not bar() and baz().
Thoughts?
Best,
David
