vlambda博客
学习文章列表

MySQL(十三):小一万字+14张图读懂锁机制

「尺有所短,寸有所长;不忘初心,方得始终。」

MySQL中的锁有很多种,各种锁应用在不同的地方。「MySQL依靠锁机制可以让多个事务更新一行数据的时候串行化」。

MySQL中锁总的来说有两种概念:Lock和Latch

  • Latch

    称为闩锁(轻量级的锁),因为Latch要求锁定的时间非常短。其目的是用来保证并发线程操作临界资源的正确性,并且通常没有死锁检测的机制。在InnoDB引擎中,Latch又分为mutex(互斥量)和rwlock(读写锁)。

  • Lock

    「Lock的对象是事务,用来锁定的是数据库中的对象,如表、页、行」。并且一般lock的对象仅在事务commit或rollback后进行释放(不同事务隔离级别释放的时间可能不同)。

一、锁的分类

实际上MySQL的锁在不同的维度上划分是多种多样的,在特地的场景下,发挥不一样的作用,下面来看看锁的分类。

  • 「锁定的粒度(加锁的范围)划分」

    • 「全局锁」

      「对整个数据库加锁」。

      应用场景是「做全库的逻辑备份」。

    • 「表锁」

      「每次操作锁住整张表」。锁定粒度大,发生锁冲突的概率最高,并发度最低。

      应用在MyISAM、InnoDB、BDB 等存储引擎中。

    • 「页锁」

      「每次锁定相邻的一组记录」,锁定粒度、开销和加锁时间都界于表锁和行锁之间,并发度一般。

      应用在BDB 存储引擎中

    • 「行锁」

      每次操作锁住一行数据。锁定粒度最小,发生锁冲突的概率最低,并发度最高。应用在InnoDB 存储引擎中。

  • 「操作类型划分」

    • 「读锁(S锁)」

      共享锁,针对同一份数据,多个读操作可以同时进行而不会互相影响。

    • 「写锁(X锁)」

      排他锁,当前写操作没有完成前,它会阻断其他写锁和读锁。

    • 「意向锁」

      InnoDB支持多粒度的锁,允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作,InnoDB存储引擎支持 一种特有锁「意向锁」。分为意向读锁(IS锁)、意向写锁(IX锁)。

  • 「锁的机制上划分」

    • 「乐观锁」

      操作数据时不会对操作的数据进行加锁,只是对记录的版本进行比对,在数据更新提交的时候才会进行冲突检测,如果发现冲突了,则提示错误信息。

    • 「悲观锁」

      在对一条数据修改的时候,为了避免同时被其他事物修改,在修改数据之前先锁定,再修改数据的方式。共享锁和排他锁是悲观锁的不同实现。

MySQL(十三):小一万字+14张图读懂锁机制

二、全局锁

2.1 什么是全局锁

「全局锁,即对整个数据库实例加锁」。一般当我们需要让整个库处于只读状态的时候,可以给数据库加上全局锁。「加上全局锁之后其他线程的:数据更新语句(增删改)、数据定义语句(包括建表、修改表结构等)都会被阻塞」。

  • 「加锁方式」

    MySQL提供了一个加全局读锁的方法,命令

    Flush tables with read lock (FTWRL)
  • 「全局锁应用场景」

    「做全库逻辑备份」。即把整库每个表都select出来保存成文本。

「通过FTWRL确保不会有其他线程对数据库做更新,然后对整个库做备份。在备份过程中整个库完全处于【只读状态】」。但是整个库都只能读不能写,会有很大的弊端:

  • 如果在主库上备份。那么在备份期间都不能执行写入操作。
  • 如果在从库上备份。那么在备份期间,从库不能执行主库同步过来的 binlog,从而造成主从延时。

由此可见,做全库逻辑备份的时候加全局锁,对系统的影响非常的大,既然如此,为什么要加全局锁呢?

2.2 为什么要加全局锁

先来看一个案例:

假设现在数据库中现在有两张表:账户余额表,订单表,当我们下一个订单时,会扣减余额,同时在订单表中写入一个订单记录。

下面通过图解来说明对这两张表进行备份的过程,由于备份数据又先后顺序,所以分两种情况来看

  • 「先备份账户余额表,再备份订单表」

    MySQL(十三):小一万字+14张图读懂锁机制

    由图可以看出:先备份账户余额表,再备份订单表导致备份数据中账户余额没扣钱,但是订单有了,商家血亏,消费者乐的不行,这肯定是不允许发生的。

  • 「先备份订单表,再备份账户余额表」

    MySQL(十三):小一万字+14张图读懂锁机制

    由图可以看出:先备份订单表,再备份账户余额表导致备份数据中账户余额扣了钱,但是订单没有了,商家白嫖,消费者肯定不干,这肯定也是不允许发生的。

案例结论:

「通过上述案例说明,不加锁的话,备份系统备份的得到的数据不一致的,其实就是数据一个逻辑时间点的,这个读视图【Read View】是逻辑不一致的」。

2.3 不加全局锁行不行

「通过上述的描述,我们知道在做数据备份的时候,需要加全局锁(FTWRL)来保证数据的一致性,但是由于FTWRL需要关闭所有表对象,数据库禁止写入,执行命令时容易导致数据库hang住」。

Q:既然加全局锁会影响业务,危害大,那做备份的时候有没有不用FTWRL,又能保证数据一致性的方法呢?A:有,方法是由有的,但是有局限性

  • 「不加全局锁怎么保证数据一致性」

    • 在之前文章的3.3可重复读(RR)MVCC实现原理一节中有提到,「可重复读隔离级别下【同一个事务里面,多次查询,都只会产生一个共用Read View】」,「因此我们将事物的隔离级别调整为可重复读(RR)时是可以得到一致性视图的,而一致性视图通过MVCC能够确保数据的逻辑一致性」。

    • 「MySQL官方提供了一个逻辑备份工具是【mysqldump】。当mysqldump使用参数 -single-transaction 时,备份数据之前会启动一个事务,在这个事务内生成一致性视图。基于MVCC,备份过程中数据是可以正常更新」。

      --single-transaction:设置事务的隔离级别为可重复读(REPEATABLE READ)

  • 「一致性视图局限性」

    「一致性视图实现的前提是事物的隔离级别是可重复读(RR),但是不是所有的引擎都支持事物的,如果使用的MyISAM引擎,一致性视图就无法使用,只能FTWRL命令」。

    MyISAM引擎不支持事务,总是能够拿到最新的数据

2.4数据库只读能否替换FTWRL

「FTWRL的本质就是给数据库加一个锁,禁止其他线程写入,也就是将数据库设置成了只读状态」。数据库设置为只读状态其实还有一种更简单的方式,直接使用以下命令即可:

set global readonly=true

但是一般不推荐使用readonly,两者比较,还是推荐使用FTWRL

  • 「readonly的值有时候会被用来做其他逻辑判断,修改global变量影响更大」

    判断一个库是主库还是备库

  • 「当备份过程中出现异常时:」

    • FTWRL命令会释放全局锁,数据库可以被其他线程正常读写
    • 设置为readonly时,由于时全局变量,数据库就会一直保持readonly状态,直到改变readonly的值

三、表锁

MySQL里面表级别的锁有两种:一种是表锁,一种是元数据锁(metadata lock,MDL)。

3.1表锁

「表级别的锁定是MySQL各【存储引擎中】最大颗粒度的锁定机制」。由于直接锁定一张表,所以获取锁和释放锁的速度很快,避免了死锁问题,但是出现锁定资源争用的概率也最高,并发量降低。

  • 「表锁的加锁语法」

    #隐式上锁(默认,自动加锁自动释放
    insert、update、delete //上写锁
    #显式上锁(手动)
    lock table tableName read;//读锁
    lock table tableName write;//写锁
  • 「表锁的释放锁语法」

    UNLOCK TABLES

    客户端断开的时候也会自动释放锁。

  • 「查看表上加过的锁」

    show open tables;

「MyISAM引擎默认的锁是表锁」。表锁一般是在数据库引擎不支持行锁的时候才会被用到的。

  • 「表级读锁」

    当前表加read锁,当前连接和其他的连接都可以读操作;但是当前连接写操作会报错,其他连接写操作会被阻塞。

  • 「表级写锁」

    当前表加write锁,当前连接可以对表做读写操作,其他连接对该表所有操作(读写操作)都被阻塞。

「表级读锁会阻塞写操作,但是不会阻塞读操作。而写锁则会把读和写操作都阻塞」。

3.2元数据锁(metadata lock,MDL)

当我们查询查询一个表中的数据时,另一个线程对这个表结构做变更,删了一列,那么查询线程拿到的结果跟表结构就不一致了,这肯定是允许。这里就用到了元数据锁

在MySQL 5.5版本中引入了MDL,「元数据锁(MDL) 不需要显式使用,在访问一个表的时候会被自动加上」。

  • 「当对一个表做增删改查的时候会加上【MDL读锁】」

    读锁之间不互斥,因此可以有多个线程同时对一张表增删改查操作。

  • 「当对一个表做结构变更的时候会加上【MDL写锁】」

    读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。

四、页锁

  • 页级锁定是「MySQL中比较独特」的一种锁定级别,在其他数据库管理软件中并不常见。

  • 页级锁定和行级锁定一样,会发生死锁

  • 使用页级锁定的主要是BerkeleyDB存储引擎。

  • 开销和加锁时间界于表锁和行锁之间;

  • 锁定粒度界于表锁和行锁之间,并发度一般。

页锁应用于 BDB 引擎,一般很少见,了解一下即可,重点理解下行锁

五、行锁

5.1行锁是什么

「行锁顾名思义就是对数据行进行加锁。行锁的锁定颗粒度在 MySQL中是最细的,应用于 InnoDB 存储引擎,通过对索引数据页上的记录加锁实现的【即行锁是针对索引加锁】」。

  • 「行锁的优缺点」

    并发情况下,产生锁等待的概率较低,支持较大的并发数,但开销大,加锁慢,而且会出现死锁。

  • 「行锁的前提条件」

    「检索数据时需要通过索引,【因为 InnoDB 是通过给索引的索引项加锁来实现行锁的】」。

    • 在不通过索引条件查询的时候,InnoDB 会使用表锁,表锁会产生锁冲突
    • 「行锁是针对索引加锁」,所以即使访问的不同记录,只要使用的是同一索引项,也可能会出现锁冲突。

MySQL会比较不同执行计划,当全表扫描比索引效率更高时,InnoDB就使用表锁。因此不一定使用了索引就一定会使用行锁,也有可能使用表锁。

  • 「行锁会产生死锁」

    在我之前文章中的回表查询有提到,当我们走辅助索引的时候,会扫两遍索引树,如下:

    MySQL(十三):小一万字+14张图读懂锁机制

    「实际上InnoDB 的行锁也是分为两步获得的:锁住主键索引,锁住非主键索引」。

    当两个事务同时执行时,

    一个锁住了主键索引,在等待其他索引;

    另一个锁住了非主键索引,在等待主键索引,

    这样就可能会发生死锁。

    「InnoDB可以检测到这种死锁,检测到后会让其中一个事务释放锁回退,另一个获取锁完成事务」。

5.2行锁的实现算法

前面讲到「InnoDB行锁是通过对 索引数据页上的记录加锁实现的」,接下来看看它具体是怎么实现,

InnoDB存储引擎有3种实现行锁的算法:

  • 「【Record Lock】:记录锁,单个行记录上的锁」

    RC、RR隔离级别都支持,如果表中没有主键和任何一个索引,那InnoDB会使用隐式的主键来进行锁定。

  • 「【Gap Lock】:间隙锁,锁定一个范围,但不包含记录本身」

    范围锁,锁定索引记录范围,确保索引记录的间隙不变,RR隔离级别支持

  • 「【Next-Key Lock】:Gap Lock与Record Lock的组合」

    锁定数据前后范围,并且锁定记录本身,RR隔离级别支持

「在RR隔离级别,InnoDB对于行的查询都是采用【Next-Key Lock】的组合锁定算法」,但是「在查询的列是唯一索引(包含主键索引)的情况下,Next-key Lock会降级为Record Lock,仅锁住索引本身而非范围」。

下面具体看下针对不同的sql语句采用的是那种加锁方式:

  • 查询语句类型一

    select ... from  ...

    「对于普通的select语句,InnoDB引擎采用MVCC机制实现非阻塞读,【InnoDB引擎不加锁】」。

  • 查询语句类型二

    select ... from ... lock in share mode

    「添加共享锁,InnoDB会使用Next-Key Lock锁进行处理,扫描如果有唯一索引,则降级为RecordLock锁」。

  • 查询语句类型三

    select ... from ... for update

    「添加排他锁,InnoDB会使用Next-Key Lock锁进行处理,扫描如果有唯一索引,则降级为RecordLock锁」。

  • 修改语句

    update ... from ... where  ...

    「InnoDB会使用Next-Key Lock锁进行处理,扫描如果有唯一索引,则降级为RecordLock锁」。

  • 删除语句

    delete ... from ... where

    「InnoDB会使用Next-Key Lock锁进行处理,扫描如果有唯一索引,则降级为RecordLock锁」。

  • 插入语句

    insert ... from  ...

    「InnoDB会在将要插入的那一行设置一个排他的RecordLock锁」。

六、读锁/写锁/意向锁

在前文中提到的锁类型按照「操作类型划分」有「读锁(S锁),写锁(X锁)」,其实它们与共享锁,排他锁是一个意思,只是不同叫法而已。

6.1共享锁(行级锁-读锁/S锁)

「共享锁(Shared Lock)又称为读锁,简称S锁,是一种行级锁」。

顾名思义:「共享锁就是多个事务对于同一数据共享一把锁,都能访问到数据,但是只能读不能修改」。

  • 「加锁方式」

    select ... from ... lock in share mode
  • 「释放方式」:

    commit;
    rollback;
  • 「共享锁工作原理」

    「一个事务获取了一条记录的共享锁后,其他事务也能获得该记录对应的共享锁,但不能获得排他锁」。即一个事务使用了共享锁(读锁),其他事务只能读取,不能写入,写操作被阻塞。

6.2排他锁(行级锁-写锁/X锁)

「排他锁(EXclusive Lock)又称为写锁,简称X锁,是一种行锁也可以是表锁」。

顾名思义:「排他锁就是不能与其他锁并存,即当前写操作没有完成前,会阻断其他写锁和读锁」。

  • 「加锁方式」

    innodb引擎默认会在update,delete语句加上 for update

    SELECT * FROM student FOR UPDATE; # 排他锁
    INSERT INTO student values ... # 排他锁
    DELETE FROM student WHERE ... # 排他锁
    UPDATE student SET ... # 排他锁
  • 「释放方式」:

    commit;
    rollback;
  • 「共享锁工作原理」

    「如一个事务获取了一条记录的排他锁,其他事务就不能对该行记录做其他操作,也不能获取该行的锁(共享锁、排他锁),但是获取到排他锁的事务可以对数据进行读写操作」。

    这里要注意一下,其他事务不加锁的读是不会被阻塞的,阻塞的是加锁的读

  • 「排他锁为什么是一种行锁也是表锁」

    innodb引擎默认会在update,delete语句加上 for update

    「读锁,写锁都属于行级锁,行级锁的实现是依靠其对应的索引,如果没用到索引的查询,就会走表锁」。

    • 有索引:以索引列为条件更新数据,会存在间隙锁,行锁,页锁,而锁住一部分行。
    • 没有索引:更新数据时会锁住整张表。
    • 「可重复读隔离级别下」

    • 「串行化隔离级别下」

      读写数据都会锁住整张表

6.3意向锁(表锁)

6.1 意向锁是什么

「意向锁(Intention Lock)简称I锁,是一种表级锁」。

「InnoDB 实现了标准的行级锁,包括:共享锁(S锁)、排它锁(X锁)」,那么为什么需要引入意向锁呢?意向锁解决了什么问题?

假设,事务A获取了某一行记录的排它锁,事物A尚未提交,事务B想要获取表锁时,则事物B必须要确认表的每一行都不存在排他锁,需要进行全表扫描,效率很低,此时就引入意向锁

  • 如果事务A获取了某一行记录的排它锁,实际此时表存在两种锁,行记录的排他锁和表上的意向排他锁。
  • 如果事务B试图在该表加表级锁时,则会被意向锁阻塞,因此事物B不必检查各个页锁或行锁,而只需检查表上的意向即可。
MySQL(十三):小一万字+14张图读懂锁机制

如上,数据库中存储数据,范围由大到小:表-->页-->行,加锁也是分别加在表-->页-->行中,当我们把锁加在更大一级范围时,也就不需要全表扫描下一级的某些锁,可以很大程度提升性能。

「锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作,InnoDB存储引擎支持一种额外的锁方式,即意向锁」

通过上述描述我们知道「意向锁是加在表上,用于防止全表扫描的一种锁,即意向锁是表锁」。意向锁分为两种类型:

  • 「意向共享锁(intention shared lock)」简称IS锁,事务想要给某一个数据行加行级共享锁(S锁)之前必须先获取该表的IS锁(表级锁)
  • 「意向排他锁(intention exclusive lock)」简称IX锁,事务想要给某一个数据行加行级排他锁(X锁)之前必须先获取该表的IX锁(表级锁)

「【意向锁都是InnoDB存储引擎自己维护的,用户是无法操作意向锁的】」。

「在为数据行加共享锁/排他锁之前,InooDB会先获取该数据行所在在数据表的对应意向锁(表级锁)」】,如果没有获取到,否则等待innodb_lock_wait_timeout超时后根据innodb_rollback_on_timeout决定是否回滚事务。

从锁粒度角度:InnoDB 允许行级锁与表级锁共存,而意向锁是表锁;

从锁模式角度:意向锁是一种独立类型,辅助解决记录锁效率不及的问题;

从兼容性角度:意向锁包含了共享/排他两种。

6.2 意向锁的兼容互斥性

  • 意向锁之间的兼容互斥性:意向锁之间是互相兼容的

意向共享锁(IS) 意向排他锁(IX)
意向共享锁(IS) 兼容 兼容
意向排他锁(IX) 兼容 兼容
  • 意向锁与其他锁兼容互斥性:意向锁与普通的排他锁/共享锁互斥

意向共享锁(IS) 意向排他锁(IX)
表级共享锁(S) 兼容 互斥
表级排他锁(X) 互斥 互斥

「上述的排他锁(X锁)共享锁(S锁)指的都是表锁,意向锁不会与行级的共享锁/排他锁互斥」

七、乐观锁/悲观锁

「乐观锁/悲观锁其实都是概念上的,只是在并发下防止数据被修改的一种加锁形式」。

7.1 悲观锁(Pessimistic Locking)

「对数据的修改抱有悲观态度的一种并发控制方式,悲观的认为自己(当前线程)拿到的数据是被修改过的,所以在操作数据之前先加锁」。

  • 「悲观锁的形式(类型)」

    「数据库的行锁、表锁、读锁、写锁、共享锁、排他锁等,以及syncronized 实现的锁都是悲观锁的范畴」。

MySQL(十三):小一万字+14张图读懂锁机制
  • 「优点」

    「可以保证数据的独占性和正确性」。

  • 「缺点」

    「每次请求都需要加锁、释放锁,这个过程会降低系统性能」。

7.2 乐观锁

「乐观锁是对于数据冲突保持一种乐观态度,每次读取数据的时都认为其他线程不会修改数据,所以不上锁,只是在数据修改后提交时才通过【版本号机制或者CAS算法】来验证数据是否被其他线程更新」。

因为乐观锁中并没有【加锁和解锁】操作,因此乐观锁策略也被称为「无锁编程」。

  • 「乐观锁实现的关键点」:检测冲突

  • 「乐观锁实现方式」

    • 版本号机制(常用)
    • CAS算法实现
  • 「优点」

    「没有加锁和解锁操作,可以提高吞吐量」

  • 「缺点」

    乐观锁需要自己实现,且外部系统不受控制

  • 「乐观锁的应用」

    在Java中java.util.concurrent.atomic包下面的原子变量类就是使用了乐观锁的一种实现方式CAS。

  • 「适用场景」:读多写少

  • 「注意」

    乐观锁不是数据库提供的功能,需要开发者自己去实现。

    除了开发者自己手动实现乐观锁之外,很多数据库访问框架也封装了乐观锁的实现

    比如 hibernate框架,MyBatis框架的OptimisticLocker插件。

7.2.1版本号机制实现乐观锁

版本号机制有两种方式:使用版本字段(version)和使用时间戳(Timestamp),两者实现原理是一样的。

前文中提到「乐观锁需要开发者自己去实现,所以版本号实现时通过在表中加字段的形式实现的」。

  • 「使用版本字段(version)」

    「在数据表增加一个版本(version) 字段,每操作一次,将那条记录的版本号加 1」。version 是用来查看被读的记录有无变化,防止记录在业务处理期间被其他事务修改。

  • 「使用时间戳(Timestamp)」

    与使用version版本字段基本一致,「同样需要给在数据表增加一个字段,字段类型使用timestamp时间戳,通过时间戳比较数据版本」。

MySQL(十三):小一万字+14张图读懂锁机制
  • 「乐观锁实现案例」

    修改用户表中Id为1的用户姓名

    • 第一步:查询记录信息

      #使用版本字段(version)
      select name,version from user where id=1;
      #使用时间戳(Timestamp)
      select name,timestamp from user where id=1;
    • 第二步:逻辑处理之后,修改姓名为张三

      #使用版本字段(version)
      update user set name = '张三',version=version+1 where id=1 and version = #{version};#version 为第一步查询的值

      #使用时间戳(timestamp)
      update user set name = '张三',timestamp=now() where id=1 and timestamp = #{timestamp};

7.2.2CAS算法实现乐观锁

「CAS算法即compare and swap(比较与交换),是一种有名的无锁算法。即不使用锁的情况下实现多线程之间的变量同步,也就是无锁编程」。

  • 「特点」

    不加锁,即使没有线程被阻塞的情况下实现变量的同步,也叫非阻塞同步

  • CAS算法涉及到三个操作数

    「当且仅当V的值等于A时,CAS通过原子方式用新值B来更新V的值,否则不会执行任何操作」(比较和替换是一个原子操作),一般情况下是一个自旋操作,即不断的重试。

    MySQL(十三):小一万字+14张图读懂锁机制
    • 变量当前内存值 V
    • 旧的预期值 A
    • 要写入的新值 B
  • 「CAS缺点」

    • 「ABA问题」

      当线程1读到某变量的值为A,在其逻辑处理的过程中,另外一个线程2将该变量的值从A先修改为B、然后又将其从B修改回A。此时,当线程1通过CAS操作进行新值写入虽然可以成功,而实际上线程1执行CAS操作时预期值的A 和读取该变量当前值的A已经不是同一个了,后者是线程2修改的

    • 「CPU开销大」

      虽然CAS算法是非阻塞的,但如果CAS操作一直不成功不断循环,会浪费CPU资源

    • 「只能保证一个共享变量的原子性」

      当对多个变量进行操作时,CAS算法无法保证原子性。

      可以将多个变量封装为一个对象再使用CAS算法(Java中的AtomicReference)

八、死锁和死锁检测

8.1 死锁是什么

「死锁是指两个或两个以上的事务在执行过程等中,因争夺资源而造成的一种相互等待的现象」。

  • 「死锁产生本质原因」

    • 系统资源有限
    • 进程推进顺序不合理
  • 「死锁产生的4个必要条件」

    • 「互斥条件(Mutual exclusion,简称Mutex)」资源要么被一个线程占用,要么是可用状态
    • 「不可抢夺(No preemption)」资源被占用后,除非占有线程主动释放,其他线程不能把它从该线程占用中抢夺
    • 「占有和等待(Hold and wait)」一个进程必须占有至少一个资源,并等待另一资源,而该资源被其他进程占用
    • 「循环等待(Circular wait)」一组等待进程{P0, P1…Pn-1, Pn},P0等待资源被P1占有,P1等待资源被P2占有,Pn-1等待资源被Pn占有,Pn等待资源被P0占有,循环等待,则形成环形结构。

「死锁发生的以上四个条件缺一都无法导致死锁,而由于互斥条件是非共享资源所必须的,不仅不能改变,还应加以保证,所以恢复死锁主要是破坏产生死锁的其他三个条件」。

8.2 常见死锁现象和解决方案

8.2.1表级锁死锁

  • 「案例」

    有线程A、B分别需要访问用户表与订单表,访问表的时候都会加表级锁。线程A访用户表,并对用户表加锁(线程A锁住了用户表),然后又访问订单表;此时线程B先访问订单表,并对订单表加锁(线程B锁住了订单表),然后线程想访问用户表。

  • 「产生原因」

    上述案例由于线程B已经锁住订单表,线程A必须等待线程B释放订单表能继续,同样线程B要等线程A释放用户表才能继续,「线程A、B相互等待对方释放锁,就产生了死锁」。

    MySQL(十三):小一万字+14张图读懂锁机制
  • 「解决方案」

    「这种死锁是由于程序的BUG产生的,比较常见,只能通过调整程序的逻辑来解决」。

    对于数据库的多表操作时,尽量按照相同的顺序进行处理,避免同时锁定两个资源,

    如操作A和B两张表时,总是按先A后B的顺序处理, 必须同时锁定两个资源时,要保证在任何时刻都应该按照相同的顺序来锁定资源。

8.2.2行级锁死锁

行级锁产生死锁有两种情况,一直是资源争夺,一种是行级锁升级为表级锁

  • 资源争夺

    • 「产生原因」

      当事务中某个查询没有走索引时,就会走全表扫描,把行级锁上升为全表记录锁定(等价于表级锁),并发下多个线程同时执行,就可能会产生死锁和阻塞

    • 「解决方案」

      SQL语句中尽量不要有太复杂的多表关联查询,并通过执行对SQL语句进行分析,建立索引优化,避免全表扫描和全表锁定。

  • 行级锁升级为表级锁


    • 「产生原因」

      两个事务分别想拿到对方持有的锁,互相等待,于是产生死锁。

      MySQL(十三):小一万字+14张图读懂锁机制
    • 「解决方案」

      • 在同一个事务中,尽量一次锁定需要的所有资源

      • 将每个资源编号,通过资源编号的线性顺序来预防死锁,当一个进程占有编号为i的资源时,那么它下一次只能申请编号大于i的资源。

    MySQL(十三):小一万字+14张图读懂锁机制

8.2.3共享锁转换为排他锁

  • 「案例」

    事务A有两个操作,首先查询一条纪录M,然后更新纪录M;此时事务B在事物A查询之后更新之前去更新纪录M,此时事物A获取了记录M的共享锁,事物B获取了记录M的排他锁, 事务B的排他锁由于事务A有共享锁,必须等A释放共享锁后才可以获取,事物B只能排队等待。

  • 「产生原因」

    案例中事物B已经进入等待,事物A更新M需要排他锁,而此时事务B已经有一个排他锁请求,并且正在等待事务A释放其共享锁,因此无法给事物A授予排他锁锁请求,事物A也进入排队等待

    注意:这里事物B还没有拿到M的排它锁,只是进入排队等到状态

  • 「解决方案」

    通过「手动实现乐观锁」进行控制,乐观锁的无锁机制可以避免长事务中的数据库加锁开销,增大并发量,提升系统性能。

8.3死锁排查

MySQL提供了几个与锁有关的参数和命令,可以辅助我们优化锁操作,减少死锁发生。

  • 「查看近期死锁日志信息」

    show engine innodb status;
    MySQL(十三):小一万字+14张图读懂锁机制

    通过以上命令查看近期死锁日志信息,然后使用执行计划进行SQL优化

  • 「查看锁状态变量」

    通过以下命令可以检查锁状态变量,从而分析系统中的行锁的争夺情况

    show status like'innodb_row_lock%';
    • Innodb_row_lock_current_waits:当前正在等待锁的数量
    • Innodb_row_lock_time:从系统启动到现在锁定总时间长度
    • Innodb_row_lock_time_avg:每次等待锁的平均时间
    • Innodb_row_lock_time_max:从系统启动到现在等待最长的一次锁的时间
    • Innodb_row_lock_waits:系统启动后到现在总共等待的次数

「如果等待次数高,而且每次等待时间长,则需要对其进行分析优化」。