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能配置成用于杀死长时间运行的空闲事务。
分享到:
相关推荐
mysql-8.0.23-winx64-debug-test
mysql-8.0.29-winx64-debug-test.zip
Windows (x86, 64-bit), ZIP Archive Debug Binaries & Test Suite (mysql-8.0.31-winx64-debug-test.zip)
mysql-8.0.16-winx64-debug-test.zip
Mysql 8.0.20 64位安装压缩包,包含用例与debug模块
mysql-8.0.19-winx64-debug-test.zip安装包
本资源包括高性能第三版的pdf文件以及word文件,用于学习MySQL使用
高性能MySQL(一)优化服务器设置 高性能MySQL(二)操作系统和硬件优化 高性能MySQL(三)复制 高性能MySQL(四)可扩展的MySQL 高性能MySQL(五)MySQL分支与变种 高性能MySQL(六)高可用性
《高性能MySQL(第3版)》是MySQL 领域的经典之作,拥有广泛的影响力。第3版更新了大量的内容,不但涵盖了最新MySQL5.5版本的新特性,也讲述了关于固态盘、高可扩展性设计和云计算环境下的数据库相关的新内容,原有的...
mysql-5.7.20-win32-debug-test.zip
《高性能 MySQL》第三版学习笔记
《高性能MySQL(第3版)》-中文高清带目录!!! 内附PDF文档与解压密码!!!
高性能MYSQL第三版 高性能MYSQL第三版 高性能MYSQL第三版 高性能MYSQL第三版 KINDLE版,
MYSQL学习笔记-索引MYSQL学习笔记-索引MYSQL学习笔记-索引
《高性能MySQL(第3版)》是MySQL 领域的经典之作,拥有广泛的影响力。第3 版更新了大量的内容,不但涵盖了最新MySQL 5.5版本的新特性,也讲述了关于固态盘、高可扩展性设计和云计算环境下的数据库相关的新内容,原有...
高性能mysql第三版---------------------------------------------------
zookeeper云的学习笔记-云的学习笔记系统-云的学习笔记系统源码-云的学习笔记管理系统-云的学习笔记管理系统java代码-云的学习笔记系统设计与实现-基于ssm的云的学习笔记系统-基于Web的云的学习笔记系统设计与实现-...