How to produce phantom reads?

The "phantom read" in MySQL on RR isolation level is hidden deep, but still can reproduce it. Here are the steps:

1. create table ab(a int primary key, b int);

2. Tx1:
   begin;
   select * from ab; // empty set

3. Tx2:
   begin;
   insert into ab values(1,1);
   commit;
   
4. Tx1:
   select * from ab; // empty set, expected phantom read missing.
   update ab set b = 2 where a = 1; // 1 row affected.
   select * from ab; // 1 row. phantom read here!!!!
   commit;
   

Erik,

I come just from test it with a very large number of rows.

You will never found phantoms on InnoDB mysql with read commited or more restricted isolation level. It is explained on documentation:

REPEATABLE READ: For consistent reads, there is an important difference from the READ COMMITTED isolation level: All consistent reads within the same transaction read the snapshot established by the first read. This convention means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other. See Section 13.6.8.2, “Consistent Nonlocking Reads”.

But you can't also found phantoms in read commited isolation level: This is necessary because “phantom rows” must be blocked for MySQL replication and recovery to work.

More detailed information: http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html

I think you will need to move to another database brand to show phantoms to your students. I use both MSSQLSERVER and Oracle.

Well ... its a pity for your first question.

InnoDB should protect against phantom reads, as others have written.

But InnoDB has a different weird behavior related to locking. When a query acquires a lock, it always acquires the lock on the most recent version of the row. So try the following

CREATE TABLE foo (i INT PRIMARY KEY, val INT);INSERT INTO foo (i, val) VALUES (1, 10), (2, 20), (3, 30);

Then in two concurrent sessions (open two terminal windows):

-- window 1                               -- window 2START TRANSACTION;                                          START TRANSACTION;                                           SELECT * FROM foo; UPDATE foo SET val=35 WHERE i=3;                                           SELECT * FROM foo;

This should show val = 10, 20, 30 in both SELECTs, since REPEATABLE-READ means the second window sees only the data as it existed when its transaction started.

However:

                                           SELECT * FROM foo FOR UPDATE;

The second window waits to acquire the lock on row 3.

COMMIT;

Now the SELECT in the second window finishes, and shows rows with val = 10, 20, 35, because locking the row causes the SELECT to see the most recent committed version. Locking operations in InnoDB act like they are run under READ-COMMITTED, regardless of the transaction's isolation level.

You can even switch back and forth:

                                           SELECT * FROM foo;                                           SELECT * FROM foo FOR UPDATE;                                           SELECT * FROM foo;                                           SELECT * FROM foo FOR UPDATE;