Commissioning of high-performance mysql-locks

The debugging of the lock is divided into two parts, one is the debugging of the server level lock. The second is the debug of the lock at the storage engine level

Debugging for server-level locks:

The types of server-level locks are table locks, global locks, named locks, character Fu Yi

Debug command:

Show Processlist to see the status of the current query process, and what States may need to be checked out in practice.

There is no way to find out who holds the lock, you can use the debug command to print information about the lock to the server error log, you can use the Mysqlladmin tool to run this command: Mysqlladmin debug can see a lot of debugging information in the log

Lock debugging in the storage engine:

Debug command;

1. Show InnoDB Status If the transaction is waiting for a lock, then a portion of the lock's information is output, but the information for all locks is not output. And it's hard to find out who owns this lock. By turning on InnoDB lock monitoring, it can monitor up to 10 locks per transaction. The way to activate this monitoring is to create a innnodb_lock_monitor table. MySQL then periodically prints the enhanced version of Show InnoDB status to the log or standard output. Remove the watch to stop the monitoring. Because the content of the output is redundant, you can use Innotop to parse and format the output, and you can use patches to simplify the results of the output.

2. Use the INFORMATION_SCHEMA series table to view the hold status of the lock. Requires MySQL version at 5.1 and above and InnoDB plugin. The specific table includes the following three types:

SELECT * FROM INFORMATION_SCHEMA.   Innodb_trx\g; Records the transactions that are currently executing, as well as some state of the transaction

SELECT * FROM INFORMATION_SCHEMA.   Innodb_locks\g; Contains the specifics of the INNODB transaction lock, including the lock that the transaction is requesting and the lock the transaction is added to.

SELECT * FROM INFORMATION_SCHEMA. Innodb_lock_waits; The state of the lock waits for the transaction that contains the blocked

One example of this use is: https://www.cnblogs.com/zhaoxinshanwei/p/9409511.html

Commissioning of high-performance mysql-locks