Today I downloaded and installed MySQL 5.2 alpha release on one of my old Dell box. I get to play a bit with Falcon engine type. For most of the cases; the Falcon seemed to be as good as InnoDB type. But I noticed the following bug in Falcon; where as the same one works fine with InnoDB.
Here is the simple scenario:
- Lets open a Session-1 to MySQL 5.2 server.
- On Session-1; create two tables namely tab_falcon and tab_innodb each with a single int column of Falcon and InnoDB engine types respectively.
- Populate both the tables and with 2 rows and commit the work.
- On the same Session-1; set autocommit to false and issue the query “select * from tab_falcon for update“. You can see that the select returns total rows that the table has.
- Now open another Session-2; and issue the same query.
- The Session-2 should wait till Session-1 actually commits or rollbacks or unlocks the table as FOR UPDATE clause is used which in-turn should do a exclusive lock on the table; but in case of Falcon; you get the results back instead of waiting for the table lock to be released; where as with InnoDB table; you get to wait.
|
Session-1
mysql> set autocommit=1; mysql> create table tab_falcon(c1 int) ENGINE=FALCON; mysql> insert into tab_falcon values (10),(20),(30),(20); mysql> commit; mysql> set autocommit=0; mysql> select c1 from tab_falcon for update; |
Sesson-2
mysql> select * from tab_falcon for update; |
But according to MySQL Falcon locks; I tried the UPDATE statements; and I can see that the session 2 actually waits when there was an update on session 1 on the same row. That means; even in the above case; it has to wait.