Re: Savepoint support proposal
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > I'd be interested to see how varied the syntax is between databases. I honestly don't know, but I am pretty sure most are standard, with some (DB2?) having extensions, but still supporting the same syntax. > I'd also be interested to see a proposal for an API along with some > real-world examples. No problem, I can do that. > My position is that I'm open to being persuaded, but you'll have > to put in the leg work. The best place for that at this stage is > dbi-users as I'd like you to be able to show wider support for the > proposals from the user community. That could be tough - savepoints were just recently added to PostgreSQL, and I don't know enough about the typical users of the other RDBMSs. However, all I would like at a minimum is the acceptance of $dbh->savepoint and friends as a future addition, so that the next version of DBD::Pg can use them instead of $dbh->pg_savepoint. It still seems like an extension of the begin() and rollback() methods to me. I see begin() and friends as being useful as methods because: - - It standardizes the call across different databases, avoiding any hard-coding of SQL. - - It forces the user to go through the interface, allowing easier tracking of the transaction state, and allowing better errors/warning, such as when trying to use begin() on a system that does not support it, or if not currently inside of a transaction. Savepoints would have the same advantages as above, plus the added perk of being able to push the list of savepoints into the database handle. It also may have implications for the disconnect() method. It's late. I will try and write up an API and a dbi-users proposal soon. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200503092341 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCL9CavJuQZxSWSsgRAgCRAJ0fDia03V56yXNR+yMTvaOBolKl4QCggxyy LFvMNCPTAYzWR+9WN4+gekI= =tQpc -END PGP SIGNATURE-
Re: [dbi] Re: Savepoint support proposal
On Wed, 09 Mar 2005 15:34:24 -0800, Dean Arnold <[EMAIL PROTECTED]> wrote: > Dan Scott wrote: > > On Wed, 9 Mar 2005 13:29:40 -0800, Darren Duncan > > <[EMAIL PROTECTED]> wrote: > > > >>At 8:55 AM + 3/9/05, Martin J. Evans wrote: > >> > >>>Just a small point. > >>>I could be wrong (I cannot look it up right now) but I think in ODBC > >>>SQLEndTran > >>>can issue a rollback or commit on ALL connections. > > > > Can someone provide a scenario where you would want to issue a global > > rollback or commit? > > > > Perhaps (probably) I'm suffering from a limited imagination here, but > > it seems like you would want to rollback or commit on a connection by > > connection basis. > 2PC. But DBI doesn't support that (yet). There's a bit more to 2PC than just that, unfortunately. In particular, the participant transactions have to be told by the coordinate that a commit is imminent, so they can indicate whether they can commit. (It's easy if they can't; they roll back and say "can't commit".) Assuming they can commit, they log a record indicating that they're in a state where they can commit or rollback - and the transaction will not then be rolled back by accident until the coordinator has issued a decision. When the coordinator records its decision, it broadcasts that to the participants, who then act accordingly. If a participant crashes between the time when it indicates that it can commit and it gets a decision from the coordinator, it contacts the coordinator after recovering (and reclaiming all the locks it held) and decides whether to commit or rollback. There are some wrinkles here (presumed commit, presumed abort, etc) and complications (heuristic rollback when a commit should have occurred), but the key point which a naive implementation based an raw non-2PC transaction support is the recovery in crash scenarios - which is critical. -- Jonathan Leffler <[EMAIL PROTECTED]> #include Guardian of DBD::Informix - v2003.04 - http://dbi.perl.org "I don't suffer from insanity - I enjoy every minute of it."
Re: [dbi] Re: Savepoint support proposal
On Wed, 09 Mar 2005 08:55:21 - (GMT), Martin J. Evans
<[EMAIL PROTECTED]> wrote:
> Just a small point.
>
> I could be wrong (I cannot look it up right now) but I think in ODBC
> SQLEndTran
> can issue a rollback or commit on ALL connections. Effectively, it issues the
> rollback/commit on each connection in the environment.
> That would be a lot harder to do with $dbh->do since you'd have to do it once
> per connection.
That would have to be issued on a driver handle, wouldn't it? And the
driver would have to know about all its children...
$dbh->{Driver}->rollback_all
$dbh->{Driver}->commit_all
--
Jonathan Leffler <[EMAIL PROTECTED]> #include
Guardian of DBD::Informix - v2003.04 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."
Re: Savepoint support proposal
On Wed, 9 Mar 2005 09:47:20 +0100, H.Merijn Brand <[EMAIL PROTECTED]> wrote: > On Tue, 8 Mar 2005 12:12:23 -0800, Jonathan Leffler > <[EMAIL PROTECTED]> wrote: > > On Tue, 8 Mar 2005 11:01:48 +, Tim Bunce <[EMAIL PROTECTED]> wrote: > > > On Mon, Mar 07, 2005 at 11:47:32PM -, Greg Sabino Mullane wrote: > > > > > While I don't have any particular objections, doesn't > > > > > Pg (and most other DBMSs supporting savepoints) have > > > > > SQL syntax to implement them ? If so, isn't just > > > > > > > > > > $dbh->do() > > > > > > > > > > sufficient ? > > > > > > > > Sure, but they also have syntax to implement BEGIN and ROLLBACK. > > > > It isn't wholly clear why we have $dbh->commit an $dbh->rollback; both > > could be simulated with $dbh->do on most databases. However, there is > > some minor performance advantage on some systems. For example, IBM > > Informix Dynamic Server (IDS) recognizes those statements in ESQL/C > > and the SQLI protocol used to transfer commands between client code > > (such as Perl + DBI) and the server sends a two byte command for > > ROLLBACK instead of sending the string. Further, the two byte command > > does not have to be parsed and executed - so there is a small, but > > barely measurable, performance benefit to using the $dbh->rollback > > statement. > > Uhh, there is much more difference! > I can workaround a lot of database problems in the commit/rollback calls in my > E/SQL code that I could not do unless I would be analyzing every do call I already got bitten by autocommit (badly; I'm keeping quiet here) - I already *have* to do a load of analysis and other futzing around with every statement. Besides, I can't stop userrs from using $dbh->do() to execute BEGIN WORK, COMMIT WORK or ROLLBACK WORK. Or from preparing and executing them. -- Jonathan Leffler <[EMAIL PROTECTED]> #include Guardian of DBD::Informix - v2003.04 - http://dbi.perl.org "I don't suffer from insanity - I enjoy every minute of it."
Re: [dbi] Re: Savepoint support proposal
Dan Scott wrote: On Wed, 9 Mar 2005 13:29:40 -0800, Darren Duncan <[EMAIL PROTECTED]> wrote: At 8:55 AM + 3/9/05, Martin J. Evans wrote: Just a small point. I could be wrong (I cannot look it up right now) but I think in ODBC SQLEndTran can issue a rollback or commit on ALL connections. Effectively, it issues the rollback/commit on each connection in the environment. That would be a lot harder to do with $dbh->do since you'd have to do it once per connection. If you wanted a global action in DBI, then reasonable syntax could be to issue the rollback/commit on the DBI class name rather than on a connection handle object. Either that, or restructure DBI so that the first thing a user does is create an "environment handle" object, off of which they call connect(), which incidentally saves us having to use any package globals. -- Darren Duncan Can someone provide a scenario where you would want to issue a global rollback or commit? Perhaps (probably) I'm suffering from a limited imagination here, but it seems like you would want to rollback or commit on a connection by connection basis. Dan 2PC. But DBI doesn't support that (yet). However, some of these arguments are starting to bring me around to the original notion. If DBI ever gets a full 2PC capability, such capability may be desirable (or at least, the creation of "commit groups", as an earlier poster intimated.) Dean Arnold Presicient Corp.
Re: [dbi] Re: Savepoint support proposal
On Wed, 9 Mar 2005 13:29:40 -0800, Darren Duncan <[EMAIL PROTECTED]> wrote: > At 8:55 AM + 3/9/05, Martin J. Evans wrote: > >Just a small point. > >I could be wrong (I cannot look it up right now) but I think in ODBC > >SQLEndTran > >can issue a rollback or commit on ALL connections. Effectively, it issues the > >rollback/commit on each connection in the environment. > >That would be a lot harder to do with $dbh->do since you'd have to do it once > >per connection. > > If you wanted a global action in DBI, then reasonable syntax could be > to issue the rollback/commit on the DBI class name rather than on a > connection handle object. Either that, or restructure DBI so that > the first thing a user does is create an "environment handle" object, > off of which they call connect(), which incidentally saves us having > to use any package globals. -- Darren Duncan Can someone provide a scenario where you would want to issue a global rollback or commit? Perhaps (probably) I'm suffering from a limited imagination here, but it seems like you would want to rollback or commit on a connection by connection basis. Dan
RE: [dbi] Re: Savepoint support proposal
At 8:55 AM + 3/9/05, Martin J. Evans wrote: Just a small point. I could be wrong (I cannot look it up right now) but I think in ODBC SQLEndTran can issue a rollback or commit on ALL connections. Effectively, it issues the rollback/commit on each connection in the environment. That would be a lot harder to do with $dbh->do since you'd have to do it once per connection. If you wanted a global action in DBI, then reasonable syntax could be to issue the rollback/commit on the DBI class name rather than on a connection handle object. Either that, or restructure DBI so that the first thing a user does is create an "environment handle" object, off of which they call connect(), which incidentally saves us having to use any package globals. -- Darren Duncan
RE: [dbi] Re: Savepoint support proposal
On 08-Mar-2005 Jonathan Leffler wrote: > I'm keeping this on dbi-dev pro tem. Greg - I suggest you ask the > question on dbi-users. > > > On Tue, 8 Mar 2005 11:01:48 +, Tim Bunce <[EMAIL PROTECTED]> wrote: >> On Mon, Mar 07, 2005 at 11:47:32PM -, Greg Sabino Mullane wrote: >> > > While I don't have any particular objections, doesn't >> > > Pg (and most other DBMSs supporting savepoints) have >> > > SQL syntax to implement them ? If so, isn't just >> > > >> > > $dbh->do() >> > > >> > > sufficient ? >> > >> > Sure, but they also have syntax to implement BEGIN and ROLLBACK. > > It isn't wholly clear why we have $dbh->commit an $dbh->rollback; both > could be simulated with $dbh->do on most databases. However, there is > some minor performance advantage on some systems. For example, IBM > Informix Dynamic Server (IDS) recognizes those statements in ESQL/C > and the SQLI protocol used to transfer commands between client code > (such as Perl + DBI) and the server sends a two byte command for > ROLLBACK instead of sending the string. Further, the two byte command > does not have to be parsed and executed - so there is a small, but > barely measurable, performance benefit to using the $dbh->rollback > statement. Just a small point. I could be wrong (I cannot look it up right now) but I think in ODBC SQLEndTran can issue a rollback or commit on ALL connections. Effectively, it issues the rollback/commit on each connection in the environment. That would be a lot harder to do with $dbh->do since you'd have to do it once per connection. Martin -- Martin J. Evans Easysoft Ltd, UK Development
Re: Savepoint support proposal
On Tue, 8 Mar 2005 12:12:23 -0800, Jonathan Leffler <[EMAIL PROTECTED]> wrote: > I'm keeping this on dbi-dev pro tem. Greg - I suggest you ask the > question on dbi-users. > > > On Tue, 8 Mar 2005 11:01:48 +, Tim Bunce <[EMAIL PROTECTED]> wrote: > > On Mon, Mar 07, 2005 at 11:47:32PM -, Greg Sabino Mullane wrote: > > > > While I don't have any particular objections, doesn't > > > > Pg (and most other DBMSs supporting savepoints) have > > > > SQL syntax to implement them ? If so, isn't just > > > > > > > > $dbh->do() > > > > > > > > sufficient ? > > > > > > Sure, but they also have syntax to implement BEGIN and ROLLBACK. > > It isn't wholly clear why we have $dbh->commit an $dbh->rollback; both > could be simulated with $dbh->do on most databases. However, there is > some minor performance advantage on some systems. For example, IBM > Informix Dynamic Server (IDS) recognizes those statements in ESQL/C > and the SQLI protocol used to transfer commands between client code > (such as Perl + DBI) and the server sends a two byte command for > ROLLBACK instead of sending the string. Further, the two byte command > does not have to be parsed and executed - so there is a small, but > barely measurable, performance benefit to using the $dbh->rollback > statement. Uhh, there is much more difference! I can workaround a lot of database problems in the commit/rollback calls in my E/SQL code that I could not do unless I would be analyzing every do call > > > I see the savepoints as an extension of the same sort of > > > transaction processing the DBI dos there. Certainly, drivers are > > > welcome to implementit any way they want (including allowing > > > savepoints via "do") but the advantages include being able to > > > track the savepoints closely, even allowing things like returning > > > an array of the current savepoints to the application. It also > > > abstracts the savepoint manipulation away from knowing the exact > > > SQL for your particular DBMS, one of the goals of DBI after all. :) > > > > I'd be interested to see how varied the syntax is between databases. -- H.Merijn BrandAmsterdam Perl Mongers (http://amsterdam.pm.org/) using Perl 5.6.2, 5.8.0, 5.8.3, & 5.9.2 on HP-UX 10.20, 11.00 & 11.11, AIX 4.3, SuSE 9.0 pro 2.4.21 & Win2k. http://www.cmve.net/~merijn Smoking perl: smokers@perl.org, perl QA: http://qa.perl.org reports to: [EMAIL PROTECTED],perl-qa@perl.org
