Bug #9512 SELECT FOR UPDATE returns phantom rows

• Submitted: 31 Mar 2005 2:29
• Modified: 4 Apr 2005 5:30
• Reporter: Peter Zaitsev (Basic Quality Contributor)
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 4.1.10a
• OS: Any (All)
• CPU Architecture: Any

[31 Mar 2005 2:29] Peter Zaitsev

Description:
In default repeatable read mode Innodb may return row by select for update which is invisible via normal select:

mysql> create table itest (id int unsigned not null primary key) type=innodb;
Query OK, 0 rows affected, 1 warning (0.36 sec)

mysql> insert into itest values(1);
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from itest;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

Now Connect in other session and run:

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

Back to original session:

mysql> select * from itest;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

Row invisible.

mysql> select * from itest for update;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

Row visible if "for update" is used 

mysql> select * from itest where id=2;
Empty set (0.00 sec)

----------------

I've seen this in more complex case - base and child table are populated, now read from base table is done "for update" as it is going to be updated,   child table is however read without "for update" which results in "child row does not exist" error in the application while it is where. 

How to repeat:
See above

[4 Apr 2005 5:30] Heikki Tuuri

Peter,

this is the documented behavior of REPEATABLE READ.

A normal consistent read SELECT in transaction T reads a consistent snapshot of the database at the time when the first SELECT in T was issued. SELECT ... FOR UPDATE is a normal read that reads all current data and locks it. The InnoDB manual states that if you want to read the latest data, you must use a locking read.

Regards,

Heikki