一.问题描述

今天在解决一个大的事务回滚的时候，mysql实例宕机了，等重启后，就发现create table出现了问题:

mysql>  CREATE TABLE `fact_sale_new` (
    ->    `id` bigint NOT NULL AUTO_INCREMENT,
    ->    `sale_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->    `prod_name` varchar(200) NOT NULL,
    ->    `sale_nums` int DEFAULT NULL,
    ->     PRIMARY KEY (`id`)
    ->  ) ENGINE=InnoDB;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> 

二.解决方案

根据报错提示，首先想到的是是否有锁，结果发现都是系统相关的表的锁

mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140421015241616:5:140420935904192
ENGINE_TRANSACTION_ID: 71735
            THREAD_ID: 2
             EVENT_ID: 1
        OBJECT_SCHEMA: mysql
          OBJECT_NAME: innodb_ddl_log
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904192
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140421015241616:12:140420935904280
ENGINE_TRANSACTION_ID: 71735
            THREAD_ID: 2
             EVENT_ID: 1
        OBJECT_SCHEMA: mysql
          OBJECT_NAME: columns
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904280
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140421015241616:16:140420935904368
ENGINE_TRANSACTION_ID: 71735
            THREAD_ID: 2
             EVENT_ID: 1
        OBJECT_SCHEMA: mysql
          OBJECT_NAME: index_column_usage
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904368
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140421015241616:19:140420935904456
ENGINE_TRANSACTION_ID: 71735
            THREAD_ID: 2
             EVENT_ID: 1
        OBJECT_SCHEMA: mysql
          OBJECT_NAME: indexes
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904456
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140421015241616:29:140420935904544
ENGINE_TRANSACTION_ID: 71735
            THREAD_ID: 2
             EVENT_ID: 1
        OBJECT_SCHEMA: mysql
          OBJECT_NAME: tables
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904544
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 6. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140421015241616:30:140420935904632
ENGINE_TRANSACTION_ID: 71735
            THREAD_ID: 2
             EVENT_ID: 1
        OBJECT_SCHEMA: mysql
          OBJECT_NAME: tablespace_files
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904632
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 7. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140421015241616:31:140420935904720
ENGINE_TRANSACTION_ID: 71735
            THREAD_ID: 2
             EVENT_ID: 1
        OBJECT_SCHEMA: mysql
          OBJECT_NAME: tablespaces
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904720
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 8. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140421015241616:18446744069414584331:140420935904808
ENGINE_TRANSACTION_ID: 71735
            THREAD_ID: 2
             EVENT_ID: 1
        OBJECT_SCHEMA: 
          OBJECT_NAME: SDI_11
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935904808
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 9. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140421015241616:4294967294:1043:179:140420935901088
ENGINE_TRANSACTION_ID: 71735
            THREAD_ID: 45
             EVENT_ID: 14
        OBJECT_SCHEMA: mysql
          OBJECT_NAME: tables
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: schema_id
OBJECT_INSTANCE_BEGIN: 140420935901088
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 5, 'fact_sale_new', 369
*************************** 10. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140421015245040:29:140420935928832
ENGINE_TRANSACTION_ID: 72198
            THREAD_ID: 45
             EVENT_ID: 19
        OBJECT_SCHEMA: mysql
          OBJECT_NAME: tables
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140420935928832
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 11. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140421015245040:4294967294:1043:179:140420935925728
ENGINE_TRANSACTION_ID: 72198
            THREAD_ID: 45
             EVENT_ID: 19
        OBJECT_SCHEMA: mysql
          OBJECT_NAME: tables
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: schema_id
OBJECT_INSTANCE_BEGIN: 140420935925728
            LOCK_TYPE: RECORD
            LOCK_MODE: S,REC_NOT_GAP
          LOCK_STATUS: WAITING
            LOCK_DATA: 5, 'fact_sale_new', 369
11 rows in set (0.01 sec)

我们再来看下有没有相关的事务:

mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 71735
                 trx_state: RUNNING
               trx_started: 2021-06-03 17:26:06
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 106524135
       trx_mysql_thread_id: 0
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 7
          trx_lock_structs: 8
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 106524127
   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
       trx_schedule_weight: NULL
1 row in set (0.00 sec)

我们发现这个回滚事务trx_rows_modified一直都没有变化，直接卡住了

ps aux |grep mysql*   
kill -9 pid1 pid2
service mysqld start

这次启动mysql服务花了一点时间，因为是回滚完成后才启动mysql的服务。

[root@hp2 ~]# service mysqld start
Starting MySQL................................................ SUCCESS! 

这次，终于正常了:

mysql>  CREATE TABLE `fact_sale_new` (
    ->    `id` bigint NOT NULL AUTO_INCREMENT,
    ->    `sale_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->    `prod_name` varchar(200) NOT NULL,
    ->    `sale_nums` int DEFAULT NULL,
    ->     PRIMARY KEY (`id`)
    ->  ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)