Generation and processing of Metadata lock on RDS MySQL table

1. Metadata lock wait for the scene to appear

2. Meaning of Metadata lock wait

3. Active transactions that cause Metadata lock wait

4. Solution

5. How to avoid long Metadata lock wait causes the related query on the table to block, affect the business

1. Metadata lock wait for the scene to appear

• Create, delete index

• Modify Table Structure

• Table maintenance operations (optimize table, repair table, etc.)

• Delete a table

• Get table-level write lock on table (lock table Tab_name write)

Note:

• The InnoDB engine table that supports transactions and the MyISAM engine table that does not support transactions will appear Metadata Lock wait waits.
• Once the Metadata Lock Wait wait occurs, all subsequent access to the table is blocked on that wait, causing the connection to accumulate and the business to be affected.

2. Meaning of Metadata lock wait

To maintain data consistency for table metadata in a concurrent environment, you cannot write to metadata when there are active transactions (explicit or implicit) on the table. So MySQL introduced metadata lock to protect the table's metadata information.

Therefore, if there is an active transaction on the table (uncommitted or rollback) on the table, the requested write session waits at Metadata lock wait.

3. Active transactions that cause Metadata lock wait

• There are currently long queries to the table

• Shows or implicitly opens a transaction without committing or rolling back, such as when the query is completed and not committed or rolled back.

• There are failed query transactions on the table

4. Solution

• Show processlist to view a query for a long time that the session has not completed, use the KILL command to terminate the query.

• Query Information_schema.innodb_trx see a transaction that has not been completed for a long time, use the KILL command to terminate the query.

SelectConcat' Kill ', i.trx_mysql_thread_id,‘;‘)From Information_schema.innodb_trx I, (Select ID,TimeFrom INFORMATION_SCHEMA.ProcesslistwhereTime = (SelectMaxTimeFrom INFORMATION_SCHEMA.Processlistwhere State =' Waiting for table metadata lock 'andsubstring (info, 1, 5) in (' alter ', ' Optim ', ' Repai ', ' lock ', ' drop ', ' creat '))) P where Timestampdiff (Second, i.trx_started, now ()) > P timeand I.trx_mysql_thread_ ID not in (connection_id (), p.id);  --Modify the query statement according to the specific scenario- -if the user who caused the blocking statement is different from the current user, terminate the session with the user who caused the blocked statement to log on       

Note: For cleanup sessions, refer to: How RDS MySQL terminates a session

• If the above two checks are not found, or if there are too many transactions, it is recommended to use the following query to terminate the session on the related library
  

  --RDS for MySQL 5.6SelectConcat' Kill ', a.owner_thread_id,‘;‘)From Information_schema.metadata_locks ALeftJoin (Select b.owner_thread_idFrom Information_schema.metadata_locks B, information_schema.metadata_locks cwhere b.owner_thread_id = c.owner_thread_idand b.lock_status =' Granted 'and c.lock_status =' Pending ') dOn a.owner_thread_id = d.owner_thread_idwhere a.lock_status =' Granted 'and d.owner_thread_idIsNull--RDS for MySQL 5.5SelectConcat' Kill ', P1.id,‘;‘)From INFORMATION_SCHEMA.Processlist P1, (Select ID,TimeFrom INFORMATION_SCHEMA.ProcesslistwhereTime = (SelectMaxTimeFrom INFORMATION_SCHEMA.Processlistwhere State =' Waiting for table metadata lock 'andSUBSTRING (info,1,5)in ( ' alter ',  ' Optim ',  ' Repai ',  ' lock ',  ' drop ',  ' creat ',  ' trunc '))) P2where p1. Time >= P2. time and p1.command in ( ' Sleep ',  ' Query ') and p1.id not in (connection_id (), p2.id); --RDS for MySQL 5.5 statement Modify the condition of the query according to the specific DDL statement situation; --if the user who caused the blocked statement is different from the current user, Terminate the session with the user who caused the blocked statement              

5. How to avoid long metadata lock wait causes the related query on the table to block, affect the business

• Perform the above operations during low-peak business periods, such as creating a delete index.

• After the database connection to RDS is established, set the session variable autocommit to 1 or on, such as set autocommit=1; or set autocommit=on;.

• Consider using events to terminate long-running transactions, such as in the following example, which terminates a transaction that takes more than 60 minutes to execute.

  CreateEvent My_long_running_trx_monitorOn schedule every60Minutestarts' 2015-09-15 11:00:00 'On completionPreserve EnableDoBeginDECLARE v_sqlvarchar500);DECLARE No_more_long_running_trxIntegerDefault0;DECLARE C_tidCursorForSelectConcat (' Kill ', trx_mysql_thread_id,‘;‘)From Information_schema.innodb_trxwhereTimestampdiff (Minute,trx_started,Now ()) >=60;DeclareContinuehandler 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;         

  
  Note: Depending on your situation, modify the run interval and the transaction execution duration yourself.

• Before performing the above 1 operations, set the session variable Lock_wait_timeout to a smaller value, such as set lock_wait_timeout=30; command can set the maximum time for metadata lock wait to 30 seconds; avoid long waits for metadata locks to affect other business queries on the table.

Generation and processing of Metadata lock on RDS MySQL table