ERROR 1205 (HY000):&nb…

最新推荐文章于 2024-02-01 17:15:38 发布

Hank丶Yoon

最新推荐文章于 2024-02-01 17:15:38 发布

阅读量415

点赞数

CC 4.0 BY-SA版权

分类专栏： MySQL

本文链接：https://blog.youkuaiyun.com/yoon0205/article/details/50605586

MySQL 专栏收录该内容

88 篇文章

订阅专栏

本文探讨了一次使用MySQL数据库时遇到的事务锁超时问题，并通过查询系统信息找到了锁超时的具体事务，最终成功定位问题原因并解决。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

版本：5.6.16

测试库一条update语句报错：ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> UPDATE profitloss_collect_form SET ApproveStatus = 2
-> WHERE formcode = 'SY2015111803' AND (
-> SELECT COUNT(8) FROM profitloss_collect_form_detail WHERE formcode = 'SY2015111803' AND checkstatus = 0
-> ) = 0;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

update语句锁超时，一定是产生锁了。

mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: 3735875273
trx_state: RUNNING
trx_started: 2015-12-07 16:55:32
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 821
trx_mysql_thread_id: 40566656
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 817
trx_lock_memory_bytes: 95784
trx_rows_locked: 104599
trx_rows_modified: 4
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: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 3731614168
trx_state: RUNNING
trx_started: 2015-12-07 11:14:59
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 40317084
trx_query: insert into profitloss_collect_form_detail ........
trx_operation_state: NULL
trx_tables_in_use: 1
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: 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: 10000
trx_is_read_only: 1
trx_autocommit_non_locking: 1
2 rows in set (0.00 sec)

mysql> kill 40317084;