I suppose this issue applies to deadlocks, live-locks, or just lock wait timeouts.
我想这个问题适用于死锁,实时锁定或只是锁定等待超时。
I'm trying to figure out what query is causing a lock that is preventing another query from executing. Oracle has (if memory serves) a LOCK table that you can join onto itself to determine which queries are locking others. I need a way to accomplish the same this in MySQL.
我试图找出导致阻止另一个查询执行的锁的查询。 Oracle(如果内存服务)有一个LOCK表,您可以将其连接到自身以确定哪些查询正在锁定其他查询。我需要一种在MySQL中完成相同的方法。
The scenario is that we have long-running jobs that occasionally create a nested transaction that updates the progress field. That way, we're not losing the transactional-ness of the work while keeping the user informed of the progress (i.e. percent complete). The nested transaction sometimes throws a lock timeout exception.
方案是我们有长时间运行的作业,偶尔会创建一个更新进度字段的嵌套事务。这样,我们就不会失去工作的交易性,同时让用户了解进度(即完成百分比)。嵌套事务有时会抛出锁定超时异常。
This is very odd, since none of the other work should write - or even read - from the Job table. Sifting through the raw SQL log confirms this. Here is the transaction section from SHOW ENGINE INNODB STATUS:
这很奇怪,因为其他工作都不应该从Job表中写入 - 甚至是读取 - 。筛选原始SQL日志确认了这一点。这是SHOW ENGINE INNODB状态的交易部分:
------------
TRANSACTIONS
------------
Trx id counter 0 479427
Purge done for trx's n:o < 0 479425 undo n:o < 0 0
History list length 19
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 3192
MySQL thread id 31, query id 17417 localhost 127.0.0.1 root
show engine innodb status
---TRANSACTION 0 0, not started, OS thread id 3776
MySQL thread id 29, query id 13062 localhost 127.0.0.1 root
---TRANSACTION 0 479190, not started, OS thread id 2540
MySQL thread id 23, query id 16103 localhost 127.0.0.1 testuser
---TRANSACTION 0 479422, not started, OS thread id 2536
MySQL thread id 19, query id 17338 localhost 127.0.0.1 testuser
---TRANSACTION 0 479194, not started, OS thread id 2528
MySQL thread id 20, query id 16103 localhost 127.0.0.1 testuser
---TRANSACTION 0 479189, not started, OS thread id 2776
MySQL thread id 22, query id 16103 localhost 127.0.0.1 testuser
---TRANSACTION 0 479426, ACTIVE 3 sec, OS thread id 2544 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 18, query id 17414 localhost 127.0.0.1 testuser Updating
update Job set progress=0.000482780829770491 where id=28
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 23927 n bits 72 index "PRIMARY" of table "test"."job" trx id 0 479426 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
0: len 8; hex 000000000000001c; asc ;; 1: len 6; hex 0000000750bf; asc P ;; 2: len 7; hex 0000005d4d2aeb; asc ]M* ;; 3: len 8; hex 0000000000000005; asc ;; 4: len 8; hex 0000000000000004; asc ;; 5: len 8; hex 0000000000000006; asc ;; 6: len 1; hex 49; asc I;; 7: len 14; hex 800000000000000002749e0e51a6; asc t Q ;; 8: len 30; hex 3c6d61703e0a20203c656e7472793e0a202020203c737472696e673e7061; asc <map> <entry> <string>pa;...(truncated); 9: len 8; hex 80001245d33e7e3c; asc E >~<;; 10: SQL NULL; 11: SQL NULL;
------------------
---TRANSACTION 0 479418, ACTIVE 31 sec, OS thread id 960
14 lock struct(s), heap size 1024, 8 row lock(s), undo log entries 3
MySQL thread id 21, query id 17404 localhost 127.0.0.1 testuser
It appears clear that there are only two transactions, and that one of the 14 locks of transaction 479418 is blocking transaction 479426. I would love to know what the offending query is. Any ideas? Even listing the 14 locks and the queries that caused them would be great.
很明显,只有两个交易,交易479418的14个锁中的一个是阻止交易479426.我很想知道违规查询是什么。有任何想法吗?即使列出14个锁和导致它们的查询也会很棒。
Thanks!
3 个解决方案
#1
4
If your queries are waiting up to three seconds, that would make them easy to capture in the slow-query-log. Another suggestion that Xaprb writes is to use InnoTop. There is a S.O. post on a similar InnoDB lock issue.
如果您的查询等待最多三秒钟,那么可以在慢查询日志中轻松捕获它们。 Xaprb写的另一个建议是使用InnoTop。有一个S.O.发布类似的InnoDB锁定问题。
However, you might want to review the code and look for places where you are doing a whole table select. For example, if you have a table work items and you want to select those that are pending, doing a
但是,您可能希望查看代码并查找正在执行整个表选择的位置。例如,如果您有一个表工作项,并且您想要选择那些待处理项,那么执行
SELECT * FROM queue WHERE status = 'pending' ORDER BY create_date LIMIT 1`
could be triggering a temp-file-sort condition that will occupy the whole table if it is in a transaction. Adding FOR UPDATE to the select could help it acquire the lock better. Apparently, clustering primary keys can help.
可能会触发临时文件排序条件,如果它在事务中,它将占用整个表。将FOR UPDATE添加到选择可以帮助它更好地获得锁定。显然,群集主键可以提供帮助。
In my environment, my query connection will report an error on a transaction lock issue, so I see errors like: Deadlock found when trying to get lock; try restarting transaction. You might have to check for warnings if your query is actually failing. (This might not help if you can't change how the application reports query failures.)
在我的环境中,我的查询连接将在事务锁定问题上报告错误,因此我看到以下错误:尝试获取锁定时发现死锁;尝试重新启动事务。如果查询实际失败,您可能需要检查警告。 (如果您无法更改应用程序报告查询失败的方式,这可能无济于事。)
#2
0
In a session of msql> try
在msql的一个会话中>试试
12.5.5.31. SHOW PROCESSLIST Syntax
12.5.5.31。 SHOW PROCESSLIST语法
SHOW [FULL] PROCESSLIST
显示[FULL] PROCESSLIST
SHOW PROCESSLIST shows you which threads are running. You can also get this information from the INFORMATION_SCHEMA PROCESSLIST table or the mysqladmin processlist command. If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using). If you do not use the FULL keyword, only the first 100 characters of each statement are shown in the Info field.
SHOW PROCESSLIST显示正在运行的线程。您还可以从INFORMATION_SCHEMA PROCESSLIST表或mysqladmin processlist命令获取此信息。如果您具有PROCESS权限,则可以查看所有线程。否则,您只能看到自己的线程(即与您正在使用的MySQL帐户关联的线程)。如果不使用FULL关键字,则只有每个语句的前100个字符显示在“信息”字段中。
#3
0
One option is to enable the general query log, which will record all statements run against mysql. Just be careful it doesn't eat all your disk space.
一种选择是启用通用查询日志,该日志将记录针对mysql运行的所有语句。小心它不会占用你所有的磁盘空间。
Compare the logs against the ids in the innodb status output, and you'll find the culprit.
将日志与innodb状态输出中的ID进行比较,您将找到罪魁祸首。
#1
4
If your queries are waiting up to three seconds, that would make them easy to capture in the slow-query-log. Another suggestion that Xaprb writes is to use InnoTop. There is a S.O. post on a similar InnoDB lock issue.
如果您的查询等待最多三秒钟,那么可以在慢查询日志中轻松捕获它们。 Xaprb写的另一个建议是使用InnoTop。有一个S.O.发布类似的InnoDB锁定问题。
However, you might want to review the code and look for places where you are doing a whole table select. For example, if you have a table work items and you want to select those that are pending, doing a
但是,您可能希望查看代码并查找正在执行整个表选择的位置。例如,如果您有一个表工作项,并且您想要选择那些待处理项,那么执行
SELECT * FROM queue WHERE status = 'pending' ORDER BY create_date LIMIT 1`
could be triggering a temp-file-sort condition that will occupy the whole table if it is in a transaction. Adding FOR UPDATE to the select could help it acquire the lock better. Apparently, clustering primary keys can help.
可能会触发临时文件排序条件,如果它在事务中,它将占用整个表。将FOR UPDATE添加到选择可以帮助它更好地获得锁定。显然,群集主键可以提供帮助。
In my environment, my query connection will report an error on a transaction lock issue, so I see errors like: Deadlock found when trying to get lock; try restarting transaction. You might have to check for warnings if your query is actually failing. (This might not help if you can't change how the application reports query failures.)
在我的环境中,我的查询连接将在事务锁定问题上报告错误,因此我看到以下错误:尝试获取锁定时发现死锁;尝试重新启动事务。如果查询实际失败,您可能需要检查警告。 (如果您无法更改应用程序报告查询失败的方式,这可能无济于事。)
#2
0
In a session of msql> try
在msql的一个会话中>试试
12.5.5.31. SHOW PROCESSLIST Syntax
12.5.5.31。 SHOW PROCESSLIST语法
SHOW [FULL] PROCESSLIST
显示[FULL] PROCESSLIST
SHOW PROCESSLIST shows you which threads are running. You can also get this information from the INFORMATION_SCHEMA PROCESSLIST table or the mysqladmin processlist command. If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using). If you do not use the FULL keyword, only the first 100 characters of each statement are shown in the Info field.
SHOW PROCESSLIST显示正在运行的线程。您还可以从INFORMATION_SCHEMA PROCESSLIST表或mysqladmin processlist命令获取此信息。如果您具有PROCESS权限,则可以查看所有线程。否则,您只能看到自己的线程(即与您正在使用的MySQL帐户关联的线程)。如果不使用FULL关键字,则只有每个语句的前100个字符显示在“信息”字段中。
#3
0
One option is to enable the general query log, which will record all statements run against mysql. Just be careful it doesn't eat all your disk space.
一种选择是启用通用查询日志,该日志将记录针对mysql运行的所有语句。小心它不会占用你所有的磁盘空间。
Compare the logs against the ids in the innodb status output, and you'll find the culprit.
将日志与innodb状态输出中的ID进行比较,您将找到罪魁祸首。