Description:
To set the transaction isolation level we have two options:
1) At boot time we can pass the --transaction-isolation option to mysqld or place it in my.cnf:
[mysqld]
...
transaction-isolation = READ-UNCOMMITTED
2) In runtime we can set the tx_isolation system variable:
mysql> SET tx_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
+-----------------------+----------------+
| @@GLOBAL.tx_isolation | @@tx_isolation |
+-----------------------+----------------+
| READ-UNCOMMITTED | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE "%isolation";
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| tx_isolation | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)
NOTE: One would expect the variable names to be the same so that either of the following commands would work:
1)[mysqld]
...
tx_isolation = READ-UNCOMMITTED
2)mysql> SET transaction-isolation = 'READ-COMMITTED';
Why are the variable names different when the most common case in mysqld is to have the same name for options and system vars (whenever possible).
How to repeat:
Try to set isolation levels with either of:
1)[mysqld]
...
tx_isolation = READ-UNCOMMITTED
2) mysql> SET transaction-isolation = 'READ-COMMITTED';
Suggested fix:
Perhaps the variable/options names can be made consistent, perhaps not. In any case, users should be alerted to this "gotcha" in the docs for:
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_tx_isolation
Adding a simple warning like the following can prevent confusion:
"To set the isolation level in the my.cnf options file or pass it at runtime to mysqld, please use transaction-isolation (URL link to http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_transaction-isola..."
