Bug #31175 SELECT ... FOR UPDATE does not block
Submitted: 24 Sep 2007 13:17 Modified: 26 Sep 2007 3:45
Reporter: Hakan Küçükyılmaz Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:5.1.23, 6.0.3 OS:Linux (64-bit)
Assigned to: CPU Architecture:Any

[24 Sep 2007 13:17] Hakan Küçükyılmaz
Description:
A SELECT ... FOR UPDATE does not block anymore.

How to repeat:
We use isolation level of READ-COMMITTED and do a SELECT ... FOR UPDATE on a primary key field. On the second connection we try to update the locked row.

Connection 1:

[15:07] root@test>set @autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

[15:07] root@test>set tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

[15:07] root@test>create table t1 (a int primary key) engine innodb;
Query OK, 0 rows affected (0.01 sec)

[15:07] root@test>insert into t1 values (1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

[15:07] root@test>commit;
Query OK, 0 rows affected (0.00 sec)

[15:07] root@test>select a from t1 where a = 2 for update;
+---+
| a |
+---+
| 2 |
+---+
1 row in set (0.00 sec)

Connection 2:
[15:06] root@test>set @autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

[15:06] root@test>set tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

-- This should wait on Connection 1
-- but it doesn't.
[15:07] root@test>update t1 set a = 5 where a = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
[24 Sep 2007 14:26] MySQL Verification Team
Thank you for the bug report. Verified as described.
[24 Sep 2007 15:17] Hakan Küçükyılmaz
Also fails with Falcon
[26 Sep 2007 3:45] Hakan Küçükyılmaz
Sorry for confusion.

  set @autocommit = 0

sets the user variable @autocommit to 0. SELECT ... FOR UPDATE works as expected when using "set autocommit = 0" or "set @@autocommit = 0".

Best regards,

Hakan