Re: Behavior of Connection.commit()

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

--
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/




"Joe Weinstein" <joeNOSPAM@xxxxxxx> wrote in message
news:43D145AD.5000607@xxxxxxxxxx
>
>
> Angel Saenz-Badillos[MS] wrote:
>
>> "The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION."
>>
>> This could be a very serious error. This may not be what is affecting
>> your scenario but here is some information that may be applicable. The
>> basic problem is that high severity SQL exceptions (level 16 or higher)
>> can rollback the transaction on the server. When this happens the old
>> driver was masking the error during COMMIT with something like the
>> following TSQL:
>>
>> IF @@TRANCOUNT > 0 COMMIT TRAN //If there is a transaction active commit
>> it, otherwise do nothing.
>>
>> We decided that faking a transaction commit was a bad idea and in the new
>> driver replaced it with something like:
>> COMMIT TRAN //Commit transaction, if no transaction active throw
>> exception.
>>
>> What this means is that the following scenario would behave
>>
>> //start transaction
>> //insert data 1
>> //execute something that throws lev 16 server exception and ignore
>> exception. At this time insert data 1 has been rolled back!
>> //insert data 2
>> //COMMIT
>>
>> In the old driver in this scenario commit would silently "work" (not
>> throw an exception), even though insert data 1 has been rolled back. In
>> the new driver this will now throw an exception and you will at least
>> know that not all is well. We felt that it was an important breaking
>> change to make.
>
> Interesting... So what does the driver do with:
>
> c = d.connect(URL, props);
> c.setAutoCommit(false); // does *not start a tx*
> s.commit();
>
> Also, when the driver throws the exception above, in
> a circumstance you suggest, presumably the connection
> is still in autoCommit(false) mode. Is it?
>
> Joe Weinstein at BEA Systems.
>
>
>>
>>
>


.

Relevant Pages

  • Re: Stored Procedure error is not catched
    ... In my stored proc I have begin transaction, ... Then call your procedures from Java, after starting a transaction in JDBC ), then catch any exception and call conn.rollbackin Java, or call conn.commitin Java. ... what does the jTDS driver do? ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Driver corrupts prepared statements in pool.
    ... This test case demonstrates a bug in the Microsoft SQL Server 2005 JDBC ... cause an exception the next time it is used. ... Microsoft SQL Server 2005 JDBC driver 1.0.809.102 ... // A connection will NOT work properly until it has been ...
    (microsoft.public.sqlserver.jdbcdriver)
  • RE: HELP on New request is not allowed to start because [1264822]
    ... allowed to start because it should come with valid transaction descriptor. ... System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean ... TRXOrders.CLogin.GetTradexUserIDFromQube(String QubeUserID, String GroupCode) ... This error always occurs when we issue the command to SQL Server. ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: HELP on New request is not allowed to start because [1264822]
    ... Do you have a link to the bug? ... application doen't raise any exception during a distributed transaction. ... This error always occurs when we issue the command to SQL Server. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: timeout exception during SqlTransaction.Commit() any workaround please help?
    ... Why aren't you using SqlClient if you're going against a SQL Server? ... But at the very and when i am closing transaction i get an exception. ...
    (microsoft.public.dotnet.framework.adonet)