Bug #53798 OPTIMIZE TABLE breaks repeatable read

• Submitted: 19 May 2010 11:22
• Modified: 12 Jul 2010 17:23
• Reporter: Jon Olav Hauglid
• Status: Closed
• Category: MySQL Server: Locking
• Severity: S3 (Non-critical)
• Version: 5.1
• OS: Any
• Assigned to: Jon Olav Hauglid
• CPU Architecture: Any

[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] MySQL Verification Team

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:[e-mail]-20100615080459-smuswd9ooeywcxuc) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

[15 Jun 2010 8:24] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100615080558-cw01bzdqr1bdmmec) (version source revid:[e-mail]-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.