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?

Facility

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.

Recumbent

Thank you. That more or less explains it. I will need to look into this "problem" in a month or two. And the so called problem is just showing that it can happen ... in another kind of database. – Facility

@deFreitas I didn't write this answer. I just edited it. You should redirect your comment to danihp which wrote the answer. – Corrigible

My fault, the comment was directed to @danihp – Effervesce

@danihp "You will never found phantoms on InnoDB mysql with read commited or more restricted isolation level." Based on my tests it's not true for READ COMMITTED, just for REPEATABLE READ, Ran a SELECT, then inserted/updated a record on another session then ran the select again on the first session, the records has changed, my MySQL version is 5.7.20 and my table is using innoBD – Effervesce

As documentation, phantom read happen when in repeatable read. Can you make your statement clearer ? dev.mysql.com/doc/refman/8.0/en/… – Jean

The default isolation level for InnoDB. It prevents any rows that are queried from being changed by other transactions, thus blocking non-repeatable reads but not phantom reads – Jean

Answer 2

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;
   

Warmblooded

Would love an explanation as to why this works. Good answer though! – Bannock

I don't understand why this works. I even think this behavior contradicts with the official documentation, which says Consistent reads within the same transaction read the snapshot established by the first read. This 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. I wonder in which section of the official documentation this behavior is describes (or justified). – Coitus

@Coitus In this document, this behavior is documented. Please refer to the Note section. – Mare

@JasonLee Thank you very much! I just read the link and confirmed you are correct. – Coitus

Answer 3

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;

Tirza

As I read document in MySQL, InnoDB default engine uses repeatable read, and so phantom read can be occurred. dev.mysql.com/doc/refman/8.0/en/… – Jean

@Jean Repeatable read is repeatable, unless you do locking reads. – Tirza

"The default isolation level for InnoDB. It prevents any rows that are queried from being changed by other transactions, thus blocking non-repeatable reads but not phantom reads". This is in above document stated. I understand your viewpoint, but can you explain for me why do mysql document say this ? – Jean

The documentation is inaccurate, or at least unclear. Changes to data by other transactions are not blocked. You can test this for yourself, using a technique similar to what I did above. Open two windows, start a transaction and query data in first window. Change data in second window. Query again in first window (same transaction as earlier), and you see the unchanged data. – Tirza

Answer 4

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;

Bourassa

And thank you also! Seems impossible in MySQL - so I might need to use Derby or some other for the demonstration. – Facility

Answer 5

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.

Alkalinity

Answer 6

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;

Handwriting

I think the OP is looking for actual code that can be executed in MySQL to demonstrate this. – Leoleod

You are right, Martin. I know a few ways, that theoretically can give a phantom read, but I have never been able to show it. A few of my students have tried hard, to no avail. – Facility

With java threads, using autocommit=false and syncronization of the threads you can produce this. – Handwriting

No, I can't. And no need to have Java enter the scene, since this is merely a database problem. – Facility

Then try using the sleep function (answer edited to show an untested example). – Handwriting

Answer 7

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.

Swain

Answer 8

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

Scrapbook

Answer 9

Trying to understand the essential reason for the top rating answer. To prevent phantom read, RR level in InnoDB is actually implemented through MVCC on behalf of the undo logs, and the mechenism is also known as Consistent Read or Snapshot Read.

The undo log works like a chain for updates with their transaction ids. So when another transaction successfully insert a record A (committed) and then the update upon A (in other transaction) could be performed afterwards since the undo log could find the entry now. Besides, the updates won't be blocked since there is no lock accquired on record A. Overall, it's totally acceptable in RR isolation semantic.

Seems it's only safe to prevent photom read through Lock Read, e.g. using "SELECT ... FOR UPDATE" which involves next-key lock.

Prelusive