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

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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.042 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.