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);
c.setAutoCommit(false);

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

s.commit();

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

try{
//create table
}catch{
//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.


.

Relevant Pages

  • Re: I need the ODBC 2.0 Oracle equivalent of a record lock
    ... I'm not a SQL programmer; my job was to focus on the mechanisms that SQL ... The trick was that if CommitTransaction was not made, we had to record, in ... it was rolled back before the database system became publicly ... So database integrity is NEVER an issue: a transaction ...
    (microsoft.public.vc.mfc)
  • Re: Locking and Delay in a Bottleneck
    ... that should avoid the gap issue on rollback, ... SQL Server MVP ... the saving transaction, this journal voucher is having a header table ... number in a table whereupon you commit. ...
    (microsoft.public.sqlserver.programming)
  • Re: Driver AutoCommit issue
    ... I am using the container managed transaction and I expect the container to handle that for me. ... Is there different driver class that i have to use in the connection pool configuration. ... Why I am saying the DML always gets committed is when I step thought the code I can see the updated data in the database immediately after the callable statement is executed Even before the EJB method that invoked the call is completed. ... I set the auto commit to false on connection as soon as I get the connection from the datasoruce. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Trigger does not seem to fire from front end or enterprise manager
    ... there is no need to even do COMMIT in a trigger. ... > the transaction will be committed as the statement completes. ... > against the inserted and deleted tables inside your trigger code. ... run a profiler trace and you will see what SQL EM submits. ...
    (microsoft.public.sqlserver.programming)
  • Re: A lazy-committing database object with curry?
    ... > I'm writing a database helper class that represents records in a SQL ... > commit the changes all at once. ... transactionally, and the transaction fails, your in memory objects will ...
    (comp.lang.python)