Table of Contents
一.问题描述
今天在解决一个大的事务回滚的时候，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)
复制「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
文章被以下合辑收录
评论
目录
