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

[30 Mar 2021 16:08] Dejan Levec
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.
[30 Mar 2021 16:10] Dejan Levec
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.
[31 Mar 2021 12:38] MySQL Verification Team
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.
[31 Mar 2021 14:45] Dejan Levec
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;
[2 Apr 2021 12:01] MySQL Verification Team
There are other modes as well.

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