MySQL 5.1 Cluster guide error on InnoDB

Writing by corra on Thursday, 13 of March , 2008 at 2:24 pm

In MySQL 5.1 Cluster Certification Study Guide at page 151 there’s the following sentence: “InnoDB operates with the REPEATABLE READ isolation level, which still allows phantom reads but suppresses non-repeatable reads”.

The sentence is wrong: the REPEATABLE READ isolation level for InnoDB engine doesn’t allow phantom reads.

In some database systems, REPEATABLE READ isolation level allows phantoms, such that if another transaction inserts new rows in the interval between the SELECT statements, the second SELECT will see them. This is not true for InnoDB; phantoms do not occur for the REPEATABLE READ level. SERIALIZABLE isolation level is similar to REPEATABLE READ with the additional restriction that rows selected by one transaction cannot be changed by another until the first transaction finishes. (as stated by MySQL 5 Certification Guide)

Here is an example to demonstrate such a behaviour.

To distinguish two different sessions I use two different prompts: s1> and s2> setting the mysql’s PROMPT statement.

Create a table on s1 and disable autocommit mode to begin a new transaction:

mysql> prompt s1> 
PROMPT set to 's1> '
s1> 

s1> create table t_idb( id int) engine=innodb;
Query OK, 0 rows affected (0.10 sec)

s1> set autocommit=OFF;
Query OK, 0 rows affected (0.00 sec)

Disable autocommit mode on s2 too to begin a new transaction

mysql> prompt s2> 
PROMPT set to 's2> '

s2> set autocommit=OFF;
Query OK, 0 rows affected (0.00 sec)

Insert some values on s1

s1> insert into t_idb values(1);
Query OK, 1 row affected (0.00 sec)

s1> insert into t_idb values(2);
Query OK, 1 row affected (0.00 sec)

Now, on s2 we won’t be able to see these values because s1 is still in the middle of the transaction (not yet committed). For example, this wouldn’t be true in the case of READ UNCOMMITTED mode.

s2> select * from t_idb;
Empty set (0.00 sec)

Now, we commit the transaction on s1.

s1> commit;
Query OK, 0 rows affected (0.01 sec)

Read again the table on s2.

s2> select * from t_idb;
Empty set (0.00 sec)

s2 is still in the middle of his transaction and thus it doesn’t see committed inserts by the another one. This is in contrast with the sentence on the Cluster guide.

s2 will be able to see the values inserted by s1 only after his transaction will be finished (committed or rollbacked)

s2> commit;
Query OK, 0 rows affected (0.00 sec)

s2> select * from t_idb;
+------+
| id   |
+------+
|    1 | 
|    2 | 
+------+
2 rows in set (0.00 sec)

Comments (5) Category: MySQLen

Name (required)

Mail (will not be published) (required)

Website

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>