Mysql queries the transaction that is being executed and how to wait for the lock

Use navicat to test and learn:

First use set autocommit = 0; (cancel autocommit, then execute the commit transaction or rollback when the statement commit or rollback is executed)

Before opening an executive update

Query the transaction in progress:

SELECT * FROM information_schema.INNODB_TRX

According to the thread ID (trx_mysql_thread_id) of this transaction:

From the figure above, you can see the corresponding mysql thread: one 94362 (the second is waiting for a lock) and the other is 93847 (the first update is performing an uncommitted transaction)

You can use the mysql command: kill thread id to kill the thread

If the thread holding the lock is not killed during this period: the second update statement prompts you to wait for the lock to time out

In querying the mysql database, you can also use:

View transactions that are being locked

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS

View transactions waiting for locks

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS

Query processes that exist in the mysql database

[sql] view plain copy

Select * from information_ schema.`PROCESSLIST` (show processlist;)

Concept:

Database, instance, session in mysql.

To establish a session in mysql is not to connect with a specific database, but to establish a session with instance (that is, to execute a query on navicat, you can query multiple databases under the port, and the database name + data table name can be used when querying)

Multiple instance can be established on a physical machine, and instances can be distinguished by port.

On the other hand, an instance can establish multiple databases, that is, a session can operate multiple databases on an instance.

Jdbc protocol connection database: if jdbc:mysql://localhost:3306/test jdbc protocol connects to the local test database with port 3306 instance, the database name does not need to be added when querying the data table.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.