金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > 7中如何定位DDL被阻塞的问题,6中如何定位DDL被阻

7中如何定位DDL被阻塞的问题,6中如何定位DDL被阻

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-02 23:21

开启MDL的instrument

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)

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

标签: mysql

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

金沙棋牌app手机下载 1 分类:

MySQL教程(7) 金沙棋牌app手机下载 2

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

 

目录(?)[+]

 

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

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

 

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

 

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

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

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.

测试场景

下面结合一个简单的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即可。

 

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

5.如何快速处理Metadata lock

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

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

 

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

 

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

 

 

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

官方的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操作,就只会输出一条记录。

 

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)

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

临时生效

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

6.如何避免Metadata Lock

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

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)

 

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

 

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 |

|  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_金沙棋牌app手机下载,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

 

 

         

       

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

 

场景1:

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

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

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

但上面这个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。

常用的是前面三个。

金沙棋牌app手机下载 3

三者的表结构完全一致,其中,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!

 

 

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锁的源头不释放,则会一直阻塞,必须需要人为干预。

 

 

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

金沙棋牌app手机下载 4  

在配置文件中设置

 

环境说明:

    MySQL 5.6.16

    OS:Linux RedHat 6.2 64bit

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

  1. 慢查询  

  2. 表上有事务未提交

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等待。

永久生效

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

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

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

场景二:

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

 

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

 

具体SQL如下,

 

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

4.导致Metadata Lock的场景

参考:

还是之前的测试Demo

场景一:

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

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

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

 

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)

场景三:

 

 

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

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

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

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 5.7的分析中,我们是首先知道引发阻塞的线程ID,然后利用events_statements_history表,查看该线程的相关SQL。

场景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

 

 

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

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

 

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

 

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)

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)

 

 

 

 

waiting for table metadata lock 问题深入分析

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

金沙棋牌app手机下载 5 分类:

-----mysql 问题处理(9) 金沙棋牌app手机下载 6

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

      相信很多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,而且表不大,数据量很少,执行却消耗几百秒没反应(现场忘记截图)

金沙棋牌app手机下载 7

补充一个测试图

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

1、        分析mysql的实例的情况

1.1 mysql> show processlist;

金沙棋牌app手机下载 8

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

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

金沙棋牌app手机下载 9

                           图1.2

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

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

金沙棋牌app手机下载 10

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

既然几个比较直接的方法都查不到原因,那只能更深入的查下了,我打算从数据字典中查下(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就行了:

金沙棋牌app手机下载 11

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

 

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

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

 

 

 

 

 

 

 

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

分类: MySQL

 

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

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

 

 

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是一个比较著名的问题:

金沙棋牌app手机下载 12

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

 

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

 

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。

在进行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.12。

 

场景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 但是解释这一行为的原因很难理解,因为错误的语句根本不会被记录到二进制日志

    

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:7中如何定位DDL被阻塞的问题,6中如何定位DDL被阻

关键词:

上一篇:没有了

下一篇:没有了