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?

[Newsgroup_User]

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

[Ashburton]

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.

[Newsgroup_User]

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

[Guest]

This is exactly correct Mack, the closing of a cftransaction tag is the same as <cftransaction action="commit">.