Set the Dbms Isolation Level

For safe operation, the shim program requires that the sql server run with the ANSI sql isolation level set to SERIALIZABLE in order to prevent phantom reads, that is that the mysql default sql isolation level be changed from the default and oxymoronic level of REPEATABLE-READ to the safer level of SERIALIZABLE. This is necessary to ensure that transactions are ACID, that is atomic, consistent, isolated, and durable. The trading-shim checks for this property, and, by design, will not start without it.

There are various ways to control the isolation level; for mysql it may be set: at the session level; on the server command line; via a command, e.g., ``set global transaction isolation level serializable;'' as by past versions of the setup script; and in the server configuration file.

Partial change, on a per session basis, misses the point that all database application write access should be safe and transactional, so that the data can be trusted, and so about which no more. Command operation unfortunately does not persist, so that although placing the command in the setup script solved the problem temporarily, the change was lost after the first server restart. The apparently most straight-forward approach might seem to be to edit the mysqld command line in order to add the argument -sql-mode=ANSI, so ensuring conformance with a number of ANSI sql requirements, including the isolation level. 3.1Most systems start the mysql server automatically, when the system starts up, and so doing this would require changing the init or startup script for the mysqld program. Since these scripts vary from one platform to the next, and are somewhat opaque even on Linux, this leaves the last option, modification of the server configuration file.

Edit the /etc/my.cnf file to insert the line transaction-isolation = SERIALIZABLE in the mysql stanza. Figure 3.8 gives a patch against that file, and in fact the patch may be found as the file sql/mysql.iso.patch in the distribution. Note that applying this change will require root authority.

Figure 3.8: Changing my.cnf
\begin{verbatim}*** my.cnf-OLD 2007-04-20 16:10:13.000000...

Bill Pippin 2010-01-14