Loading ...
Sorry, an error occurred while loading the content.

Re: [xml-dbms] Problems with CommitMode: JDBC driver bug?

Expand Messages
  • Ronald Bourret
    Since you haven t told me the exact problem you are having, it is difficult for me to tell you whether the problem is caused by XML-DBMS or the driver.
    Message 1 of 7 , Apr 30, 2003
    • 0 Attachment
      Since you haven't told me the exact problem you are having, it is
      difficult for me to tell you whether the problem is caused by XML-DBMS
      or the driver. (Personally, I hope it's the driver :)

      The only other Postgres driver I know of (I haven't used it) is:

      http://jxdbcon.sourceforge.net/

      One other note: If your application will control transactions directly,
      be sure to use org.xmlmiddleware.db.ArrayDataSource as your data source
      and set the commit mode to COMMIT_NONE. This data source allows you to
      pass Connection(s) to XML-DBMS.

      -- Ron

      Gudmundur Arni Thorisson wrote:
      >
      > Ron. Myself and Ardavan Kanani, who's posted a few message to this
      > list recently, are experiencing a problem with the CommitMode property
      > in XML-DBMS. After looking at the XML-DMBS Java source, we don't think
      > it's a problem there, but suspect a bug the JDBC driver. Can you
      > confirm this? Are there other solutitions (other JDBC driver than the
      > org.postgresql.Driver one) that might help us out? Anyone else had this
      > problem?
      >
      > Some background: We are looking to A) get CommitMode=AfterDocument to
      > work when using the Transfer class to import data via XML-DBMS, in our
      > test-setup and B) disable transactions completely and control them
      > ourselves via the calling app as necessary. The latter is important,
      > since we will probably end up using not the provided commandline method
      > of importing data via the Transfer class, but rather import the desired
      > XML-records one by one (via DOMToDBMS), all within one transaction
      > block that may or may not be correspondent with the actual source
      > XML-file (which we'd parse beforehand anyway). If the database crashes
      > or e.g. a unique constraint is violated or other error, we'd want to
      > roll back the whole thing manually and abort the job.
      > There are also performance issues at stake: we think that by not
      > having a transaction for every single SQL statement coming from
      > XML-DBMS, as it is now, we can boost performance significantly. What is
      > your experience? We're using PostgreSQL.
      >
      > BTW Apart from the aforementioned problem, we think XML-DBMS is great
      > and are liking it more and more by day! Cool Stuff, man.
      >
      > Mummi, CSHL
      >
      > --
      > ----------------------------------------------------
      > Gudmundur Arni Thorisson, B.Sc.
      > Haplotype Map project DCC group leader
      > Steinlab, Cold Spring Harbor Laboratory
      > w-phone#: 516-367-6904
      > w-fax#: 516-367-8389
      > 1 Bungtown Road, Williams Bldg
      > Cold Spring Harbor
      > 11724 New York
      > USA
      >
      >
      > To post a message, send it to: xml-dbms@yahoogroups.com
      > To unsubscribe, send a blank message to: xml-dbms-unsubscribe@yahoogroups.com
      > Or join the newsgroup at: news://news.barkto.com/homeless.xml.xml-dbms
      >
      > Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/

      --
      Ronald Bourret
      Programming, Writing, and Research
      XML, Databases, and Schemas
      http://www.rpbourret.com
    • Gudmundur Arni Thorisson
      Thanks for the pointed to that other driver, Ron, and for quick reply. Here s a more detailed description of the problem, or what we think is a problem: the
      Message 2 of 7 , May 1, 2003
      • 0 Attachment
        Thanks for the pointed to that other driver, Ron, and for quick
        reply. Here's a more detailed description of the problem, or what we
        think is a problem: the default commit mode for XML-DBMS is
        AfterStatement, so it was no surprise to us that we saw when monitoring
        the PostgreSQL server log a sequence of StartTransaction=>SQL
        statement=>CommitTransaction for every insert. (see lines snipped from
        log below). Note that there are up to 5 INSERTs for a given complex
        element mapped to the database. But it does not matter which CommitMode
        we set for XML-DBMS, the log still shows the same
        one-transaction-per-statement phenomenon and the performance is
        constant, thus our speculation that the commit mode is not changing.
        We would at the very least like to see a transaction start with a
        mapped element and commit IF that element as a whole was successfully
        INSERTed. Or, as in our case, we'd like to commit ourselves if our
        x10.000 record set was inserted successfully, otherwise roll back. Let
        me know if you still need more information on the problem. And thanks
        for the tip on that other driver, we're testing that one now. We'll let
        you know if that solves the problem.

        Mummi


        [from /var/log/postgresq.log]
        2003-05-01 11:02:37 [1241] DEBUG: StartTransactionCommand
        2003-05-01 11:02:37 [1241] LOG: query: INSERT INTO "assay"
        ("seq_for_oligo_design", "allele2_combined_seq", "id", "snp_lsid",
        "allele1_combined_seq", "status_code", "prot_lsid",
        "common_combined_seq", "strand", "illumina_strand", "lsid",
        "status_comment") VALUES
        ('CCCAGCTGCACACTCGCCTGGGGGGAGGGAAGGCCTAGGAGAATCCTATCTGGGTGGCACAGCAGAGGAC
        TTCCAGGCAGGATGCTGAAGGATAAATGTGGACACGGGTGAGGGA[G/
        A]GCACCTGTGGAATCTGCAGGGAGGCTGGACTCCAGTCCTGATGTCGGCCCGGGGAACCAAACACCTTGTT
        CAGGGTCCTTGGTCACCACCCTGACTGAGCTGACTGCTGGCCTAGGGTAA' ,
        'GAGTCGAGGTCATATCGTAATGTGGACACGGGTGAGGGAG' , 375577 ,
        'urn:LSID:ncbi.nlm.nih.gov:dbSNP/refSNP:rs1240709:101' ,
        'ACTTCGTCAGTAACGGACAATGTGGACACGGGTGAGGGAA' , 'ok' ,
        'urn:LSID:illumina.hapmap.org:Protocol:Golden_Gate_1.0.0:1' ,
        'CACCTGTGGAATCTGCAGCAAGGGTACGTCCGCGTCATCCGTCTGCCTATAGTGAGTC' ,
        'forward' , 'top' , 'urn:LSID:illumina.hapmap.org:Assay:9:1' , null )
        2003-05-01 11:02:37 [1241] DEBUG: ProcessQuery
        2003-05-01 11:02:37 [1241] DEBUG: CommitTransactionCommand
        2003-05-01 11:02:37 [1241] LOG: duration: 0.001274 sec

        On Thursday, May 1, 2003, at 01:43 America/New_York, Ronald Bourret
        wrote:

        > Since you haven't told me the exact problem you are having, it is
        > difficult for me to tell you whether the problem is caused by XML-DBMS
        > or the driver. (Personally, I hope it's the driver :)
        >
        > The only other Postgres driver I know of (I haven't used it) is:
        >
        > http://jxdbcon.sourceforge.net/
        >
        > One other note: If your application will control transactions directly,
        > be sure to use org.xmlmiddleware.db.ArrayDataSource as your data source
        > and set the commit mode to COMMIT_NONE. This data source allows you to
        > pass Connection(s) to XML-DBMS.
        >
        > -- Ron
        >
        > Gudmundur Arni Thorisson wrote:
        >>
        >> Ron. Myself and Ardavan Kanani, who's posted a few message to this
        >> list recently, are experiencing a problem with the CommitMode property
        >> in XML-DBMS. After looking at the XML-DMBS Java source, we don't think
        >> it's a problem there, but suspect a bug the JDBC driver. Can you
        >> confirm this? Are there other solutitions (other JDBC driver than the
        >> org.postgresql.Driver one) that might help us out? Anyone else had
        >> this
        >> problem?
        >>
        >> Some background: We are looking to A) get CommitMode=AfterDocument
        >> to
        >> work when using the Transfer class to import data via XML-DBMS, in our
        >> test-setup and B) disable transactions completely and control them
        >> ourselves via the calling app as necessary. The latter is important,
        >> since we will probably end up using not the provided commandline
        >> method
        >> of importing data via the Transfer class, but rather import the
        >> desired
        >> XML-records one by one (via DOMToDBMS), all within one transaction
        >> block that may or may not be correspondent with the actual source
        >> XML-file (which we'd parse beforehand anyway). If the database crashes
        >> or e.g. a unique constraint is violated or other error, we'd want to
        >> roll back the whole thing manually and abort the job.
        >> There are also performance issues at stake: we think that by not
        >> having a transaction for every single SQL statement coming from
        >> XML-DBMS, as it is now, we can boost performance significantly. What
        >> is
        >> your experience? We're using PostgreSQL.
        >>
        >> BTW Apart from the aforementioned problem, we think XML-DBMS is
        >> great
        >> and are liking it more and more by day! Cool Stuff, man.
        >>
        >> Mummi, CSHL
        >>
        >> --
        >> ----------------------------------------------------
        >> Gudmundur Arni Thorisson, B.Sc.
        >> Haplotype Map project DCC group leader
        >> Steinlab, Cold Spring Harbor Laboratory
        >> w-phone#: 516-367-6904
        >> w-fax#: 516-367-8389
        >> 1 Bungtown Road, Williams Bldg
        >> Cold Spring Harbor
        >> 11724 New York
        >> USA
        >>
        >>
        >> To post a message, send it to: xml-dbms@yahoogroups.com
        >> To unsubscribe, send a blank message to:
        >> xml-dbms-unsubscribe@yahoogroups.com
        >> Or join the newsgroup at: news://news.barkto.com/homeless.xml.xml-dbms
        >>
        >> Your use of Yahoo! Groups is subject to
        >> http://docs.yahoo.com/info/terms/
        >
        > --
        > Ronald Bourret
        > Programming, Writing, and Research
        > XML, Databases, and Schemas
        > http://www.rpbourret.com
        >
        > ------------------------ Yahoo! Groups Sponsor
        > ---------------------~-->
        > Get 128 Bit SSL Encryption!
        > http://us.click.yahoo.com/W7NydA/hdqFAA/WKnHAA/2U_rlB/TM
        > ---------------------------------------------------------------------
        > ~->
        >
        > To post a message, send it to: xml-dbms@yahoogroups.com
        > To unsubscribe, send a blank message to:
        > xml-dbms-unsubscribe@yahoogroups.com
        > Or join the newsgroup at: news://news.barkto.com/homeless.xml.xml-dbms
        >
        > Your use of Yahoo! Groups is subject to
        > http://docs.yahoo.com/info/terms/
        >
        >
        >


        [Non-text portions of this message have been removed]
      • Ronald Bourret
        I, too, looked at the XML-DBMS code (DOMToDBMS and DataHandlerBase) and don t see any obvious problems. To check if there is a driver bug, please run a trace
        Message 3 of 7 , May 1, 2003
        • 0 Attachment
          I, too, looked at the XML-DBMS code (DOMToDBMS and DataHandlerBase) and
          don't see any obvious problems. To check if there is a driver bug,
          please run a trace on the following program. (You'll need to modify the
          connection information.) This programs inserts rows with auto-commit on
          and off, so you should see commits in different places.

          -- Ron

          Test program
          ============
          import java.sql.*;
          import java.io.*;

          public class dbtest
          {

          public static void main (String[] argv)
          {
          try
          {
          // Get the connection

          String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
          String url = "jdbc:odbc:xmldbms";
          String user = "ron";
          String password = "password";
          Class.forName(driver);
          Connection conn = DriverManager.getConnection(url, user,
          password);

          // Drop and create table A. If an exception is thrown, we
          assume the table
          // didn't initially exist, so we print the error message but
          continue.

          Statement stmt = conn.createStatement();
          try
          {
          stmt.executeUpdate("DROP TABLE A");
          }
          catch (SQLException s)
          {
          s.printStackTrace();
          }
          stmt.executeUpdate("CREATE TABLE A (A1 VARCHAR(10), A2
          VARCHAR(10))");

          // Set transaction auto-commit.

          conn.setAutoCommit(true);

          // Insert stuff. You should see a commit after each statement.

          stmt.executeUpdate("INSERT INTO A (A1, A2) VALUES ('value1',
          'value2')");
          stmt.executeUpdate("INSERT INTO A (A1, A2) VALUES ('value3',
          'value4')");

          // Set transaction auto-commit.

          conn.setAutoCommit(false);

          // Insert stuff. You should see only one commit.

          stmt.executeUpdate("INSERT INTO A (A1, A2) VALUES ('value5',
          'value6')");
          stmt.executeUpdate("INSERT INTO A (A1, A2) VALUES ('value7',
          'value8')");

          conn.commit();

          // Close the connection.

          conn.close();
          }
          catch(Exception e)
          {
          e.printStackTrace();
          }
          }
          }


          Gudmundur Arni Thorisson wrote:
          >
          > Thanks for the pointed to that other driver, Ron, and for quick
          > reply. Here's a more detailed description of the problem, or what we
          > think is a problem: the default commit mode for XML-DBMS is
          > AfterStatement, so it was no surprise to us that we saw when monitoring
          > the PostgreSQL server log a sequence of StartTransaction=>SQL
          > statement=>CommitTransaction for every insert. (see lines snipped from
          > log below). Note that there are up to 5 INSERTs for a given complex
          > element mapped to the database. But it does not matter which CommitMode
          > we set for XML-DBMS, the log still shows the same
          > one-transaction-per-statement phenomenon and the performance is
          > constant, thus our speculation that the commit mode is not changing.
          > We would at the very least like to see a transaction start with a
          > mapped element and commit IF that element as a whole was successfully
          > INSERTed. Or, as in our case, we'd like to commit ourselves if our
          > x10.000 record set was inserted successfully, otherwise roll back. Let
          > me know if you still need more information on the problem. And thanks
          > for the tip on that other driver, we're testing that one now. We'll let
          > you know if that solves the problem.
          >
          > Mummi
          >
          > [from /var/log/postgresq.log]
          > 2003-05-01 11:02:37 [1241] DEBUG: StartTransactionCommand
          > 2003-05-01 11:02:37 [1241] LOG: query: INSERT INTO "assay"
          > ("seq_for_oligo_design", "allele2_combined_seq", "id", "snp_lsid",
          > "allele1_combined_seq", "status_code", "prot_lsid",
          > "common_combined_seq", "strand", "illumina_strand", "lsid",
          > "status_comment") VALUES
          > ('CCCAGCTGCACACTCGCCTGGGGGGAGGGAAGGCCTAGGAGAATCCTATCTGGGTGGCACAGCAGAGGAC
          > TTCCAGGCAGGATGCTGAAGGATAAATGTGGACACGGGTGAGGGA[G/
          > A]GCACCTGTGGAATCTGCAGGGAGGCTGGACTCCAGTCCTGATGTCGGCCCGGGGAACCAAACACCTTGTT
          > CAGGGTCCTTGGTCACCACCCTGACTGAGCTGACTGCTGGCCTAGGGTAA' ,
          > 'GAGTCGAGGTCATATCGTAATGTGGACACGGGTGAGGGAG' , 375577 ,
          > 'urn:LSID:ncbi.nlm.nih.gov:dbSNP/refSNP:rs1240709:101' ,
          > 'ACTTCGTCAGTAACGGACAATGTGGACACGGGTGAGGGAA' , 'ok' ,
          > 'urn:LSID:illumina.hapmap.org:Protocol:Golden_Gate_1.0.0:1' ,
          > 'CACCTGTGGAATCTGCAGCAAGGGTACGTCCGCGTCATCCGTCTGCCTATAGTGAGTC' ,
          > 'forward' , 'top' , 'urn:LSID:illumina.hapmap.org:Assay:9:1' , null )
          > 2003-05-01 11:02:37 [1241] DEBUG: ProcessQuery
          > 2003-05-01 11:02:37 [1241] DEBUG: CommitTransactionCommand
          > 2003-05-01 11:02:37 [1241] LOG: duration: 0.001274 sec
        • ardavan_kanani
          Ron, Here is the output from the dbtest program: 2003-05-01 14:00:01 [27335] DEBUG: BackendStartup: forked pid=9475 socket=8 2003-05-01 14:00:01 [9475]
          Message 4 of 7 , May 1, 2003
          • 0 Attachment
            Ron,

            Here is the output from the dbtest program:

            2003-05-01 14:00:01 [27335] DEBUG: BackendStartup: forked pid=9475
            socket=8
            2003-05-01 14:00:01 [9475] LOG: connection received: host=127.0.0.1
            port=49954
            2003-05-01 14:00:01 [9475] LOG: connection authorized: user=kanani
            database=kanani
            2003-05-01 14:00:01 [9475] DEBUG: /usr/local/pgsql/bin/postmaster
            child[9475]: starting with (
            2003-05-01 14:00:01 [9475] DEBUG: postgres
            2003-05-01 14:00:01 [9475] DEBUG: -v131072
            2003-05-01 14:00:01 [9475] DEBUG: -p
            2003-05-01 14:00:01 [9475] DEBUG: kanani
            2003-05-01 14:00:01 [9475] DEBUG: )
            2003-05-01 14:00:01 [9475] DEBUG: InitPostgres
            2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
            2003-05-01 14:00:01 [9475] LOG: query: set datestyle to 'ISO';
            select version(), case when pg_encoding_to_char(1) = 'SQL_ASCII' then
            'UNKNOWN' else getdatabaseencoding() end;
            2003-05-01 14:00:01 [9475] DEBUG: ProcessUtility
            2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand
            2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
            2003-05-01 14:00:01 [9475] DEBUG: ProcessQuery
            2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand
            2003-05-01 14:00:01 [9475] LOG: duration: 0.007665 sec
            2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
            2003-05-01 14:00:01 [9475] LOG: query: set client_encoding =
            'UNICODE'; show autocommit
            2003-05-01 14:00:01 [9475] DEBUG: ProcessUtility
            2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand
            2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
            2003-05-01 14:00:01 [9475] DEBUG: ProcessUtility
            2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand
            2003-05-01 14:00:01 [9475] LOG: duration: 0.000458 sec
            2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
            2003-05-01 14:00:01 [9475] LOG: query: set autocommit = on; commit;
            2003-05-01 14:00:01 [9475] DEBUG: ProcessUtility
            2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand
            2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
            2003-05-01 14:00:01 [9475] DEBUG: ProcessUtility
            2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand
            2003-05-01 14:00:01 [9475] LOG: duration: 0.001215 sec
            2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
            2003-05-01 14:00:01 [9475] LOG: query: DROP TABLE A
            2003-05-01 14:00:01 [9475] DEBUG: ProcessUtility
            2003-05-01 14:00:01 [9475] DEBUG: Drop auto-cascades to type a
            2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand
            2003-05-01 14:00:01 [9475] LOG: duration: 0.012808 sec
            2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
            2003-05-01 14:00:01 [9475] LOG: query: CREATE TABLE A (A1
            VARCHAR(10), A2 VARCHAR(10))
            2003-05-01 14:00:01 [9475] DEBUG: ProcessUtility
            2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand
            2003-05-01 14:00:01 [9475] LOG: duration: 0.009658 sec
            2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
            2003-05-01 14:00:01 [9475] LOG: query: INSERT INTO A (A1, A2)
            VALUES ('value1', 'value2')
            2003-05-01 14:00:01 [9475] DEBUG: ProcessQuery
            2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand
            2003-05-01 14:00:01 [9475] LOG: duration: 0.002792 sec
            2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
            2003-05-01 14:00:01 [9475] LOG: query: INSERT INTO A (A1, A2)
            VALUES ('value3', 'value4')
            2003-05-01 14:00:01 [9475] DEBUG: ProcessQuery
            2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand
            2003-05-01 14:00:01 [9475] LOG: duration: 0.001269 sec
            2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
            2003-05-01 14:00:01 [9475] LOG: query: set autocommit = off;
            2003-05-01 14:00:01 [9475] DEBUG: ProcessUtility
            2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand
            2003-05-01 14:00:01 [9475] LOG: duration: 0.000657 sec
            2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
            2003-05-01 14:00:01 [9475] LOG: query: INSERT INTO A (A1, A2)
            VALUES ('value5', 'value6')
            2003-05-01 14:00:01 [9475] DEBUG: ProcessQuery
            2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand
            2003-05-01 14:00:01 [9475] LOG: duration: 0.001092 sec
            2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
            2003-05-01 14:00:01 [9475] LOG: query: INSERT INTO A (A1, A2)
            VALUES ('value7', 'value8')
            2003-05-01 14:00:01 [9475] DEBUG: ProcessQuery
            2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand
            2003-05-01 14:00:01 [9475] LOG: duration: 0.000990 sec
            2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
            2003-05-01 14:00:01 [9475] LOG: query: commit;
            2003-05-01 14:00:01 [9475] DEBUG: ProcessUtility
            2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand
            2003-05-01 14:00:01 [9475] LOG: duration: 0.001312 sec
            2003-05-01 14:00:01 [9475] DEBUG: proc_exit(0)
            2003-05-01 14:00:01 [9475] DEBUG: shmem_exit(0)
            2003-05-01 14:00:01 [9475] DEBUG: exit(0)
            2003-05-01 14:00:01 [27335] DEBUG: reaping dead processes
            2003-05-01 14:00:01 [27335] DEBUG: child process (pid 9475) exited
            with exit code 0

            --- In xml-dbms@yahoogroups.com, Ronald Bourret <rpbourret@r...>
            wrote:
            > I, too, looked at the XML-DBMS code (DOMToDBMS and DataHandlerBase)
            and
            > don't see any obvious problems. To check if there is a driver bug,
            > please run a trace on the following program. (You'll need to modify
            the
            > connection information.) This programs inserts rows with auto-commit
            on
            > and off, so you should see commits in different places.
            >
            > -- Ron
            >
            > Test program
            > ============
            > import java.sql.*;
            > import java.io.*;
            >
            > public class dbtest
            > {
            >
            > public static void main (String[] argv)
            > {
            > try
            > {
            > // Get the connection
            >
            > String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
            > String url = "jdbc:odbc:xmldbms";
            > String user = "ron";
            > String password = "password";
            > Class.forName(driver);
            > Connection conn = DriverManager.getConnection(url, user,
            > password);
            >
            > // Drop and create table A. If an exception is thrown, we
            > assume the table
            > // didn't initially exist, so we print the error message
            but
            > continue.
            >
            > Statement stmt = conn.createStatement();
            > try
            > {
            > stmt.executeUpdate("DROP TABLE A");
            > }
            > catch (SQLException s)
            > {
            > s.printStackTrace();
            > }
            > stmt.executeUpdate("CREATE TABLE A (A1 VARCHAR(10), A2
            > VARCHAR(10))");
            >
            > // Set transaction auto-commit.
            >
            > conn.setAutoCommit(true);
            >
            > // Insert stuff. You should see a commit after each
            statement.
            >
            > stmt.executeUpdate("INSERT INTO A (A1, A2) VALUES ('value1
            ',
            > 'value2')");
            > stmt.executeUpdate("INSERT INTO A (A1, A2) VALUES ('value3
            ',
            > 'value4')");
            >
            > // Set transaction auto-commit.
            >
            > conn.setAutoCommit(false);
            >
            > // Insert stuff. You should see only one commit.
            >
            > stmt.executeUpdate("INSERT INTO A (A1, A2) VALUES ('value5
            ',
            > 'value6')");
            > stmt.executeUpdate("INSERT INTO A (A1, A2) VALUES ('value7
            ',
            > 'value8')");
            >
            > conn.commit();
            >
            > // Close the connection.
            >
            > conn.close();
            > }
            > catch(Exception e)
            > {
            > e.printStackTrace();
            > }
            > }
            > }
            >
            >
            > Gudmundur Arni Thorisson wrote:
            > >
            > > Thanks for the pointed to that other driver, Ron, and for quick
            > > reply. Here's a more detailed description of the problem, or what
            we
            > > think is a problem: the default commit mode for XML-DBMS is
            > > AfterStatement, so it was no surprise to us that we saw when
            monitoring
            > > the PostgreSQL server log a sequence of StartTransaction=>SQL
            > > statement=>CommitTransaction for every insert. (see lines snipped
            from
            > > log below). Note that there are up to 5 INSERTs for a given
            complex
            > > element mapped to the database. But it does not matter which
            CommitMode
            > > we set for XML-DBMS, the log still shows the same
            > > one-transaction-per-statement phenomenon and the performance is
            > > constant, thus our speculation that the commit mode is not
            changing.
            > > We would at the very least like to see a transaction start with
            a
            > > mapped element and commit IF that element as a whole was
            successfully
            > > INSERTed. Or, as in our case, we'd like to commit ourselves if our
            > > x10.000 record set was inserted successfully, otherwise roll back.
            Let
            > > me know if you still need more information on the problem. And
            thanks
            > > for the tip on that other driver, we're testing that one now. We
            'll let
            > > you know if that solves the problem.
            > >
            > > Mummi
            > >
            > > [from /var/log/postgresq.log]
            > > 2003-05-01 11:02:37 [1241] DEBUG: StartTransactionCommand
            > > 2003-05-01 11:02:37 [1241] LOG: query: INSERT INTO "assay"
            > > ("seq_for_oligo_design", "allele2_combined_seq", "id", "snp_lsid",
            > > "allele1_combined_seq", "status_code", "prot_lsid",
            > > "common_combined_seq", "strand", "illumina_strand", "lsid",
            > > "status_comment") VALUES
            > > (
            'CCCAGCTGCACACTCGCCTGGGGGGAGGGAAGGCCTAGGAGAATCCTATCTGGGTGGCACAGCAGAGG
            AC
            > > TTCCAGGCAGGATGCTGAAGGATAAATGTGGACACGGGTGAGGGA[G/
            > > A]
            GCACCTGTGGAATCTGCAGGGAGGCTGGACTCCAGTCCTGATGTCGGCCCGGGGAACCAAACACCTTGTT
            > > CAGGGTCCTTGGTCACCACCCTGACTGAGCTGACTGCTGGCCTAGGGTAA' ,
            > > 'GAGTCGAGGTCATATCGTAATGTGGACACGGGTGAGGGAG' , 375577 ,
            > > 'urn:LSID:ncbi.nlm.nih.gov:dbSNP/refSNP:rs1240709:101' ,
            > > 'ACTTCGTCAGTAACGGACAATGTGGACACGGGTGAGGGAA' , 'ok' ,
            > > 'urn:LSID:illumina.hapmap.org:Protocol:Golden_Gate_1.0.0:1' ,
            > > 'CACCTGTGGAATCTGCAGCAAGGGTACGTCCGCGTCATCCGTCTGCCTATAGTGAGTC' ,
            > > 'forward' , 'top' , 'urn:LSID:illumina.hapmap.org:Assay:9:1' ,
            null )
            > > 2003-05-01 11:02:37 [1241] DEBUG: ProcessQuery
            > > 2003-05-01 11:02:37 [1241] DEBUG: CommitTransactionCommand
            > > 2003-05-01 11:02:37 [1241] LOG: duration: 0.001274 sec
          • Ronald Bourret
            This appears to be a driver bug. Regardless of auto-commit mode, drivers are always required to start a transaction when a new transaction is needed. In
            Message 5 of 7 , May 1, 2003
            • 0 Attachment
              This appears to be a driver bug. Regardless of auto-commit mode, drivers
              are always required to start a transaction when a new transaction is
              needed. In auto-commit mode, drivers are required to automatically
              commit each statement that is executed. When auto-commit is off, the
              driver should never send a commit to the database.

              Based on the following part of the trace (annotated below), it appears
              that the driver is sending commits even when auto-commit is off. You
              need to fix the driver (if it is open source), submit a bug to the
              driver vendor, or use a different driver.

              -- Ron

              Annotated log
              =============

              // Set auto-commit to true. This is apparently done by the driver, as
              // my code does not call auto-commit here (before dropping/creating
              // the table).

              > 2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
              > 2003-05-01 14:00:01 [9475] LOG: query: set autocommit = on; commit;
              > 2003-05-01 14:00:01 [9475] DEBUG: ProcessUtility
              > 2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand

              // Trace of drop/create table snipped

              // Note that the call to set auto-commit to true was not executed.
              // Presumably, the driver saw this call, noticed that auto-commit
              // was already on, and optimized it out.

              // First insert. Note the explicit commands to start and commit
              // the transaction. The commit is as expected, since auto-commit is on.

              > 2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
              > 2003-05-01 14:00:01 [9475] LOG: query: INSERT INTO A (A1, A2)
              > VALUES ('value1', 'value2')
              > 2003-05-01 14:00:01 [9475] DEBUG: ProcessQuery
              > 2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand
              > 2003-05-01 14:00:01 [9475] LOG: duration: 0.002792 sec

              // Second insert. Again, the transaction is explicitly started and
              // committed, as expected.

              > 2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
              > 2003-05-01 14:00:01 [9475] LOG: query: INSERT INTO A (A1, A2)
              > VALUES ('value3', 'value4')
              > 2003-05-01 14:00:01 [9475] DEBUG: ProcessQuery
              > 2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand
              > 2003-05-01 14:00:01 [9475] LOG: duration: 0.001269 sec

              // Process the call to set auto-commit off.

              > 2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
              > 2003-05-01 14:00:01 [9475] LOG: query: set autocommit = off;
              > 2003-05-01 14:00:01 [9475] DEBUG: ProcessUtility
              > 2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand
              > 2003-05-01 14:00:01 [9475] LOG: duration: 0.000657 sec

              // Here's where the problems are. This is the third insert. At this
              // point, a transaction is started (correct), but commit is called
              // after the statement is executed (WRONG).

              > 2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
              > 2003-05-01 14:00:01 [9475] LOG: query: INSERT INTO A (A1, A2)
              > VALUES ('value5', 'value6')
              > 2003-05-01 14:00:01 [9475] DEBUG: ProcessQuery
              > 2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand
              > 2003-05-01 14:00:01 [9475] LOG: duration: 0.001092 sec

              // A continuation of the above problems. A new transaction is started
              // (correct given the above incorrect commit; otherwise, it wouldn't
              // be necessary), and commit is called after the statement is
              // executed (WRONG).

              > 2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
              > 2003-05-01 14:00:01 [9475] LOG: query: INSERT INTO A (A1, A2)
              > VALUES ('value7', 'value8')
              > 2003-05-01 14:00:01 [9475] DEBUG: ProcessQuery
              > 2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand
              > 2003-05-01 14:00:01 [9475] LOG: duration: 0.000990 sec

              // My call to commit is executed. Note that this is useless, as it is
              // encapsulated in its own transaction.

              > 2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
              > 2003-05-01 14:00:01 [9475] LOG: query: commit;
              > 2003-05-01 14:00:01 [9475] DEBUG: ProcessUtility
              > 2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand
              > 2003-05-01 14:00:01 [9475] LOG: duration: 0.001312 sec


              ardavan_kanani wrote:
              >
              > Ron,
              >
              > Here is the output from the dbtest program:
            • Gudmundur Arni Thorisson
              OK. Thanks a lot for the help, Ron, we appreciate it. We ll let you know how it goes with the other driver you pointed out to us, once Ardi gets that thing to
              Message 6 of 7 , May 1, 2003
              • 0 Attachment
                OK. Thanks a lot for the help, Ron, we appreciate it. We'll let you
                know how it goes with the other driver you pointed out to us, once Ardi
                gets that thing to work in our setup.


                Mummi, CSHL

                On Thursday, May 1, 2003, at 15:36 America/New_York, Ronald Bourret
                wrote:

                > This appears to be a driver bug. Regardless of auto-commit mode,
                > drivers
                > are always required to start a transaction when a new transaction is
                > needed. In auto-commit mode, drivers are required to automatically
                > commit each statement that is executed. When auto-commit is off, the
                > driver should never send a commit to the database.
                >
                > Based on the following part of the trace (annotated below), it appears
                > that the driver is sending commits even when auto-commit is off. You
                > need to fix the driver (if it is open source), submit a bug to the
                > driver vendor, or use a different driver.
                >
                > -- Ron
                >
                > Annotated log
                > =============
                >
                > // Set auto-commit to true. This is apparently done by the driver, as
                > // my code does not call auto-commit here (before dropping/creating
                > // the table).
                >
                >> 2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
                >> 2003-05-01 14:00:01 [9475] LOG: query: set autocommit = on; commit;
                >> 2003-05-01 14:00:01 [9475] DEBUG: ProcessUtility
                >> 2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand
                >
                > // Trace of drop/create table snipped
                >
                > // Note that the call to set auto-commit to true was not executed.
                > // Presumably, the driver saw this call, noticed that auto-commit
                > // was already on, and optimized it out.
                >
                > // First insert. Note the explicit commands to start and commit
                > // the transaction. The commit is as expected, since auto-commit is on.
                >
                >> 2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
                >> 2003-05-01 14:00:01 [9475] LOG: query: INSERT INTO A (A1, A2)
                >> VALUES ('value1', 'value2')
                >> 2003-05-01 14:00:01 [9475] DEBUG: ProcessQuery
                >> 2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand
                >> 2003-05-01 14:00:01 [9475] LOG: duration: 0.002792 sec
                >
                > // Second insert. Again, the transaction is explicitly started and
                > // committed, as expected.
                >
                >> 2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
                >> 2003-05-01 14:00:01 [9475] LOG: query: INSERT INTO A (A1, A2)
                >> VALUES ('value3', 'value4')
                >> 2003-05-01 14:00:01 [9475] DEBUG: ProcessQuery
                >> 2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand
                >> 2003-05-01 14:00:01 [9475] LOG: duration: 0.001269 sec
                >
                > // Process the call to set auto-commit off.
                >
                >> 2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
                >> 2003-05-01 14:00:01 [9475] LOG: query: set autocommit = off;
                >> 2003-05-01 14:00:01 [9475] DEBUG: ProcessUtility
                >> 2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand
                >> 2003-05-01 14:00:01 [9475] LOG: duration: 0.000657 sec
                >
                > // Here's where the problems are. This is the third insert. At this
                > // point, a transaction is started (correct), but commit is called
                > // after the statement is executed (WRONG).
                >
                >> 2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
                >> 2003-05-01 14:00:01 [9475] LOG: query: INSERT INTO A (A1, A2)
                >> VALUES ('value5', 'value6')
                >> 2003-05-01 14:00:01 [9475] DEBUG: ProcessQuery
                >> 2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand
                >> 2003-05-01 14:00:01 [9475] LOG: duration: 0.001092 sec
                >
                > // A continuation of the above problems. A new transaction is started
                > // (correct given the above incorrect commit; otherwise, it wouldn't
                > // be necessary), and commit is called after the statement is
                > // executed (WRONG).
                >
                >> 2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
                >> 2003-05-01 14:00:01 [9475] LOG: query: INSERT INTO A (A1, A2)
                >> VALUES ('value7', 'value8')
                >> 2003-05-01 14:00:01 [9475] DEBUG: ProcessQuery
                >> 2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand
                >> 2003-05-01 14:00:01 [9475] LOG: duration: 0.000990 sec
                >
                > // My call to commit is executed. Note that this is useless, as it is
                > // encapsulated in its own transaction.
                >
                >> 2003-05-01 14:00:01 [9475] DEBUG: StartTransactionCommand
                >> 2003-05-01 14:00:01 [9475] LOG: query: commit;
                >> 2003-05-01 14:00:01 [9475] DEBUG: ProcessUtility
                >> 2003-05-01 14:00:01 [9475] DEBUG: CommitTransactionCommand
                >> 2003-05-01 14:00:01 [9475] LOG: duration: 0.001312 sec
                >
                >
                > ardavan_kanani wrote:
                >>
                >> Ron,
                >>
                >> Here is the output from the dbtest program:
                >
                > ------------------------ Yahoo! Groups Sponsor
                > ---------------------~-->
                > Get A Free Psychic Reading!
                > Your Online Answer To Life's Important Questions.
                > http://us.click.yahoo.com/cjB9SD/od7FAA/uetFAA/2U_rlB/TM
                > ---------------------------------------------------------------------
                > ~->
                >
                > To post a message, send it to: xml-dbms@yahoogroups.com
                > To unsubscribe, send a blank message to:
                > xml-dbms-unsubscribe@yahoogroups.com
                > Or join the newsgroup at: news://news.barkto.com/homeless.xml.xml-dbms
                >
                > Your use of Yahoo! Groups is subject to
                > http://docs.yahoo.com/info/terms/
                >
                >
                >
              Your message has been successfully submitted and would be delivered to recipients shortly.