What is a deadlock? Let's talk about the understanding of MySQL deadlock-Mysql Tutorial-php.cn

What is a deadlock? The following article will take you to understand MySQL deadlock. Let’s talk about the necessary conditions for deadlock in Mysql and how to solve the deadlock problem? I hope to be helpful.

What is a deadlock? Let's talk about the understanding of MySQL deadlock

#1. What is a deadlock?

Deadlock refers to the situation in two or more different processes or threads where each thread is blocked due to competition for common resources or communication between processes (or threads). They hang and wait for each other. If there is no external force, it will eventually cause the entire system to collapse.

2. Necessary conditions for deadlock in Mysql

##Resource exclusive conditions

refers to the mutual exclusivity of multiple transactions when competing for the same resource, that is, a resource is only occupied by one transaction within a period of time, which can also be called an exclusive resource (such as a row lock).

Request and hold conditions

means that lock A has been obtained in a transaction a, but a new Lock B is requested, and the lock B is already occupied by another transaction b. At this time, the transaction a will block, but it will keep the lock A it has obtained.

Non-deprivation condition

means that lock A has been obtained in transaction a and cannot be locked before it is submitted. To deprive it, you can only commit the transaction after use and then release it yourself.

Mutual lock acquisition conditions

means that when a deadlock occurs, there must be a mutual lock acquisition process, that is, While transaction a holding lock A is acquiring lock B, transaction b holding lock B is also acquiring lock A, which ultimately leads to mutual acquisition and each transaction is blocked.

3. Mysql classic deadlock case

Assume there is a transfer scenario. When account A transfers 50 yuan to account B, account B Also transfer 30 yuan to account A. Will there be a deadlock in this process?

3.1 Table creation statement

CREATE TABLE `account` (
  `id` int(11) NOT NULL COMMENT '主键',
  `user_id` varchar(56) NOT NULL COMMENT '用户id',
  `balance` float(10,2) DEFAULT NULL COMMENT '余额',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='账户余额表';

Copy after login

3.2 Initialize related data

INSERT INTO `test`.`account` (`id`, `user_id`, `balance`) VALUES (1, 'A', 80.00);
INSERT INTO `test`.`account` (`id`, `user_id`, `balance`) VALUES (2, 'B', 60.00);

Copy after login

3.3 Normal transfer process

Before talking about the deadlock problem, let’s take a look at it first See the normal transfer process.
Under normal circumstances, user A transfers 50 yuan to user B, which can be completed within one transaction. User A's balance and user B's balance need to be obtained first. Because these two pieces of data need to be modified later, a write lock is required. (for UPDATE) Lock them to prevent other transaction changes from causing our changes to be lost and causing dirty data.
The relevant sql is as follows:

Before starting the transaction, you need to turn off the automatic submission of mysql

set autocommit=0;
# 查看事务自动提交状态状态

Copy after login

show VARIABLES like 'autocommit';![Insert picture here Description](https://img-blog.csdnimg.cn/a486a4ed5c9d4240bd115ac7b3ce5a39.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_

Q1NETiBA6ZqQIOmjjg==, size_20,color_FFFFFF,t_70,g_se,x_16)

# 转账sql
START TRANSACTION;
# 获取A 的余额并存入A_balance变量：80
SELECT user_id,@A_balance:=balance from account where user_id = 'A' for UPDATE;
# 获取B 的余额并存入B_balance变量:60
SELECT user_id,@B_balance:=balance from account where user_id = 'B' for UPDATE;

# 修改A 的余额
UPDATE account set balance = @A_balance - 50 where user_id = 'A';
# 修改B 的余额
UPDATE account set balance = @B_balance + 50 where user_id = 'B';
COMMIT;

Copy after login

Result after execution:

What is a deadlock? Lets talk about the understanding of MySQL deadlock

You can see that the data updates are normal

3.4 Deadlock transfer process

The initialized balance is:

What is a deadlock? Lets talk about the understanding of MySQL deadlock

Assume that this scenario exists under high concurrency. When user A transfers 50 yuan to user B, user B also transfers 30 yuan to user A.

Then the process and timeline of our java program operation are as follows:

1. User A transfers 50 yuan to user B, and transaction 1 needs to be opened in the program to execute sql. And get the balance of A and lock the data of A.

# 事务1
set autocommit=0;
START TRANSACTION;
# 获取A 的余额并存入A_balance变量：80
SELECT user_id,@A_balance:=balance from account where user_id = 'A' for UPDATE;

Copy after login

2. User B transfers 30 yuan to user A. He needs to open transaction 2 in the program to execute sql, obtain B's balance and lock B's data.

# 事务2
set autocommit=0;
START TRANSACTION;
# 获取A 的余额并存入A_balance变量：60
SELECT user_id,@A_balance:=balance from account where user_id = 'B' for UPDATE;

Copy after login

3. Execute the remaining sql in transaction 1

# 获取B 的余额并存入B_balance变量:60
SELECT user_id,@B_balance:=balance from account where user_id = 'B' for UPDATE;

# 修改A 的余额
UPDATE account set balance = @A_balance - 50 where user_id = 'A';
# 修改B 的余额
UPDATE account set balance = @B_balance + 50 where user_id = 'B';
COMMIT;

Copy after login

You can see that it occurs when the write lock of B data is acquired in transaction 1 A timeout condition occurred. Why is this so? Mainly because we have obtained the write lock on data B in transaction 2 in step 2, so transaction 1 will never get the write lock on data B before transaction 2 commits or rolls back.

4. Execute the remaining sql in transaction 2

# 获取A 的余额并存入B_balance变量:60
SELECT user_id,@B_balance:=balance from account where user_id = 'A' for UPDATE;

# 修改B 的余额
UPDATE account set balance = @A_balance - 30 where user_id = 'B';
# 修改A 的余额
UPDATE account set balance = @B_balance + 30 where user_id = 'A';
COMMIT;

Copy after login

##In the same way, obtain the writing of A data in transaction 2 A timeout also occurred when locking. Because the write lock on data A has been obtained in transaction 1 in step 1, transaction 2 will never get the write lock on data A before transaction 1 commits or rolls back.

5、为什么会出现这种情况呢？

主要是因为事务1和事务2存在相互等待获取锁的过程，导致两个事务都挂起阻塞，最终抛出获取锁超时的异常。

3.5 死锁导致的问题

众所周知，数据库的连接资源是很珍贵的，如果一个连接因为事务阻塞长时间不释放，那么后面新的请求要执行的sql也会排队等待，越积越多，最终会拖垮整个应用。一旦你的应用部署在微服务体系中而又没有做熔断处理，由于整个链路被阻断，那么就会引发雪崩效应，导致很严重的生产事故。

4、如何解决死锁问题？

要想解决死锁问题，我们可以从死锁的四个必要条件入手。
由于资源独占条件和不剥夺条件是锁本质的功能体现，无法修改，所以咱们从另外两个条件尝试去解决。

4.1 打破请求和保持条件

根据上面定义可知，出现这个情况是因为事务1和事务2同时去竞争锁A和锁B，那么我们是否可以保证锁A和锁B一次只能被一个事务竞争和持有呢？
答案是肯定可以的。下面咱们通过伪代码来看看：

/**
* 事务1入参(A, B)
* 事务2入参(B, A)
**/
public void transferAccounts(String userFrom, String userTo) {
     // 获取分布式锁
     Lock lock = Redisson.getLock();
     // 开启事务
     JDBC.excute("START TRANSACTION;");
     // 执行转账sql
     JDBC.excute("# 获取A 的余额并存入A_balance变量：80\n" +
             "SELECT user_id,@A_balance:=balance from account where user_id = '" + userFrom + "' for UPDATE;\n" +
             "# 获取B 的余额并存入B_balance变量:60\n" +
             "SELECT user_id,@B_balance:=balance from account where user_id = '" + userTo + "' for UPDATE;\n" +
             "\n" +
             "# 修改A 的余额\n" +
             "UPDATE account set balance = @A_balance - 50 where user_id = '" + userFrom + "';\n" +
             "# 修改B 的余额\n" +
             "UPDATE account set balance = @B_balance + 50 where user_id = '" + userTo + "';\n");
     // 提交事务
     JDBC.excute("COMMIT;");
     // 释放锁
     lock.unLock();
}

Copy after login

上面的伪代码显而易见可以解决死锁问题，因为所有的事务都是通过分布式锁来串行执行的。

那么这样就真的万事大吉了吗？

在小流量情况下看起来是没问题的，但是在高并发场景下这里将成为整个服务的性能瓶颈，因为即使你部署了再多的机器，但由于分布式锁的原因，你的业务也只能串行进行，服务性能并不因为集群部署而提高并发量，完全无法满足分布式业务下快、准、稳的要求，所以咱们不妨换种方式来看看怎么解决死锁问题。

4.2 打破相互获取锁条件（推荐）

要打破这个条件其实也很简单，那就是事务再获取锁的过程中保证顺序获取即可，也就是锁A始终在锁B之前获取。
我们来看看之前的伪代码怎么优化？

/**
* 事务1入参(A, B)
* 事务2入参(B, A)
**/
public void transferAccounts(String userFrom, String userTo) {
     // 对用户A和B进行排序，让userFrom始终为用户A，userTo始终为用户B
     if (userFrom.hashCode() > userTo.hashCode()) {
         String tmp = userFrom;
         userFrom = userTo;
         userTo = tmp;
     }
     // 开启事务
     JDBC.excute("START TRANSACTION;");
     // 执行转账sql
     JDBC.excute("# 获取A 的余额并存入A_balance变量：80\n" +
             "SELECT user_id,@A_balance:=balance from account where user_id = '" + userFrom + "' for UPDATE;\n" +
             "# 获取B 的余额并存入B_balance变量:60\n" +
             "SELECT user_id,@B_balance:=balance from account where user_id = '" + userTo + "' for UPDATE;\n" +
             "\n" +
             "# 修改A 的余额\n" +
             "UPDATE account set balance = @A_balance - 50 where user_id = '" + userFrom + "';\n" +
             "# 修改B 的余额\n" +
             "UPDATE account set balance = @B_balance + 50 where user_id = '" + userTo + "';\n");
     // 提交事务
     JDBC.excute("COMMIT;");
 }

Copy after login

假设事务1的入参为(A, B)，事务2入参为(B, A)，由于我们对两个用户参数进行了排序，所以在事务1中需要先获取锁A在获取锁B，事务2也是一样要先获取锁A在获取锁B，两个事务都是顺序获取锁，所以也就打破了相互获取锁的条件，最终完美解决死锁问题。

5、总结

因为mysql在互联网中的大量使用，所以死锁问题还是经常会被问到，希望兄弟们能掌握这方面的知识，提高自己的竞争力。

【相关推荐：mysql视频教程】

The above is the detailed content of What is a deadlock? Let's talk about the understanding of MySQL deadlock. For more information, please follow other related articles on the PHP Chinese website!