MySQL lock info
Waiting for table metadata lock - 推酷
MySQL lock info
Waiting for table metadata lock
1. 数据库发现一条根据主键更新的sql特别慢，持续70秒后显示更新超时。
user_quotation
GMT_MODIFIED
2. 任何DDL操作都显示
root localhost icbu_rfq Query
=================================================================================
isr_quotation_price \nmodify item_image
'商品图片'
可以通过下面视图查询被lock的信息
root@information_schema 06:
INNODB_LOCKS;
-------------------------+-------------+-----------+-----------+----------------------------+------------+------------+-----
lock_trx_id
lock_table
lock_index
lock_space
-------------------------+-------------+-----------+-----------+----------------------------+------------+------------+-----
13C937945:
`user_quotation`
139F30A92:
`user_quotation`
-------------------------+-------------+-----------+-----------+----------------------------+------------+------------+-----
root@information_schema 06:
INNODB_LOCK_WAITS;
-------------------+-------------------------+-----------------+-------------------------+
requesting_trx_id
requested_lock_id
blocking_trx_id
blocking_lock_id
-------------------+-------------------------+-----------------+-------------------------+
13C937945:
139F30A92:
被这个事务139F30A92 阻塞了。
root@information_schema 06:
innodb_trx
'139F30A92'
***************************
***************************
trx_id: 139F30A92
trx_state: RUNNING
trx_started:
trx_requested_lock_id:
trx_wait_started:
trx_weight:
trx_mysql_thread_id:
trx_query:
trx_operation_state:
trx_tables_in_use:
trx_tables_locked:
trx_lock_structs:
trx_lock_memory_bytes:
trx_rows_locked:
trx_rows_modified:
trx_concurrency_tickets:
trx_isolation_level:
trx_unique_checks:
trx_foreign_key_checks:
trx_last_foreign_key_error:
trx_adaptive_hash_latched:
trx_adaptive_hash_timeout:
可以看到这个的事的开始时间： trx_started:
06:27:43，已经持续了12个小时。
也可以通过 show engine innodb statusG 查看被lock的信息
MySQL thread id 6251366, OS thread handle 0x5b8c0940, query id
Slave ha waiting for the slave I/O thread to update it
TRANSACTION 139F30A92, ACTIVE 44531 sec
391 lock struct(s), heap size 4 row lock(s), undo log entries 585
MySQL thread id , OS thread handle 0&, query id
172.20.134.37 db_1
TABLE LOCK table `db_1`.`user_quotation` trx id 139F30A92 lock mode IX
RECORD LOCKS space id 2238 page no 269143 n bits 120 index `PRIMARY` of table `db_1`.`user_quotation` trx id 139F30A92 lock_mode X locks rec but not gap
RECORD LOCKS space id 2238 page no 211551 n bits 112 index `PRIMARY` of table `db_1`.`user_quotation` trx id 139F30A92 lock_mode X locks rec but not gap
RECORD LOCKS space id 2238 page no 269142 n bits 120 index `PRIMARY` of table `db_1`.`user_quotation` trx id 139F30A92 lock_mode X locks rec but not gap
TABLE LOCK table `db_1`.`buyer_profile_remind` trx id 139F30A92 lock mode IX
RECORD LOCKS space id 2238 page no 216392 n bits 112 index `PRIMARY` of table `db_1`.`user_quotation` trx id 139F30A92 lock_mode X locks rec but not gap
TABLE LOCK table `db_1`.`buyer_profile` trx id 139F30A92 lock mode IX
RECORD LOCKS space id 2179 page no 6590 n bits 824 index `idx_prof_compid` of table `db_1`.`buyer_profile` trx id 139F30A92 lock_mode X locks rec but not gap
RECORD LOCKS space id 2179 page no 39120 n bits 120 index `PRIMARY` of table `db_1`.`buyer_profile` trx id 139F30A92 lock_mode X locks rec but not gap
TABLE LOCK table `db_1`.`buyer_card_theme` trx id 139F30A92 lock mode IX
TOO MANY LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS
—————————-
本文内容遵从CC版权协议, 可以随意转载, 但必须以超链接形式标明文章原始出处
Trackback:/2013/02/mysql-lock-info-waiting-for-table-metadata-lock/
Post Footer automatically generated by
for wordpress.
已发表评论数()
&&登&&&陆&&
已收藏到推刊！
请填写推刊名
描述不能大于100个字符!
权限设置： 公开
仅自己可见DBA Thinking!
Categories
Recent Posts
Select Month
October 2014 &(1)
July 2014 &(2)
June 2014 &(5)
March 2014 &(1)
January 2014 &(1)
November 2013 &(2)
September 2013 &(1)
August 2013 &(2)
June 2013 &(2)
May 2013 &(1)
April 2013 &(1)
February 2013 &(1)
January 2013 &(4)
October 2012 &(6)
September 2012 &(11)
January 2012 &(1)
November 2011 &(1)
September 2011 &(1)
August 2011 &(2)
June 2011 &(6)
May 2011 &(4)
April 2011 &(2)
March 2011 &(5)
February 2011 &(2)
January 2011 &(6)
November 2010 &(2)
October 2010 &(1)
September 2010 &(2)
August 2010 &(3)
July 2010 &(3)
June 2010 &(3)
May 2010 &(2)
April 2010 &(3)
March 2010 &(6)
February 2010 &(5)
January 2010 &(7)
December 2009 &(11)
November 2009 &(1)
October 2009 &(4)
September 2009 &(7)
August 2009 &(2)
July 2009 &(11)
June 2009 &(19)
May 2009 &(8)
April 2009 &(13)
March 2009 &(7)
February 2009 &(11)
January 2009 &(12)
December 2008 &(8)
November 2008 &(14)
October 2008 &(10)
September 2008 &(18)
August 2008 &(10)
July 2008 &(37)
June 2008 &(28)
May 2008 &(17)
April 2008 &(38)
March 2008 &(15)
February 2008 &(15)
January 2008 &(18)
December 2007 &(15)
November 2007 &(24)
October 2007 &(18)
September 2007 &(31)
August 2007 &(58)
April 2007 &(3)
March 2007 &(2)
February 2007 &(1)
November 2006 &(1)
June 2001 &(1)
April 209 &(1)
September 208 &(1)
© 2012相关文章推荐:
mysql Waiting for table metadata lock
+------+--------+---------------------+--------+---------+-------+---------------------------------+-----------------------------------------------------------------------------------------------------+
| Command | Time
阅读（1295） 回复（2）
其他文章推荐
ITPUB官方微博:
最近在看mysql，下面是资料内容。理解起来比较容易，但测试的的时候发现结果不太一样。
一个session用shell窗口登录mysql db
另一个用mysql browser
在任一窗口采用不同的用户登录后，其中一个session执行了lock tables tests后，另一个session还是可以做read
这事为什么？
locks a table for writing. A WRITE lock is an exclusive lock. It can be acquired only when a table is not being used. Once acquired, only...
阅读（1038） 回复（1）
在Pc的r候，多人在入Pc文件的r候，霈Ftable lock的情r。
已著在yh境enque/table_size的4096{成8192，的_是生效的，能蛲r入文件的窗成2倍。
但我⑼拥脑O定^程O在正式h境，m然档抵狄呀化了，但H上s]有生效，能蛲r入文件的窗颠是一印
(已重新_server^)
有人遇^相同的情r
阅读（1754） 回复（3）
大家有遇到这这个错误吗？主要的解决思路是？
跑某一条SQL，等待多时之后报ORA-44203: timeout waiting for lock on cursor
阅读（2552） 回复（1）
In our project, there is many to many relationship need to be solved, but in our exsiting database, the middle weak entity is already there with no its own PK. For some reason, we want to create its own PK for this middle entity(AB).
What I want to do is create Temp table and copy the exsiting data from AB to Temp,
drop AB then create new AB wi...
阅读（1091） 回复（0）
In our project, there is many to many relationship need to be solved, but in our exsiting database, the middle weak entity is already there with no its own PK. For some reason, we want to create its own PK for this middle entity(AB).
What I want to do is create Temp table and copy the exsiting data from AB to Temp,
drop AB then create new AB wi...
阅读（1218） 回复（7）
盛拓传媒：
北京皓辰网域网络信息技术有限公司. 版权所有
北京市公安局海淀分局网监中心备案编号：
广播电视节目制作经营许可证：编号（京）字第1149号
ITPUB推荐文章解答你所有技术难题后使用快捷导航没有帐号？
只需一步，快速开始
查看: 1363|回复: 2
UID帖子阅读权限20精华贴数技术排名15600 专家积分0 社区排名16028 技术积分184 社区积分17 注册时间
精华贴数专家积分0 技术积分184 社区积分17 注册时间
论坛徽章:1
+------+--------+---------------------+--------+---------+-------+---------------------------------+-----------------------------------------------------------------------------------------------------+
| Id& &| User& &| Host& && && && && & | db& &&&| Command | Time&&| State& && && && && && && && && &| Info& && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && &|
+------+--------+---------------------+--------+---------+-------+---------------------------------+-----------------------------------------------------------------------------------------------------+
| 1010 | portal | 192.111.214.1:11631 | portal | Sleep& &|& & 92 |& && && && && && && && && && && &| NULL& && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && &|
| 1011 | portal | localhost:56359& &&&| portal | Sleep& &|& &&&1 |& && && && && && && && && && && &| NULL& && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && &|
| 1012 | portal | localhost:56360& &&&| portal | Sleep& &|& &&&1 |& && && && && && && && && && && &| NULL& && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && &|
| 3672 | portal | localhost& && && &&&| portal | Query& &| 25313 | Waiting for table metadata lock | alter table audit_detectdetailtb&&add partition ( partition part_ vaues less than ( |
| 4789 | portal | 192.111.214.1:35398 | portal | Sleep& &|&&1016 |& && && && && && && && && && && &| NULL& && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && &|
| 4790 | portal | 192.111.214.1:35399 | portal | Sleep& &|&&1016 |& && && && && && && && && && && &| NULL& && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && &|
| 4791 | portal | 192.111.214.1:35400 | portal | Sleep& &|&&1111 |& && && && && && && && && && && &| NULL& && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && &|
| 4792 | portal | 192.111.214.1:35401 | portal | Sleep& &|&&1016 |& && && && && && && && && && && &| NULL& && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && &|
| 4793 | portal | 192.111.214.1:35402 | portal | Sleep& &|&&1016 |& && && && && && && && && && && &| NULL& && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && &|
| 5339 | root& &| localhost& && && &&&| NULL& &| Query& &|& &&&0 | NULL& && && && && && && && && & | show processlist& && && && && && && && && && && && && && && && && && && && && && && && && && && && &|
| 5340 | portal | 192.111.214.1:11357 | portal | Sleep& &|& & 25 |& && && && && && && && && && && &| NULL& && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && &|
+------+--------+---------------------+--------+---------+-------+---------------------------------+-----------------------------------------------------------------------------------------------------+
11 rows in set (0.00 sec)
对表增加分区为什么出现Waiting for table metadata lock，重启数据库好了，但过段时间又 Waiting for table metadata lock ，不知道什么原因？？
& |& & |& & |& & 
UID帖子阅读权限20精华贴数技术排名6037 专家积分16 社区排名2626448 技术积分510 社区积分0 注册时间
精华贴数专家积分16 技术积分510 社区积分0 注册时间
论坛徽章:1
Waiting for table metadata lock 是MySQL5.5中，你对表进行DDL操作，但仍然有打开事务访问过此表（5.5.3之后MDL基于事务）。
所以找到这个打开事务的session，让所有把它rollback/commit，或者直接kill掉它就可以了。
至于找的方法，show engine innodb status\G的Transactions节去找，那些连接的事务打开超过了你修改分区表的操作的25313秒。
还有如果DDL处于Waiting for table metadata lock ，之后此表上所有的操作都会被阻塞，包括一致性读取。
UID帖子阅读权限20精华贴数技术排名15600 专家积分0 社区排名16028 技术积分184 社区积分17 注册时间
精华贴数专家积分0 技术积分184 社区积分17 注册时间
论坛徽章:1
本帖最后由 jump2009 于
11:49 编辑
谢谢 楼上哥们，
+-------+--------+---------------------+--------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
| Id& & | User& &| Host& && && && && & | db& &&&| Command | Time | State& && && && && && && && && &| Info& && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && & |
+-------+--------+---------------------+--------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
|&&1010 | portal | 192.111.214.1:11631 | portal | Sleep& &|&&434 |& && && && && && && && && && && &| NULL& && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && & |
| 17275 | portal | localhost& && && &&&| portal | Query& &|& &35 | Waiting for table metadata lock | alter table audit_detectdetailtb&&add partition ( partition part_ values less than ( |
| 17302 | portal | localhost:32027& &&&| portal | Sleep& &|& &16 |& && && && && && && && && && && &| NULL& && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && & |
| 17304 | portal | localhost:32116& &&&| portal | Sleep& &|& & 0 |& && && && && && && && && && && &| NULL& && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && & |
| 17305 | portal | 192.111.214.1:42828 | portal | Sleep& &|& & 7 |& && && && && && && && && && && &| NULL& && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && && & |
| 17306 | root& &| localhost& && && &&&| NULL& &| Query& &|& & 0 | NULL& && && && && && && && && & | show processlist& && && && && && && && && && && && && && && && && && && && && && && && && && && && & |
+-------+--------+---------------------+--------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)
mysql& kill 1010;
Query OK, 0 rows affected (0.00 sec)
+-------+--------+---------------------+--------+---------+------+-------+------------------+
| Id& & | User& &| Host& && && && && & | db& &&&| Command | Time | State | Info& && && && & |
+-------+--------+---------------------+--------+---------+------+-------+------------------+
| 17275 | root& &| localhost& && && &&&| portal | Sleep& &|& & 1 |& && & | NULL& && && && & |
| 17302 | portal | localhost:32027& &&&| portal | Sleep& &|& & 0 |& && & | NULL& && && && & |
| 17304 | portal | localhost:32116& &&&| portal | Sleep& &|& & 0 |& && & | NULL& && && && & |
| 17306 | root& &| localhost& && && &&&| NULL& &| Query& &|& & 0 | NULL&&| show processlist |
| 17307 | portal | 192.111.214.1:43095 | portal | Sleep& &|& & 6 |& && & | NULL& && && && & |
+-------+--------+---------------------+--------+---------+------+-------+------------------+
5 rows in set (0.01 sec)
mysql&&&show engine innodb status \G
*************************** 1. row ***************************
&&Type: InnoDB
=====================================
:42:43 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 18 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: _second, 259116 sleeps, 25648 10_second, 3982 background, 3979 flush
srv_master_thread log flush and writes: 259695
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 35465, signal count 45367
Mutex spin waits 497899, rounds 713454, OS waits 3428
RW-shared spins 38414, rounds 1024866, OS waits 27452
RW-excl spins 8646, rounds 231336, OS waits 4082
Spin rounds per wait: 1.43 mutex, 26.68 RW-shared, 26.76 RW-excl
------------
TRANSACTIONS
------------
Trx id counter DA6531
Purge done for trx's n & 8A2F4A undo n & 0
History list length 2588591
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION DA651A, not started, process no 27197, OS thread id
MySQL thread id 17287, query id 8371360 localhost 127.0.0.1 portal
---TRANSACTION DA652F, not started, process no 27197, OS thread id
MySQL thread id 17286, query id 8371371 localhost 127.0.0.1 portal
---TRANSACTION 0, not started, process no 27197, OS thread id
MySQL thread id 17275, query id 8371372 localhost root
show engine innodb status
---TRANSACTION DA64B9, ACTIVE 10 sec, process no 27197, OS thread id
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 17289, query id .111.214.1 portal
---TRANSACTION 8A2F3E, ACTIVE 248453 sec, process no 27197, OS thread id
8 lock struct(s), heap size 1248, 0 row lock(s), undo log entries 23565
MySQL thread id 1010, query id .111.214.1 portal
Trx read view will not see trx with id &= 8A2F3F, sees & 8A2F3B
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, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
5917132 OS file reads,
OS file writes, 5671111 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 24.94 writes/s, 12.78 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7, 118029 merges
merged operations:
insert 319874, delete mark 34231, delete 139
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276671, node heap has 694 buffer(s)
122.72 hash searches/s, 5.33 non-hash searches/s
Log sequence number
Log flushed up to& &
Last checkpoint at&&
0 pending log writes, 0 pending chkp writes
5530938 log i/o's done, 12.44 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated ; in additional pool allocated 0
Dictionary memory allocated 1956983
Buffer pool size& &8191
Free buffers& && & 0
Database pages& &&&7497
Old database pages 2747
Modified db pages&&113
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young , not young 0
0.11 youngs/s, 0.00 non-youngs/s
Pages read 5917057, created 78547, written 4834232
0.00 reads/s, 0.06 creates/s, 12.22 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
LRU len: 7497, unzip_LRU len: 0
I/O sum[931]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
2 read views open inside InnoDB
Main thread process no. 27197, id , state: sleeping
Number of rows inserted 3446256, updated 1502308, deleted 819347, read
2.17 inserts/s, 2.28 updates/s, 2.00 deletes/s, 132.05 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
kill掉1010 就好了，但ID:1010 后台究竟做什么？ 怎么知道呢？
itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有
北京市公安局海淀分局网监中心备案编号： 广播电视节目制作经营许可证：编号（京）字第1149号
