0%

MySQL 锁介绍

简介

当多个事务或者进程访问同一个资源的时候, 为了保证数据的一致性, 就需要用到锁机制

从锁定资源的角度来看, MySQL 中的锁包括

  • 表级锁: 对整张表加锁, 开销小, 加锁快;不会出现死锁;锁定粒度大, 发生锁冲突的概率最高, 并发度最低
  • 行级锁: 对某行记录加锁, 开销大, 加锁慢;会出现死锁;锁定粒度最小, 发生锁冲突的概率最低, 并发度也最高
  • 页面锁: 开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间, 并发度一般

MyISAM 存储引擎

MyISAM 存储引擎支持表级锁, 并且支持两种锁模式:

  • 对 MyISAM 表的读操作(共享锁), 不会阻塞其他进程对同一表的读请求, 但会阻塞对其的写请求, 当读锁释放后, 才会执行其他进程的写操作
  • 对 MyISAM 表的写操作(排他锁), 会阻塞其他进程对同一表的读写操作, 当该锁释放后, 才会执行其他进程的读写操作

在使用 MyISAM 存储引擎时, 执行 SQL 语句, 会自动为 SELECT 语句加上共享锁, 为 UDI(更新, 删除, 插入)操作加上排他锁

由于这个特性在多进程并发插入同一张表的时候, 就会因为排他锁而进行等待

因此可以通过配置 concurrent_insert 系统变量, 来控制其并发的插入行为

  • concurrent_insert=0 时, 不允许并发插入
  • concurrent_insert=1 时, 如果 MyISAM 表中没有空洞(即表中没有被删除的行), 允许一个进程读表时, 另一个进程向表的尾部插入记录(MySQL 默认设置)
  • concurrent_insert=2 时, 无论 MyISAM 表中有没有空洞, 都允许在表尾并发插入记录

注: 空洞是行记录被删除以后, 只是被标记为 “已删除” 其存储空间没有被回收, 也就是说没有被物理删除, 由另外一个进程, 异步对这个数据进行删除, 因为空间长度问题, 删除以后的物理空间不能被新的记录所使用, 从而形成了空洞

如果在数据插入的时候, 没有并发删除操作的话, 可以尝试把 concurrent_insert 设置为 1

反之, 在数据插入的时候有删除操作且量较大时, 也就是会产生 “空洞” 的时候, 就需要把 concurrent_insert 设置为 2

另外, 当一个进程请求某个 MyISAM 表的读锁, 另一个进程也请求同一表的写锁

即使读请求先到达, 写请求后到达, 写请求也会插到读请求之前, 因为 MySQL 的默认设置认为, 写请求比读请求重要

可以通过 low_priority_updates 来调节读写行为的优先级:

  • 数据库以读为主时, 要优先保证查询性能时, 可通过 low_priority_updates=1 设置读优先级高于写优先级
  • 数据库以写为主时, 则不用设置 low_priority_updates 参数

InnoDB 存储引擎

表级锁

当没有对数据表中的索引数据进行查询时, 会执行表锁操作

意向锁 (Intention Locks)

意向锁产生的主要目的是为了处理行锁和表锁之间的冲突, 用于表明 “某个事务正在某一行上持有了锁, 或者准备去持有锁”

  • 意向共享锁 (IS): 事务打算给数据行加行共享锁, 事务在给一个数据行加共享锁前, 必须先取得该表的 IS 锁
  • 意向排他锁 (IX): 事务打算给数据行加行排他锁, 事务在给一个数据行加排他锁前, 必须先取得该表的 IX 锁

注: 意向共享锁和意向排他锁是数据库主动加的, 不需要我们手动处理, 对于 UPDATE, DELETE 和 INSERT 语句, InnoDB 会自动给数据集加排他锁

如果表 test 没有主键和任何索引

1
2
3
4
CREATE TABLE `test` (
`ID` varchar(36) NOT NULL DEFAULT '0'
`NAME` varchar(16) NOT NULL
) ENGINE=InnoDB;

当只对一行添加排他锁时, InnoDB 使用的是表锁

1
SELECT * FROM test WHERE ID = '1' FOR UPDATE;

在另外一个会话中执行

1
SELECT * FROM test WHERE ID = '2' FOR UPDATE;

将会出现 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 错误

当为 ID 添加索引后就可以使用行锁了

行级锁

当数据查询时针对索引数据进行时, 会使用行级锁

InnoDB 行锁是通过给索引上的索引项加锁来实现的, 这一点 MySQL 与 Oracle 不同, 后者是通过在数据块中对相应数据行加锁来实现的

基本锁

共享锁 (Shared Locks: S 锁) 与排他锁 (Exclusive Locks: X 锁)

  • 共享锁 (Shared Locks: S 锁): 当一个事务读取一条记录的时候, 不会阻塞其他事务对同一记录的读请求, 但会阻塞对其的写请求, 当读锁释放后, 才会执行其他事务的写操作, 例如: select ... lock in share mode
  • 排他锁 (Exclusive Locks: X 锁): 当一个事务对一条记录进行写操作时, 会阻塞其他事务对同一表的读写操作, 当该锁释放后, 才会执行其他事务的读写操作, 例如: select ... for update

事务在请求 S 锁和 X 锁前, 需要先获得对应的 IS, IX 锁

mysql 允许拿到 S 锁的事务读一行, 允许拿到 X 锁的事务更新或删除一行

  • 加了 S 锁的记录, 允许其他事务再加 S 锁, 不允许其他事务再加 X 锁
  • 加了 X 锁的记录, 不允许其他事务再加 S 锁或者 X 锁

在高并发的情况下, 多个事务同时请求更新数据, 由于资源被占用等待事务增多, 会造成性能问题, 通过配置 innodb_lock_wait_timeout 设置适当的事务等待获取资源的最长时间, 单位为秒, 如果超过时间还未分配到资源, 则会返回应用失败

共享锁, 排他锁与意向锁兼容情况

注意: 一个事务请求的锁模式与当前的锁兼容, InnoDB 就将请求的锁授予该事务, 如果两者不兼容, 该事务就要等待锁释放

X IX S IS
X 冲突 冲突 冲突 冲突
IX 冲突 兼容 冲突 兼容
S 冲突 冲突 兼容 兼容
IS 冲突 兼容 兼容 兼容

注意: 横向是已经持有的锁, 纵向是正在请求的锁

记录锁 (Record Locks)

记录锁, 仅仅锁住索引记录的一行

单条索引记录上加锁, record lock 锁住的永远是索引, 而非记录本身, 即使该表上没有任何索引, 那么 innodb 会在后台创建一个隐藏的聚集主键索引, 那么锁住的就是这个隐藏的聚集主键索引, 所以说当一条 sql 没有走任何索引时, 那么将会在每一条聚集索引后面加 X 锁, 这个类似于表锁

间隙锁 (Gap Locks)

区间锁, 仅仅锁住一个索引区间(开区间)

在索引记录之间的间隙中加锁, 或者是在某一条索引记录之前或者之后加锁, 并不包括该索引记录本身

next-key 锁 (Next-Key Locks)

record lock + gap lock, 左开右闭区间

默认情况下, innodb 使用 next-key locks 来锁定记录

但当查询的索引含有唯一属性的时候, Next-Key Lock 会进行优化, 将其降级为 Record Lock, 即仅锁住索引本身, 不是范围

插入意向锁 (Insert Intention Locks)

Gap Lock 中存在一种插入意向锁(Insert Intention Lock), 在 insert 操作时产生, 在多事务同时写入不同数据至同一索引间隙的时候, 并不需要等待其他事务完成, 不会发生锁等待

假设有一个记录索引包含键值 4 和 7, 不同的事务分别插入 5 和 6, 每个事务都会产生一个加在 4-7 之间的插入意向锁, 获取在插入行上的排它锁, 但是不会被互相锁住, 因为数据行并不冲突

记录锁, 间隙锁, next-key 锁, 插入意向锁兼容情况

Gap Insert Intention Record Next-Key
Gap 兼容 兼容 兼容 兼容
Insert Intention 冲突 兼容 兼容 冲突
Record 兼容 兼容 冲突 冲突
Next-Key 兼容 兼容 冲突 冲突

注意: 横向是已经持有的锁, 纵向是正在请求的锁

自增锁 (AUTO-INC Locks)

AUTO-INC 锁是一种特殊的表级锁, 发生涉及 AUTO_INCREMENT 列的事务性插入操作时产生

多版本并发控制

MVCC 是行级锁的一个变种, 但是它在很多情况下避免了加锁操作, 因此开销更低, 虽然实现机制有所不同, 但大都实现了非阻塞的读操作, 写操作也只锁定必要的行

MVCC 的实现, 是通过保存数据在某个时间点的快照来实现的, 也就是说, 不管需要执行多长时间, 每个事务看到的数据都是一致的, 根据事务开始的时间不同, 每个事务对同一张表, 同一时刻看到的数据可能是不一样的

InnoDB 的 MVCC, 是通过在每行记录后面保存两个隐藏的列来实现的, 这两个列, 一个保存了行的创建时间, 一个保存行的过期时间(或删除时间), 当然存储的并不是实际的时间值, 而是系统版本号(system version number), 每开始一个新的事务, 系统版本号都会自动递增, 事务开始时刻的系统版本号会作为事务的版本号, 用来和查询到的每行记录的版本号进行比较

MVCC 只在 REPEATABLE READ 和 READ COMMITTED 两个隔离级别下工作, 其他两个隔离级别都和 MVCC 不兼容, 因为 READ UNCOMMITTED 总是读取最新的数据行, 而不是符合当前事务版本的数据行, 而 SERIALIZABLE 则会对所有读取的行都加锁

在 REPEATABLE READ 隔离级别下, MVCC 具体操作

  • SELECT: InnoDB 会根据以下两个条件检查每行记录, 只有符合上述两个条件的记录, 才能返回作为查询结果
    • InnoDB 只查找版本早于当前事务版本的数据行(也就是, 行的系统版本号小于或等于事务的系统版本号), 这样可以确保事务读取的行, 要么是在事务开始前已经存在的, 要么是事务自身插入或者修改过的
    • 行的删除版本要么未定义, 要么大于当前事务版本号, 这可以确保事务读取到的行, 在事务开始之前未被删除
  • INSERT: InnoDB 为新插入的每一行保存当前系统版本号作为行版本号
  • DELETE: InnoDB 为删除的每一行保存当前系统版本号作为行删除标识
  • UPDATE: InnoDB 为插入一行新记录, 保存当前系统版本号作为行版本号, 同时保存当前系统版本号到原来的行作为行删除标识

保存这两个额外系统版本号, 使大多数读操作都可以不用加锁

  • 优点: 读数据操作简单, 性能很好, 并且也能保证只会读取到符合标准的行
  • 缺点: 每行记录都需要额外的存储空间, 需要做更多的行检查工作, 以及一些额外的维护工作

小结

  • 在不通过索引条件查询的时候, InnoDB 使用的是表锁
  • 由于 MySQL 的行锁是针对索引加的锁, 不是针对记录加的锁, 所以虽然是访问不同行的记录, 但是如果是使用相同的索引键, 是会出现锁冲突的, 应用设计的时候要注意这一点
  • 当表有多个索引的时候, 不同的事务可以使用不同的索引锁定不同的行, 另外, 不论是使用主键索引, 唯一索引或普通索引, InnoDB 都会使用行锁来对数据加锁
  • 即便在条件中使用了索引字段, 但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的, 如果 MySQL 认为全表扫描效率更高, 比如对一些很小的表, 它就不会使用索引, 这种情况下 InnoDB 将使用表锁, 而不是行锁, 因此, 在分析锁冲突时, 别忘了检查 SQL 的执行计划, 以确认是否真正使用了索引

参考