server级别的锁等待可通过show processlist看到，包括：table locks、global locks、name locks、string locks。 

table locks 

分显式和隐式，对于myisam来说，在内部来说是一样的。但除了myisam，却有很大不同，当你建立显式锁定时，做了你告诉它该做的事；对于隐式锁定，被隐藏并很神奇（but implicit locks are hidden and “magical.”），server自动建立和释放隐式锁并通知存储引擎，存储引擎适时转换成合适的锁。比如，innodb有个关于对给定server级别的锁将建立什么类型的InnoDB表锁的规则。 

找出谁持有锁： 

show processlist可以看到很多进程等待锁，但没显示谁占有锁。我们可以使用debug命令把所得信息打印到server的error log中。 

$mysqladmin debug 

这命令输出很多信息到error log中，在尾巴附近你可看到类似以下信息的输出 

Thread database.table_name Locked/Waiting Lock_type 

7 sakila.film Locked - read Read lock without concurrent inserts 

8 sakila.film Waiting - write Highest priority write lock 

You can see that thread 8 is waiting for the lock thread 7 holds. 


Global Read Lock 

通过以下方式获取： 

mysql> FLUSH TABLES WITH READ LOCK; 

在show processlist中显示 

State: Waiting for release of readlock表明是全局读锁而不是表锁。 


name locks 

是表锁的一种，当rename或drop表时建立。在show processlist中显示 

State: Waiting for table 

也可以通过show open tables ；看到name locks的影响。 

mysql没有提供工具找出谁持有name locks，但因为持有时间短，通常不会有问题。当有疑问时，因为name lock等待表锁，所以可通过mysqladmin debug查看到。 


User Locks 

根本上说是命名互斥体， 

mysql> SELECT GET_LOCK('my lock', 100); 

+--------------------------+ 

| GET_LOCK('my lock', 100) | 

+--------------------------+ 

| 1 | 

+--------------------------+ 

1 row in set (0.00 sec) 

This attempt returned success immediately, so this thread now has a lock on that named mutex. If another thread tries to lock the same string, it will hang until it times out. This time the process list shows a different state: 

mysql> SHOW PROCESSLIST\G 

*************************** 1. row *************************** 

Id: 22 

User: baron 

Host: localhost 

db: NULL 

Command: Query 

Time: 9 

State: User lock 

Info: SELECT GET_LOCK('my lock', 100) 

The User lock state is unique to this type of lock. MySQL provides no way to find out who holds a user lock. 



Lock Waits in InnoDB 

innodb锁信息可以在show innodb status中看到，如果一个事务等待锁，可以在transactions片段看到，比如： 

You can see the effects in SHOW INNODB 

STATUS (we’ve abbreviated the results for clarity): 

1 LOCK WAIT 2 lock struct(s), heap size 1216 

2 MySQL thread id 8, query id 89 localhost baron Sending data 

3 SELECT film_id FROM sakila.film LIMIT 1 FOR UPDATE 

4 ------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED: 

5 RECORD LOCKS space id 0 page no 194 n bits 1072 index `idx_fk_language_id` of table `sakila/film` trx id 0 61714 lock_mode X waiting 

The last line shows that the query is waiting for an exclusive ( lock_mode X) lock on page 194 of the table’s idx_fk_language_id index. Eventually, the lock wait timeout will be exceeded, and the query will return an error: 

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 

不过，没有说明锁的占有者，我们可能通过查到哪些事务运行很长时间去猜；可选的方法是我们激活innodb lock monitor功能，它可以显示每个事务的10个锁。 

激活方法： 

mysql> CREATE TABLE innodb_lock_monitor(a int) ENGINE=INNODB; 

它会增强show innodb status的输出，在大多数系统中，输出会重定向到error log。 

删除这个表可以停止此功能的激活。 

此方法的缺点是锁的输出没有优化，每个事务最多打印10个锁信息，可能使得我们所需要的信息没打印出来。可以选用percona补丁或版本。另一可选的是innotop，它会解析和格式化输出，但也不是完美。 



使用INFORMATION_SCHEMA Tables----INNODB_LOCKS 

SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread, 

TIMESTAMPDIFF(SECOND, r.trx_wait_started, CURRENT_TIMESTAMP) AS wait_time, 

r.trx_query AS waiting_query, 

l.lock_table AS waiting_table_lock, 

b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread, 

SUBSTRING(p.host, 1, INSTR(p.host, ':') - 1) AS blocking_host, 

SUBSTRING(p.host, INSTR(p.host, ':') +1) AS blocking_port, 

IF(p.command = "Sleep", p.time, 0) AS idle_in_trx, 

b.trx_query AS blocking_query 

FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w 

INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS b ON b.trx_id = w.blocking_trx_id 

INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS r ON r.trx_id = w.requesting_trx_id 

INNER JOIN INFORMATION_SCHEMA.INNODB_LOCKS AS l ON w.requested_lock_id = l.lock_id 

LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id 

ORDER BY wait_time DESC\G 

If you’re suffering from a lot of locking due to threads that are idle in a transaction, the 

following variation can show you how many queries are blocked on which threads, 

without all the verbosity: 

SELECT CONCAT('thread ', b.trx_mysql_thread_id, ' from ', p.host) AS who_blocks, 

IF(p.command = "Sleep", p.time, 0) AS idle_in_trx, 

MAX(TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW())) AS max_wait_time, 

COUNT(*) AS num_waiters 

FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w 

INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS b ON b.trx_id = w.blocking_trx_id 

INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS r ON r.trx_id = w.requesting_trx_id 

LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id 

GROUP BY who_blocks ORDER BY num_waiters DESC\G 

空闲事务锁是很危险的，percona 工具pt-kill能配置成用于杀死长时间运行的空闲事务。