Shulou(Shulou.com)05/31 Report--

This article mainly shows you how to locate the script that causes lock blocking in mysql, which is easy to understand and clear. I hope it can help you solve your doubts. Let me lead you to study and learn how to locate the script that leads to lock blocking in mysql.

-View transaction locks blocking threads and waiting threads

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

L.lock_index AS waiting_index_lock

B.trx_id AS blocking_trx_id, b.`trx _ mysql_thread_ id` AS blocking_thread

SUBSTRING (p. Hostdome 1) AS blocking_host INSTR (p. Host1')

SUBSTRING (p. Hostmending INSTR (p.hostdint') + 1) AS blocking_port

IF (p.command = "Sleep", p.timejin0) 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

SELECT * FROM information_ schema.`INNODB _ LOCKS`\ G

If you are suffering from a lot of lock operations because the thread is idle in a transaction, the following variant query can

Tells you how many queries are blocked by which threads without extra useless information.

SELECT CONCAT ('thread', b.trxboxes mysqlthread reading AS who_blocks' from', p.host)

IF (p.command = "Sleep", p.timejin0) 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.trxroomid` = w.`notify _ 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

* * 1. ROW *

Who_blocks: thread 4 FROM localhost-notice that thread 4 is the source of blocking

Idle_in_trx: 2051

Max_wait_time: 10

Num_waiters: 3-Thread 4 blocks the most threads, thus judging that Thread 4 is the source of blocking

* 2. ROW * *

Who_blocks: thread 20 FROM Lenovo-PC:51603

Idle_in_trx: 0

Max_wait_time: 6

Num_waiters: 2

* 3. ROW * *

Who_blocks: thread 11 FROM localhost

Idle_in_trx: 0

Max_wait_time: 5

Num_waiters: 1

3 ROWS IN SET (0.00 sec)

Set global tx_isolation='read-committed'

Set session tx_isolation='read-committed'

Select @ @ global.tx_isolation,@@tx_isolation

These are all the contents of this article entitled "how to locate scripts that cause lock blockage due to uncommitted idle session transactions in mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.