开发环境经常出现DML语句或者DDL语句被block住的情况解释
问题： 如题
分析过程： 9846926 test 192.168.12.91:42638 test Query 3852 Waiting for table metadata lock ============================================================================================== alter table order \n modify gmt_timeout datetime comment '超时时间' 0 0 1 show engine innodb status\G 可以看到： ---TRANSACTION 5AD02DA82, ACTIVE 4212 sec 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 9846603, OS thread handle 0x7ff5e2df8700, query id 11878319368 10.13.134.143 bbc TABLE LOCK table `test`.`order` trx id 5AD02DA82 lock mode IX RECORD LOCKS space id 199033 page no 3 n bits 120 index `PRIMARY` of table `test`.`order` trx id 5AD02DA82 lock_mode X locks rec but not gap ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 可以看到这个语句执行不下去，是因为被锁住了，我们再继续观察： root@(none) 12:59:19>select trx_id,trx_state,trx_mysql_thread_id,trx_isolation_level,trx_rows_modified,trx_started from information_schema.innodb_trx; +-----------+-----------+---------------------+---------------------+-------------------+---------------------+ | trx_id | trx_state | trx_mysql_thread_id | trx_isolation_level | trx_rows_modified | trx_started | +-----------+-----------+---------------------+---------------------+-------------------+---------------------+ | 5AD997BB7 | RUNNING | 9831682 | READ COMMITTED | 0 | 2013-04-03 12:59:38 | | 5AD02DA82 | RUNNING | 9846603 | READ COMMITTED | 0 | 2013-04-03 11:42:54 | +-----------+-----------+---------------------+---------------------+-------------------+---------------------+ 2 rows in set (0.04 sec) 确实没提交，如果要想让线程9846926继续执行，解决办法：kill 9846603. 原因：日常环境开发同学经常断点调试，导致有些事务打开了没有关闭，所以等下下次调试，操作同一条记录的时候就会被阻塞，日常环境99%的情况是因为这个。 还有另外一种情况innodb层静态数据的统计，解决办法：set global innodb_stats_on_metadata=0
问题重现：
CREATE TABLE `xdual` ( `id` int(11) NOT NULL AUTO_INCREMENT, `x` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `v` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_x` (`x`), KEY `idx_v` (`v`) ) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8; root@test 07:40:10>select * from xdual; +----+---------------------+------+ | id | x | v | +----+---------------------+------+ | 2 | 2012-04-19 20:25:40 | 1 | | 4 | 2012-04-18 00:53:58 | 3 | | 6 | 2012-04-18 00:54:00 | 5 | | 8 | 2012-04-18 18:23:16 | 7 | | 10 | 2012-04-18 00:54:03 | 2 | | 12 | 2012-04-18 02:26:13 | 4 | | 14 | 2012-04-18 00:54:06 | 6 | | 15 | 2012-04-18 02:26:13 | 4 | | 16 | 2012-04-18 18:24:14 | 7 | | 18 | 2012-04-18 00:54:10 | 8 | | 22 | 2012-04-18 15:12:08 | 18 | | 26 | 2012-04-18 18:23:16 | 7 | | 34 | 2012-04-18 02:30:09 | 4 | +----+---------------------+------+ MySQL5.5.16 innodb_stats_on_metadata=OFF session 1 | session 2 | session 3 begin; | | update xdual set v=20 where id=34; | | |alter table xdual modify `v` int(11) DEFAULT NULL;| | | | | | |9915844 Waiting for table metadata lock | | | |============================================= | | alter table xdual modify v int DEFAULT NULL | | | | alter table xdual modify `v` int(11) DEFAULT NULL;| | | | | |session 2 metalock lock 解除 | | | | |Query OK, 0 rows affected (1 min 24.35 sec) | |Records: 0 Duplicates: 0 Warnings: 0 | | | root@test 07:43:50>commit; | | | | | | MySQL5.1.48 CREATE TABLE `test_lingluo` ( `id` bigint(20) unsigned NOT NULL, `biz_order_id` int(11) NOT NULL, `parent_id` int(11) NOT NULL, `auction_id` int(11) NOT NULL, `sku_id` int(11) NOT NULL, `field5` varchar(20) NOT NULL, PRIMARY KEY (`id`), KEY `idx_sku_id` (`sku_id`), KEY `idx_auction_id` (`auction_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; root@test 07:21:01>select * from test_lingluo; +----+--------------+-----------+------------+--------+--------+ | id | biz_order_id | parent_id | auction_id | sku_id | field5 | +----+--------------+-----------+------------+--------+--------+ | 1 | 1 | 1 | 123 | 123 | fffff | | 2 | 2 | 2 | 321 | 321 | aaaaa | | 3 | 3 | 3 | 456 | 456 | fffff | | 4 | 4 | 4 | 4 | 4 | eeee | | 5 | 5 | 5 | 5 | 5 | dddd | | 6 | 6 | 6 | 6 | 6 | cccc | | 7 | 7 | 7 | 7 | 1000 | dddd | | 8 | 8 | 8 | 8 | 100 | dddd | | 9 | 9 | 9 | 9 | 100000 | fffff | | 10 | 10 | 10 | 10 | 1 | dddd | +----+--------------+-----------+------------+--------+--------+ 10 rows in set (0.00 sec) session 1 | session 2 | session 3 begin; | | update test_lingluo set field5='yyyy' where id=10;| | | | |alter table test_lingluo modify field5 varchar(30) default null;| | | | |1232570045 copy to tmp table | | | | ============================== | | alter table test_lingluo modif | | y field5 varchar(30)
| | default null | | | | | | commit; | |metalock 解除 | |
背景知识：
Metadata Locking
To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted transaction in another session. The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends. A metadata lock on a table prevents changes to the table's structure. This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.
除去99%的情况，还有另外一种，丁奇已经解释过了，关于metadata lock
浙公网安备 33010602011771号