SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction

问题描述

事务中出现了耗时操作，超过了一个事务的最大处理时间，比如请求第三方接口超时等。

解决方案

增加事务锁时间

• 查询全局事务锁超时时间

show global variables like 'innodb_lock_wait_timeout';

• 设置全局事务锁超时时间，单位为秒

set global innodb_lock_wait_timeout=100;

• 查询当前会话事务锁超时时间

show variables like 'innodb_lock_wait_timeout';

删除锁死进程

若已经增加了事务锁时间还是出现改问题，应解决事务中的耗时操作，以及及时kill掉事务。

• 查询所有进程列表

show full processlist;

• 查询进行中的事务，trx_mysql_thread_id字段

select * from information_schema.innodb_trx;

• KILL掉trx_mysql_thread_id显示的进程

kill 12713500;

• 查询当前出现的锁

select * from information_schema.innodb_locks;

• 查询锁等待关系

select * from information_schema.innodb_lock_waits;

• 查询INNODB引擎状态

show engine innodb status;

输出：

=====================================
2019-07-31 08:40:26 0x7fb602d03700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 19 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 2581689 srv_active, 0 srv_shutdown, 45058550 srv_idle
srv_master_thread log flush and writes: 47640239
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 679349
OS WAIT ARRAY INFO: signal count 11362432
RW-shared spins 0, rounds 6858867, OS waits 560194
RW-excl spins 0, rounds 9391691, OS waits 43344
RW-sx spins 24360, rounds 419267, OS waits 3814
Spin rounds per wait: 6858867.00 RW-shared, 9391691.00 RW-excl, 17.21 RW-sx
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2019-01-28 10:51:40 0x7fb5d4d78700  Cannot drop table `h5`.`activity`
because it is referenced by `h5`.`activity_category_rel`
------------
TRANSACTIONS
------------
Trx id counter 6270341
Purge done for trx's n:o < 6270338 undo n:o < 0 state: running but idle
History list length 2343
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421931598859040, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598857216, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598858128, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598849008, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598855392, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598847184, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598843536, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598846272, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598848096, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598842624, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598838976, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598833504, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598840800, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598830768, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598828944, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598841712, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598839888, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598837152, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598832592, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598834416, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598838064, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598829856, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598835328, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598836240, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598825296, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598844448, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598845360, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598860864, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598828032, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598831680, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598861776, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598874544, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598870896, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598856304, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598854480, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598853568, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598852656, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598850832, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598851744, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598849920, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598827120, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421931598826208, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
20 OS file reads, 16454221 OS file writes, 6727243 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.74 writes/s, 0.68 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 1058 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 9460543, node heap has 95 buffer(s)
Hash table size 9460543, node heap has 42 buffer(s)
Hash table size 9460543, node heap has 466 buffer(s)
Hash table size 9460543, node heap has 474 buffer(s)
Hash table size 9460543, node heap has 275 buffer(s)
Hash table size 9460543, node heap has 63 buffer(s)
Hash table size 9460543, node heap has 378 buffer(s)
Hash table size 9460543, node heap has 44 buffer(s)
13.95 hash searches/s, 25.42 non-hash searches/s
---
LOG
---
Log sequence number 2943385542
Log flushed up to   2943385542
Pages flushed up to 2943256243
Last checkpoint at  2943256243
0 pending log flushes, 0 pending chkp writes
7965621 log i/o's done, 1.74 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 35181821952
Dictionary memory allocated 10026929
Buffer pool size   2096896
Free buffers       2012708
Database pages     82351
Old database pages 30235
Modified db pages  321
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 255, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 19, created 84448, written 6869657
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 82351, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   262112
Free buffers       251493
Database pages     10393
Old database pages 3816
Modified db pages  12
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 3, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 12, created 10381, written 605918
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 10393, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   262112
Free buffers       251675
Database pages     10209
Old database pages 3748
Modified db pages  43
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2, created 10207, written 634631
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 10209, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   262112
Free buffers       251786
Database pages     10093
Old database pages 3705
Modified db pages  50
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2, created 10091, written 645224
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 10093, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   262112
Free buffers       251408
Database pages     10480
Old database pages 3848
Modified db pages  52
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2, created 10478, written 718603
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 10480, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   262112
Free buffers       251486
Database pages     10400
Old database pages 3819
Modified db pages  78
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 5, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1, created 10399, written 3055971
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 10400, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   262112
Free buffers       251569
Database pages     10310
Old database pages 3785
Modified db pages  38
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 0, created 10310, written 517344
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 10310, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   262112
Free buffers       251620
Database pages     10266
Old database pages 3769
Modified db pages  34
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 0, created 10266, written 482212
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 10266, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   262112
Free buffers       251671
Database pages     10200
Old database pages 3745
Modified db pages  14
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 247, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 0, created 12316, written 209754
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 10200, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=15249, Main thread ID=140420543956736, state: sleeping
Number of rows inserted 180140059, updated 1700714, deleted 5987, read 8652301133408
35.26 inserts/s, 0.42 updates/s, 0.00 deletes/s, 877215.57 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

参考

MySQL show命令

MySQL information-schema