Re: Behavior of Connection.commit()
- From: "Angel Saenz-Badillos[MS]" <angelsa@xxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 20 Jan 2006 15:25:26 -0800
Just a quick repro program that explains the problem, I would be interested
to see how other Sql Server drivers handle this:
import java.lang.*;
import java.util.*;
import java.sql.*;
import java.math.*;
import java.text.*;
import java.io.*;
public class test
{
public static String ConnectionString = "<set your connection string
here>";
public static void main(String[] args) throws Exception
{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection conn = DriverManager.getConnection(ConnectionString);
Statement stmt = conn.createStatement();
String tablename= "testTX";
stmt.executeUpdate("if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[" + tablename + "]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1) DROP TABLE [" + tablename + "]");
stmt.executeUpdate("CREATE TABLE [" + tablename + "] (a int)");
conn.setAutoCommit(false);
stmt.executeUpdate("INSERT into [" + tablename+ "] values (1)");
try{
//This will kill the transaction!
stmt.executeUpdate("CREATE TABLE [" + tablename + "] (a int)");
}catch(Exception ex){System.out.println(ex.getMessage());}
stmt.executeUpdate("INSERT into [" + tablename+ "] values (99999)");
try{
conn.commit();
}catch(Exception ex){System.out.println("We correctly throw an exception:
"+ex.getMessage());}
conn.close();
//Verify data inserted.
Connection conn2 = DriverManager.getConnection(ConnectionString);
Statement stmt2 = conn2.createStatement();
ResultSet rs = stmt2.executeQuery("SELECT * FROM [" + tablename + "]");
while(rs.next()){
System.out.println(rs.getObject(1));
}
System.out.println("If this did not print out 2 lines: 1, 99999 you have
data corruption.");
}
}
--
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:43D17056.5050403@xxxxxxxxxx
>
>
> 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.
>>
>
.
- Follow-Ups:
- Re: Behavior of Connection.commit()
- From: Joe Weinstein
- Re: Behavior of Connection.commit()
- References:
- Re: Behavior of Connection.commit()
- From: Joe Weinstein
- Re: Behavior of Connection.commit()
- From: Joe Weinstein
- Re: Behavior of Connection.commit()
- From: Angel Saenz-Badillos[MS]
- Re: Behavior of Connection.commit()
- From: Joe Weinstein
- Re: Behavior of Connection.commit()
- From: Angel Saenz-Badillos[MS]
- Re: Behavior of Connection.commit()
- From: Joe Weinstein
- Re: Behavior of Connection.commit()
- Prev by Date: Re: Behavior of Connection.commit()
- Next by Date: Re: Behavior of Connection.commit()
- Previous by thread: Re: Behavior of Connection.commit()
- Next by thread: Re: Behavior of Connection.commit()
- Index(es):
Relevant Pages
|
