4 Replies Latest reply on Nov 26, 2008 12:07 PM by wmiddendorff

    Disabling MySQL autocommit

    Ashburton
      I'm relatively new to using cftransaction but I've come across something that I can't explain. I added "SET autocommit=0;" to the connection string of my database in CFAdmin for a MySQL 4.1 database and restarted the service. The db is using the native MySQL 4/5 driver.

      In the code attached below and found that if I remove the <cfif commitData> logic the data is still written to the tables. If I force an db error on the second insert the data appears to roll back and not write to either table. If I remove the cftransaction rollback statement it will write the first insert but not write the second.

      So, even with autocommit switched off (if I actually do have it switched off) does cfquery or something else automatically commit the data if cfcatch does not catch an error?
        • 1. Re: Disabling MySQL autocommit
          Level 7
          Ashburton wrote:
          > I'm relatively new to using cftransaction but I've come across something that I
          > can't explain. I added "SET autocommit=0;" to the connection string of my
          > database in CFAdmin for a MySQL 4.1 database and restarted the service. The db
          > is using the native MySQL 4/5 driver.
          >
          > In the code attached below and found that if I remove the <cfif commitData>
          > logic the data is still written to the tables. If I force an db error on the
          > second insert the data appears to roll back and not write to either table. If I
          > remove the cftransaction rollback statement it will write the first insert but
          > not write the second.
          >
          > So, even with autocommit switched off (if I actually do have it switched off)

          I think that autocommit is switched off only for the first transaction.
          Here's a log of the commands submitted to the MySQL server for a
          transaction:
          1 Query SET NAMES utf8
          1 Query SET character_set_results = NULL
          1 Query SET autocommit=1
          1 Query SET
          sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUT O_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'
          1 Query SET SESSION TRANSACTION ISOLATION LEVEL
          READ COMMITTED
          1 Query SET autocommit=0
          1 Query SELECT @@session.tx_isolation
          1 Query QUERY1
          1 Query QUERY2
          1 Query commit
          1 Query SET autocommit=1
          1 Query SET SESSION TRANSACTION ISOLATION LEVEL
          READ COMMITTED
          1 Query SET autocommit=1
          1 Query SET SESSION TRANSACTION ISOLATION LEVEL
          READ COMMITTED

          You can see that autocommit=0 is executed once at the start of the
          connection and it's set to 1 after the transaction is commited.

          This is the code that I used (CF performs an automatic commit when it
          reaches the </cftransaction> tag, regardless of the value of the
          autocommit variable):
          <cftransaction action = "begin">
          <cfquery datasource="ds">
          QUERY1
          </cfquery>
          <cfquery datasource="ds">
          QUERY2
          </cfquery>
          </cftransaction>

          --
          Mack
          • 2. Re: Disabling MySQL autocommit
            Ashburton Level 1
            Thanks for the feedback Mack. It made me wonder if autocommit being switched back on would affect rollback functionality. I went back and modified the code with three inserts to see if it would still work for the whole thing if the third insert threw an error and it did.

            My big concern was whether or not the commits were actually working even if the <cftransaction action="commit" /> was missing at the end.
            • 3. Re: Disabling MySQL autocommit
              Level 7
              Ashburton wrote:
              > Thanks for the feedback Mack. It made me wonder if autocommit being switched
              > back on would affect rollback functionality. I went back and modified the code
              > with three inserts to see if it would still work for the whole thing if the
              > third insert threw an error and it did.
              >
              > My big concern was whether or not the commits were actually working even if
              > the <cftransaction action="commit" /> was missing at the end.

              I think that CF autocommits the transaction if it reaches
              </cftransaction> without errors.

              --
              Mack
              • 4. Re: Disabling MySQL autocommit
                wmiddendorff
                This is exactly correct Mack, the closing of a cftransaction tag is the same as <cftransaction action="commit">.