SQL Server JDBC - Behavior of Connection.commit()

Author

Behavior of Connection.commit()

Wes Clark

2006-01-20, 1:23 pm

Running with "SET IMPLICIT_TRANSACTION
S ON", with the old driver we would
sometimes issue a commit on a connection when no work had been done on the
connection, and we would never get "Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION." We
are now getting this message. Did the old driver know no statements had been
issued on the connection since the last commit, and not issue the command to
the server? We use a framework do manage the database interface.

Wes Clark

2006-01-20, 1:23 pm

Actually, we call setAutoCommit(false)
on the connection, we don't issue "SET
IMPLICIT_TRANSACTION
S ON" via sql.

Joe Weinstein

2006-01-20, 1:23 pm

Wes Clark wrote:

> Actually, we call setAutoCommit(false)
on the connection, we don't issue "SET
> IMPLICIT_TRANSACTION
S ON" via sql.

Whew, good. I'm sure you know how bad form it would be to mix transactional
control between JDBC and DBMS-specific SQL. (I've seen it done!). Can you
verify that you *always* call setAutoCommit(false)
before calling commit()?
thanks,
Joe Weinstein at BEA Systems

Jerry Brenner

2006-01-20, 1:23 pm

Joe, funny meeting you here.

We call setAutoCommit(false)
when we first get a connection, just prior to
putting it into the connection pool. If we need to change it for some
reason, then we always change it back before committing.

"Joe Weinstein" wrote:

>
>
> Wes Clark wrote:
>
>
> Whew, good. I'm sure you know how bad form it would be to mix transactional
> control between JDBC and DBMS-specific SQL. (I've seen it done!). Can you
> verify that you *always* call setAutoCommit(false)
before calling commit()?
> thanks,
> Joe Weinstein at BEA Systems
>
>

Joe Weinstein

2006-01-20, 1:23 pm

Jerry Brenner wrote:

> Joe, funny meeting you here.
>
> We call setAutoCommit(false)
when we first get a connection, just prior to
> putting it into the connection pool. If we need to change it for some
> reason, then we always change it back before committing.
>

Hi Jerry! Good to hear from you, and MS will benefit greatly by
working with you on driver/DBMS query plan issues... I am unable
to duplicate the problem with the commit() call with some cheap JDBC
I tried... If you can provide a repro or trace the execution history
of a connection that throws this exception, that would be cool.

Email me
j
o
e
AT
b
e
a
DOT
c
o
m

[color=darkred]
> "Joe Weinstein" wrote:
>

Angel Saenz-Badillos[MS]

2006-01-20, 8:23 pm

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

--
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@bea.com> wrote in message
news:43D12F02.1060202@bea.com...
>
>
> Jerry Brenner wrote:
>
>
> Hi Jerry! Good to hear from you, and MS will benefit greatly by
> working with you on driver/DBMS query plan issues... I am unable
> to duplicate the problem with the commit() call with some cheap JDBC
> I tried... If you can provide a repro or trace the execution history
> of a connection that throws this exception, that would be cool.
>
> Email me
> j
> o
> e
> AT
> b
> e
> a
> DOT
> c
> o
> m
>
>
>

Joe Weinstein

2006-01-20, 8:23 pm

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.

>
>

Jerry Brenner

2006-01-20, 8:23 pm

I'm confused by:

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

If the exception from the 1st insert was a statement level exception (like
duplicate key violation), then it is up to the caller to decide what to do.
If the caller wants to continue, or even just call commit(), then they should
be allowed to do so. However, if the exception was a transaction level
exception or more severe, then a rollback should occur. Either way,
autocommit should remain off on the connection, so the caller should be able
to call commit() immediately.

Wes Clark

2006-01-20, 8:23 pm

conn. setAutoCommit(false)
;
conn.commit();
System.out.println("Commit 1 succeeded");
conn.commit();
System.out.println("Commit 2 succeeded");

returns

Commit 1 succeeded
Commit 2 succeeded

and no errors

Joe Weinstein

2006-01-20, 8:23 pm

Angel Saenz-Badillos[MS]

2006-01-20, 8:23 pm

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@bea.com> wrote in message
news:43D145AD.5000607@bea.com...
>
>
> Angel Saenz-Badillos[MS] wrote:
>
>
> 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.
>
>
>

Joe Weinstein

2006-01-20, 8:23 pm

Jerry Brenner wrote:

> I'm confused by:
>
>
>
>
> If the exception from the 1st insert was a statement level exception (like
> duplicate key violation), then it is up to the caller to decide what to do.
> If the caller wants to continue, or even just call commit(), then they should
> be allowed to do so. However, if the exception was a transaction level
> exception or more severe, then a rollback should occur. Either way,
> autocommit should remain off on the connection, so the caller should be able
> to call commit() immediately.

I ran the program below, in two windows, the first like:

C:\> java two_cons start

when it said to run the second, I did, like:

and the other:

C:\> java two_cons

This set up a deadlock to happen. When the second program said to press return in the first's window,
I did, and it got the deadlock message. Then I checked the autoCommit() status, which was
still false as far as the driver was concerned at least, and a final commit() on the connection
got an exception:

trying commit...
com.microsoft.sqlserver.jdbc.SQLServerException: Server failed to resume the transaction, desc:
3300000007.
at com.microsoft.sqlserver.jdbc.SQLServerException. makeFromDatabaseErro
r(Unknown Source
)
at com.microsoft.sqlserver.jdbc.IOBuffer. processPackets(Unkno
wn Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection. connectionCommand(Un
known Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendCommit(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.commit(Unknown Source)
at two_cons.main(two_cons.java:66)

Program:
import java.sql.*;
import java.util.*;

public class two_cons
{
public static void main(String args[])
throws Exception
{
Connection c = null;
try
{
Properties props = new Properties();
Driver d = new com.microsoft.sqlserver.jdbc.SQLServerDriver();

props.put("user", "joe");
props.put("password", "joe");
props.put("DatabaseName", "joe");

c = d.connect("jdbc:sqlserver://joe:1433", props );

DatabaseMetaData dd = c.getMetaData();
System.out.println("Driver version is " + dd.getDriverVersion() );

Statement s = c.createStatement();

if (args.length != 0)
{
try {s.executeUpdate("drop table joe");}catch (Exception ignore){}
try {s.executeUpdate("drop table joe2");}catch (Exception ignore){}
s.executeUpdate("create table joe(foo int)");
s.executeUpdate("insert joe values(1)");
s.executeUpdate("create table joe2(foo int)");
s.executeUpdate("insert joe2 values(1)");

c. setAutoCommit(false)
;
s.executeUpdate("update joe set foo = 2");

System.out.println("Ok, now run the other and press return");
System.in.read();
s.executeUpdate("update joe2 set foo = 2");
}
else
{
c. setAutoCommit(false)
;
s.executeUpdate("update joe2 set foo = 2");
System.out.println("Ok, now press return on the other");

s.executeUpdate("update joe set foo = 2");
}
}
catch(Exception exception1)
{
exception1.printStackTrace();
}
finally
{
try {
System.out.println("c.getAutoCommit() says " + c.getAutoCommit() );
} catch (Exception ee) {
ee.printStackTrace();
}

try {
System.out.println("trying commit..." );
c.commit();
} catch (Exception ee) {
ee.printStackTrace();
}
if (c != null) try {c.close();} catch (Exception ignore){}
}
}
}

Joe Weinstein

2006-01-20, 8:23 pm

Oh, and if I changed the program to try to
rollback instead of commit, there was *still*
a problem! There's a bug. The tx *did* get
rolled back because it was the deadlock victim,
but as far as JDBC is concerned, the user's tx
got some sort of exception, and he wants to rollback()
on an autoCommit(false) connection and should
be able to.
Joe Weinstein at BEA Systems

Joe Weinstein wrote:

>
>
> Jerry Brenner wrote:
>
>
>
>
> I ran the program below, in two windows, the first like:
>
> C:\> java two_cons start
>
> when it said to run the second, I did, like:
>
> and the other:
>
> C:\> java two_cons
>
> This set up a deadlock to happen. When the second program said to press
> return in the first's window,
> I did, and it got the deadlock message. Then I checked the autoCommit()
> status, which was
> still false as far as the driver was concerned at least, and a final
> commit() on the connection
> got an exception:
>
> trying commit...
> com.microsoft.sqlserver.jdbc.SQLServerException: Server failed to resume
> the transaction, desc:
> 3300000007.
> at
> com.microsoft.sqlserver.jdbc.SQLServerException. makeFromDatabaseErro
r(Unknown
> Source
> )
> at com.microsoft.sqlserver.jdbc.IOBuffer. processPackets(Unkno
wn
> Source)
> at
> com.microsoft.sqlserver.jdbc.SQLServerConnection. connectionCommand(Un
known
> Source)
> at
> com.microsoft.sqlserver.jdbc.SQLServerConnection.sendCommit(Unknown Source)
> at
> com.microsoft.sqlserver.jdbc.SQLServerConnection.commit(Unknown Source)
> at two_cons.main(two_cons.java:66)
>
> Program:
> import java.sql.*;
> import java.util.*;
>
> public class two_cons
> {
> public static void main(String args[])
> throws Exception
> {
> Connection c = null;
> try
> {
> Properties props = new Properties();
> Driver d = new com.microsoft.sqlserver.jdbc.SQLServerDriver();
>
> props.put("user", "joe");
> props.put("password", "joe");
> props.put("DatabaseName", "joe");
>
> c = d.connect("jdbc:sqlserver://joe:1433", props );
>
> DatabaseMetaData dd = c.getMetaData();
> System.out.println("Driver version is " +
> dd.getDriverVersion() );
>
> Statement s = c.createStatement();
>
> if (args.length != 0)
> {
> try {s.executeUpdate("drop table joe");}catch (Exception
> ignore){}
> try {s.executeUpdate("drop table joe2");}catch (Exception
> ignore){}
> s.executeUpdate("create table joe(foo int)");
> s.executeUpdate("insert joe values(1)");
> s.executeUpdate("create table joe2(foo int)");
> s.executeUpdate("insert joe2 values(1)");
>
> c. setAutoCommit(false)
;
> s.executeUpdate("update joe set foo = 2");
>
> System.out.println("Ok, now run the other and press return");
> System.in.read();
> s.executeUpdate("update joe2 set foo = 2");
> }
> else
> {
> c. setAutoCommit(false)
;
> s.executeUpdate("update joe2 set foo = 2");
> System.out.println("Ok, now press return on the other");
>
> s.executeUpdate("update joe set foo = 2");
> }
> }
> catch(Exception exception1)
> {
> exception1.printStackTrace();
> }
> finally
> {
> try {
> System.out.println("c.getAutoCommit() says " +
> c.getAutoCommit() );
> } catch (Exception ee) {
> ee.printStackTrace();
> }
>
> try {
> System.out.println("trying commit..." );
> c.commit();
> } catch (Exception ee) {
> ee.printStackTrace();
> }
> if (c != null) try {c.close();} catch (Exception ignore){}
> }
> }
> }
>

Joe Weinstein

2006-01-20, 8:23 pm

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:
>
>
>
> IF @@TRANCOUNT = 0 BEGIN TRAN //if we are currently not in a transaction,
> start one.

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

setAutoCommit(true):
"if (@@trancount > 0) rollback transaction set implicit_transaction
s off"
setAutoCommit(false)
: "if (@@trancount > 0) rollback transaction set implicit_transaction
s 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.
>
>
>
>
> 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]

2006-01-20, 8:23 pm

Rolling back we should not see that problem, I would be very interested in
getting that to repro.

--
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@bea.com> wrote in message
news:43D16E6E.4010903@bea.com...
> Oh, and if I changed the program to try to
> rollback instead of commit, there was *still*
> a problem! There's a bug. The tx *did* get
> rolled back because it was the deadlock victim,
> but as far as JDBC is concerned, the user's tx
> got some sort of exception, and he wants to rollback()
> on an autoCommit(false) connection and should
> be able to.
> Joe Weinstein at BEA Systems
>
> Joe Weinstein wrote:
>
>

Angel Saenz-Badillos[MS]

2006-01-20, 8:23 pm

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(Connec
tionString);
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(Connec
tionString);
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@bea.com> wrote in message
news:43D17056.5050403@bea.com...
>
>
> Angel Saenz-Badillos[MS] wrote:
>
>
> Surely the driver can do "set implicit_transaction
s on" instead of
> that. My old driver from '96 does this:
>
> setAutoCommit(true):
"if (@@trancount > 0) rollback transaction set
> implicit_transaction
s off"
> setAutoCommit(false)
: "if (@@trancount > 0) rollback transaction set
> implicit_transaction
s on"
>
> Joe Weinstein at BEA Systems
>
>

Joe Weinstein

2006-01-20, 8:23 pm

Ok, I just tested another driver that fails the tx because
the table is there. The subsequent commit does not fail.
The select gives only the 9999 row. I don't understand
how your program expects the commit to fail, but
then expects the row inserted in a failed tx to still
be there?
Joe

Angel Saenz-Badillos[MS] wrote:

> 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(Connec
tionString);
> 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(Connec
tionString);
> 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.");
> }
> }
>
>
>
>

Jerry Brenner

2006-01-20, 8:23 pm

I turned on the SQL Profiler and the only error that occurs is the 3902 that
we are complaining about. There are no exceptions prior to that. Joe's
program demonstrates a serious bug, but it looks we are hitting a different
bug. Unfortunately, I we haven't been able to figure out how this unit test
is different than any of the other ones that aren't failing.

Angel Saenz-Badillos[MS]

2006-01-20, 8:23 pm

Thank you for taking the time to test this with another driver. The
intention of the program is to show that with this driver when you get this
type of data corruption we will throw on Commit. It is an ugly problem, but
to us it seemed like a better option than having commit succeed and silently
corrupting your data.

This is why I mentioned that this could be a very serious error, your code
could be silently corrupting your data with your previous driver. With this
driver you can at least realize that there may be a problem but it will
probably require code changes to fix the underlying issue.

>I don't understand
> how your program expects the commit to fail, but
> then expects the row inserted in a failed tx to still
> be there?
I am sorry for not making it clearer, I am afraid I just threw it together
to post it while everybody is looking at this thread. The program was
intended to show that others drivers that don't throw on commit still have
the same data corruption issue.

--
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@bea.com> wrote in message
news:43D17774.7090200@bea.com...
> Ok, I just tested another driver that fails the tx because
> the table is there. The subsequent commit does not fail.
> The select gives only the 9999 row. I don't understand
> how your program expects the commit to fail, but
> then expects the row inserted in a failed tx to still
> be there?
> Joe
>
> Angel Saenz-Badillos[MS] wrote:
>
>

Angel Saenz-Badillos[MS]

2006-01-20, 8:23 pm

Jerry,
Thank you for taking the time to go through this. It sounds like you have a
very comprehensive testing framework, if you are interested we would be
happy to work with you to work through these and future issues. Feel free to
contact me at the above email address (without the "online").

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

"Jerry Brenner" < JerryBrenner@discuss
ions.microsoft.com> wrote in message
news:82C6BF39-564A-490D-9DAB- 2B53F762D44F@microso
ft.com...
>I turned on the SQL Profiler and the only error that occurs is the 3902
>that
> we are complaining about. There are no exceptions prior to that. Joe's
> program demonstrates a serious bug, but it looks we are hitting a
> different
> bug. Unfortunately, I we haven't been able to figure out how this unit
> test
> is different than any of the other ones that aren't failing.

Joe Weinstein

2006-01-20, 8:23 pm

Angel Saenz-Badillos[MS] wrote:

> Thank you for taking the time to test this with another driver. The
> intention of the program is to show that with this driver when you get this
> type of data corruption we will throw on Commit. It is an ugly problem, but
> to us it seemed like a better option than having commit succeed and silently
> corrupting your data.
>
> This is why I mentioned that this could be a very serious error, your code
> could be silently corrupting your data with your previous driver. With this
> driver you can at least realize that there may be a problem but it will
> probably require code changes to fix the underlying issue.

Understood. It is in fact the DBMS that throws us all most of the
curve balls. The DBMS should treat every SQL failure the same. If
somewhere in a tx we try to insert a value into a row where it doesn't
fit, we get an exception, but the tx is still alive for us to pursue
or close as we see fit. In the rare case where someone tries DDL in a
transaction, it shouldn't logically be any different.
I I alter the program so the failure is updating a varchar(1)
to be '333', it also throws an exception, but afterwards I am able to
commit or rollback as I choose.
So, the driver author's problem is what to do... I would code to
at least support the mode where any unexepected SQLException is
reacted to by a rollback(). However, I agree that for those
'executive decisions' the DBMS will make, to roll back your
exception immediately, the driver should make any immediate
commit() fail, and have it say "Commit failed because current
tx was chosen as a deadlock victim", or Commit failed because
DDL-in-tran failed". This info would have to be cached by the
driver from the exception during the execute call, and then
serve it up on commit... (Joe say thinking thinly while typing...)
Joe

>
>
>
>
> I am sorry for not making it clearer, I am afraid I just threw it together
> to post it while everybody is looking at this thread. The program was
> intended to show that others drivers that don't throw on commit still have
> the same data corruption issue.
>

Sam Wilson

2006-01-20, 8:23 pm

I'm with Joe... I'm a bit concerned why the driver doesn't issue "set
implicit_transaction
s on" which is the logical equivalent of
setAutoCommit(false)
.

Am I missing something or is this functionality not analogous??

Thanks,

Sam Wilson
Ecount, Inc.

"Joe Weinstein" wrote:

>
>
> Angel Saenz-Badillos[MS] wrote:
>
>
> Surely the driver can do "set implicit_transaction
s on" instead of
> that. My old driver from '96 does this:
>
> setAutoCommit(true):
"if (@@trancount > 0) rollback transaction set implicit_transaction
s off"
> setAutoCommit(false)
: "if (@@trancount > 0) rollback transaction set implicit_transaction
s on"
>
> Joe Weinstein at BEA Systems
>
>
>

Joe Weinstein

2006-01-22, 1:23 pm

Joe Weinstein wrote:

> Surely the driver can do "set implicit_transaction
s on" instead of
> that. My old driver from '96 does this:
>
> setAutoCommit(true):
"if (@@trancount > 0) rollback transaction set
> implicit_transaction
s off"
> setAutoCommit(false)
: "if (@@trancount > 0) rollback transaction set
> implicit_transaction
s on"
>
> Joe Weinstein at BEA Systems

And just to be clear, we consciously chose to roll back any tx context that
was not explicitly committed before a call to setAutoCommit(true). Some
folks are used to Oracle's SQL-PLUS application committing on close, so
you *could* have a connection configuration option to go the other way,
but *boy* is that odd/ugly as desired behavior.
For both polarities of the call, we do *not* send duplicate/redundant
calls. The driver keeps a flag of the setting it's supposed to be
manifesting, so whenever the user calls setAutoCommit() asking for the
same mode we're already in, we do nothing.
Joe

Angel Saenz-Badillos[MS]

2006-01-23, 1:23 pm

Sam,
The problem is the same as above. If we set set_implicit_transac
tions on we
will no longer be able to tell that a serious issue happened on commit and
we would not throw an exception:

//start transaction
//insert data 1
//execute ddl
exception. At this time insert data 1 has been rolled back!
//insert data 2 //with set_implicit_transac
tions on this will create a NEW
transaction under the covers.
//COMMIT will no longer throw an exception, it will commit the transaction
started with insert data 2 and you will silently ignore the fact that data 1
has been rolled back.

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

"Sam Wilson" < SamWilson@discussion
s.microsoft.com> wrote in message
news:250B6857-2B1A-44C3-B7A1- 33CEEBBD5E30@microso
ft.com...[color=darkred]
> I'm with Joe... I'm a bit concerned why the driver doesn't issue "set
> implicit_transaction
s on" which is the logical equivalent of
> setAutoCommit(false)
.
>
> Am I missing something or is this functionality not analogous??
>
> Thanks,
>
> Sam Wilson
> Ecount, Inc.
>
> "Joe Weinstein" wrote:
>

Joe Weinstein

2006-01-23, 8:23 pm

Angel Saenz-Badillos[MS] wrote:

> Sam,
> The problem is the same as above. If we set set_implicit_transac
tions on we
> will no longer be able to tell that a serious issue happened on commit and
> we would not throw an exception:
>
> //start transaction
> //insert data 1
> //execute ddl
> exception. At this time insert data 1 has been rolled back!
> //insert data 2 //with set_implicit_transac
tions on this will create a NEW
> transaction under the covers.

Understood. One other thing to consider is if the driver can know from
the DBMS message when (the rare case such as deadlock or DDL) when the
DBMS has already killed the whole tx, and in that case, throw an exception
from any subsequent connection, statement or result set method that would/could
do an update or query (because it may hold locks) (from this connection)
saying "The DBMS has killed the current transaction. No further DBMS
access is allowed until you call rollback()".

> //COMMIT will no longer throw an exception, it will commit the transaction
> started with insert data 2 and you will silently ignore the fact that data 1
> has been rolled back.

Correct, but there is a problem as soon as the tx has been rolled back,
and there can be troubles even allowing the insert data 2 to proceed. The
user application may depend on holding locks in order, and once it is
allowed to proceed obtaining the lock for data 2, it may then go on to
obtain other locks before trying to commit, and this not-expected order
of locking (data 1 and any other data previously locked are now unlocked)
may cause deadlocks that could kill other innocent, correct transactions.
HTH,
Joe

Angel Saenz-Badillos[MS]

2006-01-23, 8:23 pm

This is completely correct; throwing an exception on commit is only a band
aid and still causes too much pain.

>throw an exception from any subsequent connection, statement or result

>set >method that would/could do an update or query

This would be the only real solution to this problem. The big IF of course
is whether we can correctly determine all cases in which the transaction has
been rolled back by the DBMS...

--
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@bea.com> wrote in message
news:43D532F8.9090108@bea.com...
>
>
> Angel Saenz-Badillos[MS] wrote:
>
>
> Understood. One other thing to consider is if the driver can know from
> the DBMS message when (the rare case such as deadlock or DDL) when the
> DBMS has already killed the whole tx, and in that case, throw an exception
> from any subsequent connection, statement or result set method that
> would/could
> do an update or query (because it may hold locks) (from this connection)
> saying "The DBMS has killed the current transaction. No further DBMS
> access is allowed until you call rollback()".
>
>
> Correct, but there is a problem as soon as the tx has been rolled back,
> and there can be troubles even allowing the insert data 2 to proceed. The
> user application may depend on holding locks in order, and once it is
> allowed to proceed obtaining the lock for data 2, it may then go on to
> obtain other locks before trying to commit, and this not-expected order
> of locking (data 1 and any other data previously locked are now unlocked)
> may cause deadlocks that could kill other innocent, correct transactions.
> HTH,
> Joe
>

Wes Clark

2006-01-24, 8:23 pm

It turns out in very limited circumstances in a few test programs, we were
sending a "commit" sql statement to the database. A developer found this in
a trace we send in. This was messing up the driver. And I don't think this
is related to most of the discussion on this thread.

I would suggest that if the driver needs to keep track of the state of the
transaction, it would be advisable to look for SQL statements that would
affect that state. I don’t think the JDBC spec would say you can’t commit a
transaction using the SQL.

I imagine the other drivers do that very thing. Do you accept this as a
“bug” and/or should I enter this somewhere else?

Thank you for helping us get back on the road.

Joe Weinstein

2006-01-24, 8:23 pm

Wes Clark wrote:

> It turns out in very limited circumstances in a few test programs, we were
> sending a "commit" sql statement to the database. A developer found this in
> a trace we send in. This was messing up the driver. And I don't think this
> is related to most of the discussion on this thread.
>
> I would suggest that if the driver needs to keep track of the state of the
> transaction, it would be advisable to look for SQL statements that would
> affect that state. I don’t think the JDBC spec would say you can’t commit a
> transaction using the SQL.

I'd take the other position. The load imposed by the driver having to parse all the SQL,
looking for commits, rollbacks etc in SQL would be onerous and inappropriate. It would
be clearly poor style to distribute control of transactions between JDBC and SQL.
Procedures could have 'commit' in them and the driver could never know.

> I imagine the other drivers do that very thing. Do you accept this as a
> “bug” and/or should I enter this somewhere else?

They don't. I highly recommend controling your transactions within
a top-level method that does all the tx work and then calls
commit() or rollback, or letting a tx-capable container do it all for
you.
Joe Weinstein at BEA Systems

>
> Thank you for helping us get back on the road.
>

Wes Clark

2006-01-24, 8:23 pm

I'm interested in Microsoft's response. Does anyone know what the JDBC spec
says here? Does it say "commit" and "rollback" and other SQL commands that
affect the transaction state are not allowed? If so, should the driver throw
an error? That would also require parsing, of course.

How about in a batch of statements? How sure can the driver ever be about
the state of the transaction?

"Joe Weinstein" wrote:

>
>
> Wes Clark wrote:
>
>
> I'd take the other position. The load imposed by the driver having to parse all the SQL,
> looking for commits, rollbacks etc in SQL would be onerous and inappropriate. It would
> be clearly poor style to distribute control of transactions between JDBC and SQL.
> Procedures could have 'commit' in them and the driver could never know.
>
>
> They don't. I highly recommend controling your transactions within
> a top-level method that does all the tx work and then calls
> commit() or rollback, or letting a tx-capable container do it all for
> you.
> Joe Weinstein at BEA Systems
>
>
>

Joe Weinstein

2006-01-24, 8:23 pm

Wes Clark wrote:

> I'm interested in Microsoft's response. Does anyone know what the JDBC spec
> says here? Does it say "commit" and "rollback" and other SQL commands that
> affect the transaction state are not allowed? If so, should the driver throw
> an error? That would also require parsing, of course.
>
> How about in a batch of statements? How sure can the driver ever be about
> the state of the transaction?

We will await MS's response. In the meantime, the spec says nothing. It also
doesn't say what happens if the JDBC driver sends "shutdown" SQL to the DBMS
either. A batch of statements doesn't add any more complexity. The driver must
assume the transactional status is as it was set the last time via JDBC. Drivers
will never parse SQL for transactional semantics, let alone presume to react
to them, and stored procedures or even triggers could easily subvert any such
effort.
I can promise you as a former JDBC spec member and co-author of the first
commercial JDBC drivers on the market for MS SQLServer, Sybase, Oracle, and
Informix, and as a DBMS guy (joe@sybase.com '88-'96) that you should always
define, control and terminate a tx with only one API and in one logical place.
Good luck, I want you to be successful.
Joe Weinstein at BEA Systems
[color=darkred]
> "Joe Weinstein" wrote:
>
>

Angel Saenz-Badillos[MS]

2006-01-24, 8:23 pm

We will definitelly be looking at this and the separate issue with
exceptions, I am glad to hear that the inmediate issue at least is resolved.

I can't think of a single driver in existance today for Sql Server that
handles both TSQL and API transaction handling simultaneously, making this
scenario work is going to be very tough. One thing we _could_ do would be to
throw an exception whenever we detect that this is happening, so we could
only begin, roll back, and commit transactions after verifying that the
transaction count on the server is what we expect at that point in time....
On the other hand this could be even more damaging...

--
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@bea.com> wrote in message
news:%23$luq2UIGHA.3944@tk2msftngp13.phx.gbl...
>
>
> Wes Clark wrote:
>
>
> We will await MS's response. In the meantime, the spec says nothing. It
> also
> doesn't say what happens if the JDBC driver sends "shutdown" SQL to the
> DBMS
> either. A batch of statements doesn't add any more complexity. The driver
> must
> assume the transactional status is as it was set the last time via JDBC.
> Drivers
> will never parse SQL for transactional semantics, let alone presume to
> react
> to them, and stored procedures or even triggers could easily subvert any
> such
> effort.
> I can promise you as a former JDBC spec member and co-author of the first
> commercial JDBC drivers on the market for MS SQLServer, Sybase, Oracle,
> and
> Informix, and as a DBMS guy (joe@sybase.com '88-'96) that you should
> always
> define, control and terminate a tx with only one API and in one logical
> place.
> Good luck, I want you to be successful.
> Joe Weinstein at BEA Systems
>
>

Joe Weinstein

2006-01-24, 8:23 pm

Angel Saenz-Badillos[MS] wrote:

> We will definitely be looking at this and the separate issue with
> exceptions, I am glad to hear that the inmediate issue at least is resolved.
>
> I can't think of a single driver in existance today for Sql Server that
> handles both TSQL and API transaction handling simultaneously,

For *any* DBMS, and I work on Oracle's thin driver and Sybase's
jConnect driver regularly, as well as debugging ours for DB2, etc.

> making this
> scenario work is going to be very tough. One thing we _could_ do would be to
> throw an exception whenever we detect that this is happening, so we could
> only begin, roll back, and commit transactions after verifying that the
> transaction count on the server is what we expect at that point in time....
> On the other hand this could be even more damaging...

It would drive you nuts. You would have to postpend every execute with a
query to return @@trancount and process it before the next execute from any
statement, which might mean buffering tons of data etc, or even allowing
a fetch from any other sensitive cursor. or even allowing the current
statement to process output parameters. What if the SQL raised a fatal
error after committing? You wouldn't get your post-pended @@trancount
query.

"create proc my_tx_handle @whichway int as begin if (@whichway > 0)commit tran else rollback end"

"create proc my_big_tx as begin select * from the universe exec my_tx_handle 1 end"

and before processing the result set from my_big_tx the user runs another statement...
etc.

And let's assume you're processing in autoCommit(true) mode, and
suddenly you discover @@trancount is 3. What will the driver do?
The fact is that there can only be one controller of the tx state,
and if the driver isn't it, there is no way the driver can choose
what to do if/when it finds it's "not in Kansas anymore". You can
go only so far in protecting the user from himself without causing
trouble for the normal case.
Joe

Sponsored Links