MySQL 锁信息

MySQL
发布日期:   2021-07-26
更新日期:   2024-10-10
文章字数:   7.1k
阅读时长:   26 分
阅读次数:  

1 MySQL 锁定义

MySQL 锁 (Lock) 是数据库管理系统用于管理并发访问的一种机制。
在多用户同时访问数据库的环境下，可能会出现多个事务同时对相同的数据进行读取或写入操作，为了保证数据的一致性和完整性，需要使用锁来控制对数据的访问。

在并发环境下，当多个事务试图同时访问相同的数据时，锁可以确保只有一个事务能够成功地获取对数据的访问权限，其他事务需要等待或被阻塞，直到获得相应的锁为止。
锁的主要作用是防止多个事务同时对同一数据进行不一致的修改，从而避免数据的丢失、错误或混乱。

总的来说，MySQL 锁是用于管理并发访问的机制，通过控制对数据的访问权限，确保多个事务之间不会发生冲突，从而维护数据的一致性和完整性。

2 锁的一些概念

2.1 行级锁和表级锁

一般情况下, 数据库都会支持 2 种不同的锁方式

  1. 表锁, 直接锁住整张表数据
  2. 行锁, 锁住表里面的特定行数据

区别

  1. 从锁定粒度上看, 表锁肯定是大于行锁的
  2. 从加锁效率上看, 表锁快于行锁。表锁只需要直接锁住这张表就行了, 而行锁, 还需要在表里面去检索这一行数据
  3. 从冲突的概率上看, 表锁大于行锁。在表锁上, 其他任何一个事务都不能操作这张表, 但是行锁, 其他的事务还可以来操作表里面其他行的数据

注: MyISAM 只支持表级锁。

2.2 共享锁和排他锁

共享锁, 也可以叫做读锁。它允许多个事务同时对相同的资源 (通常是数据库中的某个数据) 进行读取操作，而不允许任何事务在同一时间内对该资源进行写入操作。
共享锁通常用于实现读取的并发性，多个事务可以同时读取同一份数据而不会相互干扰。

排他锁, 也可以叫做写锁。它用于确保在某一时刻只有一个事务能够对资源进行写入操作。
当一个事务获得了排他锁，其他事务无法同时获得相同资源的共享锁或排他锁，从而阻止了其他事务对该资源的读取或写入操作。

行级锁和表级锁 + 共享锁和排他锁之间两两配合, 基本就构成了整个 MySQL 所有锁的形式

  1. 共享行锁
  2. 排他行锁
  3. 共享表锁
  4. 排他表锁

3 MySQL InnoDB 锁的基本类型

官网把 InnoDB 的锁分成了 8 大类

  1. Shared and Exclusive Locks
  2. Intention Locks
  3. Record Locks
  4. Gap Locks
  5. Next-Key Locks
  6. Insert Intention Locks
  7. AUTO-INC Locks
  8. Predicate Locks for Spatial Indexes

备注: 下面锁的分析场景都需要关闭自动提交, 否则每一条语句执行完就自动提交, 事务就结束了, 达不到验证的效果,
可以在同一个 session 中执行 set autocommit = 0;, 临时关闭自动提交。

3.1 Shared and Exclusive Locks

共享和排他锁。

Shared Locks (共享锁, 简称 S 锁), 可以细分为 共享行锁 + 共享表锁, 主要用于支持多个事务同时读取相同的数据, 并发读取, 但阻止对同一数据的写入操作。
注意不要在加上了读锁以后去写数据, 不然的话可能会出现死锁的情况。
共享锁可以用 select …… lock in share mode 的方式手动加共享锁, 只要事务结束, 锁就会自动释放。

Exclusive Locks (排他锁, 简称 X 锁), 同样可以细分为 排他行锁 + 排他表锁, 主要用于确保在某一时刻只有一个事务能够对数据进行写入操作，独占写入, 阻止其他事务对同一数据进行读取或写入。

排他锁的加锁方式有三种

  1. 自动加排他锁, 在操作数据的时候, 包括增删改, 都会默认加上一个排他锁
  2. 手工加排他锁, 可以用一个 select …… for update 给一行数据 (表) 加上一个排他锁, 其他事务操作相同资源时, 进行阻塞
  3. 手工加排他锁, 可以用一个 select …… for update nowait 给一行数据 (表), 加上一个排他锁, 其他事务操作相同资源时, 直接拒绝

3.2 行锁的原理

假设当前有一张表 t1, 有 2 个字段:

字段名 字段类型 备注
id int
name varchar

同时, 表中已经有 2 条数据:

id name
1 one
2 two

场景一

假设当前表 t1 2 个字段的设置如下:

字段 类型 备注
id int 没有任何的索引
name varchar 没有任何的索引
  1. 事务 A : select * from t1 where id = 1 for update;, 看起来是锁住 id = 1 的行
  2. 事务 B : select * from t1 where id = 2 for update;, 这时候加锁失败, 阻塞住了
  3. 事务 C : insert into t1 values(3, 'three'), 这时候插入失败, 同样阻塞住了

通过上面的情景可以得到:

分析

  1. 表中的所有字段都没有索引
  2. 这时事务 A 对 id = 1 的记录加了锁
  3. 事务 B 尝试对 id = 2 的记录加锁失败
  4. 事务 C 尝试向表新增一条记录, 也失败

做个猜想: InnoDB 的行锁的依赖索引实现的

场景二

假设当前表 t1 2 个字段的设置如下:

字段 类型 备注
id int 主键索引
name varchar 没有任何的索引

现在表中已经有 2 条数据。

  1. 事务 A : select * from t1 where id = 1 for update;, 锁住 id = 1 的行
  2. 事务 B : select * from t1 where id = 1 for update;, 尝试锁住 id = 1 的行失败, 阻塞住
  3. 事务 C : select * from t1 where id = 2 for update;, 尝试锁住 id = 2 的行, 成功
  4. 事务 D : insert into t1 values(3, 'three'), 新增记录成功

分析

  1. 表中的 id 为主键
  2. 这时事务 A 对 id = 1 的记录加了锁
  3. 事务 B 尝试对 id = 1 的记录加锁失败
  4. 事务 C 尝试对 id = 2 的记录加锁成功
  5. 事务 D 尝试向表新增一条记录, 也失败

场景一和场景二表的区别就是 id 是否为主键
做个推测: InnoDB 的行锁的实现是通过主键索引实现的

场景三

假设当前表 t1 2 个字段的设置如下:

字段 类型 备注
id int 主键索引
name varchar 唯一索引
  1. 事务 A : select * from t1 where name = 'one' for update;, 锁住 name = one 的行
  2. 事务 B : select * from t1 where name = 'one' for update;, 尝试锁住 name = one 的行失败, 阻塞住
  3. 事务 C : select * from t1 where id = 1 for update;, 尝试锁住 id = 1 的行失败, 阻塞住
  4. 事务 D : select * from t1 where id = 2 for update;, 尝试锁住 id = 2 的行, 成功

可以得出一个推测: InnoDB 的行锁, 既不是锁行记录, 也不是锁字段, 而是就是通过锁住索引来实现的。

备注: 上面整个过程实际是从结论推导过程, 所以不必太过深究, 只是为了验证 InnoDB 的行锁是通过锁索引实现的。

下面做个总结:

在表中没有索引的时候, InnoDB 是如何加锁的

索引的定义

  1. 如果我们定义了主键 (PRIMARY KEY), 那么 InnoDB 会选择主键作为聚集索引
  2. 如果没有显式定义主键, 则 InnoDB 会选择第一个不包含有 NULL 值的唯一索引作为主键索引
  3. 如果也没有这样的唯一索引, 则 InnoDB 会选择内置 6 字节长的 ROWID 作为隐藏的聚集索引, 它会随着行记录的写入而主键递增

所以 InnoDB 在没有任何索引时, 会通过内置的 ROWID 作为索引, 然后借助锁住这个索引达到加锁效果

在表中没有索引的时候, 查询一行记录, 为什么也会导致锁全表

上面知道, 没有任何索引时, InnoDB 通过锁住内置的 ROWID 索引达到加锁效果。
但是在查询时, 查询条件的字段和 ROWID 之间如何对应呢?
比如条件 id = 1, 那么他对应的 ROWID 是多少呢? InnoDB 无法确定, 所以最保险的做法就是整张表锁住。

所以查询没有使用索引, 会进行全表扫描, 然后把每一个隐藏的聚集索引都锁住了。

为什么通过唯一索引给数据行加锁, 主键索引也会被锁住

通过辅助索引进行查询数据时, 先在辅助索引里面找到对应的主键的值, 然后再在主键索引里面查询数据。
所以我们通过辅助索引锁定一行数据的时候, 它跟我们检索数据的步骤是一样的, 会通过主键值找到主键索引, 然后锁定。

表锁什么时候使用
当一张表加锁时, 无法通过行锁锁住, 那么就会晋升为表锁, 也就是行锁无法锁住资源, 就直接通过表锁实现。

3.3 意向锁

3.3.1 介绍

意向锁, 重点其实是意向, 有趋势但是还未做。
所以意向锁实际上是没有真正给表或者表中的行进行加锁操作, 而更像是给表打了一个标识, 表示有锁的意思。

意向锁是由数据库自己维护的 (非存储引擎维护), 同时 2 个意向锁都是表级别的锁

  1. 给一行数据加上共享锁之前, 数据库会自动在这张表上面加一个意向共享锁 (Intention Sharded Lock, 简称 IS 锁)
  2. 给一行数据加上排他锁之前, 数据库会自动在这张表上面加一个意向排他锁 (Intention Exclusive Lock, 简称 IX 锁)

也就是说:

  1. 如果一张表上面至少有一个意向共享锁, 说明有其他的事务给表中的某些数据行加上了共享锁
  2. 如果一张表上面至少有一个意向排他锁, 说明有其他的事务给表中的某些数据行加上了排他锁

3.3.2 特点

意向锁的特点:

  1. 2 个意向锁之间不像共享和排他锁是互斥的, 他们之间是互相兼容的
  2. 2 个意向锁都是表级别锁, 所以只会影响到表级别的共享锁和排他锁, 对行级别的共享锁和排他锁无影响

举个例子吧
假设现在有一张表 t1, 数据如下

id name
1 one
2 two
3 three

情况一, id 是主键

  1. 事务 A : select * from t1 where id = 1 for update;, 这时表 t1 在行 id = 1 上有一个行级别排他锁, 同时表上有一个意向排他锁
  2. 事务 B : select * from t1 where id = 2 lock in share mode;, 尝试给 id = 2 加上行级别共享锁, 虽然表上已经有意向排他锁, 但是意向锁不互斥, 同时意向锁不影响行锁, 所以 2 个锁都加成功
  3. 事务 C : select * from t1 where id = 3 for update;, 尝试给 id = 3 加上行级别排他锁, 意向锁不互斥和意向锁不影响行锁, 所以还是加锁成功

可以发现

  1. 意向锁都是表级别的锁, 不会影响到行级别的锁
  2. 意向锁互不影响

情况二, id 是主键, name 没有任何索引

  1. 事务 A : select * from t1 where id = 1 for update;, 这时表 t1 在行 id = 1 上有一个行级别排他锁, 同时表上有一个意向排他锁
  2. 事务 B : select * from t1 where name = 'one' for update;, 没有使用索引, 需要加表级别排他锁, 但是发现上面已经有一个意向排他锁, 加锁失败, 阻塞
  3. 事务 C : select * from t1 where name = 'one' lock in share mode;, 没有使用索引, 需要加表级别共享锁, 但是发现上面已经有一个意向排他锁, 加锁失败, 阻塞

可以发现

  1. 事务 A 只是给表中的某一行加了一个行级别的排他锁, 同时给表加了一个意向排他锁
  2. 事务 B 试图给表加上表排他锁, 需要先确定表中没有其他锁, 在没有意向锁时, 需要遍历所有行确定这个结论, 但是现在有意向锁, 直接知道表中某些行有锁, 可以确定不能加了
  3. 事务 C 同理

各个锁之间的兼容关系如下

| | 共享锁 (S ) | 排他锁 (X) | 共享意向锁 (IS) | 排他意向锁 (IX) |
| :-: |:-: |:-: |:-: |
| 共享锁 (S ) | 兼容 | 排斥 | 兼容 | 排斥 |
| 排他锁 (X) | 排斥 | 排斥 | 排斥 | 排斥 |
| 共享意向锁 (IS) | 兼容 | 排斥 | 兼容 | 兼容 |
| 排他意向锁 (IX) | 排斥 | 排斥 | 兼容 | 兼容 |

3.3.3 意义

两个意向锁存在的意义

  1. 有了 2 个意向锁, 在 InnoDB 里面就可以支持更多粒度的锁, 特定场景下, 行级锁可以与表级锁共存 (共享锁和共享意向锁基本都是兼容的, 已经存在共享表锁时, 可以支持共享行锁)
  2. 可以把它理解成一个标志, 是用来提高加表锁的效率的。 比如要给一张表加表锁, 那么就需要判断是否有行锁, 先要判断有没其他的事务锁定了其中了某些行, 有了意向锁这个标志, 就可以不去遍历行

3.4 锁算法

Record Locks、Gap Locks、Next-Key Locks 虽然官方将他们分为锁的类型, 但是更形象的说法是锁的算法, 也就是在什么情况下锁定什么范围。

假设当前有一张表 t, 有 2 个字段:

  1. int 类型的 id, 是主键索引
  2. varchar 类型的 name

表中当前有 4 条数据, id 分别为 1, 4, 7, 10。

id name
1 1
4 4
7 7
10 10

3.4.1 几个名词的定义

Alt '记录'

表里面存在的数据的行, 就叫做 Record (记录), 当前 t 表有 4 个 Record。

Alt '间隙'

在表中, Record 和 Record 之间可能存在一段数据不存在的区间, 叫做 Gap (间隙), 它是一个左开右开的区间, 当前 t 表这里有 5 个 Gap。

Alt '临键'

间隙 (Gap) 连同右边的记录 (Record), 公共组成的区间, 叫做 Next-Key (临键), 它是一个左开右闭的区间, 当前 t 表这里有 4 个 Next-Key。

3.4.2 Record Lock - 记录锁

只对表中的某行记录加锁, 就是记录锁。

select * from t where id = 4 for update;
select * from t where id > 1 and id < 5 for update;

上面 2 个 SQL 都可以查询唯一一条记录: id = 4 的记录。
像这种通过唯一索引 (主键也属于唯一索引) 进行查询的语句, 同时查询结果明确只有 1 条记录的情况, 就是使用到了记录锁。
记录锁主要用于唯一性的索引 (包括唯一索引和主键索引), 非唯一性索引的只会出现间隙锁或临键锁。

记录锁是一个排他锁, 会阻塞其他事务对对应记录的所有相关操作。

3.4.3 Gap Lock - 间隙锁

在记录和记录之间存在着某些间隙, 对这些间隙进行加锁, 就是间隙锁。

特别说明:

  1. 间隙锁主要是阻塞插入 insert, 对于删改查都不会有影响, 查很容易理解, 删改, InnoDB 的 MVCC 机制可以保证数据在事务中的正常
  2. 相同的间隙锁之间不冲突, 2 个间隙锁的区间可以是包含关系, 也可以是部分重叠
  3. 间隙锁只在 RR 的隔离级别出现, 所以将隔离级别修改成别的, 可以关闭间隙锁功能
  4. 另一种关闭间隙锁的方式, 将 innodb_locks_unsafe_for_binlog 设置为 0, 这时候除了外键约束和唯一性检查会加间隙锁, 其他情况都不会用间隙锁

例子:

  1. 事务 A: select * from t where id = 6 for update;, 找不到, 导致 4 - 7 之间出现间隙锁
  2. 事务 B: insert into t ('id', 'name') values(5, '5'); 在 4 - 7 之间有间隙锁, 插入失败
  3. 事务 C: select * from t where id = 6 for update;, 查询结果为空, 没有被阻塞

3.4.4 Next-Key Lock - 临键锁

将记录和记录之间的间隙一起加锁, 就是临键锁。

特别说明:

  1. 临键锁就是 MySQL 里面默认的行锁算法, 等于间隙锁 + 记录锁
  2. 临键锁等于间隙锁 + 记录锁, 所以其锁住的范围和间隙锁是相同的, 只是间隙中命中的记录会变成记录锁, 比如查询条件为 7, 那么间隙锁的范围为 (4, 10), 命中记录 7, 最终变为 (4, 7] 和 (7, 10)
  3. 临键锁会进行退化: 通过等值查询匹配到一条记录, 退化为记录锁
  4. 临键锁会进行退化: 使用唯一性索引, 没有匹配到任何记录的时候, 退化成间隙锁

使用唯一性索引, 临键锁锁住的是条件范围内, 小于等于查询范围最小值的最后一个索引 (可以是索引本身) + 大于等于查询范围最大值的第一个索引 (不包含索引本身) 间所有的索引记录 + 索引记录前边的间隙

例子: 

select * from t where id > 5 and id < 9 for update;

查询范围为 (5, 9),
小于等于查询范围最小值 (这里就是 5) 的最后一个索引 (这里是 4)
大于等于查询范围最大值 (这里就是 9) 的第一个索引 (这里是 10)
所以这条 SQL 锁住的范围为 (4, 7] 和 (7, 10)

同理

select * from t2 where id > 5 and id <= 7 for update;

这时候锁住的范围为 (4, 7] 和 (7, 10]

select * from t2 where id > 8 and id <= 10 for update;

这时候锁住的范围为 (7, 10] 和 (10, +∞]

3.4.5 非唯一性索引的锁算法

上面讨论的锁算法讨论的都是唯一索引下的情况, 那么如果查询条件是非唯一索引 (值是可以重复的) 呢?

假设现在有一张表, id 的主键, num 是普通的索引, 表中有数据

id (主键) num (普通索引)
1 1
2 6
3 2
4 2
5 3
6 5
8 5
9 5
10 3

使用非唯一索引时, InnoDB 在使用锁的时候, 会先按照非唯一索引进行排序, 非唯一索引排序后, 非唯一索引有相同值时, 会再按照主键排序, 所以排序后的变为

id (主键) num (普通索引)
1 1
3 2
4 2
5 3
10 3
6 5
8 5
9 5
2 6

对于间隙锁的范围, 等值查询的一般情况下, 锁住的是 左区间是以当前条件向左找到第一条记录, 右区间是向右找到的第一条记录
如果现在条件为 where num = 4, num 4 向左找到的第一条记录为 (10, 3), 向右找到的第一条记录为 (6, 5), 所以这个条件锁住的间隙为 (3, 5), 在具体的是 (10, 3) 到 (6, 5)。
如果这时候想里面插入 (7, 5) 是能插入成功的, 因为不在上面说的范围内。

我们知道, 当查询条件没有索引时, 会导致锁表, 所以下面讨论的是在有索引的情况:
影响锁的范围的影响因素

  1. 条件是否为唯一索引
  2. 查询的方式, 等值查询, 范围查询

情况一: 唯一索引 + 等值查询

  1. 命中了记录, 直接就是记录锁
  2. 未命中记录, 间隙锁, 锁住的就是包住这个条件的 2 个记录的区间, (注意上面间隙锁范围的说明)

情况二: 唯一索引 + 范围查询

使用范围查询的话, 基本锁住的范围就是条件限制的范围, 这个范围内的记录加上的都是记录锁。

需要说明的是, 如果 where 主键 > 条件, 这个条件不是记录, 那么会向左找到第一条记录, 以这条记录开始,
同理如果 where 主键 < 条件, 这个条件不是记录, 会向右找到第一条记录

情况三: 非唯一索引 + 等值查询

以这个条件, 向左找到第一条记录, 向右找到的第一条记录, 组成一个区间, 中间的记录为记录锁, 同时会锁住这条记录对应的主键索引。

情况四: 非唯一索引 + 范围查询

和唯一索引不同的, 非唯一索引, 还会增加 2 侧的间隙

  1. where 非唯一索引 > 条件, 无论条件是否为记录, 都会向左找到第一条记录, 从这里开始加上条件的范围组成区间, 中间出现的记录都会加上记录锁, 同理 where 非唯一索引 < 条件 会向右找到第一条记录, 加上条件的范围。

3.5 其他几个锁

3.5.1 插入意向锁

插入意向锁是一种特殊的间隙锁, 但不同于间隙锁的是, 该锁只用于并发插入操作: 如果多个事务插入到相同的索引间隙中, 如果它们不在间隙中的相同位置插入, 则无需等待其他事务。
比如说有索引记录 4 和 7, 有两个事务想要分别插入 5, 6, 在获取插入行上的排它锁之前, 每个事务都使用插入意图锁锁定 4 和 7 之间的间隙, 但是不会互相阻塞, 因为行是不冲突的。
简单来说: 插入意向锁锁定了索引之间的间隙, 但是事务之间只要不插入同一个位置, 彼此之间是不会阻塞的。

3.5.2 自增锁

自增锁是一种比较特殊的表级锁, 主要用于在事务中插入自增字段。
通过 innodb_autoinc_lock_mode 配置控制自动增量锁定的算法, 允许用户在可预测的自动增量值序列和插入操作的最大并发之间进行权衡选择。

为了便于介绍 innodb_autoinc_lock_mode 参数, 先将需要用到自增锁的 Insert 语句进行分类

  1. INSERT-like: 所有可以向表中增加行的语句, 包含 insert, insert … select, replace, replace … select, load data, insert … on duplicate key update 6 个语句
  2. Simple inserts: 可以预先确定要插入的行数的语句, 直接的 insert 和 replace
  3. Bulk inserts: 事先不知道要插入的行数的语句, insert … select, replace … select, load data
  4. Mixed-mode inserts: 在 Simple inserts 语句的基础上, 部分行手动指定了自增列的值, 比如, insert into t1(id, name) values(1, ‘one’), (Null, ‘two’), (Null, ‘three’) 和 insert … on duplicate key update 语句

innodb_autoinc_lock_mode 三种取值的含义

traditional (取值: 0)

在此锁定模式下, 所有 “INSERT-like” 语句获得一个特殊的表级 AUTO-INC 锁, 用于插入具有 AUTO_INCREMENT 列的表。
此锁定通常保持到 insert 语句结束 (不是事务结束) , 以确保为给定的INSERT语句序列以可预测和可重复的顺序分配自动递增值, 并确保自动递增由任何给定语句分配的值是连续的。

在基于语句复制 (statement-based replication) 的情况下, 这意味着当在从服务器上复制 SQL 语句时, 自动增量列使用与主服务器上相同的值。多个 INSERT 语句的执行结果是确定性的, 从服务器再现与主服务器相同的数据

consecutive (取值: 1)

在此锁定模式下, “Simple inserts” (要插入的行数事先已知) , 不使用表级 AUTO-INC 锁, 而是通过在 mutex (轻量锁) 的控制下获得所需数量的自动递增值来避免表级 AUTO-INC 锁, 它只在分配过程的持续时间内保持, 而不是直到语句完成。
当然: 如果另一个事务保持 AUTO-INC 锁, 则 “Simple inserts” 则需要等待 AUTO-INC 锁, 然后按照 “Bulk inserts” 处理。

此锁定模式确保, 当有行数不预先知道的 INSERT 存在时, 任何 “INSERT-like” 语句分配的所有自动递增值是连续的, 并且对于基于语句的复制 (statement-based replication) 操作是安全的。
此模式大体是和 traditional 一致的。但有个特殊场景需要注意: 混合模式的插入, 可能会有部分多余自增值丢失。
InnoDB 分配比要插入的行数更多的自动增量值。但是, 所有自动分配的值都是连续生成的 (因此高于) 由最近执行的前一条语句生成的自动增量值, “多余” 的数字丢失。

interleaved (取值: 2)

在此锁定模式下, 所有 “INSERT-like” 语句不使用 AUTO-INC 锁, 可以同时执行多个语句, 这是最快和最可扩展的锁定模式。

但是当使用基于语句的复制或恢复方案时, 从二进制日志重播SQL语句时, 这是不安全的

在此锁定模式下, 自动递增值保证在所有并发执行的 “INSERT-like” 语句中是唯一且单调递增的。但是, 由于多个语句可以同时生成数字 (即, 跨语句交叉编号) , 为任何给定语句插入的行生成的值可能不是连续的。
如果执行的语句是 “simple inserts”, 其中要插入的行数已提前知道, 则除了 “Mixed-mode inserts” 之外, 为单个语句生成的数字不会有间隙。然而, 当执行 “Bulk inserts”时, 在由任何给定语句分配的自动递增值中可能存在间隙。

如果不使用二进制日志作为恢复或复制的一部分来重放 SQL 语句, 则可以使用 interleaved 模式来消除所有使用表级 AUTO-INC 锁, 以实现更大的并发性和性能, 其代价是由于并发的语句交错执行, 同一语句生成的 AUTO-INCREMENT 值可能会产生间隙。

Binlog 一般用于 MySQL 的数据复制, 通俗一点就是用于主从同步。在 MySQL 中 Binlog 的格式有 3 种, 分别是:

  1. Statement: 基于语句, 只记录对数据做修改的 SQL 语句, 能够有效的减少 binlog 的数据量, 提高读取, 基于 Binlog 重放的性能
  2. Row: 只记录被修改的行, 所以 Row 记录的 binlog 日志量一般来说会比 Statement 格式要多
  3. Mixed: Statement 和 Row 的结合, 怎么个结合法呢。表结构变更使用 statement 模式来记录, 如果 SQL 语句是 update 或者 delete 语句, 那么使用 row 模式

如果 MySQL 采用的格式为 Statement, 那么 MySQL 的主从同步实际上同步的就是一条一条的 SQL 语句。如果此时采用了交叉模式, 那么并发情况下 INSERT 语句的执行顺序就无法得到保障。
INSERT 同时交叉执行, 并且 AUTO_INCREMENT 交叉分配将会直接导致主从之间同行的数据主键 ID 不同。

和自增锁相关的三个参数

  1. auto_increment_offset: 表示起始数字
  2. auto_increment_increment: 表示自增的步长 (即每次增加 n 个数字, 2 就代表每次 +2)
  3. innodb_autoinc_lock_mode: 加锁的模式, 可以设定 3 个值, 0, 1, 2, 默认为 1

3.5.3 空间索引的谓词锁

InnoDB 支持对包含空间的列建立 SPATIAL 索引。

要处理涉及 SPATIAL 索引的操作的锁定, next-key 锁定不能很好地支持 REPEATABLE READ 或 SERIALIZABLE 事务隔离级别, 因为在多维数据中没有绝对排序概念, 因此不清楚哪个是邻键。
为了支持具有 SPATIAL 索引的表的隔离级别, InnoDB 使用谓词锁。SPATIAL 索引包含最小边界矩形 (MBR) 值, 因此 InnoDB 通过设置用于查询的 MBR 值的谓词锁来强制对索引进行一致读取。
此时, 其他事务不能插入或修改与查询条件匹配的行。

4 参考

彻底搞懂 MySQL 中的锁机制
mysql之innodb锁系统源码分析

  目录