现象：
1. 数据库发现一条根据主键更新的sql特别慢，持续70秒后显示更新超时。
[cc lang='sql']update user_quotation set GMT_MODIFIED = now() where ID = 230967517;[/cc]
2. 任何DDL操作都显示
[cc lang='sql']10060303 root localhost icbu_rfq Query 164 Waiting for table metadata lock
=================================================================================
alter table isr_quotation_price \nmodify item_image varchar(1024) comment '商品图片' [/cc]
可以通过下面视图查询被lock的信息
[cc lang='sql']root@information_schema 06:54:35:::select * from INNODB_LOCKS;
+-------------------------+-------------+-----------+-----------+----------------------------+------------+------------+-----
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-------------------------+-------------+-----------+-----------+----------------------------+------------+------------+-----
| 13C937945:2238:218183:6 | 13C937945 | X | RECORD | `db_1`.`user_quotation` | `PRIMARY` | 2238 | 218183 | 6 | 230967517 |
| 139F30A92:2238:218183:6 | 139F30A92 | X | RECORD | `db_1`.`user_quotation` | `PRIMARY` | 2238 | 218183 | 6 | 230967517 |
+-------------------------+-------------+-----------+-----------+----------------------------+------------+------------+-----
root@information_schema 06:53:23:::select * from INNODB_LOCK_WAITS;
+-------------------+-------------------------+-----------------+-------------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------------+-----------------+-------------------------+
| 13C937945 | 13C937945:2238:218183:6 | 139F30A92 | 139F30A92:2238:218183:6 |
[/cc]
被这个事务139F30A92 阻塞了。
[cc lang='sql']root@information_schema 06:55:10:::
select * from innodb_trx where trx_id = '139F30A92'\G
*************************** 1. row ***************************
trx_id: 139F30A92
trx_state: RUNNING
trx_started: 2013-02-25 06:27:43
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 976
trx_mysql_thread_id: 10059235
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 391
trx_lock_memory_bytes: 47544
trx_rows_locked: 427
trx_rows_modified: 585
trx_concurrency_tickets: 0
trx_isolation_level: READ COMMITTED
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
1 row in set (0.00 sec) [/cc]
可以看到这个的事的开始时间： trx_started: 2013-02-25 06:27:43，已经持续了12个小时。
也可以通过 show engine innodb status\G 查看被lock的信息
MySQL thread id 6251366, OS thread handle 0x5b8c0940, query id 28842999247 Slave has read all relay log; waiting for the slave I/O thread to update it
---TRANSACTION 139F30A92, ACTIVE 44531 sec
391 lock struct(s), heap size 47544, 427 row lock(s), undo log entries 585
MySQL thread id 10059235, OS thread handle 0x59172940, query id 28839628267 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
----------------------------
Do action:
[cc lang='sql']root@information_schema 08:01:37>SELECT trx_id,trx_state,trx_started,trx_mysql_thread_id
FROM innodb_trx WHERE trx_id in(SELECT lock_trx_id FROM INNODB_LOCKS);
+-----------+-----------+---------------------+---------------------+
| trx_id | trx_state | trx_started | trx_mysql_thread_id |
+-----------+-----------+---------------------+---------------------+
| 2D0F7A9DB | LOCK WAIT | 2013-04-18 08:00:57 | 17926426 |
| 2D0F65C55 | LOCK WAIT | 2013-04-18 08:00:25 | 17923010 |
| 2D0C80153 | RUNNING | 2013-04-18 07:37:59 | 17911138 |
| 2D09B8B62 | RUNNING | 2013-04-18 07:15:43 | 17916547 |
+-----------+-----------+---------------------+---------------------+
4 rows in set (0.01 sec)
root@information_schema 08:01:38>kill 17911138;
Query OK, 0 rows affected (0.00 sec)
root@information_schema 08:01:47>kill 17916547;
Query OK, 0 rows affected (0.00 sec)
root@information_schema 08:01:53>SELECT trx_id,trx_state,trx_started,trx_mysql_thread_id FROM innodb_trx WHERE trx_id in(SELECT lock_trx_id FROM INNODB_LOCKS);
Empty set (0.00 sec)[/cc]
select trx_id,trx_state,trx_started,trx_mysql_thread_id from innodb_trx ;
This work is licensed under a CC A-S 4.0 International License.