Re: undefined behaviour for sub-transactions?

On Tue, Nov 29, 2005 at 10:50:01AM -0800, Tyler MacDonald wrote:
> Tim Bunce <Tim.Bunce@xxxxxxxxx> wrote:
> > I'll guess that what you're really after is to be able to call begin_work
> > again whilst an earlier begin_work is in effect and have the DBI keep a
> > counter of how deeply nested the begin_work calls are. Then commit would
> > decrement the counter and only commit at the outer most level.
> >
> > If you really want that then it's straightforward to implement via a
> > subclass.
>
> This has been done. I'm only using it in two other packages so far,
> but both those and it's own unit tests seem to work well.
>
> http://search.cpan.org/~CRAKRJACK/DBIx-Transaction-0.001/
>
> I've got some ideas for enhancements too, but those are a bit more
> vauge. One of them is that there's differences in transaction behaviour
> across drivers when a query within a transaction fails. eg; under
> PostgreSQL, doing a SELECT on a table that doesn't exist poisons the rest of
> the transaction, whereas under MySQL and SQLite2 the transaction is allowed
> to continue.

PostgreSQL is non-standard (and inconvenient) in this respect.

> There's gotta be some way to wrap this behaviour cleanly so that
> your application can expect the same behaviour regardless of the underlying
> database layer...

There isn't, as far as I know, except to accept the 'lowest common
denominator'. In this case that means forcing a rollback if any
statement fails.

> but this leads to another question :-)
>
> Are all database drivers expected to supply one method to execute a
> query? Eg; do "do", "execute", etc. all always funnel into one core method
> that returns success, error, or exception (if RaiseError) is turned on? Or
> if I wanted to create this functionality and expect it to work under
> multiple database drivers, should I override multiple methods?

execute() is sufficient if the driver doesn't also supply it's own do()
because DBI's default do() calls execute(). But some drivers do supply
their own do() method (for good reasons).

Tim.
.

Relevant Pages

  • Re: detecting the existance of a table [was: undefined behaviour for sub-transactions?]
    ... rolls-back just that statement and does not abort the whole transaction. ... If that's not true for PostgreSQL then that's certainly inconvenient. ... >> because DBI's default docalls execute(). ... But some drivers do supply ...
    (perl.dbi.users)
  • Re: [PATCH/RFC] I/O-check interface for drivers error handling
    ... > One issue with that is how to notify drivers that they need to make this call. ... > In may cases, DMA completion will be signalled by an interrupt, but if the ... > beginning of the transaction and the end of the transaction occur in ... its cookies in any context). ...
    (Linux-Kernel)
  • Re: speeding up ugen by an order of magnitude.
    ... On Wed, 7 Jul 2004, M. Warner Losh wrote: ... > usb drivers are very easy to write. ... I don't think this has to sacrifice generalness.. ... only 1 x 1KB transaction per mSec when USB2 is capable of delivering ...
    (freebsd-current)
  • Re: continuing after failed operation
    ... Is this true for other drivers, ... >> DBI documentation.) ... > finally you can call commit or rollback on the whole lot. ... >> within one transaction? ...
    (perl.dbi.users)
  • Re: getting rid of the "Issuing rollback()" warning
    ... >> All I'm proposing to change is to add a way for drivers to indicate ... >> if they are in a transaction or not, and then to use that to disable ... that I had to do a rollback() after a simple SELECT statement ...
    (perl.dbi.users)