MySQL锁机制

1.表级锁&行级锁

数据库中的锁通常分为两种：

  • 表级锁：对整张表加锁。开销小，加锁快，不会出现死锁。但是锁的粒度大，发生锁冲突的概率高，并发度低。
  • 行级锁：对某行记录加锁。开销大，加锁慢，会出现死锁。但是锁的粒度小，发生锁冲突的概率低，并发度高。

2.排它锁&共享锁

  • 排它锁(Exclusive)：又称为 X 锁，写锁；
  • 共享锁(Shared)：又称为 S 锁，读锁；

X 锁和 S 锁之间具有如下关系：S-S 之间可以兼容，但是 X-S、S-X、X-X 之间是互斥的。

  • 一个事务对数据对象 O 加了 S 锁，可以对 O 进行读取操作但不能对其进行更新操作。加锁期间其它事务能对 O 加 S 锁但不能加 X 锁；
  • 一个事务对数据对象 O 加了 X 锁，可以对 O 进行读取和更新。加锁期间其它事务不能对 O 加任何锁。

可以通过 SELECT ... LOCK IN SHARE MODE 强制获取共享锁，通过 SELECT ... FOR UPDATE 获取排它锁。

3.InnoDB行级锁

行级锁

InnoDB 存储引擎支持事务处理，表支持行级锁定，并发能力更好。使用的过程中应该注意以下 3 点：

  1. InnoDB 行锁是通过给索引上的索引项加锁来实现的，而不是给表的行记录加锁实现的，这就意味着只有通过索引条件检索数据，InnoDB 才使用行级锁，否则 InnoDB 将使用表锁。
  2. 由于 InnoDB 的行锁实现是针对索引字段添加的锁，不是针对行记录加的锁，因此虽然访问的是 InnoDB 引擎下表的不同行，但是如果使用相同的索引字段作为过滤条件，依然会发生锁冲突，只能串行进行，不能并发进行。
  3. 即使 SQL 中使用了索引，但是经过 MySQL 的优化器后，如果认为全表扫描比使用索引效率更高，此时会放弃使用索引，因此也不会使用行锁，而是使用表锁，比如对一些很小的表，MySQL 就不会去使用索引。

注意，如果过滤条件使用的是辅助索引，那么它会通过辅助索引树回表到主键索引树，来查找到该行内容以获取行锁。

间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做 "间隙(GAP)"，InnoDB 也会对这个 "间隙" 加锁，这种锁机制就是所谓的【间隙锁】。

举例来说， 假如 user 表中只有 101 条记录， 其 userid 的值分别是 1,2,...,100,101，对于下面的 SQL：

SELECT * FROM user WHERE userid > 100 FOR UPDATE;

它是一个范围条件的检索，InnoDB 不仅会对符合条件的 userid 值为 101 的记录加锁，也会对 userid 大于 101（但是这些记录并不存在）的 "间隙" 加锁，以防止其它事务在表的末尾增加数据。

InnoDB 使用间隙锁的目的，为了防止【幻读】，以满足串行化隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了 userid 大于 100 的任何记录，那么本事务如果再次执行上述语句，就会发生幻读。

现在有这样一张数据表，其中 uid 为自增的主键字段，同时 age 字段为辅助索引：

mysql> select * from student;
+-----+-------------+-----+-----+
| uid | name        | age | sex |
+-----+-------------+-----+-----+
|   1 | zhangsan    |  18 | M   |
|   2 | gaoyang     |  20 | W   |
|   3 | chenwei     |  22 | M   |
|   4 | linfeng     |  21 | W   |
|   5 | liuxiang    |  19 | W   |
|   6 | niuer       |  17 | M   |
|   7 | liuxiaohong |  28 | W   |
|   8 | gaolishi    |  37 | M   |
|   9 | yuanwei     |  13 | W   |
+-----+-------------+-----+-----+
9 rows in set (0.26 sec)

那么辅助索引树的叶子节点所在层结构大致如下：

如果现在在事务中使用如下 SQL 语句：

SELECT * FROM student WHERE age>21;

那么间隙锁的情况如下图所示：

由于在辅助索引树中，如果辅助索引相同，那么会按照主键索引按照升序进行排列，因此如果现在在事务中插入一个新的记录，其 age 字段为 21，那么其主键字段会大于 4，则会插入到间隙锁 1 所在位置，但是由于间隙锁 1 的存在，则会导致插入失败。而如果插入的 age 字段值为 19，则会插入成功。

如果使用的是等值查询：

SELECT * FROM student WHERE age=28;

那么其间隙锁情况如下：

此时，插入【22, 36】之间的值都会被阻塞。

意向共享锁&意向排它锁

如果要获取一张表的共享锁 S 或者排它锁 X，首先需要确定这张表没有被其它事务获取或 X 锁。但是如果表中的数据过多，遍历所有数据来查看是否存在某行数据或者获取过行锁 X 锁或者整张表存在表锁 X 锁，效率就会下降，为了提高效率，因此便有了如下的两把锁：

  • 意向共享锁(IS锁)：事务计划给记录加行共享锁，事务在给一行记录加共享锁前，必须先取得该表的 IS 锁。
  • 意向排他锁(IX锁)：事务计划给记录加行排他锁，事务在给一行记录加排他锁前，必须先取得该表的 IX 锁。

X、S、IS 锁和 IX 锁之间的兼容性如下：

X IX S IS
X ❌ ❌ ❌ ❌
IX ❌ ✔️ ❌ ✔️
S ❌ ❌ ✔️ ✔️
IS ❌ ✔️ ✔️ ✔️

表格中的X和S指的是表锁，不是行锁！！！

注意：

  1. 意向锁是由 InnoDB 存储引擎获取行锁之前自己获取的;
  2. 意向锁之间都是兼容的，不会产生冲突;
  3. 意向锁存在的意义是为了更高效的获取表锁;
  4. 意向锁是表级锁，协调表锁和行锁的共存关系。主要目的是显示事务正在锁定某行或者试图锁定某行;

4.InnoDB表级锁

在绝大部分情况下都应该使用行锁，因为事务和行锁往往是选择 InnoDB 的理由，但个别情况下也使用表级锁；

  1. 事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间等待和锁冲突；
  2. 事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。

常用的相关命令如下：

  • LOCK TABLE user READ: 读锁锁表
  • LOCK TABLE user WRITE: 写锁锁表
  • COMMIT/ROLLBACK: 事务提交或者回滚
  • UNLOCK TABLES: 本身自带提交事务，释放线程占用的所有表锁

5.死锁

MyISAM 表锁是 deadlock free 的， 这是因为 MyISAM 总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，即锁的粒度比较小，这就决定了在 InnoDB 中发生死锁是可能的。

死锁问题一般都是我们自己的应用造成的，和多线程编程的死锁情况相似，大部分都是由于我们多个线程在获取多个锁资源的时候，获取的顺序不同而导致的死锁问题。因此我们应用在对数据库的多个表做更新的时候，不同的代码段，应对这些表按相同的顺序进行更新操作，以防止锁冲突导致死锁问题。

6.锁优化

  1. 尽量使用较低的隔离级别;
  2. 设计合理的索引并尽量使用索引访问数据，使加锁更加准确，减少锁冲突的机会提高并发能力;
  3. 选择合理的事务大小，小事务发生锁冲突的概率小;
  4. 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会;
  5. 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响;
  6. 不要申请超过实际需要的锁级别;
  7. 除非必须，查询时不要显示加锁;

7.MVCC多版本并发控制

MVCC 是多版本并发控制(Multi-Version Concurrency Control，简称 MVCC)，是 MySQL 中基于乐观锁理论实现隔离级别的方式，用于实现已提交读和可重复读隔离级别的实现，也经常称为多版本数据库。

MVCC 机制会生成一个数据请求时间点的一致性数据快照(Snapshot)，并用这个快照来提供一定级别(语句级或事务级)的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本(系统版本号和事务版本号)。

在 MVCC 多版本并发控制中，读操作可以分为两类：

  1. 快照读(snapshot read): 读的是记录的可见版本，不用加锁。如 select;
  2. 当前读(current read): 读取的是记录的最新版本，并且当前读返回的记录。如 insert，delete，update，select...lock in share, mode/for update

MVCC 中的每一行记录实际上有多个版本，每个版本的记录除了数据本身之外，还增加了其它字段，比如：

  • DB_TRX_ID：用于记录当前事务ID
  • DB_ROLL_PTR：指向 undo log 日志上数据的指针

MVCC 对于已提交读和可重复读的不同点在于：

  • 已提交读：每次执行语句的时候都重新生成一次快照(Read View)，即每次 select 查询时。
  • 可重复读：同一个事务开始的时候生成一个当前事务全局性的快照(Read View)，即第一次select查询时。

快照内容的读取原则如下：

  • 版本未提交无法读取生成快照；
  • 版本已提交，但是在快照创建后提交的，无法读取；
  • 版本已提交，但是在快照创建前提交的，可以读取；
  • 当前事务内自己的更新，可以读到；

8.MyISAM表级锁

MyISAM存储引擎不支持事务处理，因此它的并发比较简单，只支持到表锁的粒度，粒度比较大，并发能力一般，但不会引起死锁的问题，它支持表级共享的读锁和互斥的写锁。

对 MyISAM 表的读操作（共享锁），不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对 MyISAM 表的写操作（排它锁），则会阻塞其他用户对同一表的读和写操作。MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户控制，是 MySQL Server 端自动完成的。

在 MyISAM 存储引擎下，多个线程并发操作时，线程1试图获取读锁，线程2获取写锁，一般 MyISAM 认为写操作要比读操作重要，因此线程2几乎都会优先获取写锁，写操作完成后，线程1才会获取读锁。即使线程1的读锁请求先到达，线程2的写锁请求后到达，那么线程2写锁的获取也会排在线程1读锁的前面。

因此，MyISAM 存储引擎不适合大量的更新操作和查询操作，因为查询操作获取读锁的优先级比较低，会导致客户端查询获取结果的过程很慢。当然 MySQL 提供了很多参数设置，可以调整读锁的获取优先级。

posted @ 2022-12-22 20:08  Leaos  阅读(74)  评论(0)  编辑  收藏  举报