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:

  1. Lets open a Session-1 to MySQL 5.2 server.
  2. 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.
  3. Populate both the tables and with 2 rows and commit the work.
  4. 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.
  5. Now open another Session-2; and issue the same query.
  6. 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;
Query OK, 0 rows affected (0.00 sec)

mysql> create table tab_falcon(c1 int) ENGINE=FALCON;
Query OK, 0 rows affected (0.25 sec)

mysql> insert into tab_falcon values (10),(20),(30),(20);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

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

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

mysql> select c1 from tab_falcon for update;
+——+
| c1 |
+——+
| 10 |
| 20 |
| 30 |
| 20 |
+——+
4 rows in set (0.00 sec)

 Sesson-2

mysql> select * from tab_falcon for update;
+——+
| c1 |
+——+
| 10 |
| 20 |
| 30 |
| 20 |
+——+
4 rows in set (0.00 sec)

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.