MYSQL MDL锁之select阻塞ddl操作

最新推荐文章于 2024-08-28 14:40:38 发布

zuozhiji

最新推荐文章于 2024-08-28 14:40:38 发布

阅读量1.6k

点赞数

分类专栏： MYSQL

本文链接：https://blog.csdn.net/jolly10/article/details/79724830

版权

MYSQL 专栏收录该内容

108 篇文章 3 订阅

订阅专栏

今天做了一个简单的测试，当session1做一个小表的查询结束后，session2对些表做alter操作，发现session会由于拿不到这个阻表的MDL锁而被阻塞，这种情况在oracle中是不会发生的，由于经常用oracle的思维去学mysql，觉得这样的结果有点不可思议。

session 1:

mysql> use l5m
Database changed
mysql> select * from t1;
+------+------+------+------+
| c1   | c2   | c5   | c6   |
+------+------+------+------+
|    1 | A    | NULL | NULL |
|    2 | B    | NULL | NULL |
|    3 | C    | NULL | NULL |
|    4 | D    | NULL | NULL |
+------+------+------+------+
4 rows in set (0.00 sec)

session 2:

mysql> use l5m;
Database changed
mysql> alter table t1 drop column c6;
#hang住了

session 3：

mysql> mysql> show processlist;
+----+------+-----------+--------------------+---------+------+---------------------------------+-------------------------------+
| Id | User | Host      | db                 | Command | Time | State                           | Info                          |
+----+------+-----------+--------------------+---------+------+---------------------------------+-------------------------------+
| 31 | root | localhost | information_schema | Query   |    0 | starting                        | show processlist              |
| 34 | root | localhost | l5m                | Sleep   |  108 |                                 | NULL                          |
| 35 | root | localhost | l5m                | Query   |   56 | Waiting for table metadata lock | alter table t1 drop column c6 |
+----+------+-----------+--------------------+---------+------+---------------------------------+-------------------------------+
3 rows in set (0.00 sec)

可以看到alter操作在等待MDL.

mysql> select *from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 421929623238480
                 trx_state: RUNNING
               trx_started: 2018-03-28 19:21:06
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 34
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 0
         trx_rows_modified: 0
   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)

当前正在运行的事务线程是trx_mysql_thread_id：34，这个进程在show processlist中的状态是sleep状态，说明进程没有在执行任何语句，语句执行完了没有提交从而释放所执有的锁。

event_statement_current可以查看当前所有运行的sql

mysql> select *from performance_schema.events_statements_current\G
*************************** 1. row ***************************
              THREAD_ID: 56
               EVENT_ID: 45
           END_EVENT_ID: NULL
             EVENT_NAME: statement/sql/select
                 SOURCE: socket_connection.cc:101
            TIMER_START: 432625083748987000
              TIMER_END: 432625084020897000
             TIMER_WAIT: 271910000
              LOCK_TIME: 127000000
               SQL_TEXT: select *from performance_schema.events_statements_current
                 DIGEST: NULL
            DIGEST_TEXT: NULL
         CURRENT_SCHEMA: information_schema
            OBJECT_TYPE: NULL
          OBJECT_SCHEMA: NULL
            OBJECT_NAME: NULL
  OBJECT_INSTANCE_BEGIN: NULL
            MYSQL_ERRNO: 0
      RETURNED_SQLSTATE: NULL
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0
          ROWS_AFFECTED: 0
              ROWS_SENT: 0
          ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
     CREATED_TMP_TABLES: 0
       SELECT_FULL_JOIN: 0
 SELECT_FULL_RANGE_JOIN: 0
           SELECT_RANGE: 0
     SELECT_RANGE_CHECK: 0
            SELECT_SCAN: 1
      SORT_MERGE_PASSES: 0
             SORT_RANGE: 0
              SORT_ROWS: 0
              SORT_SCAN: 0
          NO_INDEX_USED: 1
     NO_GOOD_INDEX_USED: 0
       NESTING_EVENT_ID: NULL
     NESTING_EVENT_TYPE: NULL
    NESTING_EVENT_LEVEL: 0
*************************** 2. row ***************************
              THREAD_ID: 59
               EVENT_ID: 9
           END_EVENT_ID: 9
             EVENT_NAME: statement/sql/select
                 SOURCE: socket_connection.cc:101
            TIMER_START: 432017260752293000
              TIMER_END: 432017261073214000
             TIMER_WAIT: 320921000
              LOCK_TIME: 204000000
               SQL_TEXT: select * from t1
                 DIGEST: 52efb366ff07b747f41a99d625b6169e
            DIGEST_TEXT: SELECT * FROM `t1`
         CURRENT_SCHEMA: l5m
            OBJECT_TYPE: NULL
          OBJECT_SCHEMA: NULL
            OBJECT_NAME: NULL
  OBJECT_INSTANCE_BEGIN: NULL
            MYSQL_ERRNO: 0
      RETURNED_SQLSTATE: NULL
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0
          ROWS_AFFECTED: 0
              ROWS_SENT: 4
          ROWS_EXAMINED: 4
CREATED_TMP_DISK_TABLES: 0
     CREATED_TMP_TABLES: 0
       SELECT_FULL_JOIN: 0
 SELECT_FULL_RANGE_JOIN: 0
           SELECT_RANGE: 0
     SELECT_RANGE_CHECK: 0
            SELECT_SCAN: 1
      SORT_MERGE_PASSES: 0
             SORT_RANGE: 0
              SORT_ROWS: 0
              SORT_SCAN: 0
          NO_INDEX_USED: 1
     NO_GOOD_INDEX_USED: 0
       NESTING_EVENT_ID: NULL
     NESTING_EVENT_TYPE: NULL
    NESTING_EVENT_LEVEL: 0
*************************** 3. row ***************************
              THREAD_ID: 60
               EVENT_ID: 5
           END_EVENT_ID: NULL
             EVENT_NAME: statement/sql/alter_table
                 SOURCE: socket_connection.cc:101
            TIMER_START: 432069090402932000
              TIMER_END: 432625084061093000
             TIMER_WAIT: 555993658161000
              LOCK_TIME: 0
               SQL_TEXT: alter table t1 drop column c6
                 DIGEST: NULL
            DIGEST_TEXT: NULL
         CURRENT_SCHEMA: l5m
            OBJECT_TYPE: NULL
          OBJECT_SCHEMA: NULL
            OBJECT_NAME: NULL
  OBJECT_INSTANCE_BEGIN: NULL
            MYSQL_ERRNO: 0
      RETURNED_SQLSTATE: NULL
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0
          ROWS_AFFECTED: 0
              ROWS_SENT: 0
          ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
     CREATED_TMP_TABLES: 0
       SELECT_FULL_JOIN: 0
 SELECT_FULL_RANGE_JOIN: 0
           SELECT_RANGE: 0
     SELECT_RANGE_CHECK: 0
            SELECT_SCAN: 0
      SORT_MERGE_PASSES: 0
             SORT_RANGE: 0
              SORT_ROWS: 0
              SORT_SCAN: 0
          NO_INDEX_USED: 0
     NO_GOOD_INDEX_USED: 0
       NESTING_EVENT_ID: NULL
     NESTING_EVENT_TYPE: NULL
    NESTING_EVENT_LEVEL: 0
3 rows in set (0.00 sec)这

alter语句一直在等待select语句结束事务：

session 1:

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

session 2:

mysql> alter table t1 drop column c6;
Query OK, 0 rows affected (13 min 14.50 sec)等待的时间
Records: 0  Duplicates: 0  Warnings: 0

session1提交事务之后，session2才拿到MDL后执行语句。

所以在mysql做ddl语句的时候一定要特别小心，select语句会执有表的MDL_SHARED_READ(SR)锁，而DDL语句在opening tables阶段会需要MDL_SHARED_NO_WRITE(SNW)锁，在RENAME阶段更会需要MDL_EXCLUSIVE锁（X）,而SR锁与X锁是互斥的。

所以5.6推出了online ddl功能，可以解决DDL语句经常被锁的问题。

参考：https://dev.mysql.com/doc/refman/5.6/en/metadata-locking.html