Bug #103153 ALTER with LOCK=NONE blocks SELECTs

• Submitted: 30 Mar 16:08
• Modified: 31 Mar 14:45
• Reporter: Dejan Levec
• Status: Not a Bug
• Category: MySQL Server: Locking
• Severity: S3 (Non-critical)
• Version: 5.7, 8.0
• OS: Any
• CPU Architecture: Any

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