How to produce "phantom read" in REPEATABLE READ? (MySQL)

Question

Using "repeatable read", it should be possible to produce a phantom read, but how? I need it for an example teaching CS-students.

I think that I must make a "SELECT ... WHERE x<=888" on a non-indexed field x, with an upperlimit 888 not present, and then on another connection insert a new row with a value just below 888.

Except it doesn't work. Do I need a very large table? Or something else?

Answer 1

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.

Answer 2

To complement Dani's good answer, you could use Microsoft Sql Server to show that behavior to your students.

Sql Server shows phantom reads in the repeatable read isolation level as claimed by the documentation here.

Postgres subscribes to the same notion as InnoDb as explained here. With Postgres too, no phantom reads happen in repeatable read and is thus also unsuited for your didactic purpose.

Sql Server offers another isolation level, snapshot, that does what MySql InnoDb and Postgres does in repeatable read (which is a lock-free, version-based implementation of repeatable read without phantom reads, but is not serializable).

Sql Server Express is free although you do need a Windows machine. You could also get yourself a Windows Azure account and show that behavior with Sql Azure online.

Answer 3

Possibility to reproduce phantom reads for InnoDB engine for isolation level REPEATABLE READ is questionable, because InnoDB uses Multiversion concurrency control - for every row MVCC engine knows transaction numbers when row was inserted and deleted and can reproduce history of row updates.

So, all consequent SELECT statements will show state of table in the beginning of transaction, except for rows that were inserted, deleted or updated by same this transaction. No new rows committed by other transactions will appear, because they will have insertion transaction numbers greater that of this transaction, and range of rows has no matter here.

I was able to reproduce PHANTOM READS for isolation level REPEATABLE READ for Apache Derby database, because it does not use multiversion concurrency control (version 10.8.2.2 in the moment of writing of this answer).

To reproduce, set proper transaction level (in ij - Derby's SQL client):

-- Set autocommit off
autocommit off;
-- Set isolation level corresponding to ANSI REPEATABLE READ
set isolation rs;

T1:

SELECT * FROM TableN;

T2:

INSERT INTO TableN VALUES(55, 1);
COMMIT;

T1 again:

SELECT * FROM TableN;

Now T1 should see one more row;

Answer 4

Phantom reads can occur because not range-locks exist, then an example is (pseudocode):

Thread1

Transaction 1

Update TableN set X=2 where X=1

wait(s1)
Select TableN where X=1

Commit 

thread2

Transaction 2:

insert into tableN(id, X) values(55,1)
commit;
notify(s1)

In wikipedia there are another example of phantom reads: Phantom Reads|wikipedia

The important thing here is the transactions syncronization, you can use sync points.

EDIT Example using mysql sleep function(not tested):

--on thread 1
Create TableN(id int, x int);
insert into TableN(id, X) values(1,1);
insert into TableN(id, X) values(2,1);
insert into TableN(id, X) values(3,1);

BEGIN TRANSACTION;
Update TableN set X=2 where X=1
SELECT SLEEP(30) FROM DUAL;
select TableN from where X=1;
COMMIT; 
--In other thread, before 20 secs;
BEGIN TRANSACTION;
insert into TableN(id, X) values(55,1);
COMMIT;

Answer 5

mysql use Consistent Nonlocking Read to avoid phantom read,it mean that if you run a query,mysql will use a snapshot to present your query. base on undo log. but there is an exception,if you update a row in this transactions,you will see the latest version of this row,so you can produce a phantom read use the method @ColinBinWang provide. if you want a more detailed explanation, see https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

Answer 6

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 2
START 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;

Answer 7

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;
   

Answer 8

You can produce phantom read in REPEATABLE READ in MySQL.

First, set REPEATABLE READ:

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Then, create person table with id and name as shown below.

person table:

id	name
1	John
2	David

Then, take these steps below with MySQL queries. *I used 2 command prompts:

Flow	Transaction 1 (T1)	Transaction 2 (T2)	Explanation
Step 1	BEGIN;		T1 starts.
Step 2		BEGIN;	T2 starts.
Step 3	SELECT * FROM person; 1 John 2 David		T1 reads 2 rows.
Step 4		INSERT INTO person VALUES (3, 'Tom');	T2 inserts the row with 3 and Tom to person table.
Step 5		COMMIT;	T2 commits.
Step 6	SELECT * FROM person; 1 John 2 David		T1 reads 2 rows after T2 commits. *Phantom read doesn't occur for now!!
Step 7	UPDATE person set name = 'Lisa' where id = 3;		Now to your surprise, T1 can update the new row which T2 has just inserted from Tom to Lisa.
Step 8	SELECT * FROM person; 1 John 2 David 3 Lisa		Now to your surprise, T1 reads 3 rows after T2 commits. *Phantom read occurs!!
Step 7	COMMIT;		T1 commits.

In addition, I did these steps above in REPEATABLE READ in Postgresql but phantom read didn't occur.