MySQL-锁

前言

​ 数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。在任何多线程环境下，存在资源竞争都需要锁机制来保证数据的一致性。

​ MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。MySQL各存储引擎使用了三种类型（级别）的锁定机制：表级锁定，行级锁定和页级锁定。

锁的分类-按数据操作的颗粒度分类

​ 在关系型数据库中，按数据操作的颗粒度划分，分为表锁，行锁和页锁。表锁

表锁：

​ MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

​ 使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。

1）元数据锁（Meta Data Lock）：

​ 元数据锁，也被简称为MDL锁。由于InnoDB的表会存在一个.frm文件，这个文件中主要存储表的结构（DDL语句），而MDL锁就是基于.frm文件中的元数据加锁的。主要是用于表结构变更的时候，防止其他事务按照原有表结构插入、维护数据。

2）意向锁

​ 意向锁则是InnoDB中为了支持多粒度的锁，为了兼容行锁、表锁而设计的。当事务对数据增加行锁之前先增加一个行级别的意向锁，当获取一个表级锁时，就会先看一下表上是否有意向锁，如果有的话再判断一下与自身是否冲突，如果没有冲突就获取锁，如果有冲突就进行阻塞，等待锁释放。

​ 比如表上存在一个意向共享锁，那么只能执行表级别的读锁；如果要执行表记的写锁时，就会出现冲突，事务则会陷入阻塞，直至T1释放了锁（事务结束）为止。或者表上没有增加意向锁，说明可以直接获取表级别的读锁或写锁。

3）自增锁

​ 自增锁，这个是专门为了提升自增ID的并发插入性能而设计的，通常情况下咱们在建表时，都会对一张表的主键设置自增特性。自增锁主要负责维护并发事务下自增列的顺序，也就是说，每当一个事务想向表中插入数据时，都要先获取自增锁先分配一个自增的顺序值，但不同模式下的自增锁也会有些许不同。

MySQL中可能出现的三种插入类型：

1）普通插入：指通过INSERT INTO table_name(...) VALUES(...)这种方式插入。

2）批量插入：指通过INSERT ... SELECT ...这种方式批量插入查询出的数据。

3）混合插入：指通过INSERT INTO table_name(id,...) VALUES(1,...),(NULL,...),(3,...)这种方式插入，其中一部分指定ID，一部分不指定。

​ 自增锁也是一种特殊的表锁，但它仅为具备AUTO_INCREMENT自增字段的表服务，同时自增锁也分成了不同的级别，可以通过innodb_autoinc_lock_mode参数控制。

• innodb_autoinc_lock_mode = 0：传统模式。事务T1获取自增锁插入数据，事务T2也要插入数据，此时事务T2只能阻塞等待，也就是传统模式下的自增锁，同时只允许一条线程执行，这种形式显然性能较低。
• innodb_autoinc_lock_mode = 1：连续模式（MySQL8.0以前的默认模式）。连续模式是为了解决传统模式弊端而产生。在执行插入时，经由优化器就能知道要插入多少条数据，因此会预分配范围自增值。
• innodb_autoinc_lock_mode = 2：交错模式（MySQL8.0之后的默认模式）。在交错插入模式中，都不会再使用表级别的自增锁，而是全都使用Mutex-Lock互斥锁来确保安全性

4）全局锁

​ 全局锁是基于整个数据库来加锁的，加上全局锁之后，整个数据库只能允许读，不允许做任何写操作，一般全局锁是在对整库做数据备份时使用。

特点：

​ 表锁开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发最低。

---

行锁：

​ MySQL中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大，行级锁定也最容易发生死锁。。行级锁分为共享锁和排他锁。InnoDB存储引擎默认采用行锁。InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）;二是采用了行级锁。

​ 从底层来说，InnoDB存储引擎数据是以聚簇索引来进行存储表数据。也就是说为了避免全表扫描，肯定要利用主键索引和辅助索引。而获取指定行记录，对行记录进行加锁，肯定要基于索引来命中数据，才能执行后续的操作。

1）记录锁（Record Lock）

​ 记录锁其实很好理解，对表中的记录加锁，叫做记录锁，简称行锁。比如下面例子，它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行。

SELECT * FROM `test` WHERE `id`=1 FOR UPDATE;

注意：

• 列必须为唯一索引列或主键列，否则上述语句加的锁就会变成临键锁。
• 同时查询语句必须为精准匹配（=），不能为 >、<、like等，否则也会退化成临键锁。

​ 记录锁是锁住记录，锁住索引记录，而不是真正的数据记录。如果要锁的列没有索引，进行全表记录加锁。记录锁也是排它(X)锁,所以会阻塞其他事务对其插入、更新、删除。

2）间歇锁（Gap Lock）

​ 间隙锁 是 Innodb 在 RR(可重复读) 隔离级别 下为了解决幻读问题时引入的锁机制。间隙锁是innodb中行锁的一种。使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。当符合这个区间的数据要变更，就会先进行阻塞，直至当前事物进行提交。

3）临键锁（Next-Key Locks）

​ Next-key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。通过临建锁可以解决幻读的问题， 每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的。

​ **临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。**也就是说如果存在

特点：

​ 行锁开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

小结

​ 唯一索引列，则进行添加记录锁；若不存在唯一索引列，则用临界锁锁定一个范围的数据。

行锁并不是一成不变的，行锁会在某些特殊情况下发生粗化，主要有两种情况：

• 在内存中专门分配了一块空间存储锁对象，当该区域满了后，就会将行锁粗化为表锁。
• 当做范围性写操作时，由于要加的行锁较多，此时行锁开销会较大，也会粗化成表锁。

​ 当然，这两种情况其实很少见，因此只需要知道有锁粗化这回事即可，这种锁粗化的现象其实在SQLServer数据库中更常见，因为SQLServer中的锁机制是基于行记录实现的，而MySQL中的锁机制则是基于事务实现的。

行锁优化

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
• 合理设计索引，尽量缩小锁的范围
• 尽可能较少检索条件，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度
• 尽可能低级别事务隔离

---

页锁：

​ 页锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁，是对命中索引的行记录所在的页进行加锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。使用页级锁定的主要是BerkeleyDB存储引擎。页面锁是Berkeley DB存储引擎支持的一种锁粒度，当然，由于BDB引擎被Oracle收购的原因，因此MySQL5.1以后不再直接性的支持该引擎（需自己整合），因此页锁见的也比较少，大家稍微了解即可。

特点：

​ 开销和加锁时间界于表锁和行锁之间：会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

小结

​ 总的来说，MySQL这3种锁的特性可大致归纳如下：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

其他

共享锁、排他锁

​ InnoDB 实现了标准的行级锁，包括两种：共享锁（简称 s 锁）、排它锁（简称 x 锁）。

​ 对于共享锁而言，对当前行加共享锁，不会阻塞其他事务对同一行的读请求，但会阻塞对同一行的写请求。只有当读锁释放后，才会执行其它事物的写操作。

​ 对于排它锁而言，会阻塞其他事务对同一行的读和写操作，只有当写锁释放后，才会执行其它事务的读写操作。

-- 加共享锁（S）
select * from table_name where ... lock in share mode

-- 加排它锁（X)
select * from table_name where ... for update

小结

• InnoDB 中的行锁的实现依赖于索引，一旦某个加锁操作没有使用到索引，那么该锁就会退化为表锁。
• 记录锁存在于包括主键索引在内的唯一索引中，锁定单条索引记录。
• 间隙锁存在于非唯一索引中，锁定开区间范围内的一段间隔，它是基于临键锁实现的。
• 临键锁存在于非唯一索引中，该类型的每条记录的索引上都存在这种锁，它是一种特殊的间隙锁，锁定一段左开右闭的索引区间。

死锁和避免死锁

​ 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。 InnoDB的行级锁是基于索引实现的，如果查询语句没有命中任何索引，那么InnoDB会使用表级锁.。此外，InnoDB的行级锁是针对索引加的锁，不针对数据记录，因此即使访问不同行的记录，如果使用了相同的索引键仍然会出现锁冲突。

​ 不同于MyISAM总是一次性获得所需的全部锁，InnoDB的锁是逐步获得的，当两个事务都需要获得对方持有的锁，导致双方都在等待，这就产生了死锁。 发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退，另一个则可以获取锁完成事务。

可以采取以上方式避免死锁：

1）**通过表级锁来减少死锁产生的概率。**对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率
2）**多个程序尽量约定以相同的顺序访问表。**如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁的产生概率。
3）同一个事务尽可能做到一次锁定所需要的所有资源，这样可以减少死锁产生概率

隔离级别与锁的关系

读取未提交

读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突

读取已提交

读操作需要加共享锁，但是在语句执行完以后释放共享锁。

可重复读

读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。

可串行化

因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

数据库的乐观锁和悲观锁

​ 数据库中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：

​ 假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。

​ 实现方式：使用数据库中的锁机制。

乐观锁：

​ 假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过Version的方式来进行锁定。
​ 实现方式：一般会使用版本号机制或CAS算法实现。

​ 像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行Retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

MySQL锁的由来与分类

​ 客户端发往MySQL的一条条SQL语句，实际上都可以理解成一个个单独的事务。而**事务是基于数据库连接的，而每个数据库连接在MySQL中，又会用一条工作线程来维护，也意味着一个事务的执行，本质上就是一条工作线程在执行，当出现多个事务同时执行时，这种情况则被称之为并发事务，所谓的并发事务也就是指多条线程并发执行。**也就是说，数据库的锁机制本身是为了解决并发事务带来的问题而诞生的，主要是确保数据库中，多条工作线程并行执行时的数据安全性。

​ 而MySQL的锁机制与索引机制类似，都是由存储引擎负责实现的，这也就意味着不同的存储引擎，支持的锁也并不同。

MVCC之间的锁机制

​ 对于并发事务通常可以通过其提供的各类锁，去确保各场景下的线程安全问题，从而能够防止脏写、脏读、不可重复读及幻读这类问题出现。但由于加锁后会让一部分事务串行化，而MySQL本身就是基于磁盘实现的，性能无法跟内存型数据库娉美，因此并发事务串行化会使其效率更低。

​ MVCC机制的出现是用于解决加锁串行化相关问题，而一般出现这种主要是为了解决读写冲突，即有读写冲突时，也可以不加锁解决，从而确保了任何时刻的读操作都是非阻塞的。MVCC是按照事务的隔离级别来进行进行数据隔离。

总结

​ 如果要进行备份的话，需要增加全局锁。如果日常使用的话，尽量命中索引，命中索引会进行行锁，若是名不中则进行表锁。现在的版本已经去掉了页锁。