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