In-depth explanation of Mysql deadlock viewing and deadlock removal

Preface

I encountered a Mysql deadlock problem some time ago, so I sorted it out.

Problem description: Mysql modification statements do not seem to take effect, and an exception will pop up when using the Mysql GUI tool to edit the value of a field.

What is deadlock?

Before solving the MySQL deadlock problem, let's first understand what a deadlock is.

Deadlock refers to a phenomenon in which two or more processes wait for each other due to competition for resources during execution. Without external force, they will not be able to move forward. At this time, the system is said to be in a deadlock state or the system has a deadlock. These processes that are always waiting for each other are called deadlocked processes.

Deadlock manifestation

There are two specific manifestations of deadlock:

Mysql add and modify statements do not work properly. When you use the Mysql GUI tool to edit the value of a field, an exception occurs. How to avoid deadlock

The way to prevent deadlock is to avoid situations where deadlock conditions are met. To this end, we need to follow the following principles during the development process:

1. Try to avoid concurrent execution of statements that involve modifying data.

2. Each transaction is required to lock all the data to be used at one time, otherwise execution is not allowed.

3. A locking order is specified in advance, and all transactions must lock the data in this order. For example, the order in which different processes update objects within a transaction should be kept consistent as much as possible.

View Deadlocks

There are many ways to query whether there is a locked table in MySQL. Here we only introduce the most commonly used one.

1. View ongoing transactions

SELECT * FROM information_schema.INNODB_TRX

You can see that the transaction with process id 3175 is locked, and another transaction with id 3173 is being executed, but has not been committed.

2. View the locked transaction

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 

3. View transactions waiting for locks

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 

4. Check whether the table is locked

SHOW OPEN TABLES where In_use > 0; 

When a deadlock occurs, these methods can query information related to the current deadlock.

5. View the log of recent deadlocks

show engine innodb status 

Resolving deadlock

If you need to resolve the deadlock, there is a simple and brutal way, which is to find the process ID and kill it directly.

View the processes currently in progress

show processlist

// You can also use SELECT * FROM information_schema.INNODB_TRX;

The process id found by these two commands is the same.

Kill the process ID corresponding to the process

kill id

Verify (check if there is still a lock after killing)

SHOW OPEN TABLES where In_use > 0;

Reference Links

Mysql view table and unlock table

What is MySQL deadlock?

This is the end of this article about MySQL deadlock checking and deadlock removal. For more information about MySQL deadlock checking and deadlock removal, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!