MySQL 5.7 上的元数据锁定，找不到锁定过程? [英] Metadata lock on MySQL 5.7, can't find locking process?
问题描述
所以我有一个 python 服务器，它与 MySQL 数据库有(当前处于休眠状态)连接.我转到 phpMyAdmin 并尝试截断工具"表，它是组织"数据库的一部分.但它不起作用.问题是，我似乎无法找到哪个查询实际上正在锁定它.
So I have a python server with a (currently asleep) connection to the MySQL database. I go to phpMyAdmin and just try to truncate the "tools" table, which is part of the "organize" database. But it doesn't work. Problem is, I can't seem to find which query is actually locking it.
mysql> show full processlist;
+-----+------+-----------+----------+---------+------+---------------------------------+------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+----------+---------+------+---------------------------------+------------------------+
| 175 | user | localhost | organize | Sleep | 1235 | | NULL |
| 244 | user | localhost | NULL | Query | 0 | starting | show full processlist |
| 307 | user | localhost | organize | Query | 272 | Waiting for table metadata lock | TRUNCATE TABLE `tools` |
| 308 | user | localhost | NULL | Sleep | 272 | | NULL |
+-----+------+-----------+----------+---------+------+---------------------------------+------------------------+
4 rows in set (0.00 sec)
mysql>
虽然 SHOW ENGINE INNODB STATUS 给出以下内容:
While the SHOW ENGINE INNODB STATUS gives the following:
mysql> SHOW ENGINE INNODB STATUS;
---------------------------------------------------------------------------+
| InnoDB | |
=====================================
2017-03-16 14:18:08 0x7fa0da508700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 6 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 46 srv_active, 0 srv_shutdown, 4115 srv_idle
srv_master_thread log flush and writes: 4161
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2
OS WAIT ARRAY INFO: signal count 2
RW-shared spins 0, rounds 4, OS waits 2
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 4.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 5384
Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421804127426384, 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
229 OS file reads, 390 OS file writes, 13 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 69257, node heap has 0 buffer(s)
Hash table size 69257, node heap has 0 buffer(s)
Hash table size 69257, node heap has 0 buffer(s)
Hash table size 69257, node heap has 0 buffer(s)
Hash table size 69257, node heap has 0 buffer(s)
Hash table size 69257, node heap has 0 buffer(s)
Hash table size 69257, node heap has 0 buffer(s)
Hash table size 69257, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 2232766
Log flushed up to 2232766
Pages flushed up to 2232766
Last checkpoint at 2232757
0 pending log flushes, 0 pending chkp writes
10 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 274857984
Dictionary memory allocated 317906
Buffer pool size 16384
Free buffers 16129
Database pages 255
Old database pages 0
Modified db pages 0
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 203, created 52, written 370
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 255, 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=25594, Main thread ID=140328678156032, state: sleeping
Number of rows inserted 2782, updated 0, deleted 0, read 2792
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
查看 performance_schema.meta_locks 给出了这个:
mysql> select * from metadata_locks;
+-------------+--------------------+------------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+------------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE | organize | AccessMatrixView | 72627408 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6030 | 334 | 4 |
| TABLE | organize | access_matrix | 80700416 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6030 | 334 | 4 |
| TABLE | organize | people | 81091984 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6030 | 334 | 4 |
| TABLE | organize | tools | 79476128 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6030 | 334 | 7 |
| GLOBAL | NULL | NULL | 140327657064288 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | sql_base.cc:5496 | 335 | 170 |
| SCHEMA | organize | NULL | 140327657360976 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5481 | 335 | 170 |
| TABLE | organize | tools | 140327657150944 | EXCLUSIVE | TRANSACTION | PENDING | sql_parse.cc:6030 | 335 | 170 |
| TABLE | performance_schema | metadata_locks | 140327923115360 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6030 | 351 | 94 |
+-------------+--------------------+------------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
8 rows in set (0.00 sec)
mysql>
有人可以帮我诊断这里的问题吗?
Can anyone help me diagnose the issue here?
推荐答案
所以为了扩展另一个答案，今天我发现 TRUNCATE 不等同于 DELETE FROM表.傻我.
So to expand upon the other answer, this is the day I figure out TRUNCATE isn't equivalent to DELETE FROM table. Silly me.
根据文档:
从逻辑上讲，TRUNCATE TABLE 类似于删除所有行的 DELETE 语句，或一系列 DROP TABLE 和 CREATE TABLE 语句.为了实现高性能，它绕过了删除数据的 DML 方法.因此，它不能被回滚，它不会导致 ON DELETE 触发器被触发，并且它不能对具有父子外键关系的 InnoDB 表执行.
Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements. To achieve high performance, it bypasses the DML method of deleting data. Thus, it cannot be rolled back, it does not cause ON DELETE triggers to fire, and it cannot be performed for InnoDB tables with parent-child foreign key relationships.
虽然 TRUNCATE TABLE 与 DELETE 类似，但它被归类为 DDL 语句而不是 DML 语句.`
Although TRUNCATE TABLE is similar to DELETE, it is classified as a DDL statement rather than a DML statement.`
本质上，处于 SLEEP 状态的进程正在读取表，但是 TRUNCATE 正在尝试执行 DDL 语句，因此它被阻塞，直到另一个 SLEEP-ing 进程被终止.
Essentially, a process in the SLEEP state was reading the table, but the TRUNCATE was trying to do a DDL statement, hence it being blocked until the other SLEEP-ing process was killed.
这篇关于MySQL 5.7 上的元数据锁定，找不到锁定过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
