undefined behaviour for sub-transactions?

I'm looking through the DBI documentation and I can't find anything about
sub transactions (eg; if you do a "begin_work" ... "commit/rollback" when
begin_work has already been called).

This seems to work under DBD::Pg. Under DBD::mysql, I'm not sure if the
begin_work is just being ignored, or if creating sub-transactions is the
cause of my woes. (see http://rt.cpan.org/NoAuth/Bug.html?id=15803).

I think some pod on the "Transactions" heading of the DBI manpage would be
useful, telling us what to expect (even if it is the unexpected) when we use
sub-transactions under DBI. If this is an engine-specific thing, could DBI
be made to be capable of figuring out whether a particular engine supports
sub-transactions? Then any sub-begin-works could just be ignored, and, say,
attempting to "commit" when a "rollback" has already been called could cause
a fatal error. Or is there already some way to take care of this? There's
very little about transactions in the DBI documentation.

Thanks,
Tyler

Re: undefined behaviour for sub-transactions?

On 11/14/05 21:52, "Tyler MacDonald" wrote:

> I'm looking through the DBI documentation and I can't find anything about
> sub transactions (eg; if you do a "begin_work" ... "commit/rollback" when
> begin_work has already been called).
>
> This seems to work under DBD::Pg. Under DBD::mysql, I'm not sure if the
> begin_work is just being ignored, or if creating sub-transactions is the
> cause of my woes. (see http://rt.cpan.org/NoAuth/Bug.html?id=15803).

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.

>
> I think some pod on the "Transactions" heading of the DBI manpage would be
> useful, telling us what to expect (even if it is the unexpected) when we use
> sub-transactions under DBI. If this is an engine-specific thing, could DBI
> be made to be capable of figuring out whether a particular engine supports
> sub-transactions? Then any sub-begin-works could just be ignored, and, say,
> attempting to "commit" when a "rollback" has already been called could cause
> a fatal error. Or is there already some way to take care of this? There's
> very little about transactions in the DBI documentation.
>
> Thanks,
> Tyler
>

Re: undefined behaviour for sub-transactions?

Paul DuBois 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

Re: undefined behaviour for sub-transactions?

On 11/15/05, Tyler MacDonald wrote:

> But it's licensed under the GPL, so if I wrote my package using i=
t,
> I would be encumbered by the GPL by proxy. :-/

You're most definitely using a real lot of modules, which are have
perl's dual license, one of them being the GPL.

Besides, open source authors typically tend to be open to questions
like "would it be possible to release the module under Perl's default
license instead"? Just try.

Jochen

--
Often it does seem a pity that Noah and his party did not miss the
boat. (Mark Twain)

Re: [GENERAL] undefined behaviour for sub-transactions?

On 11/30/05, Tyler MacDonald wrote:
> Andrew Sullivan wrote:
> > The inconvenience I'll grant, but the non-standard claim I think
> > needs some justification. When the database encounters an error in a
> > transaction, it is supposed to report an error. An error in a
> > transaction causes the whole transaction to fail: that's what the
> > atomicity rule of ACID means, I think. I actually am sort of
> > unconvinced that SQLite's transactions are real ones -- I just did
> > some playing around with it, and it seems that any error allows you
> > to commit anyway. Certainly, MySQL's support of transactions is
> > occasionally pretty dodgy, unless you use the strict mode.
>
> Either way the end result is that some database drivers poison a
> transaction if there's any error, others are selective about which errors
> are fatal and which are not, and still others just don't care at all.
>

that is a mis-conception... a transaction *must* be atomic (all or nothing)=
....
the reason some databases act that bad is because they don't support
savepoints, and because postgres does it doesn't need that
awfulness...

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: [GENERAL] undefined behaviour for sub-transactions?

Jaime Casanova wrote:
> > Either way the end result is that some database drivers poison a
> > transaction if there's any error, others are selective about which errors
> > are fatal and which are not, and still others just don't care at all.
> that is a mis-conception... a transaction *must* be atomic (all or nothing)...
> the reason some databases act that bad is because they don't support
> savepoints, and because postgres does it doesn't need that
> awfulness...

OK, maybe I should have s/poison/behave properly with/. :-)

- Tyler

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: [GENERAL] undefined behaviour for sub-transactions?

On Thu, Dec 01, 2005 at 01:04:52PM -0500, Greg Stark wrote:
> Where is Postgres at with psql using savepoints implicitly to wrap every
> client command btw? My single biggest pet peeve with Postgres is that setting
> autocommit off in psql is basically unusable because any typo forces you to
> start your transaction all over again.

Are you looking for 8.1's ON_ERROR_ROLLBACK?

test=> \set ON_ERROR_ROLLBACK interactive
test=> begin;
BEGIN
test=> create table foo (x integer);
CREATE TABLE
test=> roeiuqrepuqw;
ERROR: syntax error at or near "roeiuqrepuqw" at character 1
LINE 1: roeiuqrepuqw;
^
test=> insert into foo values (123);
INSERT 0 1
test=> commit;
COMMIT
test=> select * from foo;
x
-----
123
(1 row)

--
Michael Fuhr

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org