Re: Behavior of Connection.commit()

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



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.makeFromDatabaseError(Unknown Source
)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectionCommand(Unknown 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){}
        }
    }
}


.

Relevant Pages

  • Re: HELP ME
    ... We need to insert/update data from temp table to staging tables. ... any User Define Exception to be handled and then use Raise Exception ... Above code template does not specify Commit statement. ...
    (comp.databases.oracle.misc)
  • HELP ME
    ... We need to insert/update data from temp table to staging tables. ... any User Define Exception to be handled and then use Raise Exception ... Above code template does not specify Commit statement. ...
    (comp.databases.oracle.misc)
  • Re: HELP ME
    ... We need to insert/update data from temp table to staging tables. ... any User Define Exception to be handled and then use Raise Exception ... Above code template does not specify Commit statement. ...
    (comp.databases.oracle.misc)
  • Re: HELP ME
    ... We need to execute update first and if SQL%ROWCOUNT < 1 then need to ... any User Define Exception to be handled and then use Raise Exception ... Above code template does not specify Commit statement. ... Name, Table primary key etc. ...
    (comp.databases.oracle.misc)
  • Re: Transaktionen bei INSERT-Fehler richtig abschliessen
    ... IBSQL.ExecQuery; // INSERT ... Exception erst bei Commit auftritt - falls es da überhaupt ... nach INSERT aber vor dem Commit kaputtgehen. ... In dem Fall wirst Du noch ein Rollback versuchen. ...
    (de.comp.lang.delphi.datenbanken)
Loading