MySQL and Coldfusion Isolation Types

I was working a locking and speed problem with our MySQL Servers today and found something interesting you may want to have a look at.

MySQL defaults its tables to MyISAM. In order to create Foreign Key relationships, the tables must be changed to InnoDB. The problem with InnoDB is that by default the isolation level for the tables are "REPEATABLE-READ".

In reading the documentation for CF on cftransaction tags, adobe says that "REPEATABLE-READ" is the same as read-committed, except that rows in the recordset are exclusively locked until the transaction completes. Due to high overhead, Macromedia does not recommend this isolation level for normal database access..

The recommended isolation level is "READ-COMMITTED".

Now you can go through ever query statement and encase each with a cftransaction tag specifying the isolation type OR you can set it as the general isolation type for MySQL.

This is how to change the default isolation level for MySQL. It's simple and fast and requires a quick MySQL restart.

1. Run the following SQL to see what the default Isolation level was for the DB.
SELECT @@global.tx_isolation;
SELECT @@tx_isolation;

If you got back anything but "READ-COMMITTED", continue on to step 2.

2. I created a text file called my.cnf.
------------------------------
File contents
=====================
[mysqld]
transaction-isolation = READ-COMMITTED
=====================
Note: Do not use the dashes or "File contents". Just the portion between the "=" signs. If you have a my.cnf, just add "transaction-isolation = READ-COMMITTED" to it.

3. Uploaded the file (my.cnf) to /etc/ on the server (Linux)
4. Restart MySQL /etc/init.d/mysql restart
5. Re-run the following SQL
SELECT @@global.tx_isolation ;
SELECT @@tx_isolation;

You should get back "READ-COMMITTED"

Boom! Game on! Now your servers will be running what Adobe recommends for CF.

When I did this, immediately I saw huge performance gains from my production box!

Hope this helps!

Steve H.
www.fusecast.com
Web hosting, design and development services.