如何解决MySQL中的死锁问题？
发布时间：2022-01-14 16:30:06 所属栏目：大数据 来源：互联网
导读：前段时间阿粉在公司开发的时候，不慎导致了数据库产生了锁表的操作，因为阿粉之前从来没有遇到过，之前只是了解过一点，所以导致了锁表，于是阿粉为了解决问题，研究了一下这一部分的内容，于是决定把这一块的知识分享给大家。 数据库锁定机制 话说如果你只
|
前段时间阿粉在公司开发的时候，不慎导致了数据库产生了锁表的操作，因为阿粉之前从来没有遇到过，之前只是了解过一点，所以导致了锁表，于是阿粉为了解决问题，研究了一下这一部分的内容，于是决定把这一块的知识分享给大家。 数据库锁定机制 话说如果你只是单纯的说 "锁表",总是让人感觉有点 Low ，而我们就直接换个比较高大上一点的名词，锁定机制! 为了保证数据的完整，也就是他的一致性和有效性，所以才会让数据库出现了锁定机制，相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。 MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking) BDB存储引擎采用的是页面锁(page-level locking)，也支持表级锁 InnoDB存储引擎既支持行级锁(row-level locking)，也支持表级锁 我们先来瞅瞅看一下这些锁都是个什么鬼东西! 行级锁(row-level locking) 锁的对象颗粒度很小 各大数据库中锁粒度最小的 锁定资源占用概率最小 虽然说行级锁的优点是很明显，但是相对的弊端也因为他的优点出现了， 因为锁定的粒度比较小，所以每次获取锁和释放锁需要做更多的内容，带来的消耗必然也就更大了， 行级锁也是最容易发生死锁的。 并发度也最高 表级锁(table-level locking) 和行锁相反，粒度是最大的 逻辑简单，对系统的负面影响比较小 获取锁和释放锁速度快 并发度最低。 弊端也有，因为粒度比较大，锁定资源占用概率也会很大， 页级锁(page-level locking) 比较特殊，介于行锁和表锁之间，所以他的能力都是介于两者之间的，就是 粒度比较 表级锁 > 页锁 > 行锁 而他的并发度也就是一般般了。但是他会出现死锁，这三个当中，看来也就只有表锁不会出现死锁了。 我们在这里已经算是对锁机制大致有了个了解，我们再细致的看一下 表锁 表锁实际上分为2种类型， 读锁定 写锁定 而这两种锁定，又是通过其内部的队列来进行维护的， 当前读锁队列 (Current read-lock queue) 挂起的读锁队列(Pending read-lock queue) 挂起写锁队列(Pending write-lock queue) 当前写锁队列(Current write-lock queue) 什么意思呢? 当前读锁的队列，实际上就是包含了当前所有的持有读锁的线程，这里面的内容就是按照获取锁的时间进行有序的排放， 而挂起的读锁队列中存放的则是等待获取所得线程。 那写锁大家肯定也知道是什么意思了，就像是个4*100接力一样。拿着“棒子”的，就是当前读/写锁队列，等着接 “棒子” 的就是 挂起的读/写锁队列。 行锁 MySQL的 InnoDB 存储引擎支持行级锁，InnoDB 的行锁是通过给索引项加锁实现的。 这句话说明了什么? 说明了一件事：只有通过索引条件检索数据时，InnoDB 才使用行锁，否则使用表锁。 是不是感觉很诧异，但是事实上就是这样的。 InnoDB 级别的行锁也是分成了两种 共享锁 独占锁 共享锁和独占锁(Shared and Exclusive Locks)，InnoDB 通过共享锁和独占锁两种方式实现了标准的行锁。共享锁(S 锁)：允许事务获得锁后去读数据，独占锁(X 锁)：允许事务获得锁后去更新或删除数据。一个事务获取的共享锁 S 后，允许其他事务获取 S 锁，此时两个事务都持有共享锁 S，但是不允许其他事务获取 X 锁。如果一个事务获取的独占锁(X)，则不允许其他事务获取 S 或者 X 锁，必须等到该事务释放锁后才可以获取到 很多读者肯定也都了解的很深入，肯定还有其他的，对，还有一种就是意向共享锁和意向独占锁。 这种意向共享锁和意向独占锁的意思就是如果我需要一个共享锁，但是这个共享锁这时候正锁定这资源，那我自己就可以加一个共享锁，只能等这个共享锁释放之后，我才能锁定，这个锁就可以称之为意向共享锁，同理，独占锁也是一样的。 而他们之间的逻辑关系是这个样子的。  数据库锁表的原因 其实最简单的就是会出现在 insert、update、delete 这些操作的并发操作上，当我们使用多个数据库连接的时候，同时对一个表中的数据进行更新的操作的时候，那么速度就会对应的变慢，如果持续一段时间之后，那么就会出现锁表的现象了。 那么都有哪些操作会导致出现锁表呢? (1) 插入查询的语句 insert into table values select xxxx from table2 这种情况就会锁住table2. (2) 更新并发操作 update table1 table2 set table1.name = ‘xxx’ where table1.id = table2.id 这样也会导致锁表。 怎么样降低锁表的情况? MyISAM表锁的优化： 缩短锁定的时间：这么说吧，实际上最简单的就是加索引，让你的索引利用最大化， 合理利用读写优先级：写优先，读其次。 Innodb行锁的优化 加索引，让查询走索引 学会控制事务 发生死锁的必要条件有4个，分别为互斥条件、不可剥夺条件、请求与保持条件和循环等待条件，如图1-6所示。 ▲图1-6 死锁的必要条件 1. 互斥条件 在一段时间内，计算机中的某个资源只能被一个进程占用。此时，如果其他进程请求该资源，则只能等待。 2. 不可剥夺条件 某个进程获得的资源在使用完毕之前，不能被其他进程强行夺走，只能由获得资源的进程主动释放。 3. 请求与保持条件 进程已经获得了至少一个资源，又要请求其他资源，但请求的资源已经被其他进程占有，此时请求的进程就会被阻塞，并且不会释放自己已获得的资源。 4. 循环等待条件 系统中的进程之间相互等待，同时各自占用的资源又会被下一个进程所请求。例如有进程A、进程B和进程C三个进程，进程A请求的资源被进程B占用，进程B请求的资源被进程C占用，进程C请求的资源被进程A占用，于是形成了循环等待条件，如图1-7所示。 需要注意的是，只有4个必要条件都满足时，才会发生死锁。 处理死锁有4种方法，分别为预防死锁、避免死锁、检测死锁和解除死锁，如图1-8所示。 预防死锁：处理死锁最直接的方法就是破坏造成死锁的4个必要条件中的一个或多个，以防止死锁的发生。 避免死锁：在系统资源的分配过程中，使用某种策略或者方法防止系统进入不安全状态，从而避免死锁的发生。 检测死锁：这种方法允许系统在运行过程中发生死锁，但是能够检测死锁的发生，并采取适当的措施清除死锁。 解除死锁：当检测出死锁后，采用适当的策略和方法将进程从死锁状态解脱出来。 在实际工作中，通常采用有序资源分配法和银行家算法这两种方式来避免死锁，大家可自行了解。 2 MySQL中的死锁问题 在MySQL 5.5.5及以上版本中，MySQL的默认存储引擎是InnoDB。该存储引擎使用的是行级锁，在某种情况下会产生死锁问题，所以InnoDB存储引擎采用了一种叫作等待图(wait-for graph)的方法来自动检测死锁，如果发现死锁，就会自动回滚一个事务。 接下来，我们看一个MySQL中的死锁案例。 第一步：打开终端A，登录MySQL，将事务隔离级别设置为可重复读，开启事务后为account数据表中id为1的数据添加排他锁，如下所示。 mysql> set session transaction isolation level repeatable read; Query OK, 0 rows affected (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from account where id =1 for update; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 300 | +----+--------+---------+ 1 row in set (0.00 sec) 第二步：打开终端B，登录MySQL，将事务隔离级别设置为可重复读，开启事务后为account数据表中id为2的数据添加排他锁，如下所示。 mysql> set session transaction isolation level repeatable read; Query OK, 0 rows affected (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from account where id =2 for update; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 2 | 李四 | 350 | +----+--------+---------+ 1 row in set (0.00 sec) 第三步：在终端A为account数据表中id为2的数据添加排他锁，如下所示。 mysql> select * from account where id =2 for update; 此时，线程会一直卡住，因为在等待终端B中id为2的数据释放排他锁。 第四步：在终端B中为account数据表中id为1的数据添加排他锁，如下所示。 mysql> select * from account where id =1 for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction 此时发生了死锁。通过如下命令可以查看死锁的日志信息。 show engine innodb statusG 通过命令行查看LATEST DETECTED DEADLOCK选项相关的信息，可以发现死锁的相关信息，或者通过配置innodb_print_all_deadlocks(MySQL 5.6.2版本开始提供)参数为ON，将死锁相关信息打印到MySQL错误日志中。 在MySQL中，通常通过以下几种方式来避免死锁。 尽量让数据表中的数据检索都通过索引来完成，避免无效索引导致行锁升级为表锁。 合理设计索引，尽量缩小锁的范围。 尽量减少查询条件的范围，尽量避免间隙锁或缩小间隙锁的范围。 尽量控制事务的大小，减少一次事务锁定的资源数量，缩短锁定资源的时间。 如果一条SQL语句涉及事务加锁操作，则尽量将其放在整个事务的最后执行。 尽可能使用低级别的事务隔离机制。 关于作者：肖宇，分布式事务架构专家，Apache ShenYu(incubating)网关创始人，Dromara开源组织创始人，Hmily、RainCat、Myth等分布式事务框架的作者。Apache ShardingSphere Committer。 冰河，互联网高级技术专家、MySQL技术专家、分布式事务架构专家。多年来，一直致力于分布式系统架构、微服务、分布式数据库、分布式事务与大数据技术的研究，在高并发、高可用、高可扩展性、高可维护性和大数据等领域拥有丰富的架构经验。 （编辑：许昌站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
