This topic describes how to use Data Management Service (DMS) to release metadata locks that block subsequent operations on a table.
Background information
MySQL 5.5 or later uses metadata locking to ensure consistency between Data Definition Language (DDL) and Data Manipulation Language (DML) operations. However, metadata locks may block subsequent operations on tables, for example, if you perform ALTER operations while DML operations are in progress, or if you perform ALTER operations while queries for data spanning a long time range are being processed.
Scenarios
- Create and delete indexes from a table.
- Modify the structure of a table.
- Manage a table, including optimize, repair, and other operations.
- Delete a table.
- Acquire table-level write locks.
Causes
- Requests for querying data spanning a long time range from a table are being processed.
- Explicitly or implicitly started transactions are not committed, nor are they rolled back after errors occur. Such transactions include those for completing query operations.
- Some query transactions failed in a table.
Procedure
- Use DMS to log on to the target database of your ApsaraDB for RDS instance.
Note In the Network address:Port field, you must enter the Internal Endpoint and Internal Port of your ApsaraDB for RDS instance. Then, you can log on to the ApsaraDB for RDS console and navigate to the Basic Information page to obtain the internal endpoint and internal port.
- In the top navigation bar of the DMS home page, choose .
- Enter show full processlist; and click the execute button to view the status of all threads. If a large number
of threads whose State reads as Waiting for table metadata lock are found, operations on the target database are blocked. In the Info column, obtain the tables where operations are to be performed. Then, find the sessions
that solicit the operations, and note down the Id of the sessions.
Note
- You must find the sessions holding locks on tables but not those waiting to release locks. You can distinguish between the two types of sessions based on the State and Info columns.
- You can run the following command to query transactions that have been pending for
a long time. If the account blocking operations is not the one you are using, log
on to the target database by using this account, and then terminate the sessions.
select concat('kill ',i.trx_mysql_thread_id,';') from information_schema.innodb_trx i, (select id, time from information_schema.processlist where time = (select max(time) from information_schema.processlist where state = 'Waiting for table metadata lock' and substring(info, 1, 5) in ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat'))) p where timestampdiff(second, i.trx_started, now()) > p.time and i.trx_mysql_thread_id not in (connection_id(),p.id);
- Enter kill Id and click the execute button to terminate the session specified by the Id parameter. An example command is as follows: kill 267. After the session is terminated, its metadata lock is released.
What to do next
- Manage tables during off-peak hours, including creating and deleting indexes.
- Enable autocommit mode.
- Set the lock_wait_timeout parameter to a small value.
- Use events to terminate transactions that have been running for a long time. In the
following example, transactions that have been running for more than 60 minutes are
terminated:
create event my_long_running_trx_monitor on schedule every 60 minute starts '2015-09-15 11:00:00' on completion preserve enable do begin declare v_sql varchar(500); declare no_more_long_running_trx integer default 0; declare c_tid cursor for select concat ('kill ',trx_mysql_thread_id,';') from information_schema.innodb_trx where timestampdiff(minute,trx_started,now()) >= 60; declare continue handler for not found set no_more_long_running_trx=1; open c_tid; repeat fetch c_tid into v_sql; set @v_sql=v_sql; prepare stmt from @v_sql; execute stmt; deallocate prepare stmt; until no_more_long_running_trx end repeat; close c_tid; end;