How to distinguish between deadlock and lock waiting in MySQL

This article introduces how to distinguish between deadlock and lock waiting in MySQL. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

[database version] MySQL5.7

Program error 1205 Lock wat timeout exceeded, thought to be deadlock, in fact, are two concepts, indicating that the deadlock monitoring module is no problem. Deadlock monitoring is a deadlock, not lock waiting.

The error message is as follows:

SQLSTATEHY000]: General error: 1205 Lock wat timeout exceeded; try restarting transaction (SQL: UPDATE xx set xxx = 1205 where id=1 and rock_id > 1)

1213 Deadlock found when trying to get lock; try restarting transaction

Https://www.cnblogs.com/tartis/p/9366574.html

Https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlock-example.html

MySQL 5.7We can query lock status information with the following statement

SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread B.trx_query blocking_query FROM information_ schema.`INNODB _ LOCK_ WAITS` w INNER JOIN information_ schema.`INNODB _ TRX`b ON b.`trx _ id` = w.`requests _ trx_ id`INNER JOIN information_ schema.`INNODB _ T RX`r ON r.`trx _ id` = w.`requesting _ trx_ id`

MySQL8.0, we view the lock information with the following statement

Select * from * innocent database lockout waits; this is all about how to distinguish between deadlocks and lock waits in MySQL. I hope the above content can be of some help and learn more knowledge. If you think the article is good, you can share it for more people to see.