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

Problems with CommitMode: JDBC driver bug?

Expand Messages
  • Gudmundur Arni Thorisson
    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
    Message 1 of 7 , Apr 30, 2003
      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
    • 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 2 of 7 , Apr 30, 2003
        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 3 of 7 , May 1, 2003
          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 4 of 7 , May 1, 2003
            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 5 of 7 , May 1, 2003
              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 6 of 7 , May 1, 2003
                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 7 of 7 , May 1, 2003
                  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.