MySQL Bugs: #103153: ALTER with LOCK=NONE blocks SELECTs

Bug #103153	ALTER with LOCK=NONE blocks SELECTs
Submitted:	30 Mar 2021 16:08	Modified:	31 Mar 2021 14:45
Reporter:	Dejan Levec	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.7, 8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When performing ALTER TABLE, LOCK=NONE on a table which has an active transaction, that selects from the same table, ALTER query is stuck waiting for metadata lock.

However, any subsequent SELECTs also wait for metadata lock, which is not expected.

Those SELECTs wait until either ALTER query is killed, or the first transaction is committed/rolled back.

Expectation: When using LOCK=NONE, ALTER statement should not lock DML on the same table.

How to repeat:

--- initialization
DROP DATABASE IF EXISTS db1;
CREATE DATABASE db1;
CREATE TABLE `tbl` (`column` varchar(255) NOT NULL) ENGINE=InnoDB;

--- connection 1
START TRANSACTION;
SELECT * FROM tbl1;

--- connection 2
ALTER TABLE lala ADD COLUMN `test` tinyint(1) NOT NULL DEFAULT '1', LOCK=NONE;

--- connection 3
SELECT * FROM tbl1;

# connection 3 would be stuck on 'waiting for metadata lock' until either connection 1 is commited/rolled back, or 2 is aborted.

I used different names for the same table in the original submission, here is the correct version:

--- initialization
DROP DATABASE IF EXISTS db1;
CREATE DATABASE db1;
CREATE TABLE `tbl1` (`column` varchar(255) NOT NULL) ENGINE=InnoDB;

--- connection 1
START TRANSACTION;
SELECT * FROM tbl1;

--- connection 2
ALTER TABLE tbl1 ADD COLUMN `test` tinyint(1) NOT NULL DEFAULT '1', LOCK=NONE;

--- connection 3
SELECT * FROM tbl1;

# connection 3 would be stuck on 'waiting for metadata lock' until either connection 1 is commited/rolled back, or 2 is aborted.

Hi Mr. Levec,

Thank you for your bug report.

However, this is not a bug.

You are using a COPY algorithm, so this behaviour is expected.

Not a bug.

Hello,

Same issue is present when using ALGORITHM=INPLACE:

---------------------

--- initialization
DROP DATABASE IF EXISTS db1;
CREATE DATABASE db1;
USE db1;
CREATE TABLE `tbl1` (`column` varchar(255) NOT NULL) ENGINE=InnoDB;

--- connection 1
USE db1;
START TRANSACTION;
SELECT * FROM tbl1;

--- connection 2
USE db1;
ALTER TABLE tbl1 ADD COLUMN `test` tinyint(1) NOT NULL DEFAULT '1', ALGORITHM=INPLACE, LOCK=NONE;

--- connection 3
USE db1;
SELECT * FROM tbl1;

There are other modes as well.

Also, it is a big difference whether MDL has been already taken or not.