MySQL Bugs: #62180: Metadata lock during index creation eliminates concurrent selects

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:	None
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

Description:
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.

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

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.

| version|5.5.12-log|
| version_comment| MySQL Community Server(GPL)|
| version_compile_machine| x86_64|
| innodb_version | 1.1.6 |

CREATE TABLE `t1` (
  `f1` int(11) NOT NULL AUTO_INCREMENT,
  `f2` int(11) DEFAULT NULL,
  PRIMARY KEY (`f1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Reproduce steps:
a)filling:
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.

Sorry, but with InnoDB
and fast index creation mechanism it should NOT be so.

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.

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.

Duplicate of a known internal bug, fixed since 5.5.14.