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
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,
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
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
//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.