MySQL 锁

官网保平安：https://www.mysql.com/
MySQL 思维导图：https://www.processon.com/view/link/63bc2c8ea82ed9463ba99f38

MySQL 中锁的种类 ★★★

按锁粒度从大到小分类：表锁、页锁和行锁 以及特殊场景下使用的 全局锁

  • 表锁：表级别的锁定是 MySQL 各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。常用表级锁主要是 MyISAM 引擎。

  • 行锁：与表锁正相反，行锁最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力从而提高系统的整体性能。
    虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。
    InnoDB 的行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。当我们执行 UPDATE、DELETE 语句时，如果 WHERE 条件中字段没有命中索引或者索引失效的话，就会导致扫描全表对表中的所有记录进行加锁，很多时候即使用了索引也有可能会走全表扫描，这是因为 MySQL 优化器的原因。
    由于 MVCC 的存在，对于一般的 SELECT 语句，InnoDB 不会加任何锁，因为一般的 SELECT 语句都是当前读。

  • 页锁：相对偏中性的页级锁，页锁是 MySQL 中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。

  • 全局锁：是对整个数据库实例加锁。使用场景一般在全库逻辑备份时。

如果按锁级别分类则有：共享锁（Share Lock，S 锁，读锁）、排他锁（Exclusive Lock，X 锁，写锁，独占锁）。不论是表级锁还是行级锁，都存在共享锁和排他锁这两类。

  • 共享锁：事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。

  • 排他锁：事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。

面向编程的两种锁思想：悲观锁、乐观锁。

  • 悲观锁：就是比较悲观的锁，总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，直到操作完成之后才会释放锁，在上锁期间其他人不能操作数据。
    通常来讲，在数据库上的悲观锁需要数据库本身提供支持，即通过常用的 SELECT … FOR UPDATE 操作来实现悲观锁。
    当执行 SELECT … FOR UPDATE 时会获取被 SELECT 中的数据行的行锁，因此其他并发执行的 SELECT … FOR UPDATE 如果试图选中同一行则会发生排斥（需要等待行锁被释放），因此达到锁的效果。
    SELECT … FOR UPDATE 获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。
    需要特别注意的是，不同的数据库对 SELECT … FOR UPDATE 的实现和支持都是有所区别的，例如 Oracle 支持 SELECT FOR UPDATE no wait 表示如果拿不到锁立刻报错，而不是等待，MySQL 没有 no wait 这个选项。
    另外，MySQL 还有个问题是：SELECT … FOR UPDATE 语句执行中所有扫描过的行都会被锁上，这一点很容易造成问题。因此，如果在 MySQL 中用悲观锁务必要确定使用了索引，而不是全表扫描。

  • 乐观锁：总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，如果别人修改了数据则放弃操作，否则执行操作。
    可以使用版本号机制和CAS算法实现，适用于多读的应用类型，这样可以提高吞吐量。

    • CAS是一种无锁算法，CAS有3个操作数，内存值V，旧的预期值A，要修改的新值B。当且仅当预期值A和内存值V相同时，将内存值V修改为B，否则什么都不做。

    乐观锁在数据库上的实现完全是逻辑的，不需要数据库提供特殊的支持。一般的做法是在需要锁的数据上增加一个版本号或者时间戳，然后按照如下方式实现：

    1
    2
    3
    4
    5
    6
    7
    8
    1.  SELECT data AS old_data, version AS old_version FROM …;
    2.  根据获取的数据进行业务操作，得到new_data和new_version
    3.  UPDATE SET data = new_data, version = new_version WHERE version = old_version
        if (updated row > 0) {
            // 乐观锁获取成功，操作完成
        } else {
            // 乐观锁获取失败，回滚并重试
        }

    乐观锁是否在事务中其实都是无所谓的，其底层机制是这样：在数据库内部 update 同一行的时候是不允许并发的，即数据库每次执行一条 update 语句时会获取被 update 行的写锁，直到这一行被成功更新后才释放。因此在业务操作进行前获取需要锁的数据的当前版本号，然后实际更新数据时再次对比版本号确认与之前获取的相同，并更新版本号，即可确认这其间没有发生并发的修改。如果更新失败，即可认为老版本的数据已经被并发修改掉而不存在了，此时认为获取锁失败，需要回滚整个业务操作并可根据需要重试整个过程。

  • 悲观锁与乐观锁的应用场景

    一般情况下，读多写少更适合用乐观锁，读少写多更适合用悲观锁。乐观锁在不发生取锁失败的情况下开销比悲观锁小，但是一旦发生失败回滚开销则比较大，因此适合用在取锁失败概率比较小的场景，可以提升系统并发性能。

意向锁是加在表级别的一个锁，分为意向共享（读）锁（IS）、意向排他（写）锁（IX）。意向锁，顾名思义，就是指明接下来要做的是一个什么类型的操作。

  • 意向共享锁：在准备给表数据添加一个S锁时，需要先获得该表的IS锁。

  • 意向排他锁：在准备给表数据添加一个X锁时，需要先获得该表的IX锁。

意向锁的出现还有一个主要原因是为了在支持不同粒度锁时，能有更高的效率。比如：

  1. 事务A 对 表T 中的某一数据行添加了行锁。
  2. 这时 事务B 要对 表T 添加 表X锁，但是在添加之前需要先检查是否有其他事务持有该表的X锁和行X锁，如果持有则要阻塞。
    • 对于表X锁，事务B 可以直接判断是否有其他事务是有，
    • 对于行X锁，事务B 只能通过遍历 表T 中的所有行是否有锁，这样判断效率很低，非常耗时。

意向锁就是为了解决上述的问题。意向锁是表级别的锁，上述过程变成了：

  1. 在 事务A 在更新数据添加行锁之前，会在表级别由数据库自动添加一个IX锁。
  2. 当 事务B 在需要获取 X锁 时，只需要检查表级别是否有 IX锁，如果有 IX锁 代表当前有其他事务正在对表或者表中数据执行写操作，不能加锁成功。

意向锁为自动添加

锁的颗粒度：在给定的资源上，锁定的数据量越少，则系统的并发程度越高，如果系统花费大量的时间来管理锁，而不是存取数据，那么系统的性能可能会因此受到影响。

怎么加读锁和写锁 ★★★

读锁和写锁均阻塞不了 快照读。

写锁

语法：SELECT … FOR UPDATE

加锁之后另一个线程只可以 快照读，不可以修改。

  • 若一个线程 FOR UPDATE 执行锁住某行数据，另一个线程读取的时候，sql 里没有 FOR SHARE，则可以正常读取，因为没有 FOR SHARE 是快照读（快照读不需要加锁）。
  • FOR UPDATE 如果使用索引字段（一般为主键）进行筛选，则获取的是行级别锁，只会锁定筛选出的数据，如：SELECT * FROM tb WHERE ai=2 FOR UPDATE。
  • FOR UPDATE 没有使用索引字段，则获取的是表锁。
1
2
3
4
5
6
7
8
-- 进程1
SET AUTOCOMMIT;
SELECT * FROM tb FOR UPDATE ;

-- 进程2：
SELECT * FROM tb;  --不会阻塞
INSERT INTO tb(myname, id) VALUES("1", 200);  -- 阻塞
UPDATE tb SET myname="2"  -- 阻塞

读锁

语法：SELECT … FOR SHARE 和 SELECT … LOCK IN SHARE MODE。FOR SHARE 是 LOCK IN SHARE MODE 的替代品，但是 LOCK IN SHARE MODE 仍然可以向后兼容。

上锁之后，另一个线程可以读，但不能修改。

InnoDB 有哪几类行锁

https://blog.csdn.net/qq_53267860/article/details/125338623

InnoDB 支持三种行锁定方式：

  • 记录锁（Record Lock）：记录锁只存在于使用唯一索引和主键索引中，锁定单条索引记录。

    • 例如：SELECT * FROM table WHERE id = 1 FOR UPDATE; id 为 1 的记录行会被锁住。
    • 需要注意的是：id 列必须为唯一索引列或主键列，否则上述语句加的锁就会变成临键锁。同时查询语句必须为精准匹配（=），不能为 >、<、like 等，否则也会退化成临键锁。

  • 间隙锁（Gap Lock）：锁定一个范围，不包括记录本身。间隙锁存在于非唯一索引和主键索引中，锁定开区间范围内的一段间隔，它是基于临键锁实现的。

    • 例如：SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE; 所有在（1，10）区间内的记录行都会被锁住，所有 id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞，但是 1 和 10 两条记录行并不会被锁住。

  • 临键锁（Next-key Lock）：其实就是 Record Lock+Gap Lock，锁定一个范围，包含记录本身。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

InnoDB 的默认隔离级别 REPEATABLE-READ（可重读）是可以解决幻读问题发生的，主要有下面两种情况：

  • 快照读：由 MVCC 机制来保证不出现幻读。
  • 当前读：使用 Next-Key Lock 进行加锁来保证不出现幻读。

索引与锁有什么关系

InnoDB

  • 对于 UPDATE、DELETE、INSERT 语句，InnoDB 会自动给涉及到的数据集加写锁

  • 对于 SELECT 语句，InnoDB 默认不会加任何锁类型，我们可以手动加锁，如使用 SELECT … FOR UPDATE 加写锁，使用 SELECT … FOR SHARE 或 SELECT … LOCK IN SHARE MODE 加读锁。

加过排他锁（写锁）的数据行，其他事务中是不能修改其数据的，也不能通过 FOR UPDATE 和 LOCK IN SHARE MODE 锁的方式查询数据；但可以直接通过 SELECT … FROM 查询数据，因为普通查询是快照读，没有任何锁机制。

索引和锁的关系：

  1. InnoDB 的行锁是通过给索引的索引项加锁来实现的。

    • 唯一索引、主键索引
      • 对于指定查询某一条记录的加锁语句，如果该记录不存在，会产生记录锁和间隙锁，如果记录存在，则只会产生记录锁，如：WHERE id=5 FOR UPDATE;
      • 对于查找某一范围内的查询语句，会产生间隙锁，如：WHERE id BETWEEN 5 AND 7 FOR UPDATE;
    • 普通索引
      • 在普通索引列上，不管是何种查询，只要加锁，都会产生间隙锁或者临键锁，这跟唯一索引不一样。
        1. 命中记录时（包括>=和between），锁记录所在索引区间和前一个索引区间（临键锁）
        2. 未命中记录时，锁记录所在索引区间（间隙锁）
      • 在普通索引跟唯一索引中，数据间隙的分析，数据行是优先根据普通索引排序，再根据唯一索引排序。

  2. 不通过索引条件查询时，InnoDB 一定会使用表锁（因为没有索引只能全表扫描）。

  3. 即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查 SQL 的执行计划，以确认是否真正使用了索引。

MyISAM

  • MyISAM 在执行查询语句 SELECT 前，会自动给涉及的所有表加 读锁，
  • MyISAM 在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加 写锁，这个过程并不需要用户干预。

自增锁有了解吗？

不太重要的一个知识点，简单了解即可。

关系型数据库设计表的时候，通常会有一列作为自增主键。InnoDB 中的自增主键会涉及一种比较特殊的表级锁— 自增锁（AUTO-INC Locks） 。

1
2
3
4
5
6
CREATE TABLE `sequence_id` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `stub` CHAR(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `stub` (`stub`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

更准确点来说，不仅仅是自增主键，AUTO_INCREMENT 的列都会涉及到自增锁，毕竟非主键也可以设置自增长。

如果一个事务正在插入数据到有自增列的表时，会先获取自增锁，拿不到就可能会被阻塞住。这里的阻塞行为只是自增锁行为的其中一种，可以理解为自增锁就是一个接口，其具体的实现有多种。具体的配置项为 innodb_autoinc_lock_mode （MySQL 5.1.22 引入），可以选择的值如下：

innodb_autoinc_lock_mode 介绍
0 传统模式
1 连续模式（MySQL 8.0 之前默认）
2 交错模式（MySQL 8.0 之后默认）

交错模式下，所有的“INSERT-LIKE”语句（所有的插入语句，包括：INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT、LOAD DATA等）都不使用表级锁，使用的是轻量级互斥锁实现，多条插入语句可以并发执行，速度更快，扩展性也更好。

不过，如果你的 MySQL 数据库有主从同步需求并且 Binlog 存储格式为 Statement 的话，不要将 InnoDB 自增锁模式设置为交叉模式，不然会有数据不一致性问题。这是因为并发情况下插入语句的执行顺序就无法得到保障。

如果 MySQL 采用的格式为 Statement ，那么 MySQL 的主从同步实际上同步的就是一条一条的 SQL 语句。

最后，再推荐一篇文章：为什么 MySQL 的自增主键不单调也不连续 。

MySQL
#MySQL
MySQL 锁
https://flepeng.github.io/041-MySQL-41-底层原理-MySQL-锁/
作者
Lepeng
发布于
2020年8月8日
许可协议