MySQL Bugs: #106480: ALGORITHM=INSTANT takes exclusive metadata lock on table

Bug #106480	ALGORITHM=INSTANT takes exclusive metadata lock on table
Submitted:	17 Feb 2022 0:11	Modified:	13 May 2022 15:49
Reporter:	Jayden Navarro	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The MySQL docs suggest that using ALGORITHM=INSTANT to add a column does not take an exclusive metadata lock on the table being modified, however we've observed that it does acquire the metadata lock. This blocks new DML queries from using the table while the ALTER TABLE query is trying to acquire the lock.

From the docs:

> Instant operations only modify metadata in the data dictionary. No metadata locks are taken on the table, and table data is unaffected, making operations instantaneous. Concurrent DML is unaffected.
- https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-performance.html

> INSTANT: Operations only modify metadata in the data dictionary. No exclusive metadata locks are taken on the table during preparation and execution, and table data is unaffected, making operations instantaneous. Concurrent DML is permitted.
- https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

Looking at the documentation in the code, it mentions that at the end of the execution phase the lock is upgraded to exclusive:

>    *) We ensure that no other connection uses the table by upgrading our
>       lock on it to exclusive.
- https://github.com/mysql/mysql-server/blob/6846e6b2f72931991cc9fd589dc9946ea2ab58c9/sql/ha...

There is no mention of skipping this upgrade to an exclusive lock if the locking mode is HA_ALTER_INPLACE_INSTANT, which leads me to believe that only the public facing documentation is incorrect.

This is where I believe the lock is being upgraded, which happens regardless of locking mode:

>    // Upgrade to EXCLUSIVE before commit.
>    if (wait_while_table_is_used(thd, table, HA_EXTRA_PREPARE_FOR_RENAME))
>      goto rollback;
- https://github.com/mysql/mysql-server/blob/6846e6b2f72931991cc9fd589dc9946ea2ab58c9/sql/sq...

How to repeat:
--
-- SETUP
--
CREATE TABLE `test` (
  `id` INTEGER NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `test` VALUES (1);
INSERT INTO `test` VALUES (2);

--
-- CONNECTION 1
--
START TRANSACTION;
SELECT * FROM `test` WHERE id=1;

--
-- CONNECTION 2
--
-- This will block until the transaction opened by connection 1 finishes,
-- as the ALTER TABLE needs to acquire an exclusive metadata lock to commit
-- the DDL change.
--
ALTER TABLE `test`
ADD COLUMN `newColumn` INTEGER DEFAULT NULL,
ALGORITHM=INSTANT;

--
-- CONNECTION 3
--
-- This will block until the ALTER TABLE completes since its acquisition
-- of a non-exclusive metadata lock on the table is queued behind the
-- ALTER TABLE.
--
-- Note that this does not conflict with the transaction on connection 1,
-- so the only explanation for it blocking is that its blocked behind the
-- ALTER TABLE.
--
SELECT * FROM `test` WHERE id=2;

--
-- CONNECTION 1
--
COMMIT;

--
-- After the transaction on connection 1 commits, the ALTER TABLE can
-- acquire the lock and proceed, and then the SELECT on connection 3
-- can finish after the ALTER TABLE.
--
-- Note that the SELECT from connection 3 shows `newColumn`, showing it
-- was executed after the DDL change was committed.
--

Suggested fix:
If this is intended behavior, update documentation to mention that an exclusive table lock is taken when ALGORITHM=INSTANT is used. If not intended behavior, change behavior to match the documentation.

Hi Mr. Navarro,

Thank you for your bug report.

However, documentation is correct regarding the exclusive lock. This variant of DDL takes the share lock, except at the very end, where it takes exclusive lock for a very short time.

You can check this by running read-only (SELECT with no locks) queries during this DDL. If our documentation, on dev.mysql.com, does not mention that exclusive lock is taken for a very short time, that can be amended if you agree to treat this report as a documentation request.

We are waiting on your feedback.

Hi,

Another small addition .....

Waiting on the concurrent transaction to finish is expected behaviour. If you wish it to be documented better, just let us know .....

What we wrote to you about concurrent read-only queries, it does not include open transactions !!! Just simple SELECTs .....

Yes, this can be treated as a documentation request.

I believe these statements in the documentation are incorrect, specifically where they state "No metadata locks are taken on the table". This is false, as at some point during the query an exclusive metadata lock is taken on the table:

> Instant operations only modify metadata in the data dictionary. No metadata locks are taken on the table, and table data is unaffected, making operations instantaneous. Concurrent DML is unaffected.
- https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-performance.html

> INSTANT: Operations only modify metadata in the data dictionary. No exclusive metadata locks are taken on the table during preparation and execution, and table data is unaffected, making operations instantaneous. Concurrent DML is permitted.
- https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

Hi Mr. Navarro,

It turns out that this particular feature is not documented sufficiently.

Regarding waiting for pending transactions before starting the INSTANT DDL, that is expected behaviour. The explanation is simple ......

MySQL  does not support having multiple versions of table in the data-dictionary and table definition/open tables caches at the same time, nor SE support this AFAIK. So ALTER TABLE needs to invalidate all these caches at some point and update them while ensuring that there is no concurrent statement are
using them, for which we acquire exclusive lock.

There are other solutions that are discussed strictly internally, but those are left for some distant future.

This is a missing piece in our documentation.

INSTANT method is ideal for the DDL that ou are attempting, since it will require only a change in the attributes.

Regarding the exclusive lock, it will be taken only once for the table 'test' in this case, before calling storage engine's inplace or instant ALTER TABLE commit hook/invalidating caches and updating data dictionary.

This should be also added to our documentation.

Verified as a documentation request.

This should

Posted by developer:
 
The referenced documentation has been revised. The changes should appear online soon.
Thank you for the bug report.