MySQL锁机制详解

发布于:2023-01-20 ⋅ 阅读:(19) ⋅ 点赞:(0) ⋅ 评论:(0)

最近在复习MySQL高级部分的知识——宋红康老师MySQL教程

锁机制 为实现MySQL 的各个隔离级别提供了保证

MySQL并发事务访问相同记录的情况

读-读情况

读-读情况,即并发事务相继读取相同的记录。读取操作本身不会对记录有任何影响,并不会引起什么问题,所以允许这种情况的发生。

写-写情况

写-写 情况,即并发事务相继对相同的记录做出改动。

会产生脏写问题,mysql中的任何隔离级别都不允许这种问题产生(加锁机制实现,让它们排队执行)。

事务执行前本来没有锁。当另一个事务想对这条记录做改动时,首先会看看内存中有没有与这条记录关联的 锁结构当没有的时候 才会在内存中生成一个 锁结构 与之关联。比如,事务 T1 要对这条记录做改动,就需要生成一个 锁结构 与之关联:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CrZ6s62P-1660305419447)(D:\note\笔记仓库\图片\image-20220711192633239.png)]

is_wating代表的是当前事务的执行状态。要么等待、要么执行

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-txqbxi8I-1660305419451)(D:\note\笔记仓库\图片\image-20220711193732567.png)]

上图所示:事务1执行完后,就会释放掉锁。然后发现T2事务在等等待,就会唤醒T2,将is_waiting改为false。

小结几种说法:

  • 不加锁

    意思就是不需要在内存中生成对应的 锁结构 ,可以直接执行操作。

  • 获取锁成功,或者加锁成功

    意思就是在内存中生成了对应的 锁结构 ,而且锁结构的 is_waiting 属性为 false ,也就是事务 可以继续执行操作。

  • 获取锁失败,或者加锁失败,或者没有获取到锁

    意思就是在内存中生成了对应的 锁结构 ,不过锁结构的 is_waiting 属性为 true ,也就是事务 需要等待,不可以继续执行操作。

读-写或写-读情况

读-写写-读 ,即一个事务进行读取操作,另一个进行改动操作。这种情况下可能发生 脏读 、 不可重 复读 、 幻读 的问题。

各个数据库厂商对 SQL标准 的支持都可能不一样。比如MySQL在 REPEATABLE READ 隔离级别上就已经解决了 幻读 问题(MVCC)。

并发问题的解决方案

怎么解决 脏读 、 不可重复读 、 幻读 这些问题呢?其实有两种可选的解决方案:

  • 方案一:读操作利用MVCC原理,写操作进行加锁(可重复读、读已提交)
    • READ COMMITTED 隔离级别下,每次select操作会生成ReadView,ReadView本身就保证了不能够读取未提交的的事务,所以不能发生脏读
    • REPEATABLE READ 隔离级别下,一个事务在执行过程中只有 第一次执行SELECT操作 才会生成一个ReadView,之后的SELECT操作都 复用 这个ReadView,这样也就避免了不可重复读和幻读的问题。
  • 方案二:读、写操作都采用 加锁 的方式。(读未提交、串行化)

小结对比发现:

  • 采用 MVCC 方式的话, 读-写 操作彼此并不冲突, 性能更高 。
  • 采用 加锁 方式的话, 读-写 操作彼此需要 排队执行 ,影响性能。

锁的不同角度分类

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9eNgGXze-1660305419454)(D:\note\笔记仓库\图片\image-20220812182200467.png)]

从数据操作的类型划分:读锁、写锁

  • 读锁 :也称为 共享锁 、英文用 S 表示。针对同一份数据,多个事务的读操作可以同时进行而不会互相影响,相互不阻塞的。

  • 写锁 :也称为 排他锁 、英文用 X 表示。当前写操作没有完成前,它会阻断其他写锁和读锁。这样 就能确保在给定的时间里,只有一个事务能执行写入,并防止其他用户读取正在写入的同一资源。

  • 兼容性情况:

    在这里插入图片描述

对于 InnoDB 引擎来说,读锁和写锁可以加在表上,也可以加在行上。


在这里插入图片描述

从数据操作的粒度划分:表级锁、页级锁、行锁

表锁

表锁是开销最小的策略,并且能够避免死锁产生,任何存储引擎都支持。

但是会对并发效率大打折扣

① 表级别的S锁、X锁

在对某个表执行SELECT、INSERT、DELETE、UPDATE语句时,InnoDB存储引擎是不会为这个表添加表级别的 S锁 或者 X锁 的。

在对某个表执行一些诸如 ALTER TABLE 、 DROP TABLE 这类的 DDL 语句时,其他事务对这个表并发执行诸如SELECT、INSERT、DELETE、UPDATE的语句会发生阻塞。相反,也会阻塞。这是通过在server层使用了一种称为元数据锁的结构来实现。

  • LOCK TABLES t READ :InnoDB存储引擎会对表 t 加表级别的 S锁
  • LOCK TABLES t WRITE :InnoDB存储引擎会对表 t 加表级别的 X锁

总结:

MyISAM在执行查询语句(SELECT)前,会给涉及的所有表加读锁,在执行增删改操作前,会给涉及的表加写锁。InnoDB存储引擎是不会为这个表添加表级别的读锁和写锁的。

MySQL的表级锁有两种模式:(以MyISAM表进行操作的演示)

  • 表共享读锁(Table Read Lock)
  • 表独占写锁(Table Write Lock)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-phhStVAV-1660305419458)(D:\note\笔记仓库\图片\image-20220812183446232.png)]

② 意向锁 (intention lock)

InnoDB 支持 多粒度锁(multiple granularity locking) ,它允许 行级锁表级锁 共存,而意向锁就是其中的一种 表锁

  1. 意向锁的存在是为了协调行锁和表锁的关系,支持多粒度(表锁和行锁)的锁并存。
  2. 意向锁是一种不与行级锁冲突表级锁`,这一点非常重要。
  3. 表明“某个事务的某些行为持有了锁或该事务准备去持有锁

意向锁分为两种:

  • 意向共享锁(intention shared lock, IS):事务有意向对表中的某些行加共享锁(S锁)
-- 事务要获取某些行的 S 锁,必须先获得表的 IS 锁。
SELECT column FROM table ... LOCK IN SHARE MODE;
  • 意向排他锁(intention exclusive lock, IX):事务有意向对表中的某些行加排他锁(X锁)
-- 事务要获取某些行的 X 锁,必须先获得表的 IX 锁。
SELECT column FROM table ... FOR UPDATE;

即:意向锁是由存储引擎 自己维护的 ,用户无法手动操作意向锁,在为数据行加共享 / 排他锁之前, InooDB 会先获取该数据行 所在数据表的对应意向锁

意向锁解决的问题:

如果我们给某一行数据加了排它锁,那么数据库会自动给更大一级的空间(例如数据库或表)加上排它意向锁,告诉其他人这个数据页或者数据表已经有人给它上过==排它锁==了,其它事务想要获取表的排它锁时就无法成功

  • 如果事务想要获得数据表中某些记录的共享锁,就需要自动在数据表上添加意向共享锁。
  • 如果事务想要获得数据表中某些记录的排他锁,就需要自动在数据表上添加意向排他锁。

意向锁的并发性

意向锁不会与行级的共享 / 排他锁互斥!正因为如此,意向锁并不会影响到多个事务对不同数据行加排他锁时的并发性。(不然我们直接用普通的表锁就行了)

总结

  1. InnoDB 支持 多粒度锁 ,特定场景下,行级锁可以与表级锁共存。
  2. 意向锁之间互不排斥,但除了 IS 与 S 兼容外, 意向锁会与 共享锁 / 排他锁 互斥
  3. IX,IS是表级锁,不会和行级的X,S锁发生冲突。只会和表级的X,S发生冲突。
  4. 意向锁在保证并发性的前提下,实现了 行锁和表锁共存满足事务隔离性 的要求。
③ 自增锁(AUTO-INC锁)

自增锁是当使用含有AUTO_INCREMENT列的表中插入数据时,需要获取的一种特殊表级锁。

一个事务在插入的过程中,其它含有插入数据的事务将被阻塞。(防止插入数据的ID出错)

插入数据的方式总共分为三类,分别是 “ Simple inserts ”,“ Bulk inserts ”和“ Mixed-mode inserts ”。

1. “Simple inserts” (简单插入)

可以 预先确定要插入的行数(当语句被初始处理时)的语句。包括没有嵌套子查询的单行和多行INSERT...VALUES()REPLACE 语句。比如我们上面举的例子就属于该类插入,已经确定要插入的行 数。

2. “Bulk inserts” (批量插入)

事先不知道要插入的行数 (和所需自动递增值的数量)的语句。比如 INSERT ... SELECTREPLACE ... SELECTLOAD DATA 语句,但不包括纯INSERT。 InnoDB在每处理一行,为AUTO_INCREMENT列

3. “Mixed-mode inserts” (混合模式插入)

这些是“Simple inserts”语句但是指定部分新行的自动递增值。例如 INSERT INTO teacher (id,name) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d'); 只是指定了部分id的值。另一种类型的“混合模式插入”是 INSERT ... ON DUPLICATE KEY UPDATE

上面所诉的并发能力其实很低下,innodb通过innodb_autoinc_lock_mode的不同取值来提供不同的锁机制。

innodb_autoinc_lock_mode有三种取值,分别对应与不同锁定模式:

(1)innodb_autoinc_lock_mode = 0(“传统”锁定模式)

在此锁定模式下,所有类型的insert语句都会获得一个特殊的表级AUTO-INC锁

(2)innodb_autoinc_lock_mode = 1(“连续”锁定模式)

在 MySQL 8.0 之前,连续锁定模式是 默认 的。

在这个模式下,==“bulk inserts”==仍然使用AUTO-INC表级锁,并保持到语句结束。这适用于所有INSERT … SELECT,REPLACE … SELECT和LOAD DATA语句。同一时刻只有一个语句可以持有AUTO-INC锁。

对于==“Simple inserts”(要插入的行数事先已知),则通过在 mutex(轻量锁) 的控制下获得所需数量的自动递增值==来避免表级AUTO-INC锁, 它只在分配过程的持续时间内保持,而不是直到语句完成。不使用表级AUTO-INC锁,除非AUTO-INC锁由另一个事务保持。如果另一个事务保持AUTO-INC锁,则“Simple inserts”等待AUTO-INC锁,如同它是一个“bulk inserts”。

(3)innodb_autoinc_lock_mode = 2(“交错”锁定模式)

从 MySQL 8.0 开始,交错锁模式是 默认 设置。

在此锁定模式下,自动递增值 保证 在所有并发执行的所有类型的insert语句中是 唯一单调递增 的。但是,由于多个语句可以同时生成数字(即,跨语句交叉编号),为任何给定语句插入的行生成的值可能不是连续的。

如果执行的语句是==“simple inserts"==,其中要插入的行数已提前知道,除了"Mixed-mode inserts"之外,为单个语句生成的数字不会有间隙。然而,当执行"bulk inserts"时,在由任何给定语句分配的自动递增值中可能存在间隙。

④ 元数据锁(MDL锁)

当对一个表做增删改查操作的时候,加 MDL读锁;当要对表做结构变更操作的时候,加 MDL 写锁

解决了DML和DDL操作之间的一致性问题不需要显式使用,在访问一个表的时候会被自动加上。

InnoDB中的行锁

行锁(Row Lock)也称为记录锁,顾名思义,就是锁住某一行(某条记录 row)。需要注意的是,MySQL服务器层并没有实现行锁机制,行级锁只在存储引擎层实现

**优点:**锁定力度小,发生锁冲突概率低,可以实现的并发度高

**缺点:**对于锁的开销比较大(锁太多),加锁会比较慢,容易出现死锁情况。

InnoDB与MyISAM的最大不同有两点:一是支持事务(TRANSACTION);二是采用了行级锁。

① 记录锁(Record Locks)

记录锁也就是仅仅把一条记录锁,官方的类型名称为:LOCK_REC_NOT_GAP。比如我们把id值为8的那条记录加一个记录锁的示意图如果所示。仅仅是锁住了id值为8的记录,对周围的数据没有影响。

在这里插入图片描述

记录锁是有S锁和X锁之分的,称之为 S型记录锁X型记录锁

  • 当一个事务获取了一条记录的S型记录锁后,其他事务也可以继续获取该记录的S型记录锁,但不可以继续获取X型记录锁;
  • 当一个事务获取了一条记录的X型记录锁后,其他事务既不可以继续获取该记录的S型记录锁,也不可以继续获取X型记录锁。
② 间隙锁(Gap Locks)

MySQLREPEATABLE READ 隔离级别下是可以解决幻读问题的,解决方案有两种,可以使用 MVCC 方 案解决,也可以采用 加锁 方案解决。

由于我们无法给 幻影记录 加上 记录锁,InnoDB提出了一种称之为 Gap Locks 的锁,官方的类型名称为: LOCK_GAP ,我们可以简称为 gap锁 。比如,把id值为8的那条 记录加一个gap锁的示意图如下。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fJHniEah-1660305419461)(D:\note\笔记仓库\图片\image-20220812191048180.png)]

图中id值为8的记录加了gap锁,意味着 不允许别的事务在id值为8的记录前边的间隙插入新记录 。即id为3~8之间

虽然共享gap锁独占gap锁这样的说法,但是它们起到的作用是相同的。

  • Infimum记录,表示该页面中最小的记录。
  • Supremun记录,表示该页面中最大的记录。

注意,给一条记录加了gap锁只是不允许其他事务往这条记录前边的间隙插入新记录。

如果我们想要在(最大值,正无穷)这个区间加间隙锁,怎么办?

通过Supremun记录来为表中(最大值,正无穷)这个区间提供间隙锁。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hUVspxbx-1660305419463)(D:\note\笔记仓库\图片\image-20220812191356323.png)]

③ 临键锁(Next-Key Locks)

既能 锁住某条记录 ,又能 阻止 其他事务在该记录前边的间隙插入新记录——间隙锁与记录锁的合体

innodb默认的锁就是Next-Key locks,官方的类型名称为: LOCK_ORDINARY

页锁

页锁的开销介于表锁和行锁之间,会出现死锁。锁定粒度介于表锁和行锁之间,并发度一般。

每个层级的锁数量是有限制的,因为锁会占用内存空间, 锁空间的大小是有限的当某个层级的锁数量 超过了这个层级的阈值时,就会进行 锁升级。锁升级就是用更大粒度的锁替代多个更小粒度的锁,比如 InnoDB 中行锁升级为表锁,这样做的好处是占用的锁空间降低了,但同时数据的并发度也下降了。

从对待锁的态度划分:乐观锁、悲观锁

乐观锁和悲观锁并不是锁,而是锁的 设计思想

1. 悲观锁(Pessimistic Locking)

通过数据库自身的锁机制来实现,从而保证数据操作的排它性。

共享资源每次只给一个线程使用,其它线程阻塞, 用完后再把资源转让给其它线程。比如行锁,表锁等,读锁,写锁等,都是在做操作之前先上锁,当其他线程想要访问数据时,都需要阻塞挂起。

2. 乐观锁(Optimistic Locking)
  • 乐观锁的版本号机制

  • 乐观锁的时间戳机制

    where quntity - num > 0 来优化sql语句,只要满足剩余量能够支持当前事务的要求即可

两种锁的适用场景
  1. 乐观锁 适合 读操作多 的场景,相对来说写的操作比较少。它的优点在于 程序实现不存在死锁 问题,不过适用场景也会相对乐观,因为它阻止不了除了程序以外的数据库操作。
  2. 悲观锁 适合 写操作多 的场景,因为写的操作具有 排它性 。采用悲观锁的方式,可以在数据库层 面阻止其他事务对该数据的操作权限,防止 读 - 写写 - 写 的冲突。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fpeigBIb-1660305419464)(D:\note\笔记仓库\图片\image-20220812192414936.png)]

按加锁的方式划分:显式锁、隐式锁

隐式锁

一个事务对新插入的记录可以不显示加锁(生成锁结构),但是由于事务ID的存在,相当于加了一个隐式锁。

别的事务对这条记录加S锁或者X锁时,由于隐式锁的存在,会帮助当前事务生成一个锁结构,然后自己生成一个锁结构进入等待状态。

隐式锁是一种延迟加载的机制,从而减少加锁的数量。

隐式锁在实际内存对象中并不含有这个锁信息。只有当产生锁等待时,隐式锁转化为显式锁。

隐式锁的逻辑过程如下:

  1. InnoDB的每条记录中都一个隐含的trx_id字段,这个字段存在于聚簇索引的B+Tree中。
  2. 在操作一条记录前,首先根据记录中的trx_id检查该事务是否是活动的事务(未提交或回滚)。如果是活动的事务,首先将 隐式锁 转换为 显式锁 (就是为该事务添加一个锁)。
  3. 检查是否有锁冲突,如果有冲突,创建锁,并设置为waiting状态。如果没有冲突不加锁,跳到E。
  4. 等待加锁成功,被唤醒,或者超时。
  5. 写数据,并将自己的trx_id写入trx_id字段。
显式锁

通过特定的语句进行加锁,我们一般称之为显示加锁,例如:

显示加共享锁:

select .... lock in share mode

显示加排它锁:

select .... for update

其它锁之:全局锁

全局锁就是对 整个数据库实例 加锁。当你需要让整个库处于 只读状态 的时候,可以使用这个命令,之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结 构等)和更新类事务的提交语句。全局锁的典型使用 场景 是:做 全库逻辑备份

全局锁的命令:

Flush tables with read lock

其它锁之:死锁

  1. 概念

两个事务都持有对方需要的锁,并且在等待对方释放,并且双方都不会释放自己的锁。

产生死锁的必要条件
  1. 两个或者两个以上事务
  2. 每个事务都已经持有锁并且申请新的锁
  3. 锁资源同时只能被同一个事务持有或者不兼容
  4. 事务之间因为持有锁和申请锁导致彼此循环等待

死锁的关键在于:两个(或以上)的Session加锁的顺序不一致。

如何处理死锁

**方式1:**等待,直到超时(innodb_lock_wait_timeout=50s)

等待一定事件后就退出,容易使普通锁的等待也失效

**方式2:**使用死锁检测处理死锁程序

方式1检测死锁太过被动,innodb还提供了wait-for graph算法来主动进行死锁检测,每当加锁请求无法立即满足需要并进入等待时,wait-for graph算法都会被触发。

这是一种较为主动的死锁检测机制,要求数据库保存锁的信息链表事物等待链表两部分信息。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-m8oiyLYt-1660305419465)(D:\note\笔记仓库\图片\image-20220812193544849.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZFzAwWOX-1660305419467)(D:\note\笔记仓库\图片\image-20220812193553638.png)]

死锁检测的原理是构建一个以事务为顶点,锁为边的有向图,判断有向图是否存在环,存在既有死锁。

一旦检测到回路、有死锁,这时候InnoDB存储引擎会选择回滚undo量最小的事务,让其他事务继续执行(innodb_deadlock_detect=on表示开启这个逻辑)。

缺点:每个新的被阻塞的线程,都要判断是不是由于自己的加入导致了死锁,这个操作时间复杂度是O(n)。如果100个并发线程同时更新同一行,意味着要检测100*100=1万次,1万个线程就会有1千万次检测。

如何解决?

  • 方式1:关闭死锁检测,但意味着可能会出现大量的超时,会导致业务有损。
  • 方式2:控制并发访问的数量。比如在中间件中实现对于相同行的更新,在进入引擎之前排队,这样在InnoDB内部就不会有大量的死锁检测工作。

进一步的思路:

可以考虑通过将一行改成逻辑上的多行来减少锁冲突。比如,连锁超市账户总额的记录,可以考虑放到多条记录上。账户总额等于这多个记录的值的总和。

如何避免死锁

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZqwsZXSD-1660305419468)(D:\note\笔记仓库\图片\image-20220812193830372.png)]

锁的内部结构

如果符合下边这些条件的记录会放在一个锁结构中:

  • 在同一个事务中进行加锁操作
  • 被加锁的记录在同一个页面中
  • 加锁的类型是一样的
  • 等待状态是一样的

InnoDB 存储引擎中的 锁结构 如下:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6McOHnnl-1660305419471)(D:\note\笔记仓库\图片\image-20220812194042392.png)]

1. 锁所在的事务信息

锁所在的事务信息在内存结构中只是一个指针,通过指针可以找到内存中关于该事务的更多信息,比方说事务id等。

2. 索引信息

对于 行锁 来说,需要记录一下加锁的记录是属于哪个索引的。这里也是一个指针。

3. 表锁/行锁信息

表锁结构行锁结构 在这个位置的内容是不同的:

  • 表锁:

    记载着是对哪个表加的锁,还有其他的一些信息。

  • 行锁:

    记载了三个重要的信息:

    • Space ID :记录所在表空间。
    • Page Number :记录所在页号。
    • n_bits :对于行锁来说,一条记录就对应着一个比特位,一个页面中包含很多记录,用不同的比特位来区分到底是哪一条记录加了锁。为此在行锁结构的末尾放置了一堆比特位,这个==n_bis属性代表使用了多少比特位==。

    n_bits的值一般都比页面中记录条数多一些。主要是为了之后在页面中插入了新记录后也不至于重新分配锁结构

4. type_mode

这是一个32位的数,被分成了 lock_modelock_typerec_lock_type 三个部分,如图所示:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6LZ9g9xP-1660305419472)(D:\note\笔记仓库\图片\image-20220812194427732.png)]

  • 锁的模式(lock_mode),占用低4位,可选的值如下:

    • LOCK_IS (十进制的 0 ):表示共享意向锁,也就是 IS锁
    • LOCK_IX (十进制的 1 ):表示独占意向锁,也就是 IX锁
    • LOCK_S (十进制的 2 ):表示共享锁,也就是 S锁
    • LOCK_X (十进制的 3 ):表示独占锁,也就是 X锁
    • LOCK_AUTO_INC (十进制的 4 ):表示 AUTO-INC锁
  • 锁的类型(lock_type),占用第5~8位,不过现阶段只有第5位和第6位被使用:

    • LOCK_TABLE (十进制的 16 ),也就是当第5个比特位置为1时,表示表级锁。
    • LOCK_REC (十进制的 32 ),也就是当第6个比特位置为1时,表示行级锁。
  • 行锁的具体类型(rec_lock_type),使用其余的位来表示。只有在lock_type的值为LOCK_REC时,也就是只有在该锁为行级锁时,才会被细分为更多的类型:

    • LOCK_ORDINARY (十进制的 0 ):表示 next-key锁
    • LOCK_GAP (十进制的 512 ):也就是当第10个比特位置为1时,表示 gap锁
    • LOCK_REC_NOT_GAP (十进制的 1024 ):也就是当第11个比特位置为1时,表示正经 记录锁
    • LOCK_INSERT_INTENTION (十进制的 2048 ):也就是当第12个比特位置为1时,表示插入意向锁。其他的类型:还有一些不常用的类型我们就不多说了。
  • is_waiting属性呢?基于内存空间的节省,所以把is_waiting属性放到了type_mode这个32 位的数字中:

    • 当第9个比特位置为 1 时,表示 is_waitingtrue;当这个比特位为 0 时,表示 is_waitingfalse /
  • 一堆比特位`:

    锁结构最后的一堆比特位就对应着一个页面中的记录,一个比特位映射一个 heap_no ,即一个比特位映射到页内的一条记录。

锁监控

(1)查询正在被锁阻塞的sql语句。

SELECT * FROM information_schema.INNODB_TRX\G;

(2)查询锁等待情况

SELECT * FROM data_lock_waits\G;

(3)查询锁的情况

SELECT * from performance_schema.data_locks\G;