Re: undefined behaviour for sub-transactions?

Paul DuBois <paul@xxxxxxxxx> wrote:
> MySQL doesn't have sub-transactions (nested transactions). You can set
> savepoints within a transaction and roll back to a given savepoint and then
> continue the transaction from there.

Hopefully it will some day. :) I'm guessing other DBI drivers have
this behaviour too... when DBI talks to a driver, does it know if it
supports sub-transactions? For that matter, does it know if there is a
transaction already in progress?

I'm trying to support multiple database drivers at once. I have
methods that do small tasks (such as updating a table), and methods that
glue these small tasks together into big tasks (such as updating a table and
all of it's foreign keys). Whether it is a small task or a large task that
is being done, I would like the operation to be completely atomic.

I have each small task do it's own "begin_work/commit", and then
each large task wraps the whole thing in it's own "begin_work/commit". With
an engine that supports nested transactions, this works perfectly. With
MySQL, all of the SQL is still executed and comitted, but I get a warning on
the last commit, even it PrintWarn is set to 0.

I'd rather not have to investigate / guess what every single DBD
driver does and add change the behviour based on $dbh->{Driver}->{Name}.

Is there a way to ask DBI if it is already in a transaction? If it
knows this and I can get that information out of it, I could make this
package a lot more useable.

Thanks,
Tyler

.

Relevant Pages

  • Re: Autonomous Transactions
    ... Note that savepoints are a convenient way to place markers as to places ... in the transaction flow you may want to rollback, ... data, and if the inner most procedure issues a commit, it will affect ...
    (comp.databases.oracle.server)
  • Re: Transaction span stored procedures
    ... spanning a transaction over multiple stored procedures? ... > Savepoints" in SQL BOL. ... > transactions inside stored procedures. ...
    (microsoft.public.sqlserver.programming)
  • Re: Transaction span stored procedures
    ... See "Rollbacks in Stored Procedures and Triggers" and "Transaction ... Savepoints" in SQL BOL. ... It is @@trancount and you have to use savepoints to manage nested ...
    (microsoft.public.sqlserver.programming)
  • Re: "transactions" on a DataSet object
    ... So essentially you also need savepoints in your transaction? ... don't think that's possible to do within a dataset without a full deep copy. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: DBI v2 - The Plan and How You Can Help
    ... What I'd like *you* to do is make proposals for what a Perl 6 DBI API> should look like. ... you could probably easily support replaying the transaction in the ... Perl 6 will be able to serialise continuations and probably also coroutines, ... in a coroutine to automatically resume itself once the continuation is thaw'ed. ...
    (perl.dbi.users)