如何查看是否存在行锁？

小灰狼 2018-01-25 04:44:35

由于系统平台有点多，多个Web系统访问同一个MySQL数据库。
现在发现在更新某个表的记录时经常莫名其妙地发生等待。怀疑应该是某个Web系统在完成数据库业务处理后没有提交事务或者关闭连接。
但是在mysql服务器上，用什么办法可以知道一个表是否发生了行锁

以下方法我都试过了，程序中锁行和解锁后，下面的显示结果是一样的
show full processlist
show engine innodb status
show status like '%lock%';
select * from information_schema.INNODB_LOCK_WAITS
show status like 'innodb_row_lock%';

...全文

1361 11 打赏收藏转发到动态举报

写回复

用AI写文章

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

zjcxc 2018-02-01

打赏
举报

TRX.trx_started 这个是事务开始的时间，你通过这个开始时间查 binlog 不就行了？

小灰狼 2018-02-01

打赏
举报

引用 7 楼 zjcxc 的回复:

相关的表关联上去不就行了，参考如下，各表其他列的意义，自己在官网文档上查吧

SELECT 
	TH.processlist_id,	-- 这个就是你要的 connection_id， 你可以 kill 这个，达到终止它的操作的目的
	TH.processlist_command as command,	-- 这个为 sleep，则表明操作结束了，但没有提交事务，也就是事务挂起了
	TIMESTAMPDIFF(second, TRX.trx_started, NOW()) as tx_duration, -- 事务已经开启多长时间了
	ESC.current_schema, ESC.sql_text	-- 这个不一定能查到，最后执行的 SQL（事务中有多个语句时，这个只是最后一个，江代表是产生锁的那个）
FROM performance_schema.threads TH
	INNER JOIN information_schema.innodb_trx TRX
		ON TRX.trx_mysql_thread_id = TH.processlist_id
	LEFT JOIN performance_schema.events_statements_current ESC
		ON ESC.thread_id = TH.thread_id

试过了，查询得到的结果都有，总算是有了点线索但是，这里即使得到了最后一条sql，用处也不是很大。因为我们基本上都能猜到锁行时的那条语句是什么，毕竟在程序中对行的 update 语句进行了封装，所有的对行的修改操作的SQL语句都是一样的。知道了事务Id，事务开启时间，可以推算是哪些连接和线程锁了行，然后杀死这个连接，避免了通过重启服务器来解决死锁的问题。但是根本性的问题还是没有解决

。归根结底，除了知道最后一条SQL语句之外，如果能够知道这个事务之前连续的几条对数据库的增/删/改的操作的SQL语句，就有了非常有价值的线索。所以我想，能不能通过事务Id和线程Id，在数据库的二进制日志中找到线索，mysql服务器是启用了二进制日志的。

zjcxc 2018-02-01

打赏
举报

相关的表关联上去不就行了，参考如下，各表其他列的意义，自己在官网文档上查吧

SELECT 
	TH.processlist_id,	-- 这个就是你要的 connection_id， 你可以 kill 这个，达到终止它的操作的目的
	TH.processlist_command as command,	-- 这个为 sleep，则表明操作结束了，但没有提交事务，也就是事务挂起了
	TIMESTAMPDIFF(second, TRX.trx_started, NOW()) as tx_duration, -- 事务已经开启多长时间了
	ESC.current_schema, ESC.sql_text	-- 这个不一定能查到，最后执行的 SQL（事务中有多个语句时，这个只是最后一个，江代表是产生锁的那个）
FROM performance_schema.threads TH
	INNER JOIN information_schema.innodb_trx TRX
		ON TRX.trx_mysql_thread_id = TH.processlist_id
	LEFT JOIN performance_schema.events_statements_current ESC
		ON ESC.thread_id = TH.thread_id

小灰狼 2018-02-01

打赏
举报

引用 5 楼 zjcxc 的回复:

我这个测试只有一个连接，开事务加锁
另一个连接只是查询状态，跟测试的连接没关系，在这个连接中查出了数据，并不像你说的不出数据

查到的这些信息对我来说没有什么帮助

产生锁的根本原因，是程序中对行加了锁，但是没有解锁，导致其它线程再也无法访修改这条记录，最终导致整个系统无法使用，除非系统服务重启。
现在最需要做的是，找到锁记录的那个线程，修改它，让它及时释放锁，这样才能从根本上解决问题。
在你举的例子中，可以从脚本中得到连接Id，但是软件的连接是在Java程序中的，我无法知道它的ConnectionId。而直接从 information_schema.innodb_trx 中找，又没有提供有价值的线索，让我知道到底是什么样的语句锁的记录。

换句话说，我是想找到锁行的那个连接的线索，想知道它到底干了些什么，这样才好反向推断是软件中的哪个业务逻辑在坏事！

小灰狼 2018-02-01

打赏
举报

晕死！数据库服务器是放在阿里云上的，阿里云服务器不支持启用 general_log

小灰狼 2018-02-01

打赏
举报

引用 9 楼 zjcxc 的回复:

TRX.trx_started 这个是事务开始的时间，你通过这个开始时间查 binlog 不就行了？

想了一下，查binlog 好象不行记得以前看过文章，说在 oracle 里，重做日志的内容只有在进行事务提交时才会执行写入操作。而我的程序查错是要找到事务没有提交的那一串数据库操作。估计在mysql里应该也是差不多的原理。刚才试了一下，事务没有提交时，binlog 文件导出成sql之后，确实没有找到没有提交的事务的语句。另外，网上找了一下，发现可以启用 general_log，在这里可以找到所有的 sql 语句日志。

zjcxc 2018-01-31

打赏
举报

mysql> create temporary table t(id int); Query OK, 0 rows affected (0.01 sec) mysql> start transaction; insert into t values(1); Query OK, 0 rows affected (0.01 sec) Query OK, 1 row affected (0.00 sec) mysql> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 56 | +-----------------+ 1 row in set (0.00 sec) ------------------------------------------------------------------------------------ 然后另开一个窗口查询 mysql> SELECT * FROM information_schema.innodb_trx tx WHERE trx_mysql_thread_id = 56 \G *************************** 1. row *************************** trx_id: 195298 trx_state: RUNNING trx_started: 2018-01-31 16:02:14 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 1 trx_mysql_thread_id: 56 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 0 trx_lock_structs: 0 trx_lock_memory_bytes: 1136 trx_rows_locked: 0 trx_rows_modified: 1 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_is_read_only: 0 trx_autocommit_non_locking: 0

zjcxc 2018-01-31