这有一把钥匙，打开MySQL死锁问题！

2020-08-13 15:11:29 浏览数 (1)

今天继续来讲面试，已经出了将近十个美团java一面真题系列文章了，今天来讲一讲mysql死锁发生的原因和解决，相信大多数小伙伴对mysql不陌生甚至经常在使用。但是可能对mysql锁这方面还不太熟悉，但是经常面试的时候却经常会被问到，我们根据面经来进行补短板，查漏补缺。下面开始今天的干货内容吧，走起

问题的引入

假如，A准备给B转钱，而B在同时也在给A转钱。

我的理解是，在数据库方面，就同时有两个事务，第一个，是A操作带来的，他锁定自己的行信息，修改余额，然后准备去锁定B的行信息；第二个，是B带来的，B先锁定自己的行信息，修改余额，然后准备去锁定A的行信息。

由于同时发生的，那么他们就死锁了。

问题是：

1，场景如第一行，是同时互相转钱，我这样的后台数据库操作逻辑对不对？

2，如果是对的，死锁是否会发生？如果是不对的，该如何设计后台实现？

3，如果死锁会发生，该如何避免，mysql的事务自动会处理（有人说会自动处理），那我能不能代码的设计上，避免这种死锁的情况？

1. 这种情况是有可能导致死锁的，A在等待B释放资源，B在等待A释放资源，相互等待资源，造成死锁。如果出现死锁会报ERROR，可在日志里查询到，已经出现死锁的情况，mysql会自动检测到了两个会话互相等待锁的情况，然后把最后一个会话去做回滚操作。

2. 针对这个问题避免死锁的方法：

① 设置锁优先级：提前设置优先级，如果运行A和B出现死锁，优先级低的回滚，优先级高的先执行，这样即可解决死锁问题。

② 以固定顺序访问：设定一个顺序，比如先A后B，或者先B后A，保证不管在什么时候都尊重这个顺序（通常是按ID大小的顺序），这样就会减少死锁发生的概率了。

③ 设置锁超时时间set lock_timeout：尝试获取锁的时候加一个锁超时时间，超过这个时间放弃对该锁请求。比如设置A的超时时间为10毫秒，B为100毫秒，A试了10毫秒以后获取不到资源，然后会自动断开，A断开了，这时B就可以获取资源了，避免了死锁。（但是这个方法不太好的地方在于，还需要对A再提交一次，而且timeout时间需要综合很多其他因素去设置）

④ 对所使用的数据全部加锁：每一个事务一次就将所有要使用到的数据全部加锁，否则就不允许执行，比如A在给B转钱的时候，会使用到A账户转账前，A相互转账后，B账户转账前，B账户转账后，所以就算是A给B转账，也要把A、B账户所有信息都一起加锁（这样B想给A转账也不行，因为被锁住了，不过这个还是傻，效率很低，可能又会带来其他死锁问题） 以上是能想到的解决你提出的这个场景的几种方法，关于避免死锁的方法太多了，还有其他建立索引， 设置事务隔离级别，编写应用程序让进程持有锁的时间尽可能短，等等。

下面来系统学习学习mysql事务及锁机制

1. 事务的引入

1. 银行转账业务

A向B转账1000，A-1000，B 1000

2. 网上购买商品业务

2. 事务的基本概念

数据库事务是构成单一逻辑工作单元的操作集合

注意点：

1. 数据库事务可以包含一个或多个数据库操作，但这些操作构成一个逻辑上的整体

2. 构成逻辑整体的这些数据库操作,要么全部执行成功，要么全部不执行

3. 构成事务的所有操作，要么全都对数据库产生影响,要么全都不产生影响,即不管事务是否执行成功，数据库总能保持一致性状态

4. 并发操作下，事务的控制尤为关键

引入事务时如何解决问题呢?

3. 事务的四大特性

数据库事务 transanction 正确执行的四个基本要素。ACID,原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。

（1）原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

（2）一致性：事务的执行结果必须使数据库从一个一 致性状态到另一 个一致性状态。一致性状态是指:1.系统的状态满足数据的完整性约束(主码，参照完整性,check约束等) 2.系统的状态反应数据库本应描述的现实世界的真实状态，比如转账前后两个账户的金额总和应该保持不变。

（3）隔离性:并发执行的事务不会相互影响,其对数据库的影响和它们串行执行时一样。比如多个用户同时往一个账户转账，最后账户的结果应该和他们按先后次序转账的结果一样。

（4）持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

在事务的四个特点中，一致性是事务的根本追求，而在某些情况下会又会对一致性造成破坏：

  • 事务的并发执行
  • 事务故障或系统故障

数据库系统通过并发控制技术和旦志恢复技术来避免这种情况的发生

  • 并发控制技术保证了事务的隔离性，使数据库的一致性状态不会因为并发执行的操作被破坏。
  • 日志恢复技术保证了事务的原子性,使一致性状态不会因事务或系统故障被破坏。同时使已提交的对数据库的修改不会因系统崩溃而丢失,保证了事务的持久性。

4. 事务的实现原理

  • 事务的原子性是通过undo log来实现的
  • 事务的持久性是通过redo log来实现的
  • 事务的隔离性是通过(读写锁 MVCC)来实现的
  • 事务的一致性是通过原子性，持久性，隔离性来实现的!!!

4.1 原子性实现原理：Undo Log

Undo Log是为了实现事务的原子性，在MySQL数据库InnoDB存储引擎中，还用Undo Log来实现多版本并发控制(简称: MVCC)

在操作任何数据之前，首先将数据备份到一个地方(这个存储数据备份的地万称为Undo Log),然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态

注意: undo log是逻辑日志，可以理解为:

  • 当delete一条记录时，undo log中 会记录一条对应的Insert记录
  • 当insert一条记录时，undo log中会记录一条 对应的delete记录
  • 当update一条记录时，它记录一条 对应相反的update记录

4.2. 持久性实现原理：Redo Log

和Undo Log相反，Redo Log记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是RedoLog已经持久化。系统可以根据Redo Log的内容，将所有数据恢复到最新的状态。

5. Mysql的隔离级别(解决办法)

事务具有隔离性理论上来说事务之间的执行不应该相互产生影响，其对数据库的影响应该和它们串行执行时一样。

然而完全的隔离性会导致系统并发性能很低降低对资源的利用率,因而实际上对隔离

性的要求会有所放宽，这也会一定程度造成对数据库一致性 要求降低

SQL标准为事务定义了不同的隔离级别，从低到高依次是

  • 读未提交(READ UNCOMMITTED):对事务处理的读取没有任何限制，不推荐
  • 读已提交(READ COMMITTED) ;
  • 可重复读(REPEATABLE READ）
  • 串行化(SERIALIZABLE)

不同的隔离级别可能导致不同的并发异常，如下图:

事务的隔离级别

脏读

不可重复读

幻读

读未提交(READ UNCOMMITTED)

读已提交(READ COMMITTED)

可重复读(REPEATABLE READ）

串行化(SERIALIZABLE)

6. 隔离性的实现原理：锁

在mysql中，锁可以分为两类:

  • 共享锁: 共享锁定是将对象数据变为只读形式，不能进行更新，所以也成为读取锁定;
  • 排他锁: 排他锁定是当执行INSERT/UPDATE/DELEE的时候，其它事务不能读取该数据，因此也成为写入锁定。

锁的粒度:锁定对象的大小是锁的粒度：

  • 记录
  • 表
  • 数据库

可能出现的问题:

死锁:多个事务持有锁并互相循环等待其他事务的锁导致所有事务都无法继续执行

扩展:除了锁可以实现并发控制之外，还有其他策略:

  • 基于时间戳的并发控制
  • 基于有效性检查的并发控制
  • 基于快照隔离的并发控制

6.1 锁的分类

  • 共享锁Shared Locks (简称S锁， 属于行锁)
  • 排他锁Exclusive Locks (简称X锁，属于行锁)
  • 意向共享锁Intention Shared Locks (简称IS锁， 属于表锁)
  • 意向排他锁Intention Exclusive Locks (简称 IX锁,属于表锁)
  • 自增锁AUTO-INC Locks

6.1.1 共享锁

共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据库,但是只能读不能修改;

事务A:

代码语言：javascript复制
select * from student where id= 1 lock in share mode;

事务B:

代码语言：javascript复制
select * from student where id = 1;(读取数据没问题)

事务B:

代码语言：javascript复制
update student set name='hehe' whereid =1;

事务B: 无法修改会卡死，当事务A提交事务之后，会立刻修改成功

6.1.2 排它锁

排它锁不能与其他锁并存，如一个事务获取了一个数据行的排它锁,其他事务就不能再获取改行的锁，只有当前获取了排它锁的事务可以对数据进行读取和修改。

delete、update、 insert默认是排他锁

事务A:

代码语言：javascript复制
select * from student where id= 1 for update;

事务B:

代码语言：javascript复制
select * from student where id= 1 for update;
select * from student where id= 1 lock in share mode;

注意:事务B操作的时候回卡死，提交事务立马成功。

6.1.3 向共享锁和意向排他锁

意向共享锁: 表示事务准备给数据行加入共享锁，也就是说一个数据行在加共享锁之前必须先取得该表的IS锁

意向排他锁: 表示事务准备给数据行加入排它锁，也就是说一一个数据行加排它锁之前必须先取得该表的IX锁。

意向锁是InnoDB数据操作之前自动加的，不需要用户干预

6.1.4 自增锁

针对自增列自增长的一个特殊的表级别锁

代码语言：javascript复制
SHOW VARIABLES LIKE ' innodb_ _autoinc_ .lock_ mode';
-.-
默认值1代表连续，事务未提交则id永久丢失

7. 故障及故障恢复

事务的执行流程如下:

  • 系统会为每个事务开辟一个私有工作区
  • 事务读操作将从磁盘中拷贝数据项到工作区中，在执行写操作前所有的更新都作用于工作区中的拷贝
  • 事务的写操作将把数据输出到内存的缓冲区中,等到合适的时间再由缓冲区管理器将数据写入到磁盘

故障

由于数据库存在立即修改和延迟修改，所以在事务执行过程中可能存在以下情况:

  • 在事务提交前出现故障,但是事务对数据库的部分修改经写入磁盘数据库中。这导致了事务的原子性被破坏。
  • 在系统崩溃前事务已经提交,但数据还在内存缓冲区中，没有写入磁盘。系统恢复时将丢失此次已提交的修改。这是对事务持久性的破坏。

故障恢复

  • 撤销事务undo:将事务更新的所有数据项恢复为日志中的旧值
  • 重做事务redo:将事务更新的所有数据项恢复为日志中的新值。
  • 事务正常回滚/因事务故障中止将进行redo
  • 系统从崩溃中恢复时将先进行redo再进行undo。

END

云数据库SQLServer 数据库 数据备份 sql

0 人点赞