本章对行锁表所、共享锁排他锁进行详细说明。这是数据库锁的核心知识。

MySQL中几种重要的锁概念

共享锁（S）和 排他锁（X）

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

  • 共享锁允许持锁事务读取一行
  • 排它锁允许持锁事务更新或者删除一行

如果事务 T1 持有行 r 的 s 锁，那么另一个事务 T2 请求 r行 的锁时，会做如下处理：

  • T2 请求 s 锁立即被允许，结果 T1 T2 都持有 r 行的 s 锁
  • T2 请求 x 锁不能被立即允许

如果 T1 持有 r 的 x 锁，那么 T2 请求 r 的 x、s 锁都不能被立即允许，T2 必须等待T1释放 x 锁才行。

注意：排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。mysql InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select ...for update语句，加共享锁可以使用select ... lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select ...from...查询数据，因为普通查询没有任何锁机制。

意向锁

innodb的意向锁主要用户多粒度的锁并存的情况。比如事务A要在一个表上加S锁，如果表中的一行已被事务B加了X锁，那么该锁的申请也应被阻塞。如果表中的数据很多，逐行检查锁标志的开销将很大，系统的性能将会受到影响。为了解决这个问题，可以在表级上引入新的锁类型来表示其所属行的加锁情况，这就引出了“意向锁”的概念。

举个例子，如果表中记录1亿，事务A把其中有几条记录上了行锁了，这时事务B需要给这个表加表级锁，如果没有意向锁的话，那就要去表中查找这一亿条记录是否上锁了。如果存在意向锁，那么假如事务Ａ在更新一条记录之前，先加意向锁，再加Ｘ锁，事务B先检查该表上是否存在意向锁，存在的意向锁是否与自己准备加的锁冲突，如果有冲突，则等待直到事务Ａ释放，而无须逐条记录去检测。事务Ｂ更新表时，其实无须知道到底哪一行被锁了，它只要知道反正有一行被锁了就行了。

说白了意向锁的主要作用是处理行锁和表锁之间的矛盾，能够显示“某个事务正在某一行上持有了锁，或者准备去持有锁”

  • 意向排它锁（简称 IX 锁）表明一个事务意图在某个表中设置某些行的 x 锁
  • 意向共享锁（简称 IS 锁）表明一个事务意图在某个表中设置某些行的 s 锁

例如， SELECT ... LOCK IN SHARE MODE 设置一个 IS 锁, SELECT ... FOR UPDATE 设置一个 IX 锁。

意向锁的原则如下：

  • 一个事务必须先持有该表上的 IS 或者更强的锁才能持有该表中某行的 S 锁
  • 一个事务必须先持有该表上的 IX 锁才能持有该表中某行的 X 锁
next-key锁

InnoDB有三种行锁的算法：

  • Record Lock：单个行记录上的锁。分为S Lock和X Lock
  • Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。
  • Next-Key Lock：1+2，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

在默认情况下，mysql的事务隔离级别是可重复读，并且innodb_locks_unsafe_for_binlog参数为0，这时默认采用next-key locks。所谓Next-Key Locks，就是Record lock和gap lock的结合，即除了锁住记录本身，还要再锁住索引之间的间隙。

例子：假设一个索引包含值 10,11,13和20，索引上可能的NK 锁包括如下几个区间（注意开闭区间）

1
2
3
4
5
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

Innodb使用NK 锁来进行索引搜索和扫描，阻止了幻读。

间隙锁在Innodb中是被“十足的抑制”的，也就是说，他们只阻止其他事务插入到间隙中，他们不阻止其他事物在同一个间隙上获得间隙锁。

下篇文章会详细介绍一下。

MyISAM和InnoDB关于锁方面的区别

结论：

  • MyISAM默认使用的是表级锁，不支持行级锁
  • InnoDB默认使用的是行级锁，也支持表级锁

所谓表级锁，就是锁住整张表。开销小，加锁快；不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。

MyISAM在执行select的时候会产生一个表共享读锁，当进行更新等操作的时候会产生表独占写锁（排他锁）。所以：

  • myISAM表的读操作，不会阻塞其他用户对同一个表的读请求，但会阻塞对同一个表的写请求。
  • myISAM表的写操作，会阻塞其他用户对同一个表的读和写操作。
  • myISAM表的读、写操作之间、以及写操作之间是串行的。

这里的读是共享锁，也可以将其变为排他锁，语法是select … for update

上面说完了MyISAM的表锁，下面要说说InnoDB啦。InnoDB支持行级锁。

所谓行级锁，就是锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发性也最高。

SELECT … LOCK IN SHARE MODE 和 SELECT … FOR UPDATE

如果你在查询数据，然后在同一个事务里插入或者修改相关的数据，常规的 select 语句不会提供足够的保护。其他的事务可以修改或者删除你正在查询的行。InnoDB 支持两种可以提供安全机制的读取锁：

  • SELECT ... LOCK IN SHARE MODE
  • SELECT ... FOR UPDATE

SELECT … LOCK IN SHARE MODE 在读取的行上设置一个共享锁，其他的session可以读这些行，但在你的事务提交之前不可以修改它们。如果这些行里有被其他的还没有提交的事务修改，你的查询会等到那个事务结束之后使用最新的值。

索引搜索遇到的记录，SELECT … FOR UPDATE 会锁住行及任何关联的索引条目，和你对那些行执行 update 语句相同。其他的事务会被阻塞在比如执行 update 操作，获取共享锁，或从某些事务隔离级别读取数据等操作。

使用 SELECT FOR UPDATE 为 update 操作锁定行，只适用于 autocommit 被禁用（当使用 START TRANSACTION 开始事务或者设置 autocommit 为0时）。如果 autocommit 已启用，符合规范的行不会被锁定。

以上是对官方文档的翻译解读。

SELECT … LOCK IN SHARE MODE ：共享锁(S锁, share locks)。其他事务可以读取数据，但不能对该数据进行修改，直到所有的共享锁被释放。

如果事务对某行数据加上共享锁之后，可进行读写操作；其他事务可以对该数据加共享锁，但不能加排他锁，且只能读数据，不能修改数据。

SELECT … FOR UPDATE：排他锁(X锁, exclusive locks)。如果事务对数据加上排他锁之后，则其他事务不能对该数据加任何的锁。获取排他锁的事务既能读取数据，也能修改数据。

注：普通 select 语句默认不加锁，而CUD操作默认加排他锁。

  • 当前事务获取共享锁后，可以读写，其他事务是否可以进行读写操作和获取共享锁：可以读，可以获取共享锁，不可以写
  • 两个事务同时获取共享锁后，是否可以进行update操作：不可以
  • 当前事务获取排他锁后，其他事务是否可以进行读写操作和获取共享锁：其他事务可以读，不可以获取共享锁，不可以写
  • 是否可对一条数据加多个排他锁：不可以
  • 行锁和索引的关系：查询字段未加索引（主键索引、普通索引等）时，使用表锁

注：InnoDB行级锁基于索引实现。

  • 未加索引时，两种行锁情况为（使用表锁）：

    • 事务1获取某行数据共享锁，其他事务可以获取不同行数据的共享锁，不可以获取不同行数据的排他锁
    • 事务1获取某行数据排他锁，其他事务不可以获取不同行数据的共享锁、排他锁

  • 加索引后，两种行锁为（使用行锁）：

    • 事务1获取某行数据共享锁，其他事务可以获取不同行数据的排他锁
    • 事务1获取某行数据排他锁，其他事务可以获取不同行数据的共享锁、排他锁
  • 索引数据重复率太高会导致全表扫描：当表中索引字段数据重复率太高，则MySQL可能会忽略索引，进行全表扫描，此时使用表锁。可使用 force index 强制使用索引。

总结（很重要）

  • MyISAM默认使用的是表级锁，不支持行级锁
    • 执行select的时候会产生一个表共享读锁
    • 当进行更新等操作的时候会产生表独占写锁（排他锁）
    • 读不会阻塞其他session的读以及获取表共享读锁
    • 写会阻塞其他session读和写操作
    • 写与读之间是串行的
  • InnoDB默认使用的是行级锁，也支持表级锁
    • InnoDB 支持两种可以提供安全机制的读取锁：SELECT … LOCK IN SHARE MODE以及SELECT … FOR UPDATE
    • SELECT … LOCK IN SHARE MODE 在读取的行上设置一个共享锁
    • SELECT … FOR UPDATE：排他锁
    • 一个session对某一行上共享锁，其他的session可以读这行，也可以获取共享锁，但是不允许写，更不允许获取写锁。对于其他行，可以读写其他行数据也可以上读写锁。
    • 一个session对某一行上排他锁，其他的session则不能加任何锁，包括共享锁。允许读这一行，但是不能写。允许对其他行数据进行读写以及上读写锁。
    • InnoDB中行级锁基于索引实现，所以在不加索引的时候，这两者上的其实都是表锁；加上索引之后，使用行锁。

以上的内容都是从博客：https://blog.csdn.net/u012099869/article/details/52778728 中整理而来，具体的实验也在他的博客中进行了详细的展示。

MyISAM适合场景

  • 频繁执行全表count语句(MyISAM已经用一个表保存了行数)
  • 对数据进行增删改的频率不高，查询非常频繁
  • 没有事务

InnoDB适合场景

  • 数据增删改查都相当频繁
  • 可靠性要求比较高，要求支持事务