Re: commoditization suggestion #1 - disconnect() transaction behaviour

[Tim Bunce]

On Mon, Aug 09, 2004 at 10:16:10PM -0700, Darren Duncan wrote:
 [30 lines]
 
 I suggest that the disconnect() function follow the same behaviour as 
 DESTROY for how transactions are dealt with.  Or in other words, any 
 current transaction should *always* be rolled back when a database 
 connection is closed, regardless of how it is closed.

I agree and will add this to the list of potentially incompatible
changes for DBI v2 and I'll change Driver.xst to do that.

However pure-perl drivers, or compiled drivers not using Driver.xst[*],
which support transactions will need to make the change themselves.
There's no need to wait for DBI v2 to do that.

Tim.

[*] Are there any compiled drivers not using Driver.xst?

commoditization suggestion #1 - disconnect() transaction behaviour

[Darren Duncan]

Here's my first suggestion towards giving the various DBD modules 
more consistent behaviour, which would aid in both database 
portability and in the construction of a common test suite.

This suggestion affects DBD modules for databases that have support 
for transactions; it should have no impact on DBD modules for 
databases that lack said support, and hence are auto-commit all the 
time.

I reference this portion of the DBI user documentation, under disconnect():
	The transaction behaviour of the disconnect method is, sadly, 
undefined. Some database systems (such as Oracle and Ingres) will 
automatically commit any outstanding changes, but others (such as 
Informix) will rollback any outstanding changes. Applications not 
using AutoCommit should explicitly call commit or rollback before 
calling disconnect.

	The database is automatically disconnected by the DESTROY 
method if still connected when there are no longer any references to 
the handle. The DESTROY method for each driver should implicitly call 
rollback to undo any uncommitted changes. This is vital behaviour to 
ensure that incomplete transactions don't get committed simply 
because Perl calls DESTROY on every object before exiting. Also, do 
not rely on the order of object destruction during global 
destruction, as it is undefined.

	Generally, if you want your changes to be commited or rolled 
back when you disconnect, then you should explicitly call commit or 
rollback before disconnecting.

I suggest that the disconnect() function follow the same behaviour as 
DESTROY for how transactions are dealt with.  Or in other words, any 
current transaction should *always* be rolled back when a database 
connection is closed, regardless of how it is closed.  The DBD 
modules should do this by themselves, and hence the users of DBI can 
count on the shortest/simplest path (no explicit c/r pre disc) 
always being consistent.  They know that they must always explicitly 
commit() transactions (or use auto-commit), or changes won't be 
saved, period.

Now, if you think my suggestion to always-rollback vs always-commit 
is arbitrary, I suggest that always-rollback is a safer default 
option.

Since this suggestion has nothing to do with SQL, I believe it is 
well within the domain of things to implement in the DBI/DBD domain, 
and should not have to be the domain of wrapper modules.

I will also note that this change should not break any user 
applications if they were doing the right thing to begin with, 
which is be explicit, as the documentation says.  Only applications 
that counted on undefined default behaviour to be a certain way, 
which is commit, and used a database that did just that, might 
break.

Any feedback?  Can we do this thing?  Or are there any good reasons not to?
-- Darren Duncan

Re: commoditization suggestion #1 - disconnect() transaction behaviour

[Darren Duncan]

At 6:54 AM -0700 8/10/04, Jonathan Leffler wrote:
Succinctly, I think rollback on termination is the only option that 
makes any sense - but the DBMS must implement that.  There are 
circumstances (kill -9 on Unix) where there is nothing the Perl 
ensemble can do about the behaviour of the incomplete TX.  And,
although the driver can (and in my view should) rollback anything 
that has not formally been committed, there is still a chance that 
the DBMS will not do as you would expect.
That is true, of course.
But the situation is no better when users have to explicitly commit() 
or rollback() before disconnect().  A kill -9 would cause as much 
trouble then as when the DBD module would call rollback() on their 
behalf.

Perhaps, as far as rigorous behaviour goes, the DBI documentation 
could just be updated to say, if my suggestion is followed, that a 
rollback will always happen assuming that the relevant processes are 
not killed.  If they are killed, then the default database behaviour 
takes over.  If all of the processes are allowed to keep running, 
then a rollback is guaranteed.

And DESTROY() already does what I suggest for disconnect(), which is 
the best it can to be rigorous under the circumstances.

-- Darren Duncan