Mysql锁——初级篇

Mysql锁——初级篇

事务的隔离性由锁来实现。

概述

在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。为保证数据的一致性，需要对并发操作进行控制，因此产生了锁。同时锁机制也为实现MySQL的各个隔离级别提供了保证。锁冲突也是影响数据库并发访问性能的一个重要因素。所以锁对数据库而言显得尤其重要，也更加复杂。

MySQL并发事务访问相同记录

并发事务访问相同记录的情况大致可以划分为 3 种：

读-读情况

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

写-写情况

写-写情况，即并发事务相继对相同的记录做出改动。
在这种情况下会发生脏写的问题，任何一种隔离级别都不允许这种问题的发生。所以在多个未提交事务相继对一条记录做改动时，需要让它们排队执行，这个排队的过程其实是通过锁来实现的。这个所谓的锁其实是一个内存中的结构，在事务执行前本来是没有锁的，也就是说一开始是没有锁结构和记录进行关联的，如图所示：

当一个事务想对这条记录做改动时，首先会看看内存中有没有与这条记录关联的锁结构，当没有的时候就会在内存中生成一个锁结构与之关联。比如，事务T1要对这条记录做改动，就需要生成一个锁结构与之关联：

小结几种说法：

• 不加锁
  意思就是不需要在内存中生成对应的锁结构，可以直接执行操作。
• 获取锁成功，或者加锁成功
  意思就是在内存中生成了对应的锁结构，而且锁结构的is_waiting属性为false，也就是事务
  可以继续执行操作。
• 获取锁失败，或者加锁失败，或者没有获取到锁
  意思就是在内存中生成了对应的锁结构，不过锁结构的is_waiting属性为true，也就是事务
  需要等待，不可以继续执行操作。

读-写或写-读情况

读-写或写-读，即一个事务进行读取操作，另一个进行改动操作。这种情况下可能发生脏读、不可重复读、幻读的问题。
各个数据库厂商对SQL标准的支持都可能不一样。比如MySQL在REPEATABLE READ隔离级别上就已经解决了幻读问题。

并发问题的解决方案

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

• 方案一：读操作利用多版本并发控制（MVCC，下章讲解），写操作进行加锁。

普通的SELECT语句在READ COMMITTED和REPEATABLE READ隔离级别下会使用到MVCC读取记录。

• 在READ COMMITTED隔离级别下，一个事务在执行过程中每次执行SELECT操作时都会生成一个ReadView，ReadView的存在本身就保证了事务不可以读取到未提交的事务所做的更改，也是避免了脏读现象；
• 在REPEATABLE READ隔离级别下，一个事务在执行过程中只有第一次执行SELECT操作才会生成一个ReadView，之后的SELECT操作都复用这个ReadView，这样也就避免了不可重复读和幻读的问题。

• 方案二：读、写操作都采用加锁的方式。
• 小结对比发现：
  • 采用MVCC方式的话，读-写操作彼此并不冲突，性能更高。
  • 采用加锁方式的话，读-写操作彼此需要排队执行，影响性能。
    一般情况下我们当然愿意采用MVCC来解决读-写操作并发执行的问题，但是业务在某些特殊情下，要求必须采用加锁的方式执行。下面就讲解下MySQL中不同类别的锁。

锁的不同角度分类

锁的分类图，如下：

操作类型划分

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

• 读锁：也称为共享锁、英文用S表示。针对同一份数据，多个事务的读操作可以同时进行而不会互相影响，相互不阻塞的。
• 写锁：也称为排他锁、英文用X表示。当前写操作没有完成前，它会阻断其他写锁和读锁。这样就能确保在给定的时间里，只有一个事务能执行写入，并防止其他用户读取正在写入的同一资源。
  需要注意的是对于 InnoDB 引擎来说，读锁和写锁可以加在表上，也可以加在行上。

操作的粒度划分

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

表锁（Table Lock）

① 表级别的S锁、X锁

在对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，InnoDB存储引擎是不会为这个表添加表级别的S锁或者X锁的。在对某个表执行一些诸如ALTER TABLE、DROP TABLE这类的DDL语句时，其他事务对这个表并发执行诸如SELECT、INSERT、DELETE、UPDATE的语句会发生阻塞。同理，某个事务中对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，在其他会话中对这个表执行DDL语句也会发生阻塞。这个过程其实是通过在server层使用一种称之为元数据锁（英文名：Metadata Locks，简称MDL）结构来实现的。
一般情况下，不会使用InnoDB存储引擎提供的表级别的S锁和X锁。只会在一些特殊情况下，比方说崩溃恢复过程中用到。比如，在系统变量autocommit=0，innodb_table_locks = 1时，手动获取InnoDB存储引擎提供的表t的S锁或者X锁可以这么写：

• LOCK TABLES t READ：InnoDB存储引擎会对表t加表级别的S锁。
• LOCK TABLES t WRITE：InnoDB存储引擎会对表t加表级别的X锁。
  不过尽量避免在使用InnoDB存储引擎的表上使用LOCK TABLES这样的手动锁表语句，它们并不会提供什么额外的保护，只是会降低并发能力而已。InnoDB的厉害之处还是实现了更细粒度的行锁，关于InnoDB表级别的S锁和X锁大家了解一下就可以了。
  MySQL的表级锁有两种模式：（以MyISAM表进行操作的演示）
• 表共享读锁（Table Read Lock）
• 表独占写锁（Table Write Lock）

锁类型	自己可读	自己可写	自己可操作其他表	他人可读	他人可写
读锁	是	否	否	是	否，等
写锁	是	是	否	否，等	否，等

② 意向锁 （intention lock）
InnoDB 支持多粒度锁（multiple granularity locking），它允许行级锁与表级锁共存，而 意向锁 就是其中的一种表锁。
意向锁分为两种：

• 意向共享锁 （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 会先获取该数据行所在数据表的对应意向锁。
意向锁的并发性
意向锁不会与行级的共享 / 排他锁互斥！正因为如此，意向锁并不会影响到多个事务对不同数据行加排他锁时的并发性。（不然我们直接用普通的表锁就行了）
我们扩展一下上面 teacher表的例子来概括一下意向锁的作用（一条数据从被锁定到被释放的过程中，可能存在多种不同锁，但是这里我们只着重表现意向锁）。
从上面的案例可以得到如下结论：

1. InnoDB 支持多粒度锁，特定场景下，行级锁可以与表级锁共存。
2. 意向锁之间互不排斥，但除了 IS 与 S 兼容外，意向锁会与 共享锁 / 排他锁 互斥。
3. IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。
4. 意向锁在保证并发性的前提下，实现了行锁和表锁共存且满足事务隔离性的要求。
   ③ 自增锁（AUTO-INC锁）
   在使用MySQL过程中，我们可以为表的某个列添加AUTO_INCREMENT属性。举例：

CREATE TABLE `teacher` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

由于这个表的id字段声明了AUTO_INCREMENT，意味着在书写插入语句时不需要为其赋值，SQL语句修改如下所示。

INSERT INTO `teacher` (name) VALUES ('zhangsan'), ('lisi');

上边的插入语句并没有为id列显式赋值，所以系统会自动为它赋上递增的值，结果如下所示。

mysql> select * from teacher;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | lisi |
+----+----------+
2 rows in set (0.00 sec)

现在我们看到的上面插入数据只是一种简单的插入模式，所有插入数据的方式总共分为三类，分别是“Simple inserts”，“Bulk inserts”和“Mixed-mode inserts”。
1. “Simple inserts” （简单插入）
可以预先确定要插入的行数（当语句被初始处理时）的语句。包括没有嵌套子查询的单行和多行
INSERT...VALUES()和REPLACE语句。比如我们上面举的例子就属于该类插入，已经确定要插入的行
数。
2. “Bulk inserts” （批量插入）
事先不知道要插入的行数（和所需自动递增值的数量）的语句。比如INSERT ... SELECT，REPLACE... SELECT和LOAD 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_autoinc_lock_mode有三种取值，分别对应与不同锁定模式：
（1）innodb_autoinc_lock_mode = 0(“传统”锁定模式)
在此锁定模式下，所有类型的insert语句都会获得一个特殊的表级AUTO-INC锁，用于插入具有
AUTO_INCREMENT列的表。这种模式其实就如我们上面的例子，即每当执行insert的时候，都会得到一个表级锁(AUTO-INC锁)，使得语句中生成的auto_increment为顺序，且在binlog中重放的时候，可以保证master与slave中数据的auto_increment是相同的。因为是表级锁，当在同一时间多个事务中执行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语句中是唯一且单调递增的。但是，由于多个语句可以同时生成数字（即，跨语句交叉编号）， 为任何给定语句插入的行生成的值可不是连续的。
④ 元数据锁（MDL锁）
MySQL5.5引入了meta data lock，简称MDL锁，属于表锁范畴。MDL 的作用是，保证读写的正确性。比如，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，增加了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。
因此， 当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写锁。
不是连续的**。
④ 元数据锁（MDL锁）
MySQL5.5引入了meta data lock，简称MDL锁，属于表锁范畴。MDL 的作用是，保证读写的正确性。比如，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，增加了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。
因此， 当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写锁。