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?
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.