Detailed explanation of MySQL deadlock usage and detection and avoidance methods

This article brings you relevant knowledge about mysql, which mainly introduces the detailed explanation of MySQL deadlock usage and detection and avoidance methods. The article introduces the content in detail around the theme, which has certain significance. For reference value, let’s take a look at it below. I hope it will be helpful to everyone.

Recommended study: mysql video tutorial

When we use locks, there is a problem that needs to be paid attention to and avoided. We know that exclusive locks have mutually exclusive characteristics. When a transaction or a thread holds a lock, it will prevent other threads from acquiring the lock. This will cause blocking waiting. If you wait in a loop, it may cause a deadlock.

We need to analyze this problem from several aspects. One is why the lock is not released, the second is what to do if it is blocked, and the third is how deadlock occurs and how to avoid it.

Lock release and blocking

Review: When will the lock be released?
Transaction ends (commit, rollback);
The client connection is disconnected.

If a transaction has not released the lock, how long will other transactions be blocked? Will they wait forever?
If so, when concurrent access is relatively high, if a large number of transactions are blocked due to Failure to obtain the required lock immediately and hanging will occupy a lot of computer resources, cause serious performance problems, and even drag across the database.

Are you afraid of this mistake online?

[Err] 1205 - Lock wait timeout exceeded; try restarting transaction

MySQL has a parameter to control the waiting time to acquire the lock, the default is 50 seconds.

show VARIABLES like "innodb_lock_wait_timeout";

For deadlock, no matter how long you wait, you cannot obtain the lock. In this case, do you need to wait 50 seconds? Isn't that 50 seconds wasted in vain?

Occurrence and detection of deadlock

Demonstration, open two sessions:

Facilitates the withdrawal of the timeline, pictures are used here, those who are interested can follow Imitate it

Chestnut one:

## Chestnut two:

In the first transaction, a deadlock was detected and exited immediately. The second transaction obtained the lock without waiting for 50 seconds:

[Err] 1213 - Deadlock found when trying to get lock; try restarting transaction

Why can it be detected directly? This is because certain conditions need to be met for the occurrence of deadlock. For us programmers, having clear conditions means being able to determine. Therefore, when a deadlock occurs, InnoDB can generally automatically detect it through the algorithm (wait-for graph). arrive.

So what conditions need to be met for deadlock? The conditions for deadlock to occur, because the lock itself is mutually exclusive:

(1) At the same time, only There is a transaction holding this lock;
• (2) Other transactions need to release the lock before this transaction can acquire the lock, and cannot forcibly deprive it;
• (3) When multiple When a transaction forms a waiting loop, a deadlock occurs.

The barber shop has two directors. There is Teacher Tony who is responsible for cutting hair, and Teacher Kelvin who is responsible for washing hair. Teacher Tony cannot cut the hair of two people at the same time. This is called mutual exclusion.

When Tony is cutting someone's hair, you can't ask him to stop and cut your hair. This is called Cannot be taken away by force

.

If Tony’s client says to Kelvin: How can I cut my hair if you don’t wash it for me? Kelvin’s client says to Tony: How can I wash my hair if you don’t cut my hair? This is called forming a waiting loop. .
In fact, there are many situations where deadlock occurs, but they all meet the above three conditions. This is also why table locks will not cause deadlock, because the resources of table locks are acquired all at once
.
If the lock has not been released, it may cause a lot of blocking or deadlock, resulting in a decrease in system throughput. At this time, you need to check which transactions hold the lock.

View lock information (log)

First of all, the SHow STATUS command includes some row lock information:

show status like 'innodb_row_lock_%';

lnnodb_row_lock_current_waits: The number of locks currently waiting for;

lnnodb_row_lock_time: The total length of time from system startup to the current lock, in ms;

Innodb_row_lock_time_avg: The average time spent waiting each time;

Innodb_row_lock_time_max: The longest wait time from system startup to now;
lnnodb_row_lock_waits: The total number of waits from system startup to now.


SHOW command is a summary information. InnoDB also provides three tables to analyze transactions and locks:

select * from information_schema.INNODB_TRX; --当前运行的所有事务﹐还有具体的语句

select* from information_schema.INNODB_LOCKS; --当前出现的锁

select * from information_schema.INNODB_LOCK_WAITS; --锁等待的对应关系

更加详细的锁信息，开启标准监控和锁监控:

额外的监控肯定会消耗额外的性能

set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;

通过分析锁日志，找出持有锁的事务之后呢?
如果一个事务长时间持有锁不释放，可以kill事务对应的线程ID，也就是INNODB_TRX表中的trx_mysql_thread_id，例如执行kill 4，kill 7， kill 8。
当然，死锁的问题不能每次都靠kill线程来解决，这是治标不治本的行为。我们应该尽量在应用端，也就是在编码的过程中避免。
有哪些可以避免死锁的方法呢?

死锁的避免

• 1、在程序中，操作多张表时，尽量以相同的顺序来访问（避免形成等待环路)
• 2、批量操作单张表数据的时候，先对数据进行排序(避免形成等待环路);
• 3、申请足够级别的锁，如果要操作数据，就申请排它锁;
• 4、尽量使用索引访问数据，避免没有where条件的操作，避免锁表;
• 5、如果可以，大事务化成小事务;
• 6、使用等值查询而不是范围查询查询数据，命中记录，避免间隙锁对并发的影响。

推荐学习：mysql视频教程

The above is the detailed content of Detailed explanation of MySQL deadlock usage and detection and avoidance methods. For more information, please follow other related articles on the PHP Chinese website!