您的位置:新葡亰496net > 网络数据库 > 7中定位DDL被打断的问题,MySQL怎么着定位未提交

7中定位DDL被打断的问题,MySQL怎么着定位未提交

发布时间:2019-06-30 12:57编辑:网络数据库浏览(158)

    在上一篇文章《MySQL 5.7中如何定位DDL被阻塞的问题》中,对于DDL被阻塞问题的定位,我们主要是基于MySQL 5.7新引入的performance_schema.metadata_locks表。提出的定位方法,颇有种"锦上添花"的意味,而且,也只适用于MySQL 5.7开始的版本。

    在上篇文章《MySQL表结构变更,不可不知的Metadata Lock》中,我们介绍了MDL引入的背景,及基本概念,从“道”的层面知道了什么是MDL。下面就从“术”的层面看看如何定位MDL的相关问题。

    在上篇文章《MySQL表结构变更,不可不知的Metadata Lock》中,我们介绍了MDL引入的背景,及基本概念,从“道”的层面知道了什么是MDL。下面就从“术”的层面看看如何定位MDL的相关问题。

    一、问题描述

    MySQL版本为5.6.12。

    但在实际生产中,MySQL 5.6还是占绝不多数。虽然MySQL 8.0都已经GA了,但鉴于数据库的特殊性,在对待升级的这个事情上,相当一部分人还是秉持着一种“不主动”的态度。

    在MySQL 5.7中,针对MDL,引入了一张新表performance_schema.metadata_locks,该表可对外展示MDL的相关信息,包括其作用对象,类型及持有等待情况。

    在MySQL 5.7中,针对MDL,引入了一张新表performance_schema.metadata_locks,该表可对外展示MDL的相关信息,包括其作用对象,类型及持有等待情况。

    我们经常会碰到这样的情况,某个事务执行完了未提交,后续再来一个DDL和DML操作,导致后面的session要么处于waiting for metadata lock,要么是锁等待超时。这时我们往往只能找到这个未提交的事务的事务id和session id,但是一般都处于sleep状态,不好分析事务内容到底是什么,所以通常都是粗鲁地kill这个session后解决问题,但是应用层的研发人员往往找不到到底是哪个事务引起的,后面再出现问题时还要重复kill。那这个情况下,怎么办呢?

    在进行alter table操作时,有时会出现Waiting for table metadata lock的等待场景。而且,一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态,后续对TableA的任何操作(包括读)都无法进行,也会在Opening tables的阶段进入Waiting for table metadata lock的队列。如果是产品环境的核心表出现了这样的锁等待队列,就会造成灾难性的后果。

    既然MySQL 5.6用者众多,有没有一种方法,来解决MySQL 5.6的这个痛点呢?

     

    开启MDL的instrument

    下面我先模拟两种情况

    造成alter table产生Waiting for table metadata lock的原因其实很简单,一般是以下几个简单的场景:

     

    开启MDL的instrument

    但是相关instrument并没有开启(MySQL 8.0是默认开启的),其可通过如下两种方式开启,

    新葡亰496net 1

     

    还是之前的测试Demo

    但是相关instrument并没有开启(MySQL 8.0是默认开启的),其可通过如下两种方式开启,

    临时生效

    这里我特意在开启session后执行一条update,又执行了一条insert语句。

    场景一:

    会话1开启了事务并执行了三个操作,但未提交,此时,会话2执行了alter table操作,被阻塞。

    临时生效

    修改performance_schema.setup_instrume nts表,但实例重启后,又会恢复为默认值。

    新葡亰496net 2

    通过show processlist可以看到TableA上有正在进行的操作(包括读),此时alter table语句无法获取到metadata 独占锁,会进行等待。

    session1> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    session1> delete from slowtech.t1 where id=2;
    Query OK, 1 row affected (0.00 sec)
    
    session1> select * from slowtech.t1;
     ------ ------ 
    | id   | name |
     ------ ------ 
    |    1 | a    |
     ------ ------ 
    row in set (0.00 sec)
    
    session1> update slowtech.t1 set name='c' where id=1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    session2> alter table slowtech.t1 add c1 int; ##被阻塞
    
    session3> show processlist;
     ---- ------ ----------- ------ --------- ------ --------------------------------- ------------------------------------ 
    | Id | User | Host      | db   | Command | Time | State                           | Info                               |
     ---- ------ ----------- ------ --------- ------ --------------------------------- ------------------------------------ 
    |  2 | root | localhost | NULL | Sleep   |   51 |                                 | NULL                               |
    |  3 | root | localhost | NULL | Query   |    0 | starting                        | show processlist                   |
    |  4 | root | localhost | NULL | Query   |    9 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int |
     ---- ------ ----------- ------ --------- ------ --------------------------------- ------------------------------------ 
    rows in set (0.00 sec)
    

    修改performance_schema.setup_instrume nts表,但实例重启后,又会恢复为默认值。

    UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
    WHERE NAME = 'wait/lock/metadata/sql/mdl';
    

    这时session2一直卡住

    这是最基本的一种情形,这个和mysql 5.6中的online ddl并不冲突。一般alter table的操作过程中(见下图),在after create步骤会获取metadata 独占锁,当进行到altering table的过程时(通常是最花时间的步骤),对该表的读写都可以正常进行,这就是online ddl的表现,并不会像之前在整个alter table过程中阻塞写入。(当然,也并不是所有类型的alter操作都能online的,具体可以参见官方手册:)

     

    UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
    WHERE NAME = 'wait/lock/metadata/sql/mdl';
    

    永久生效

    我们再开一个窗口session3。

    新葡亰496net 3  

    其实,导致DDL阻塞的操作,无非两类: 

     

    在配置文件中设置

    mysql> show processlist;

    场景二:

    1. 慢查询  

    2. 表上有事务未提交

    永久生效

    [mysqld]
    performance-schema-instrument='wait/lock/metadata/sql/mdl=ON' 
    

    ---- ------ ----------- ------ --------- ------ --------------------------------- ----------------------------------------------------

    通过show processlist看不到TableA上有任何操作,但实际上存在有未提交的事务,可以在information_schema.innodb_trx中查看到。在事务没有完成之前,TableA上的锁不会释放,alter table同样获取不到metadata的独占锁。

    其中,第一类比较好定位,通过show processlist即能发现。而第二类基本没法定位,因为未提交事务的连接在show processlist中的输出同空闲连接一样。

    在配置文件中设置

    测试场景

    | Id | User | Host      | db  | Command | Time | State                          | Info                                              |

     

    如下面Id为2的连接,虽然Command显示为“Sleep”,其实是事务未提交。

    [mysqld]
    performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
    

    下面结合一个简单的Demo,来看看在MySQL 5.7中如何定位DDL操作的阻塞问题。

    ---- ------ ----------- ------ --------- ------ --------------------------------- ----------------------------------------------------

    场景三:

    mysql> show processlist;
     ---- ------ ----------- ------ --------- ------ --------------------------------- ------------------------------------ 
    | Id | User | Host      | db   | Command | Time | State                           | Info                               |
     ---- ------ ----------- ------ --------- ------ --------------------------------- ------------------------------------ 
    |  2 | root | localhost | NULL | Sleep   |   77 |                                 | NULL                               |
    |  3 | root | localhost | NULL | Query   |    0 | starting                        | show processlist                   |
    |  4 | root | localhost | NULL | Query   |   44 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int |
     ---- ------ ----------- ------ --------- ------ --------------------------------- ------------------------------------ 
    3 rows in set (0.00 sec)
    

     

    session1> begin;
    Query OK, 0 rows affected (0.00 sec)
    session1> delete from slowtech.t1 where id=2;
    Query OK, 1 row affected (0.00 sec)
    session1> select * from slowtech.t1;
     ------ ------ 
    | id | name |
     ------ ------ 
    | 1 | a |
     ------ ------ 
    1 row in set (0.00 sec)
    session1> update slowtech.t1 set name='c' where id=1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1 Changed: 1 Warnings: 0
    session2> alter table slowtech.t1 add c1 int; ##被阻塞
    session3> show processlist;
     ---- ------ ----------- ------ --------- ------ --------------------------------- ------------------------------------ 
    | Id | User | Host  | db | Command | Time | State       | Info        |
     ---- ------ ----------- ------ --------- ------ --------------------------------- ------------------------------------ 
    | 2 | root | localhost | NULL | Sleep | 51 |         | NULL        |
    | 3 | root | localhost | NULL | Query | 0 | starting      | show processlist     |
    | 4 | root | localhost | NULL | Query | 9 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int |
     ---- ------ ----------- ------ --------- ------ --------------------------------- ------------------------------------ 
    3 rows in set (0.00 sec)
    session3> select object_type,object_schema,object_name,lock_type,lock_duration,lock_status,owner_thread_id from performance_schema.metadata_locks;
     ------------- -------------------- ---------------- --------------------- --------------- ------------- ----------------- 
    | object_type | object_schema  | object_name | lock_type   | lock_duration | lock_status | owner_thread_id |
     ------------- -------------------- ---------------- --------------------- --------------- ------------- ----------------- 
    | TABLE  | slowtech   | t1    | SHARED_WRITE  | TRANSACTION | GRANTED  |    27 |
    | GLOBAL  | NULL    | NULL   | INTENTION_EXCLUSIVE | STATEMENT  | GRANTED  |    29 |
    | SCHEMA  | slowtech   | NULL   | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED  |    29 |
    | TABLE  | slowtech   | t1    | SHARED_UPGRADABLE | TRANSACTION | GRANTED  |    29 |
    | TABLE  | slowtech   | t1    | EXCLUSIVE   | TRANSACTION | PENDING  |    29 |
    | TABLE  | performance_schema | metadata_locks | SHARED_READ   | TRANSACTION | GRANTED  |    28 |
     ------------- -------------------- ---------------- --------------------- --------------- ------------- ----------------- 
    6 rows in set (0.00 sec)
    

    |  4 | root | localhost | test | Sleep  |  841 |                                | NULL                                              |

    通过show processlist看不到TableA上有任何操作,在information_schema.innodb_trx中也没有任何进行中的事务。这很可能是因为在一个显式的事务中,对TableA进行了一个失败的操作(比如查询了一个不存在的字段),这时事务没有开始,但是失败语句获取到的锁依然有效。从performance_schema.events_statements_current表中可以查到失败的语句。

     

    测试场景

    下面结合一个简单的Demo,来看看在MySQL 5.7中如何定位DDL操作的阻塞问题。

    session1> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    session1> delete from slowtech.t1 where id=2;
    Query OK, 1 row affected (0.00 sec)
    
    session1> select * from slowtech.t1;
     ------ ------ 
    | id   | name |
     ------ ------ 
    |    1 | a    |
     ------ ------ 
    1 row in set (0.00 sec)
    
    session1> update slowtech.t1 set name='c' where id=1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    session2> alter table slowtech.t1 add c1 int; ##被阻塞
    
    session3> show processlist;
     ---- ------ ----------- ------ --------- ------ --------------------------------- ------------------------------------ 
    | Id | User | Host      | db   | Command | Time | State                           | Info                               |
     ---- ------ ----------- ------ --------- ------ --------------------------------- ------------------------------------ 
    |  2 | root | localhost | NULL | Sleep   |   51 |                                 | NULL                               |
    |  3 | root | localhost | NULL | Query   |    0 | starting                        | show processlist                   |
    |  4 | root | localhost | NULL | Query   |    9 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int |
     ---- ------ ----------- ------ --------- ------ --------------------------------- ------------------------------------ 
    3 rows in set (0.00 sec)
    
    session3> select object_type,object_schema,object_name,lock_type,lock_duration,lock_status,owner_thread_id from performance_schema.metadata_locks;
     ------------- -------------------- ---------------- --------------------- --------------- ------------- ----------------- 
    | object_type | object_schema      | object_name    | lock_type           | lock_duration | lock_status | owner_thread_id |
     ------------- -------------------- ---------------- --------------------- --------------- ------------- ----------------- 
    | TABLE       | slowtech           | t1             | SHARED_WRITE        | TRANSACTION   | GRANTED     |              27 |
    | GLOBAL      | NULL               | NULL           | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     |              29 |
    | SCHEMA      | slowtech           | NULL           | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     |              29 |
    | TABLE       | slowtech           | t1             | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     |              29 |
    | TABLE       | slowtech           | t1             | EXCLUSIVE           | TRANSACTION   | PENDING     |              29 |
    | TABLE       | performance_schema | metadata_locks | SHARED_READ         | TRANSACTION   | GRANTED     |              28 |
     ------------- -------------------- ---------------- --------------------- --------------- ------------- ----------------- 
    6 rows in set (0.00 sec)
    

    这里,重点关注lock_status,"PENDING"代表线程在等待MDL,而"GRANTED"则代表线程持有MDL。

     

    如何找出引起阻塞的会话

    结合owner_thread_id,可以可到,是29号线程在等待27号线程的MDL,此时,可kill掉52号线程。

    但需要注意的是,owner_thread_id给出的只是线程ID,并不是show processlist中的ID。如果要查找线程对应的processlist id,需查询performance_schema.threads表。

    session3> select * from performance_schema.threads where thread_id in (27,29)G
    *************************** 1. row ***************************
              THREAD_ID: 27
                   NAME: thread/sql/one_connection
                   TYPE: FOREGROUND
         PROCESSLIST_ID: 2
       PROCESSLIST_USER: root
       PROCESSLIST_HOST: localhost
         PROCESSLIST_DB: NULL
    PROCESSLIST_COMMAND: Sleep
       PROCESSLIST_TIME: 214
      PROCESSLIST_STATE: NULL
       PROCESSLIST_INFO: NULL
       PARENT_THREAD_ID: 1
                   ROLE: NULL
           INSTRUMENTED: YES
                HISTORY: YES
        CONNECTION_TYPE: Socket
           THREAD_OS_ID: 9800
    *************************** 2. row ***************************
              THREAD_ID: 29
                   NAME: thread/sql/one_connection
                   TYPE: FOREGROUND
         PROCESSLIST_ID: 4
       PROCESSLIST_USER: root
       PROCESSLIST_HOST: localhost
         PROCESSLIST_DB: NULL
    PROCESSLIST_COMMAND: Query
       PROCESSLIST_TIME: 172
      PROCESSLIST_STATE: Waiting for table metadata lock
       PROCESSLIST_INFO: alter table slowtech.t1 add c1 int
       PARENT_THREAD_ID: 1
                   ROLE: NULL
           INSTRUMENTED: YES
                HISTORY: YES
        CONNECTION_TYPE: Socket
           THREAD_OS_ID: 9907
    2 rows in set (0.00 sec)
    

    将这两张表结合,借鉴sys.innodb_lock _waits的输出,实际上我们也可以直观地呈现MDL的等待关系。

    SELECT
        a.OBJECT_SCHEMA AS locked_schema,
        a.OBJECT_NAME AS locked_table,
        "Metadata Lock" AS locked_type,
        c.PROCESSLIST_ID AS waiting_processlist_id,
        c.PROCESSLIST_TIME AS waiting_age,
        c.PROCESSLIST_INFO AS waiting_query,
        c.PROCESSLIST_STATE AS waiting_state,
        d.PROCESSLIST_ID AS blocking_processlist_id,
        d.PROCESSLIST_TIME AS blocking_age,
        d.PROCESSLIST_INFO AS blocking_query,
        concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
    FROM
        performance_schema.metadata_locks a
    JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
    AND a.OBJECT_NAME = b.OBJECT_NAME
    AND a.lock_status = 'PENDING'
    AND b.lock_status = 'GRANTED'
    AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
    AND a.lock_type = 'EXCLUSIVE'
    JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
    JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_IDG
    
    *************************** 1. row ***************************
                   locked_schema: slowtech
                    locked_table: t1
                     locked_type: Metadata Lock
          waiting_processlist_id: 4
                     waiting_age: 259
                   waiting_query: alter table slowtech.t1 add c1 int
                   waiting_state: Waiting for table metadata lock
         blocking_processlist_id: 2
                    blocking_age: 301
                  blocking_query: NULL
    sql_kill_blocking_connection: KILL 2
    1 row in set (0.00 sec)
    

    输出一目了然,DDL操作如果要获得MDL,执行kill 2即可。

     

    这里,重点关注lock_status,"PENDING"代表线程在等待MDL,而"GRANTED"则代表线程持有MDL。

    |  5 | root | localhost | test | Query  |  709 | Waiting for table metadata lock | alter table test_lock add column name2 varchar(50) |

    官方手册上对此的说明如下:

    所以,网上有kill空闲(Command为Sleep)连接的说法,其实也不无道理,但这样做就太简单粗暴了,难免会误杀。

    官方的sys.schematablelock_waits

    实际上,MySQL 5.7在sys库中也集成了类似功能,同样的场景,其输出如下,

    mysql> select * from sys.schema_table_lock_waitsG
    *************************** 1. row ***************************
                   object_schema: slowtech
                     object_name: t1
               waiting_thread_id: 29
                     waiting_pid: 4
                 waiting_account: root@localhost
               waiting_lock_type: EXCLUSIVE
           waiting_lock_duration: TRANSACTION
                   waiting_query: alter table slowtech.t1 add c1 int
              waiting_query_secs: 446
     waiting_query_rows_affected: 0
     waiting_query_rows_examined: 0
              blocking_thread_id: 27
                    blocking_pid: 2
                blocking_account: root@localhost
              blocking_lock_type: SHARED_READ
          blocking_lock_duration: TRANSACTION
         sql_kill_blocking_query: KILL QUERY 2
    sql_kill_blocking_connection: KILL 2
    *************************** 2. row ***************************
                   object_schema: slowtech
                     object_name: t1
               waiting_thread_id: 29
                     waiting_pid: 4
                 waiting_account: root@localhost
               waiting_lock_type: EXCLUSIVE
           waiting_lock_duration: TRANSACTION
                   waiting_query: alter table slowtech.t1 add c1 int
              waiting_query_secs: 446
     waiting_query_rows_affected: 0
     waiting_query_rows_examined: 0
              blocking_thread_id: 29
                    blocking_pid: 4
                blocking_account: root@localhost
              blocking_lock_type: SHARED_UPGRADABLE
          blocking_lock_duration: TRANSACTION
         sql_kill_blocking_query: KILL QUERY 4
    sql_kill_blocking_connection: KILL 4
    2 rows in set (0.00 sec)
    

    具体分析下官方的输出,

    只有一个alter table操作,却产生了两条记录,而且两条记录的kill对象竟然还不一样,对表结构不熟悉及不仔细看记录内容的话,难免会kill错对象。

    不仅如此,如果有N个查询被DDL操作堵塞,则会产生N*2条记录。在阻塞操作较多的情况下,这N*2条记录完全是个噪音。

    而之前的SQL,无论有多少操作被阻塞,一个alter table操作,就只会输出一条记录。

     

    如何找出引起阻塞的会话

    |  6 | root | localhost | NULL | Query  |    0 | starting                        | show processlist                                  |

    If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.

    其实,既然是事务,在information_schema. innodb_trx中肯定会有记录,如会话1中的事务,在表中的记录如下,

    如何查看阻塞会话已经执行过的操作

    但上面这个SQL也有遗憾,其blocking_query为NULL,而在会话1中,其明明已经执行了三个SQL。

    这个与performance_schema.threads(类似于show processlist)有关,其只会输出当前正在运行的SQL,对于已经执行过的,实际上是没办法看到。

    但在线上,kill是一个需要谨慎的操作,毕竟你很难知道kill的是不是业务关键操作?又或者,是个批量update操作?那么,有没有办法抓到该事务之前的操作呢?

    答案,有。

    即Performance Schema中记录Statement Event(操作事件)的表,具体包括events_statements_current,events_statements_history,events_statements_history_long,prepared_statements_instances。

    常用的是前面三个。

    新葡亰496net 4

    三者的表结构完全一致,其中,events_statements_history又包含了events_statements_current的操作,所以我们这里会使用events_statements_history。

    终极SQL如下,

    SELECT
        locked_schema,
        locked_table,
        locked_type,
        waiting_processlist_id,
        waiting_age,
        waiting_query,
        waiting_state,
        blocking_processlist_id,
        blocking_age,
        substring_index(sql_text,"transaction_begin;" ,-1) AS blocking_query,
        sql_kill_blocking_connection
    FROM
        (
            SELECT
                b.OWNER_THREAD_ID AS granted_thread_id,
                a.OBJECT_SCHEMA AS locked_schema,
                a.OBJECT_NAME AS locked_table,
                "Metadata Lock" AS locked_type,
                c.PROCESSLIST_ID AS waiting_processlist_id,
                c.PROCESSLIST_TIME AS waiting_age,
                c.PROCESSLIST_INFO AS waiting_query,
                c.PROCESSLIST_STATE AS waiting_state,
                d.PROCESSLIST_ID AS blocking_processlist_id,
                d.PROCESSLIST_TIME AS blocking_age,
                d.PROCESSLIST_INFO AS blocking_query,
                concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
            FROM
                performance_schema.metadata_locks a
            JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
            AND a.OBJECT_NAME = b.OBJECT_NAME
            AND a.lock_status = 'PENDING'
            AND b.lock_status = 'GRANTED'
            AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
            AND a.lock_type = 'EXCLUSIVE'
            JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
            JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID
        ) t1,
        (
            SELECT
                thread_id,
                group_concat(   CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text
            FROM
                performance_schema.events_statements_history
            GROUP BY thread_id
        ) t2
    WHERE
        t1.granted_thread_id = t2.thread_id G
    
    *************************** 1. row ***************************
                   locked_schema: slowtech
                    locked_table: t1
                     locked_type: Metadata Lock
          waiting_processlist_id: 4
                     waiting_age: 294
                   waiting_query: alter table slowtech.t1 add c1 int
                   waiting_state: Waiting for table metadata lock
         blocking_processlist_id: 2
                    blocking_age: 336
                  blocking_query: delete from slowtech.t1 where id=2;select * from slowtech.t1;update slowtech.t1 set name='c' where id=1
    sql_kill_blocking_connection: KILL 2
    1 row in set, 1 warning (0.00 sec)
    

    从上面的输出可以看到,blocking_query中包含了会话1中当前事务的所有操作,按执行的先后顺序输出。

    需要注意的是,默认情况下,events_statements_history只会保留每个线程最近的10个操作,如果事务中进行的操作较多,实际上也是没办法抓全的。

    Anyway, it is better than nothing!

     

    结合owner_thread_id,可以可到,是29号线程在等待27号线程的MDL,此时,可kill掉52号线程。

    ---- ------ ----------- ------ --------- ------ --------------------------------- ----------------------------------------------------

    也就是说除了语法错误,其他错误语句获取到的锁在这个事务提交或回滚之前,仍然不会释放掉。because the failed statement is written to the binary log and the locks protect log consistency 但是解释这一行为的原因很难理解,因为错误的语句根本不会被记录到二进制日志。

    mysql> select * from information_schema.innodb_trxG
    *************************** 1. row ***************************
                        trx_id: 1050390
                     trx_state: RUNNING
                   trx_started: 2018-07-17 08:55:32
         trx_requested_lock_id: NULL
              trx_wait_started: NULL
                    trx_weight: 4
           trx_mysql_thread_id: 2
                     trx_query: NULL
           trx_operation_state: NULL
             trx_tables_in_use: 0
             trx_tables_locked: 1
              trx_lock_structs: 2
         trx_lock_memory_bytes: 1136
               trx_rows_locked: 3
             trx_rows_modified: 2
       trx_concurrency_tickets: 0
           trx_isolation_level: REPEATABLE READ
             trx_unique_checks: 1
        trx_foreign_key_checks: 1
    trx_last_foreign_key_error: NULL
     trx_adaptive_hash_latched: 0
     trx_adaptive_hash_timeout: 0
              trx_is_read_only: 0
    trx_autocommit_non_locking: 0
    1 row in set (0.00 sec)
    

    但需要注意的是,owner_thread_id给出的只是线程ID,并不是show processlist中的ID。如果要查找线程对应的processlist id,需查询performance_schema.threads表。

    可看到ddl操作也被卡住了,之前的事务1也处于sleep状态,无法得知它到底执行了什么。

     

     

    session3> select * from performance_schema.threads where thread_id in (27,29)G
    *************************** 1. row ***************************
       THREAD_ID: 27
        NAME: thread/sql/one_connection
        TYPE: FOREGROUND
      PROCESSLIST_ID: 2
     PROCESSLIST_USER: root
     PROCESSLIST_HOST: localhost
      PROCESSLIST_DB: NULL
    PROCESSLIST_COMMAND: Sleep
     PROCESSLIST_TIME: 214
     PROCESSLIST_STATE: NULL
     PROCESSLIST_INFO: NULL
     PARENT_THREAD_ID: 1
        ROLE: NULL
      INSTRUMENTED: YES
       HISTORY: YES
     CONNECTION_TYPE: Socket
      THREAD_OS_ID: 9800
    *************************** 2. row ***************************
       THREAD_ID: 29
        NAME: thread/sql/one_connection
        TYPE: FOREGROUND
      PROCESSLIST_ID: 4
     PROCESSLIST_USER: root
     PROCESSLIST_HOST: localhost
      PROCESSLIST_DB: NULL
    PROCESSLIST_COMMAND: Query
     PROCESSLIST_TIME: 172
     PROCESSLIST_STATE: Waiting for table metadata lock
     PROCESSLIST_INFO: alter table slowtech.t1 add c1 int
     PARENT_THREAD_ID: 1
        ROLE: NULL
      INSTRUMENTED: YES
       HISTORY: YES
     CONNECTION_TYPE: Socket
      THREAD_OS_ID: 9907
    2 rows in set (0.00 sec)
    

    这时我们查询innodb_trx表可看到事务1也看不到sql信息。

    总之,alter table的语句是很危险的,在操作之前最好确认对要操作的表没有任何进行中的操作、没有未提交事务、也没有显式事务中的报错语句。如果有alter table的维护任务,在无人监管的时候运行,最好通过lock_wait_timeout设置好超时时间,避免长时间的metedata锁等待。

    其中trx_mysql_thread_id是线程id,结合performance_schema.threads,可以知道当前哪些连接上存在着活跃事务,这样就进一步缩小了可被kill的线程范围。

    将这两张表结合,借鉴sys.innodb_lock _waits的输出,实际上我们也可以直观地呈现MDL的等待关系。

    二、解决方案

     

     但从影响程度上,和kill所有Command为Sleep的连接没太大区别,毕竟,kill真正的空闲连接对业务的影响不大。

    SELECT
     a.OBJECT_SCHEMA AS locked_schema,
     a.OBJECT_NAME AS locked_table,
     "Metadata Lock" AS locked_type,
     c.PROCESSLIST_ID AS waiting_processlist_id,
     c.PROCESSLIST_TIME AS waiting_age,
     c.PROCESSLIST_INFO AS waiting_query,
     c.PROCESSLIST_STATE AS waiting_state,
     d.PROCESSLIST_ID AS blocking_processlist_id,
     d.PROCESSLIST_TIME AS blocking_age,
     d.PROCESSLIST_INFO AS blocking_query,
     concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
    FROM
     performance_schema.metadata_locks a
    JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
    AND a.OBJECT_NAME = b.OBJECT_NAME
    AND a.lock_status = 'PENDING'
    AND b.lock_status = 'GRANTED'
    AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
    AND a.lock_type = 'EXCLUSIVE'
    JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
    JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_IDG
    
    *************************** 1. row ***************************
        locked_schema: slowtech
        locked_table: t1
         locked_type: Metadata Lock
      waiting_processlist_id: 4
         waiting_age: 259
        waiting_query: alter table slowtech.t1 add c1 int
        waiting_state: Waiting for table metadata lock
      blocking_processlist_id: 2
        blocking_age: 301
        blocking_query: NULL
    sql_kill_blocking_connection: KILL 2
    1 row in set (0.00 sec)
    

    方案一

     

     此时,依然可以借助performance_schema. events_statements_history表。

    输出一目了然,DDL操作如果要获得MDL,执行kill 2即可。

    我想到的第一种方法是利用performance_schema中的相关信息查询

     

     在上篇MySQL 5.7的分析中,我们是首先知道引发阻塞的线程ID,然后利用events_statements_history表,查看该线程的相关SQL。

    官方的sys.schematablelock_waits

    mysql> show variables like 'performance_schema';

    新葡亰496net, 

     而在MySQL 5.6中,我们并不知道引发阻塞的线程ID,但是,我们可以反其道而行之,利用穷举法,首先统计出所有线程在当前事务执行过的所有SQL,然后再判断这些SQL中是否包含目标表。

    实际上,MySQL 5.7在sys库中也集成了类似功能,同样的场景,其输出如下,

    -------------------- -------

     

     

    mysql> select * from sys.schema_table_lock_waitsG
    *************************** 1. row ***************************
        object_schema: slowtech
         object_name: t1
       waiting_thread_id: 29
         waiting_pid: 4
        waiting_account: root@localhost
       waiting_lock_type: EXCLUSIVE
      waiting_lock_duration: TRANSACTION
        waiting_query: alter table slowtech.t1 add c1 int
       waiting_query_secs: 446
     waiting_query_rows_affected: 0
     waiting_query_rows_examined: 0
       blocking_thread_id: 27
        blocking_pid: 2
       blocking_account: root@localhost
       blocking_lock_type: SHARED_READ
      blocking_lock_duration: TRANSACTION
      sql_kill_blocking_query: KILL QUERY 2
    sql_kill_blocking_connection: KILL 2
    *************************** 2. row ***************************
        object_schema: slowtech
         object_name: t1
       waiting_thread_id: 29
         waiting_pid: 4
        waiting_account: root@localhost
       waiting_lock_type: EXCLUSIVE
      waiting_lock_duration: TRANSACTION
        waiting_query: alter table slowtech.t1 add c1 int
       waiting_query_secs: 446
     waiting_query_rows_affected: 0
     waiting_query_rows_examined: 0
       blocking_thread_id: 29
        blocking_pid: 4
       blocking_account: root@localhost
       blocking_lock_type: SHARED_UPGRADABLE
      blocking_lock_duration: TRANSACTION
      sql_kill_blocking_query: KILL QUERY 4
    sql_kill_blocking_connection: KILL 4
    2 rows in set (0.00 sec)
    

    |Variable_name      | Value |

     

    具体SQL如下,

    具体分析下官方的输出,

    -------------------- -------

     

    SELECT
        processlist_id,
        sql_text 
    FROM
        (
        SELECT
            c.processlist_id,
            substring_index( sql_text, "transaction_begin;",-1 ) sql_text 
        FROM
            information_schema.innodb_trx a,
            (
            SELECT
                thread_id,
                group_concat( CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text 
            FROM
                performance_schema.events_statements_history 
            GROUP BY
                thread_id 
            ) b,
            performance_schema.threads c 
        WHERE
            a.trx_mysql_thread_id = c.processlist_id 
            AND b.thread_id = c.thread_id 
        ) t 
    WHERE
        sql_text LIKE '%t1%';
    
     ---------------- --------------------------------------------------------------------------------------------------------- 
    | processlist_id | sql_text                                                                                                |
     ---------------- --------------------------------------------------------------------------------------------------------- 
    |              2 | delete from slowtech.t1 where id=2;select * from slowtech.t1;update slowtech.t1 set name='c' where id=1 |
     ---------------- --------------------------------------------------------------------------------------------------------- 
    1 row in set (0.01 sec)
    

    只有一个alter table操作,却产生了两条记录,而且两条记录的kill对象竟然还不一样,对表结构不熟悉及不仔细看记录内容的话,难免会kill错对象。

    |performance_schema  | ON    |

     

    从输出来看,确实也达到了预期效果。

    不仅如此,如果有N个查询被DDL操作堵塞,则会产生N*2条记录。在阻塞操作较多的情况下,这N*2条记录完全是个噪音。

    -------------------- -------

    记一次MySQL中Waiting for table metadata lock的解决方法

    标签: mysql

    2017-03-29 12:06 2684人阅读 评论(0) 收藏 举报

    新葡亰496net 5 分类:

    MySQL教程(7) 新葡亰496net 6

    版权声明:本文为博主原创文章,未经博主允许不得转载。

     

    目录(?)[ ]

     

    最近项目中的数据库查询经常挂起,应用程序启动后也报操作超时。测试人员就说数据库又挂了(貌似他们眼中的连接失败,查询无果都是挂了),通过 show processlist 一看,满屏都是 Waiting for table metadata lock 状态的连接。第一反应就是kill掉这些连接,奈何连接实在太多,实在kill不过来,于是重启服务,貌似重启果真能解决90%的问题,但如果不找到问题原因,问题也肯定会再次出现。

    在网上查询得知MySQL在进行一些alter table等DDL操作时,如果该表上有未提交的事务则会出现 Waiting for table metadata lock ,而一旦出现metadata lock,该表上的后续操作都会被阻塞(详见 )。所以这个问题需从两方面解决:

     

    而之前的SQL,无论有多少操作被阻塞,一个alter table操作,就只会输出一条记录。

    1 row in set(0.00 sec)

    1. 查看未提交事务

    从 information_schema.innodb_trx 表中查看当前未提交的事务

    select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trxG
    
    • 1

    (G作为结束符时,MySQL Client会把结果以列模式展示,对于列比较长的表,展示更直观)

    字段意义:

    • trx_state: 事务状态,一般为RUNNING
    • trx_started: 事务执行的起始时间,若时间较长,则要分析该事务是否合理
    • trx_mysql_thread_id: MySQL的线程ID,用于kill
    • trx_query: 事务中的sql

    一般只要kill掉这些线程,DDL操作就不会Waiting for table metadata lock。

    需要注意的是,在MySQL5.6中,events_statements_history默认是没有开启的。

    如何查看阻塞会话已经执行过的操作

    通过查看events_statements_current表可看到每一个session正在执行的sql,哪怕它依旧执行完成了,只是没有提交。这里可看到事务1最后执行的正是insert into test_lock values(4,'andy');

    2. 调整锁超时阈值

    lock_wait_timeout 表示获取metadata lock的超时(单位为秒),允许的值范围为1到31536000(1年)。 默认值为31536000。详见  。默认值为一年!!!已哭瞎!将其调整为30分钟

    set session lock_wait_timeout = 1800;
    set global lock_wait_timeout = 1800;
    
    • 1
    • 2

    好让出现该问题时快速故障(failfast)

     

     

     

     

     

     

    mysql> SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE '%statements%';
     -------------------------------- --------- 
    | NAME                           | ENABLED |
     -------------------------------- --------- 
    | events_statements_current      | YES     |
    | events_statements_history      | NO      |
    | events_statements_history_long | NO      |
    | statements_digest              | YES     |
     -------------------------------- --------- 
    4 rows in set (0.00 sec)
    

    但上面这个SQL也有遗憾,其blocking_query为NULL,而在会话1中,其明明已经执行了三个SQL。

    mysql> select * from performance_schema.events_statements_currentG

    waiting for table metadata lock 问题深入分析

    2015-12-07 17:50 2980人阅读 评论(3) 收藏 举报

    新葡亰496net 7 分类:

    -----mysql 问题处理(9) 新葡亰496net 8

    版权声明:本文为博主原创文章,未经博主允许不得转载。

          相信很多msyql dba都碰到锁的问题,在MySQL 5.5.3版本中引入了Metadata lock: DDL语句打破了事务的隔离级别。
    那么会有同学问,为什么在Mysql 5.5.3之前就很少遇到这种锁呢?原因是
        5.5.3版本之前,MySQL事务对于表结构元数据(Metadata)的锁定是语句(statement)粒度的:即语句执行完成后,不管事务是否可以完成,其表结构就可以被其他会话更新掉!
        引入Metadata lock后,表结构元数据(Metadata)的锁定变成了事务(transaction)粒度的,即只有事务结束时才会释放Metadata lock

    怎么出现的?

    程序或者脚本显式开启事务(start transaction),该事务内的query语句(包含select)会占用相关表的metadata lock(profile:Opening tables阶段)。导致后续的所有DDL操作语句全部被阻塞,原因就是获取不到metadata lock。(在mysql 5.6版本后有优化)官方手册参阅:

    我们下面以现网case来探讨这个问题的出现于解决:

     

    case

    业务执行一条简单的alter table 操作,增加一个字段,很普通的一条sql,而且表不大,数据量很少,执行却消耗几百秒没反应(现场忘记截图)

    新葡亰496net 9

    补充一个测试图

    从图可以看出业务执行的语句遇到metadata lock了。

    1、        分析mysql的实例的情况

    1.1 mysql> show processlist;

    新葡亰496net 10

    除了有一个 Waiting for table 之外没有其它的操作进程,全部是sleep进程。这时你觉得奇怪吗?为什么没有其它的进程锁住这个表,会导致这个ddl语句一直卡住呢? 我们接着分析。

    1.2 查看表是否太大 mysql> show table status like 'tbl_xx' G

    新葡亰496net 11

                               图1.2

    看出表非常小,不存在由于数据量大导致更新慢的问题;

    1.3 查看引擎状态 mysql> show engine innodb status G

    新葡亰496net 12

    数据量太大,一屏幕都显示不完,不看了。

    既然几个比较直接的方法都查不到原因,那只能更深入的查下了,我打算从数据字典中查下(information_schema,performance_schema):

    1.4,查找当前等待事务:

    mysql> select * from  performance_schema .events_waits_current;

    Empty set (0.03 sec)

    显示空。

    查找information_schema中的事件表(EVENTS)、锁等待表(INNODB_LOCK_WAITS),innodb当前出现的锁(INNODB_LOCKS)均没看到异常(这里就不贴图了)。

    1.5 查找事务

    既然造成该锁的原因是事务没有提交导致的,那我们应该去查找当前是否有事务在运行(runing注:由于事务一直是runing状态,这也就是为什么我之前查找各种锁都找不到的原因)

    mysql> select * from information_schema.innodb_trx;

    (此图又被刷不见了)不过有重大发现:一个trx_mysql_thread_id: 275255348 是从trx_started: 2015-12-03 14:58:45 一直处于runing状态的。

    既然我们找到了id了 那我们再回顾使用show processlist查找该ID就行了:

    新葡亰496net 13

    发现了吗,该ID一直是sleep状态。很难发现该进程打开了这个表(可以通过show open tables 查看当前打开的表)。

     

    解决办法:询问了开发这个点的脚本,操作。确认后通过后台mysql 直接kill掉这个进程,业务的alter操作瞬间完成。

    附:欢迎大家一起探讨研究

     

     

     

     

     

     

     

    MySQL事务与Metadata Lock问题 2014-12-25 11:25:00

    分类: MySQL

     

     

    这个与performance_schema.threads(类似于show processlist)有关,其只会输出当前正在运行的SQL,对于已经执行过的,实际上是没办法看到。

    不过方案1有个缺陷,一个事务可能有一组sql组成,这个方法只能看到这个事务最后执行的是什么SQL,无法看到全部。也就是说,关于information_schema.processlist和events_statements_current如何一一对应起来,可以通过performance_schema.threads表来关联,语句如下:

    环境说明:

        MySQL 5.6.16

        OS:Linux RedHat 6.2 64bit

    但在线上,kill是一个需要谨慎的操作,毕竟你很难知道kill的是不是业务关键操作?又或者,是个批量update操作?那么,有没有办法抓到该事务之前的操作呢?

    mysql> select * from performance_schema.events_statements_current where THREAD_ID in (select THREAD_ID from performance_schema.threads where PROCESSLIST_ID=4)G

    1.问题描述

        目前新上一个使用MySQL数据库项目,在数据库中,每隔5分钟做truncate某个表操作,经常出现metadata lock锁等待,导致后面的对这个表的所有操作(包括读)全部metadata lock等待。严重影响了数据库运行。

        且metadata lock锁等待不同于普通的行级锁,等待超时时间默认为365天,而普通的行级锁超时是120s

    mysql> show variables like '%lock_wait%';

    -------------------------- ----------

    | Variable_name            | Value    |

    -------------------------- ----------

    | innodb_lock_wait_timeout | 120      |

    | lock_wait_timeout        | 31536000 |

    -------------------------- ----------

    2 rows in set (0.00 sec)

     

     

     

    因此如果metadata lock锁的源头不释放,则会一直阻塞,必须需要人为干预。

     

    答案,有。

    如果你是MySQL 5.7版本,可以通过查看sys.session视图和sys.processlist视图得到最后一次执行的SQL语句。

    2.为什么需要Metadata lock

    Metadata lock介绍:参考官方手册:

        MySQL 5.5.3 and up uses metadata locking to manage access to objects (tables, triggers, and so forth). Metadata locking is used to ensure data consistency but does involve some overhead, which increases as query volume increases. Metadata contention increases the more that multiple queries attempt to access the same objects.

       

     

    参考MySQL bug989:

        该bug是一个比较著名的问题:

    新葡亰496net 14

    我们知道,binlog内操作的记录是基于事务的提交顺序进行的,如果有一个事务未执行完成,而这个时候drop了表,这样在从库的执行顺序就会出现问题。

     

    因此MySQL在5.5.3版本后引入了Metadata lock锁,事务释放后才会释放Metadata lock,这样在事务完成期间,是不能进行DDL操作的。

     

    即Performance Schema中记录Statement Event(操作事件)的表,具体包括

    方案二

    3.Metadata lock监控

         当对表的DDL操作很慢的时候,可以通过如下方法查看当前是否是在等待Metadata lock:

     

    mysql> select * from information_schema.processlist where state = 'Waiting for table metadata lock';

    ---- ------ ----------- ------ --------- ------ --------------------------------- --------------------------

    | ID | USER | HOST      | DB   | COMMAND | TIME | STATE                           | INFO                     |

    ---- ------ ----------- ------ --------- ------ --------------------------------- --------------------------

    |  7 | root | localhost | NULL | Query   |   56 | Waiting for table metadata lock | truncate table baofeng.a |

    7中定位DDL被打断的问题,MySQL怎么着定位未提交业务实施的SQL语句。|  9 | root | localhost | NULL | Query   |    4 | Waiting for table metadata lock | select * from baofeng.a  |

    ---- ------ ----------- ------ --------- ------ --------------------------------- --------------------------

    2 rows in set (0.00 sec)

     

     

        其中线程ID为7的truncate被其他事务阻塞,而线程ID为9的被truncate table阻塞,因此该查询主要看哪个会话在做DDL操作,其他的会话的state为metadata lock均为被该DDL阻塞。

     

        那么问题来了,怎么去判断DDL被什么锁住了?

        这个从目前来看比较困难,不能直观的去判断,网上有人做了一个插件可以实现(在MariaDB 10中默认已提供类似功能):

            

       

    [root@mysql-db101 tmp]# tar -xzvf mysql-5.6.16.tar.gz

    [root@mysql-db101 tmp]# unzip mysql-plugin-mdl-info-master.zip

    [root@mysql-db101 tmp]# cp -r ./mysql-plugin-mdl-info-master/src ./mysql-5.6.16/plugin/mdl_info

    [root@mysql-db101 tmp]# cd ./mysql-5.6.16

     

    [root@mysql-db101 mysql-5.6.16]# cmake

    -DCMAKE_INSTALL_PREFIX=/home/mysql/mysql

    -DMYSQL_DATADIR=/home/mysql/data

    -DMYSQL_TCP_PORT=3306

    #注意,mysql是以源代码编译出的debug版本,那编译插件的时候不要加 -DBUILD_CONFIG=mysql_release

     

    [root@mysql-db101 mysql-5.6.16]# cd plugin/mdl_info/

    [root@mysql-db101 mdl_info]# make

    [root@mysql-db101 mdl_info]# make install

     

    mysql> INSTALL PLUGIN MDL_LOCKS SONAME 'mdl_info.so';

    ERROR 1127 (HY000): Can't find symbol 'MDL_LOCKS' in library

    mysql> 

    mysql> 

    mysql> show variables like '%plugin%';

    --------------- -------------------------------

    | Variable_name | Value                         |

    --------------- -------------------------------

    | plugin_dir    | /home/mysql/mysql/lib/plugin/ |

    --------------- -------------------------------

    1 row in set (0.00 sec)

     

    mysql> INSTALL PLUGIN MDL_info SONAME 'mdl_info.so';     

    Query OK, 0 rows affected (0.02 sec)

    #这里plugin_name为MDL_INFO,而非文档中说的MDL_LOCKS

     

    mysql> select * from information_schema.mdl_info;

    ----------- ------------- --------------------- ----------- ---------- ------

    | THREAD_ID | DURATION    | TYPE                | NAMESPACE | DATABASE | NAME |

    ----------- ------------- --------------------- ----------- ---------- ------

    |         6 | TRANSACTION | SHARED_READ         | TABLE     | baofeng  | a    |

    |         7 | STATEMENT   | INTENTION_EXCLUSIVE | GLOBAL    |          |      |

    |         7 | TRANSACTION | INTENTION_EXCLUSIVE | SCHEMA    | baofeng  |      |

    ----------- ------------- --------------------- ----------- ---------- ------

    3 rows in set (0.00 sec)

    这里可以看到线程6阻塞了线程7的truncate操作

     

     

               

    通过如下语句查看相应的会话情况:

    mysql> select 

        ->     a.*,b.user,b.host,b.command,b.time,b.state,b.info

        -> from

        ->     information_schema.mdl_info a,

        ->     information_schema.PROCESSLIST b

        -> where

        ->     a.thread_id = b.idG;

    *************************** 1. row ***************************

    THREAD_ID: 6

     DURATION: TRANSACTION

         TYPE: SHARED_READ

    NAMESPACE: TABLE

     DATABASE: baofeng

         NAME: a

         user: root

         host: localhost

      command: Sleep

         time: 1035

        state: 

         info: NULL

    *************************** 2. row ***************************

    THREAD_ID: 7

     DURATION: STATEMENT

         TYPE: INTENTION_EXCLUSIVE

    NAMESPACE: GLOBAL

     DATABASE: 

         NAME: 

         user: root

         host: localhost

      command: Query

         time: 990

        state: Waiting for table metadata lock

         info: truncate table baofeng.a

    *************************** 3. row ***************************

    THREAD_ID: 7

     DURATION: TRANSACTION

         TYPE: INTENTION_EXCLUSIVE

    NAMESPACE: SCHEMA

     DATABASE: baofeng

         NAME: 

         user: root

         host: localhost

      command: Query

         time: 990

        state: Waiting for table metadata lock

         info: truncate table baofeng.a

    3 rows in set (0.00 sec)

     

    ERROR: 

    No query specified

     

     

             

           

    events_statements_current,events_statements_history,events_statements_history_long,prepared_statements_instances。

    然后我想到了是不是可以用general_log的方式,一般情况下general_log不大可能打开,所以我们先打开general_log等着问题复现的方式来定位,经测试,即使事务没有提交,一样会写到general_log。

    4.导致Metadata Lock的场景

    参考:

    常用的是前面三个。

    mysql> show variables like '%general%';

    场景1:

        会话1正在对表a进行DML操作(包括query),这个时候会话2执行DDL操作,需要获取metadata独占锁,因此等待会话1。

        这个时候可以通过show processlist能查看该会话(该会话的state不会waiting for table metadata lock)

    新葡亰496net 15

    ------------------ -------------------------------------------

    场景2:

        会话1对表a进行DML(包括query)事务操作后,没有commit/rollback,这个时候show processlist是看到的只是会话处于sleep状态,执行的SQL显示为空。而这个时候会话2执行DDL操作,同样获取不到metadata独占锁,就会等待。可以通过查询系统事务表有体现:

     

    mysql> select * from information_schema.innodb_trxG;

    *************************** 1. row ***************************

                        trx_id: 11134

                     trx_state: RUNNING

                   trx_started: 2014-12-23 10:23:44

         trx_requested_lock_id: NULL

              trx_wait_started: NULL

                    trx_weight: 0

           trx_mysql_thread_id: 9

                     trx_query: NULL

           trx_operation_state: NULL

             trx_tables_in_use: 0

             trx_tables_locked: 0

              trx_lock_structs: 0

         trx_lock_memory_bytes: 360

               trx_rows_locked: 0

             trx_rows_modified: 0

       trx_concurrency_tickets: 0

           trx_isolation_level: READ COMMITTED

             trx_unique_checks: 1

        trx_foreign_key_checks: 1

    trx_last_foreign_key_error: NULL

     trx_adaptive_hash_latched: 0

     trx_adaptive_hash_timeout: 10000

              trx_is_read_only: 0

    trx_autocommit_non_locking: 0

    1 row in set (0.00 sec)

     

    ERROR: 

    No query specified

     

     

        但是如果事务很多,则没办法判断是哪个会话导致。

        该场景最为普遍,而且是最频发,后面重点会对该场景进行测试。

     

    三者的表结构完全一致,其中,events_statements_history又包含了events_statements_current的操作,所以我们这里会使用events_statements_history。

    | Variable_name    | Value                                     |

    场景3:

        通过show processlist看不到表A上有任何操作,在information_schema.innodb_trx中也没有任何进行中的事务。这很可能是因为在一个显式的事务中,对表A进行了一个失败的操作(比如查询了一个不存在的字段),这时事务没有开始,但是失败语句获取到的锁依然有效。从performance_schema.events_statements_current表中可以查到失败的语句。

     

    官方手册上对此的说明如下:

        If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.

        也就是说除了语法错误,其他错误语句获取到的锁在这个事务提交或回滚之前,仍然不会释放掉。because the failed statement is written to the binary log and the locks protect log consistency 但是解释这一行为的原因很难理解,因为错误的语句根本不会被记录到二进制日志

        

    终极SQL如下,

    ------------------ -------------------------------------------

    5.如何快速处理Metadata lock

        如果是手工执行的DDL操作,例如加字段、drop表等,可以手工Cancel,先查找对当前该表是否在执行长事务操作,或者有未提交事务。确认没问题后再执行DDL操作。

        如果是应用程序中执行的DDL操作,例如truncate,这个时候没办法调整应用,则可以利用MDL插件,去查询当前的DDL被哪个会话阻塞,kill掉该会话线程。这个方法前提是需要安装MDL插件,目前我们的环境还都没有安装。

     

    SELECT
     locked_schema,
     locked_table,
     locked_type,
     waiting_processlist_id,
     waiting_age,
     waiting_query,
     waiting_state,
     blocking_processlist_id,
     blocking_age,
     substring_index(sql_text,"transaction_begin;" ,-1) AS blocking_query,
     sql_kill_blocking_connection
    FROM
     (
      SELECT
       b.OWNER_THREAD_ID AS granted_thread_id,
       a.OBJECT_SCHEMA AS locked_schema,
       a.OBJECT_NAME AS locked_table,
       "Metadata Lock" AS locked_type,
       c.PROCESSLIST_ID AS waiting_processlist_id,
       c.PROCESSLIST_TIME AS waiting_age,
       c.PROCESSLIST_INFO AS waiting_query,
       c.PROCESSLIST_STATE AS waiting_state,
       d.PROCESSLIST_ID AS blocking_processlist_id,
       d.PROCESSLIST_TIME AS blocking_age,
       d.PROCESSLIST_INFO AS blocking_query,
       concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
      FROM
       performance_schema.metadata_locks a
      JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
      AND a.OBJECT_NAME = b.OBJECT_NAME
      AND a.lock_status = 'PENDING'
      AND b.lock_status = 'GRANTED'
      AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
      AND a.lock_type = 'EXCLUSIVE'
      JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
      JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID
     ) t1,
     (
      SELECT
       thread_id,
       group_concat( CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text
      FROM
       performance_schema.events_statements_history
      GROUP BY thread_id
     ) t2
    WHERE
     t1.granted_thread_id = t2.thread_id G
    *************************** 1. row ***************************
        locked_schema: slowtech
        locked_table: t1
         locked_type: Metadata Lock
      waiting_processlist_id: 4
         waiting_age: 294
        waiting_query: alter table slowtech.t1 add c1 int
        waiting_state: Waiting for table metadata lock
      blocking_processlist_id: 2
        blocking_age: 336
        blocking_query: delete from slowtech.t1 where id=2;select * from slowtech.t1;update slowtech.t1 set name='c' where id=1
    sql_kill_blocking_connection: KILL 2
    1 row in set, 1 warning (0.00 sec)
    

    | general_log      | OFF                                       |

    6.如何避免Metadata Lock

    从上面的输出可以看到,blocking_query中包含了会话1中当前事务的所有操作,按执行的先后顺序输出。

    | general_log_file | /data/mysql/3306/data/qs-ops-db-01.log |

    6.1.关注autocommit

        autocommit分成2个层次:

    • 数据库autocommit
    • 客户端工具的autocommit

        MySQL默认的autocommit为1,即自动提交,这种方式不太安全,因为事务默认不受人为控制,因此建议关闭autocommit。咨询了支付宝的MySQL DBA,支付宝的MySQL的autocommit全部是关闭的。

        

        客户端工具分为2种,一种是继承数据库的autocommit模式,例如SQLyog、Mysql命令行接口;还有一种是自己独立的autocommit,例如MySQL workbench,设置工具本身的autocommit,而无视数据库层面autocommit。

        无论是开发还是维护,一定要弄清楚自己的客户端的autocommit模式。

     

        无论是使用哪种客户端工具,首先要弄清楚当前环境下的autocommit方式是什么,如果不是autocommit,一定要确保所有的操作都需要显示的commit/rollback,否则即使是select查询某个表,甚至是语义(select一个错误的字段)报错,也会造成对其他会话对该表的DDL的metadata lock等待。

    需要注意的是,默认情况下,events_statements_history只会保留每个线程最近的10个操作,如果事务中进行的操作较多,实际上也是没办法抓全的。

    ------------------ -------------------------------------------

    6.2.开发中注意事项

        1、首先要确认驱动中的autocommit级别,例如JDBC中,默认conn.setAutoCommit()=true,

              当在该模式下,无论做select还是DML操作,均会自动提交,不会造成应用阻塞DDL操作。

        2、当我们需要开启事务,设置conn.setAutoCommit(false),任何SQL操作(包括读)操作后 需要显式的调用conn.commit(),或者事务完成后conn.setAutoCommit(true)开启默认自动提交,才会释放元数据锁。

        3、注意SQL执行后,一定要确保在很短的时间内显式commit/rollback或者conn.setAutoCommit(true)

     

        做了相关测试,数据库的autocommit参数的设置结果,与应用中的conn.setAutoCommit(false/true)没有任何关系。

     

    来自为知笔记(Wiz)

     

     

     

     

    总结

    2 rows in set (0.00 sec)

    以上所述是小编给大家介绍的解决MySQL 5.7中如何定位DDL被阻塞的问题,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持!

    mysql> set global general_log=1;

    您可能感兴趣的文章:

    • 浅析mysql.data.dll驱动各版本介绍
    • PHP Warning: PHP Startup: Unable to load dynamic library D:/php5/ext/php_mysqli.dll
    • libmysql.dll与php.ini是否真的要拷贝到c:windows目录下呢
    • 关于在php.ini中添加extension=php_mysqli.dll指令的说明

    Query OK, 0 rowsaffected (0.00 sec)

    开启general日志后,只要知道了未提交事务的进程号就可以完美找到对应的SQL语句了。

    $ cat /data/mysql/3306/data/qs-ops-db-01.log | grep 4

    mysqld, Version: 5.7.17-log (MySQL Community Server (GPL)). started with:

    Tcp port: 3306  Unix socket: /data/mysql/3306/mysql.sock

    Time                 Id Command    Argument

    2017-03-29T07:22:00.949233Z 4 Query begin

    2017-03-29T07:22:11.090712Z 4 Query update test_lock set id=123 where id=1

    2017-03-29T07:22:18.347311Z 4 Query insert into test_lock values(4,'andy')

    这样只要后续能否复现的话,就能找到所有的SQL了,就是如果此会话是长连接,那么必然执行的SQL语句较多,这时候就需要慢慢排查了。

    方案三

    假如后面应用层最终commit了,那么会在binlog里记录,可以根据当时的session id去binlog里面查看完整事务。

    新葡亰496net 16

    本文由新葡亰496net发布于网络数据库,转载请注明出处:7中定位DDL被打断的问题,MySQL怎么着定位未提交

    关键词: