Mysql queries the transactions being executed and how to wait for locks

Use navicat to test and learn:

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

Open an update

Query the transactions being executed:

SELECT * FROM information_schema.INNODB_TRX 

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

From the above figure, we can see the corresponding mysql threads: one is 94362 (the second one is waiting for the lock) and the other is 93847 (the first update is executing without committing the 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 that the waiting time for the lock has timed out

You can also use the query mysql database:

View locked transactions

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

View transactions waiting for locks

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

Query the 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, you do not connect to a specific database, but to establish a session with the instance (that is, when executing a query on Navicat, you can query multiple databases corresponding to the port, and the database name + data table name is sufficient for the query)

Multiple instance can be created on a physical machine and the instances can be distinguished by port .

An instance can create multiple databases, that is, a session can operate multiple databases on an instance.

The jdbc protocol connects to the database: jdbc:mysql://localhost:3306/test The jdbc protocol connects to the test database under the local instance with port 3306. You do not need to add the database name when querying the data table.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links