Re: Behavior of Connection.commit()

Angel Saenz-Badillos[MS] wrote:

You have to remember that Sql Server does not understand the concept of a autoCommit(false) state, we have to fake this with "Begin transaction" blocks. By default the Sql Server database is in the equivalent of autoCommit(true) state, anything you do will permanently update the database.

When we set autoCommit to off the expectation is that no SQL statements will be committed (that is, the database will not be permanently updated) until you have explicitly told it to commit by invoking the commit() method. To do this in Sql Server we issue the following command to the server:

c = d.connect(URL, props);

IF @@TRANCOUNT = 0 BEGIN TRAN //if we are currently not in a transaction, start one.

Surely the driver can do "set implicit_transactions on" instead of that. My old driver from '96 does this:

setAutoCommit(true):  "if (@@trancount > 0) rollback transaction set implicit_transactions off"
setAutoCommit(false): "if (@@trancount > 0) rollback transaction set implicit_transactions on"

Joe Weinstein at BEA Systems


COMMIT TRAN; IF @@TRANCOUNT = 0 BEGIN TRAN //Commit the existing transaction, start a new transaction to fake the fact that we are still in autoCommit(false) state.

Also, when the driver throws the exception above, in
a circumstance you suggest, presumably the connection
is still in autoCommit(false) mode. Is it?

Thanks to the BEGIN TRAN at the end of the commit it would be, but this should be no consolation. When you receive the "no corresponding begin" exception you probably have data corruption.

We recently looked into this error for a large customer. They started getting this error with our driver and filed it as a bug. When we investigated it turned out that they were doing something as inoquous as

//create table
//ignore failure, this means that table already existed. }

This was sometimes running inside of a transaction, when the table was already in the database the Sql Exception would roll back the transaction but they would ignore it. With their previous driver they had no idea that everything before the above code was being rolled back.

I really should put this in an easier to read format, maybe a blog with source code you can try.