MySQL Bugs: #26826: Falcon engine does not lock properly

Bug #26826	Falcon engine does not lock properly
Submitted:	3 Mar 2007 19:58	Modified:	20 Apr 2007 14:47
Reporter:	Venu Anuganti	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S2 (Serious)
Version:	5.2	OS:	Any
Assigned to:	Jim Starkey	CPU Architecture:	Any
Tags:	falcon, locking, select for update

Description:
I downloaded MySQL 5.2 and playing a bit with Falcon engine; and found the following case where a exclusive-lock is expected and it is not doing so. But Innodb does this along with any other databases like Oracle etc.

How to repeat:
Here is the steps to reproduce the bug.

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. 
Populate both the tables and with 2 rows and commit the work. 
3. 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. 
4. Now open another Session-2; and issue the same query. 
5. 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 immediately instead of waiting for the table lock to be released; where as with InnoDB table type; you get to wait.

Here is the actual output:

++++++++++++++++++++++
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) 

++++++++++++++++++++++
Sesson-2
++++++++++++++++++++++

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

it should be the case for all platforms and not specific to Windows

Select for update isn't working yet - should be pushed soon

SELECT FOR UPDATE works now:

Say, we have two connection called T1 and T2:

T1:
CREATE TABLE t1 (a int) Engine Falcon;
INSERT INTO t1 VALUES (1), (2), (3);
COMMIT;
SET @@autocommit = 0;
SELECT a FROM t1 FOR UPDATE;
[15:09] root@test>SELECT a FROM t1 FOR UPDATE;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

T2:
[15:09] root@test>SELECT a FROM t1 FOR UPDATE;
-- This waits now until T1 either COMMITs or ROLLBACKs

T1:
COMMIT;

T2:
[15:09] root@test>SELECT a FROM t1 FOR UPDATE;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (36.09 sec)

See also Bug#27722

Best regards,

Hakan

Venu,

you will find this fix in the upcoming alpha release.

A note has been added to the 5.2.4 changelog.

This bug report entry has been moved to the 6.0.0 Falcon changelog.