LOCK TABLES FOR BACKUP locking performance_schema table operations

Description

Documentation states:

LOCK TABLES FOR BACKUP uses a new MDL lock type to block updates to non-transactional tables and DDL statements for all tables. If there is an active LOCK TABLES FOR BACKUP lock then all DDL statements and all updates to MyISAM, CSV, MEMORY, ARCHIVE, TokuDB, and MyRocks tables will be blocked in the Waiting for backup lock status, visible in PERFORMANCE_SCHEMA or PROCESSLIST.

It does not specifically state if performance_schema tables (which are non-transactional) are also affected. If this is only a DOC bug please we can add the component in the bug report.

 

How to reproduce:

  • Start PS MySQL 8.0

  • Start LOCK TABLES FOR BACKUP

  • truncate performance_schema.events_statements_summary_by_digest

 

How it can affect:

A long backup operation that uses LOCK TABLES FOR BACKUP can block system operations like truncation of P_S tables.

Environment

None

AFFECTED CS IDs

CS0015496

Smart Checklist

Activity

Show:

Lalit Choudhary February 12, 2021 at 1:53 PM

Hi

Thank you for the report.

verified as described. 

 

Jericho Rivera January 30, 2021 at 9:28 AM

It basically does the same.

Upstream documentation states:

LOCK INSTANCE FOR BACKUP permits DDL operations that only affect user-created temporary tables. In effect, files that belong to user-created temporary tables can be created, renamed, or removed while a backup lock is held. Creation of binary log files is also permitted.

 

 

This means LOCK TABLES FOR BACKUP and LOCK INSTANCE FOR BACKUP seems to operate similarly?

 

George Lorch January 29, 2021 at 1:31 PM

out of curiosity, what does LOCK INSTANCE FOR BACKUP do? 

Details

Assignee

Reporter

Affects versions

Priority

Smart Checklist

Created January 29, 2021 at 6:35 AM
Updated March 6, 2024 at 10:44 AM