Bug #53798 OPTIMIZE TABLE breaks repeatable read
Submitted: 19 May 2010 11:22 Modified: 12 Jul 2010 17:23
Reporter: Jon Olav Hauglid Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.1 OS:Any
Assigned to: Jon Olav Hauglid CPU Architecture:Any
Triage: Triaged: D2 (Serious) / R5 (Severe) / E5 (Major)

[19 May 2010 11:22] Jon Olav Hauglid
Description:
OPTIMIZE TABLE can be run on a table in use by a transaction running in a different connection. This causes repeatable read to break.

How to repeat:
MTR test case based on test made by Shane Bester:

CREATE TABLE t1 (a INT) engine=innodb;
INSERT INTO t1 VALUES (1), (2), (3);

connect (con1, localhost, root);
START TRANSACTION WITH CONSISTENT SNAPSHOT;
SELECT * FROM t1;

connection default;
OPTIMIZE TABLE t1;

connection con1;
SELECT * FROM t1;

The first SELECT gives:
SELECT * FROM t1;
a
1
2
3

The second SELECT (in the same transaction) gives:
SELECT * FROM t1;
a

Suggested fix:
This bug is fixed by metadata locking in 5.5+
[19 May 2010 11:32] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/108616

3020 Jon Olav Hauglid	2010-05-19
      Bug #53798 OPTIMIZE TABLE breaks repeatable read
      
      The problem was that OPTMIZE TABLE was allowed to run on a table
      in use by a transaction in a different connection. This caused
      repeatable read to break.
      
      This bug was fixed by the introduction of metadata locking, WL#4284.
      OPTIMIZE TABLE will now be blocked until the transaction using the
      table, has ended.
      
      This patch contains a regression test added to innodb_mysql_lock.test
      and no code changes.
[20 May 2010 7:06] Jon Olav Hauglid
Test case pushed to mysql-trunk-runtime (Ver 5.5.5-m3).
[21 May 2010 6:03] Konstantin Osipov
Will not be fixed in 5.1, needs MDL.
[12 Jun 2010 11:01] Shane Bester
see also comments in this post:
http://ebergen.net/wordpress/2007/05/07/how-alter-table-locks-tables-and-handles-transacti...
[15 Jun 2010 8:08] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)
[15 Jun 2010 8:24] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)
[12 Jul 2010 17:23] Paul Dubois
Noted in 5.5.5 changelog.

OPTIMIZE TABLE could be run on a table in use by a transaction in a
different session, causing repeatable read to break.