Bug #56060 InnoDB repeatable read anomaly should be documented

• Submitted: 17 Aug 2010 18:59
• Modified: 10 May 2012 23:15
• Reporter: Andrew Hutchings
• Status: Closed
• Category: MySQL Server: Documentation
• Severity: S3 (Non-critical)
• OS: Any
• Assigned to: John Russell
• CPU Architecture: Any

[17 Aug 2010 18:59] Andrew Hutchings

Description:
When repeatable read is used it is possible to delete (and in fact update) rows committed by another transaction even though they cannot be selected.

This behaviour is expected according to bug#30184 but I cannot find it documented anywhere.

How to repeat:
mysql1> set autocommit=0;
mysql2> set autocommit=0;

mysql1> create table t1 (a int primary key, b char(20)) engine innodb;
mysql1> select * from t1;
mysql2> select * from t1;

mysql1> insert into t1 values (1, 'hello'),(2, 'world');
mysql1> commit;

mysql2> select * from t1;
(Note, no rows shown here)
mysql2> delete from t1;
(Note, shows 2 rows affected here)
mysql2> commit;

Rows are now gone.

[10 May 2012 23:15] John Russell

Adding this note:

The snapshot of the database state applies to SELECT statements
within a transaction, not necessarily to DML statements. If you
insert or modify some rows and then commit that transaction, a DELETE
or UPDATE statement issued from another concurrent REPEATABLE READ
transaction could affect those just-committed rows, even though the
session could not query them.

to this page, and the equivalents for 5.1 and 5.6:

http://dev.mysql.com/doc/refman/5.5/en/innodb-consistent-read.html

Will look for other opportunities to cross-reference, e.g. under DELETE and UPDATE syntax.