Maison > Article > base de données > Qu'est-ce que l'impasse MySQL
Qu'est-ce que l'impasse MySQL
- 青灯夜游original
- 2023-04-06 16:41:342988parcourir
Dans MySQL, le blocage fait référence à un phénomène d'attente les uns pour les autres en raison de la concurrence pour les ressources entre deux ou plusieurs processus ou threads différents en raison de la concurrence pour des ressources ou des processus (ou threads) communs. La communication entre les threads provoque le blocage de chaque thread ; Attendez-vous les uns les autres. S'il n'y a pas de force extérieure, cela finira par provoquer l'effondrement de tout le système. Les conditions nécessaires pour un blocage dans MySQL : 1. Conditions d'exclusivité des ressources ; 2. Conditions de demande et de conservation 3. Conditions de non-privation 4. Conditions de verrouillage d'acquisition mutuelle ;
L'environnement d'exploitation de ce tutoriel : système windows7, version mysql8, ordinateur Dell G3.
1. Qu'est-ce que l'impasse ?
L'impasse fait référence à un phénomène d'attente les uns pour les autres en raison de la concurrence pour les ressources entre deux ou plusieurs processus ou threads différents en raison de la concurrence pour les ressources communes ou entre processus (ou threads). La communication fait que chaque thread se bloque et attend ; l’un pour l’autre. S’il n’y a pas de force extérieure, cela finira par provoquer l’effondrement du système tout entier.
À l'heure actuelle, on dit que le système est dans un état de blocage ou que le système est dans une impasse. Ces processus qui s'attendent toujours les uns les autres sont appelés processus de blocage.
2. Conditions nécessaires au blocage dans Mysql
Condition d'exclusivité des ressources
fait référence à l'exclusivité mutuelle lorsque plusieurs transactions sont en concurrence pour la même ressource, c'est-à-dire, sur une période donnée, une ressource n'est utilisée que par Occupé par une transaction, elle peut également être appelée ressource exclusive (comme un verrou de ligne).
Conditions de demande et de conservation
signifie que le verrou A a été obtenu dans une transaction a, mais qu'une nouvelle demande de verrou B a été effectuée et que le verrou B est déjà occupé par une autre transaction b. temps, la transaction a se bloquera, mais elle conservera le verrou A qu'elle a obtenu.
Aucune condition de privation
fait référence à un verrou A qui a été acquis dans la transaction a. Il ne peut être privé avant d'être soumis. Il ne peut être engagé qu'après utilisation puis libéré par lui-même.
Conditions d'acquisition de verrouillage mutuel
signifie que lorsqu'une impasse se produit, il doit y avoir un processus d'acquisition de verrouillage mutuel, c'est-à-dire qu'une transaction contenant un verrou A acquiert le verrou B, et en même temps, la transaction détenir le verrou B b, c'est également acquérir le verrou A, ce qui conduit finalement à une acquisition mutuelle et chaque transaction est bloquée.
3. Cas d'impasse classique de Mysql
Supposons qu'il y ait un scénario de transfert lorsque le compte A transfère 50 yuans vers le compte B, le compte B transfère également 30 yuans vers le compte A. Y aura-t-il des problèmes dans ce processus ? Qu’en est-il des situations de blocage ?
3.1 Déclaration de création de table
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='账户余额表';
3.2 Initialiser les données associées
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);
3.3 Processus de transfert normal
Avant de parler du problème de blocage, regardons-le Consultez d’abord le processus de transfert normal.
Dans des circonstances normales, l'utilisateur A transfère 50 yuans à l'utilisateur B, ce qui peut être effectué en une seule transaction. Le solde de l'utilisateur A et le solde de l'utilisateur B doivent d'abord être obtenus. Étant donné que ces deux données doivent être modifiées ultérieurement, un verrou en écriture. (pour UPDATE) les verrouille pour empêcher d'autres modifications de transaction d'entraîner la perte de nos modifications et de provoquer des données sales.
Le SQL pertinent est le suivant :
Vous devez désactiver la validation automatique de MySQL avant d'ouvrir la transaction
set autocommit=0; # 查看事务自动提交状态状态
afficher les VARIABLES comme 'autocommit' ;
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;Résultat après exécution :
3.4 Processus de transfert dans une impasse
Le solde initialisé est
En supposant une concurrence élevée Il existe un scénario dans lequel l'utilisateur A transfère 50 yuans à l'utilisateur B, et l'utilisateur B transfère également 30 yuans à l'utilisateur A.Ensuite, le processus et le calendrier de fonctionnement de notre programme Java sont les suivants : 1. L'utilisateur A transfère 50 yuans à l'utilisateur B. Il doit ouvrir la transaction 1 dans le programme pour exécuter SQL, obtenir le solde de A et verrouiller la pièce de A. de données.
# 事务1 set autocommit=0; START TRANSACTION; # 获取A 的余额并存入A_balance变量：80 SELECT user_id,@A_balance:=balance from account where user_id = 'A' for UPDATE;2. L'utilisateur B transfère 30 yuans à l'utilisateur A. Il doit ouvrir la transaction 2 dans le programme pour exécuter SQL, obtenir le solde de B et verrouiller les données de B.
# 事务2 set autocommit=0; START TRANSACTION; # 获取A 的余额并存入A_balance变量：60 SELECT user_id,@A_balance:=balance from account where user_id = 'B' for UPDATE;3. Exécutez le SQL restant dans la 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;
🎜可以看到，在事务1中获取B数据的写锁时出现了超时情况。为什么会这样呢？主要是因为我们在步骤2的时候已经在事务2中获取到B数据的写锁了，那么在事务2提交或回滚前事务1永远都拿不到B数据的写锁。
4.在事务2中执行剩下的sql
# 获取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;
同理可得，在事务2中获取A数据的写锁时也出现了超时情况。因为步骤1的时候已经在事务1中获取到A数据的写锁了，那么在事务1提交或回滚前事务2永远都拿不到A数据的写锁。
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();
}
上面的伪代码显而易见可以解决死锁问题，因为所有的事务都是通过分布式锁来串行执行的。
那么这样就真的万事大吉了吗？
在小流量情况下看起来是没问题的，但是在高并发场景下这里将成为整个服务的性能瓶颈，因为即使你部署了再多的机器，但由于分布式锁的原因，你的业务也只能串行进行，服务性能并不因为集群部署而提高并发量，完全无法满足分布式业务下快、准、稳的要求，所以咱们不妨换种方式来看看怎么解决死锁问题。
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;");
}
假设事务1的入参为(A, B)，事务2入参为(B, A)，由于我们对两个用户参数进行了排序，所以在事务1中需要先获取锁A在获取锁B，事务2也是一样要先获取锁A在获取锁B，两个事务都是顺序获取锁，所以也就打破了相互获取锁的条件，最终完美解决死锁问题。
5、 如何预防死锁
阻止死锁的途径就是避免满足死锁条件的情况发生，为此我们在开发的过程中需要遵循如下原则：
1.尽量避免并发的执行涉及到修改数据的语句。
2.要求每一个事务一次就将所有要使用到的数据全部加锁，否则就不允许执行。
3.预先规定一个加锁顺序，所有的事务都必须按照这个顺序对数据执行封锁。如不同的过程在事务内部对对象的更新执行顺序应尽量保证一致。
4.每个事务的执行时间不可太长，对程序段的事务可考虑将其分割为几个事务。在事务中不要求输入，应该在事务之前得到输入，然后快速执行事务。
5.使用尽可能低的隔离级别。6.数据存储空间离散法。该方法是指采用各种手段，将逻辑上在一个表中的数据分散的若干离散的空间上去，以便改善对表的访问性能。主要通过将大表按行或者列分解为若干小表，或者按照不同的用户群两种方法实现。
7.编写应用程序，让进程持有锁的时间尽可能短，这样其它进程就不必花太长的时间等待锁被释放。
死锁的概念：
如果一组进程中的每一个进程都在等待仅由该组进程中的其他进程才能引发的事件，那么改组进程是死锁的。
死锁的常见表现：
死锁不仅会发生多个进程中，也会发生在一个进程中。
（1）多进程死锁：有进程A，进程B，进程A拥有资源1，需要请求正在被进程B占有的资源2。而进程B拥有资源2，请求正在被进程A战友的资源1。两个进程都在等待对方释放资源后请求该资源，而相互僵持，陷入死锁。
（2）单进程死锁：进程A拥有资源1，而它又在请求资源1，而它所请求的资源1必须等待该资源使用完毕得到释放后才可被请求。这样，就陷入了自己的死锁。
产生死锁的原因：
（1）进程推进顺序不当造成死锁。
（2）竞争不可抢占性资源引起死锁。
（3）竞争可消耗性资源引起死锁。
死锁的四个必要条件（四个条件四者不可缺一）：
（1）互斥条件。某段时间内，一个资源一次只能被一个进程访问。
（2）请求和保持条件。进程A已经拥有至少一个资源，此时又去申请其他资源，而该资源又正在被进程使用，此时请求进程阻塞，但对自己已经获得的资源保持不放。
（3）不可抢占资源。进程已获得的资源在未使用完不能被抢占，只能在自己使用完时由自己释放。
（4）循环等待序列。存在一个循环等待序列P0P1P2……Pn，P0请求正在被进程P1占有的资源，P1请求正在被P2占有的资源……Pn正在请求被进程P0占有的资源。
解除死锁的两种方法：
（1）终止（或撤销）进程。终止（或撤销）系统中的一个或多个死锁进程，直至打破循环环路，使系统从死锁状态中解除出来。
（2）抢占资源。从一个或多个进程中抢占足够数量的资源，分配给死锁进程，以打破死锁状态。
6、死锁场景
本文死锁场景皆为工作中遇到（或同事遇到）并解决的死锁场景，写这篇文章的目的是整理和分享，欢迎指正和补充，本文死锁场景包括：
行锁导致死锁
gap lock/next keys lock导致死锁
index merge 导致死锁
唯一索引冲突导致死锁
注：以下场景隔离级别均为默认的Repeatable Read；
1）行锁导致死锁
死锁原因详解：
1.两个事务执行过程时间上有交集，并且过程发生在两者提交之前
2.事务1更新uid=1的记录，事务2更新uid=2的记录，在RR级别，由于uid是唯一索引，因此两个事务将分别持有uid=1和2所在行的独占锁
3.事务1执行到第二条更新语句时，发现uid=2的行被锁住，进入阻塞等待锁释放；
4.事务2执行到第二条语句时发现uid=1的行被锁，同样进入阻塞
5.两个事务互相等待，死锁产生。
相应业务案例和解决方案：
该场景常见于事务中存在for循环更新某条记录的情况，死锁日志显示lock_mode X locks rec but not gap waiting（即行锁而非间隙锁），解决方案：
1.避免循环更新，优化为一条where锁定要更新的记录批量更新
2.如果非要循环更新，尝试取消事务（能接受的话），即每一条更新为一个独立的事务
2）gap lock/next keys lock导致死锁
死锁原因分析：
1.事务1执行delete age = 27，务2执行delete age = 31，在RR级别，操作条件不是唯一索引时，行锁会升级为next keys
lock（可以理解为间隙锁），因此事务1锁住了25到27和27到29的区间，事务2锁住了29到31的区间
2.事务1执行insert age = 30，等待事务2释放锁
3.事务2执行insert age = 28，等待事务1释放锁
4.死锁产生，死锁日志显示lock_mode X locks gap before rec insert intention waiting
解决方案：
1.降低事务隔离级别到Read Committed，该隔离级别下间隙锁降级为行锁，可以减少死锁发生的概率
2.避免这种场景- -
3）index merge导致死锁
t_user结构改造为：
死锁分析：
1.在符合场景前提的情况下（即表数据量较大，index_merge未关闭），通过explain分析update t_user where zone_id = 1 and uid = 1可以发现type是index_merge，即会用到zone_id和uid两个索引
2.上锁的过程为：事务1：
① 锁住zone_id=1对应的间隙锁: zoneId in (1,2)
② 锁住索引zone_id=1对应的主键索引行锁id = [1,2]
③ 锁住uid=1对应的间隙锁: uid in (1, 2)
④ 锁住uid=1对应的主键索引行锁: id = [1, 3]事务2：
① 锁住zone_id=2对应的间隙锁: zoneId in (1,2)
② 锁住索引zone_id=2对应的主键索引行锁id = [3,4]
③ 锁住uid=2对应的间隙锁: uid in (1,2)
④ 锁住uid=2对应的主键索引行锁: id = [2, 4]
1、如果两个事务上锁的顺序相反，则有一定的概率出现死锁。另外，index_merge的形式锁住了很多不符合条件的行，浪费了资源。一般死锁日志打印的信息为：
lock_mode X locks rec but not gap waiting Record lock
解决方案：创建联合索引，使执行计划只会用到一个索引。
注：
update table set name = “wea” where col_1 = 1 or col_2 = 2 ;
col_1和col_2为联合索引，遵循最左原则col_1会走索引，但col_2会对整个索引进行扫描，此时会对整个索引加锁。
【相关推荐：mysql视频教程】
Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!