Re: Savepoint support proposal
FTR: ADO supports nested transactions: http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthbegintrans.asp?frame=no The level of nesting may be limited, e.g. Access and Foxpro support 5 levels, Oracle and SQL Server only 1. With some bookkeeping, it should be possible to emulate savepoints on top of nested transactions. Of course, it is a bit weird if the database supports savepoints, ADO maps this to nested transactions and DBD::ADO maps it back to savepoints - that's layering. Steffen
Re: Savepoint support proposal
On Wed, 9 Mar 2005 19:34:51 -0800, Jonathan Leffler [EMAIL PROTECTED] wrote: 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. there's an idea -- restrict what is allowed in $dbh-do under some kind of strict mode. Basically a non-starter due to the obvious slight performance penalty, but it might allow weak enforcement of a site-wide database policy. This is politics not engineering however, an obvious better solution is not to give to the keys to unlicensed drivers (please forgive the pun)
Re: Savepoint support proposal
On Thu, Mar 10, 2005 at 04:45:06AM -, Greg Sabino Mullane wrote: 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's far better for an application to use pg_savepoint() than for the DBI to prematurely standardize on an API that has to change later. In general I don't add 'esoteric' features to the DBI API until at least two or three separate drivers have added driver-private APIs for the same feature. That way we have a range of practical experience to drawn on. It's late. I will try and write up an API and a dbi-users proposal soon. Feel free to bounce it off me before sending to the list so save one round of debate on the first version. Tim.
Re: Savepoint support proposal
On Thu, Mar 10, 2005 at 08:54:54AM -, Martin J. Evans wrote: although ODBC does not define what happens if one of the commits on a connection fails (this alone probably makes commiting on an environment unpopular if used at all). Exactly. This (commit_all etc) won't make it into the DBI for that reason. Drivers are, of course, fee to add whatever they want. Tim.
Re: Savepoint support proposal
On 10-Mar-2005 Tim Bunce wrote: On Thu, Mar 10, 2005 at 08:54:54AM -, Martin J. Evans wrote: although ODBC does not define what happens if one of the commits on a connection fails (this alone probably makes commiting on an environment unpopular if used at all). Exactly. This (commit_all etc) won't make it into the DBI for that reason. Drivers are, of course, fee to add whatever they want. Tim. Just for clarification. I was not putting this up as a proposal, it was just a comment on things you can't do in SQL in one go. If I were looking into this I would be looking at XA not this half-hearted attempt in ODBC. Martin -- Martin J. Evans Easysoft Ltd, UK Development
Re: Savepoint support proposal
For MySQL, the savepoint info is: - Available as of MySQL 4.1.1 for InnoDB transactions. - Set a savepoint with: SAVEPOINT savepoint_name - Rollback syntax accepts optional TO SAVEPOINT clause: ROLLBACK [TO SAVEPOINT savepoint_name]
Re: Savepoint support proposal
On Thu, Mar 10, 2005 at 10:54:37AM -, Martin J. Evans wrote: On 10-Mar-2005 Tim Bunce wrote: On Thu, Mar 10, 2005 at 08:54:54AM -, Martin J. Evans wrote: although ODBC does not define what happens if one of the commits on a connection fails (this alone probably makes commiting on an environment unpopular if used at all). Exactly. This (commit_all etc) won't make it into the DBI for that reason. Drivers are, of course, fee to add whatever they want. Tim. Just for clarification. I was not putting this up as a proposal, it was just a comment on things you can't do in SQL in one go. Sure, I know, just thought I'd use it to point out my position on 'transactional' features that can't be done right. If I were looking into this I would be looking at XA not this half-hearted attempt in ODBC. Yeap. That's pretty much what I said in my first message on this thread :) Tim.
Re: Savepoint support proposal
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 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. Well, it's not quite that bad. You can make a good effort and catch 99% of it. DBD::Pg does this by checking the string to be executed: if ($string =~ /^\s*(BEGIN|COMMIT|ROLLBACK|END|ABORT)\b/) { die Please use DBI functions for transaction handling\n; } The actual code is in C and a lot uglier than that, but that's the gist of it. Makes it very hard for the users to get around using the DBI methods for transaction control. We'd do the same for SAVEPOINT of course. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200503102215 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCMQ2UvJuQZxSWSsgRAhtIAJ9bEjoMthhte56DeP5/4MVf/lyxsACg/lk8 A/g9oUYvRFL3B8/yaecbFVo= =CZji -END PGP SIGNATURE-
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(savepoint-sql) 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. snipped Martin -- Martin J. Evans Easysoft Ltd, UK Development
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 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
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: 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(savepoint-sql) 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 disclaimer.h 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 disclaimer.h 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 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 disclaimer.h 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
-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: Savepoint support proposal
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(savepoint-sql) sufficient ? Sure, but they also have syntax to implement BEGIN and ROLLBACK. 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. I'd also be interested to see a proposal for an API along with some real-world examples. 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. Tim.
Re: Savepoint support proposal
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(savepoint-sql) 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. 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. The SQL 2003 (1999) syntax is in section 16 of ISO/IEC 9075-2:2003: 16 Transaction management 16.1 start transaction statement Start an SQL-transaction and set its characteristics. start transaction statement::= START TRANSACTION [ transaction mode [ { comma transaction mode }...] ] transaction mode::= isolation level | transaction access mode | diagnostics size transaction access mode::= READ ONLY | READ WRITE isolation level::= ISOLATION LEVEL level of isolation level of isolation::= READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE diagnostics size::= DIAGNOSTICS SIZE number of conditions number of conditions::= simple value specification 16.2 set transaction statement Set the characteristics of the next SQL-transaction for the SQL-agent. NOTE 402 - This statement has no effect on any SQL-transactions subsequent to the next SQL-transaction. set transaction statement::= SET [ LOCAL ] transaction characteristics transaction characteristics::= TRANSACTION transaction mode [ { comma transaction mode }... ] 16.3 set constraints mode statement If an SQL-transaction is currently active, then set the constraint mode for that SQL-transaction in the current SQL-session. If no SQL-transaction is currently active, then set the constraint mode for the next SQL-transaction in the current SQL-session for the SQL-agent. NOTE 404 This statement has no effect on any SQL-transactions subsequent to this SQL-transaction. set constraints mode statement::= SET CONSTRAINTS constraint name list { DEFERRED | IMMEDIATE } constraint name list::= ALL | constraint name [ { comma constraint name }... ] 16.4 savepoint statement Establish a savepoint. savepoint statement::= SAVEPOINT savepoint specifier savepoint specifier::= savepoint name 16.5 release savepoint statement Destroy a savepoint. release savepoint statement::= RELEASE SAVEPOINT savepoint specifier 16.6 commit statement Terminate the current SQL-transaction with commit. commit statement::= COMMIT [ WORK ] [ AND [ NO ] CHAIN ] 16.7 rollback statement Terminate the current SQL-transaction with rollback, or rollback all actions affecting SQL-data and/or schemas since the establishment of a savepoint. rollback statement::= ROLLBACK [ WORK ] [ AND [ NO ] CHAIN ] [ savepoint clause ] savepoint clause::= TO SAVEPOINT savepoint specifier (If you want to see the hyperlinked HTML from which I cut'n'pasted the syntax, tell me. Tools and all available on request.) As it happens, IDS does not yet support savepoints; it's on my list of things I'm looking at for a future release. But if we implement them, it will be using the standard syntax first, and any IBM proprietary variations second. I'd also be interested to see a proposal for an API along with some real-world examples. 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. Tim. -- Jonathan Leffler [EMAIL PROTECTED] #include disclaimer.h Guardian of DBD::Informix -
Re: Savepoint support proposal
On Sun, Mar 06, 2005 at 08:41:01PM -0800, Dean Arnold wrote: Greg Sabino Mullane wrote: We are working on implementing savepoint support in DBD::Pg, and someone pointed out (quite rightly) that perhaps there should be DBI method support for savepoints, as they are a standard SQL construct used by more than just PostgreSQL. So I would like to propose that DBI implement a savepoint, release, and rollbackto method, similar to the existing begin, commit, and rollback methods for database handles. In short, these would be simple wrappers, with the actual implementation left to the DBDs. I can whip up a prototype and more detailed docs, but wanted to see if there was any input or objections before I did. Thanks, - -- Greg Sabino Mullane [EMAIL PROTECTED] 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(savepoint-sql) sufficient ? my 2 cents, Mine too. The only significant reason I can see for a specific API for savepoints would be to support distributed transaction management (ala XA). If that is the only significant reason then any API proposals should be made in that context. Tim.
Savepoint support proposal
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 We are working on implementing savepoint support in DBD::Pg, and someone pointed out (quite rightly) that perhaps there should be DBI method support for savepoints, as they are a standard SQL construct used by more than just PostgreSQL. So I would like to propose that DBI implement a savepoint, release, and rollbackto method, similar to the existing begin, commit, and rollback methods for database handles. In short, these would be simple wrappers, with the actual implementation left to the DBDs. I can whip up a prototype and more detailed docs, but wanted to see if there was any input or objections before I did. Thanks, - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200503060449 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCKtJXvJuQZxSWSsgRAu0YAKDF+UCOI5wXNjEAlvIYpzd2EwtmawCffaHa vEgSlMJmZLq9B2PzXHe4+BM= =yFW+ -END PGP SIGNATURE-
Re: Savepoint support proposal
Greg Sabino Mullane wrote: We are working on implementing savepoint support in DBD::Pg, and someone pointed out (quite rightly) that perhaps there should be DBI method support for savepoints, as they are a standard SQL construct used by more than just PostgreSQL. So I would like to propose that DBI implement a savepoint, release, and rollbackto method, similar to the existing begin, commit, and rollback methods for database handles. In short, these would be simple wrappers, with the actual implementation left to the DBDs. I can whip up a prototype and more detailed docs, but wanted to see if there was any input or objections before I did. Thanks, - -- Greg Sabino Mullane [EMAIL PROTECTED] 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(savepoint-sql) sufficient ? my 2 cents, Dean Arnold Presicient Corp.