Bug #62180 Metadata lock during index creation eliminates concurrent selects
Submitted: 16 Aug 2011 16:18 Modified: 20 Aug 2011 15:31
Reporter: Dima Drug Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:5.5.12 OS:Any
Assigned to: CPU Architecture:Any
Tags: concurrent select, create index, Metadata lock

[16 Aug 2011 16:18] Dima Drug
If using InnoDB storage engine according to the documentation (http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-create-index-concurrency.html) we can select data from table during index creation. But in version 5.5 metadata locking mechanism was changed and the thread that creates index holds exclusive metadata lock for the table and blocks even SELECTs on this object.
Select thread has the following wait:
EVENT_NAME: wait/synch/cond/sql/MDL_context::COND_wait_status
SOURCE: mdl.cc:979

How to repeat:
1. Create simple InnoDB storage engine table with PK which has sufficient amount of data.
2. Run secondary index creation process from one session.
3. Run SELECT * FROM SAMPLE_TABLE LIMIT 1 in parallel session.

Second session will be blocked till the end of index creation process.
[16 Aug 2011 16:26] Dima Drug
Not 100% right link to InnoDB doc (but content is the same). 
Should be this http://dev.mysql.com/doc/innodb/1.1/en/innodb-create-index-concurrency.html
[18 Aug 2011 18:43] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior. Please send us output of SHOW CREATE TABLE, statement you use to create an index and indicate accurate version of MySQL you are using.
[19 Aug 2011 10:42] Dima Drug
| version|5.5.12-log|
| version_comment| MySQL Community Server(GPL)|
| version_compile_machine| x86_64|
| innodb_version | 1.1.6 |

  `f2` int(11) DEFAULT NULL,
  PRIMARY KEY (`f1`)

Reproduce steps:
SET @Iter = 1;
INSERT INTO t1(f2) SELECT @Iter := @Iter + 1 FROM information_schema.columns i1, information_schema.columns i2, information_schema.columns i3 LIMIT 10000000;
b) in one session run secondary index creation.
CREATE INDEX idx ON t1(f2); 
c) at the same moment in separate session run simple query
SELECT * FROM t1 WHERE f1 = 1;

The second query won't return results till index creation is finished, but with InnoDB and fast index creation mechanism it should be so.
[19 Aug 2011 10:44] Dima Drug
Sorry, but with InnoDB
and fast index creation mechanism it should NOT be so.
[19 Aug 2011 17:49] Sveta Smirnova
Thank you for the feedback.

I could repeat described behavior with version 5.5.11, but could not with current version 5.5.14. Please upgrade and test if it fixed in your environment too.
[20 Aug 2011 15:17] Dima Drug
It works.
Unfortunately I could not found earlier that this bug was fixed in 5.5.14.
(InnoDB Storage Engine: InnoDB now permits concurrent reads while creating a secondary index. (Bug #11853126)).
Thank you for your feedback and sorry for taking time on duplicate bugs.
[20 Aug 2011 15:31] Valeriy Kravchuk
Duplicate of a known internal bug, fixed since 5.5.14.