表锁

一般在执行对表的DDL语句的时候会加表锁，加表锁的时候需要执行 加表锁的名称。在加锁之后只能访问这些加了锁的表，对于其他没有加锁的表是不能够直接访问的。结束之后使用unlock命令释放所有的表锁。 **所以不会发生死锁**

  1. mysql> lock table products read;
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> select * from products where id = 100;
  6. mysql> unlock tables;
  7. Query OK, 0 rows affected (0.00 sec)

对表锁的加锁规则

对表锁的加锁规则和行锁其实差不多

  • 对于读锁
    • 持有读锁的会话可以读表，但不能写表；
    • 允许多个会话同时持有读锁；
    • 其他会话就算没有给表加读锁，也是可以读表的，但是不能写表；
    • 其他会话申请该表写锁时会阻塞，直到锁释放。

  • 对于写锁

    • 持有写锁的会话既可以读表，也可以写表；
    • 只有持有写锁的会话才可以访问该表，其他会话访问该表会被阻塞，直到锁释放；
    • 其他会话无论申请该表的读锁或写锁，都会阻塞，直到锁释放。

      表锁的释放规则

  • 使用 UNLOCK TABLES 语句可以显示释放表锁；

  • 如果会话在持有表锁的情况下执行 LOCK TABLES 语句，将会释放该会话之前持有的锁；
  • 如果会话在持有表锁的情况下执行 START TRANSACTION 或 BEGIN 开启一个事务，将会释放该会话之前持有的锁；
  • 如果会话连接断开，将会释放该会话所有的锁。

    行锁

    表锁占用系统资源少，但是锁的粒度比较大，影响系统的性能。而行锁的粒度相对而言就小了很多，但是遇到冲突的概率就大的多了。
    一般来说 对一条数据的增删改会加写锁，读锁可以执行加读锁，也可以显示执行 Select …… FOR Update 加上写锁

行锁这个名字听起来像是这个锁加在某个数据行上，实际上这里要指出的是：在 MySQL 中，行锁是加在索引上的。所以要深入了解行锁，还需要先了解下 MySQL 中索引的结构。
索引

行锁的加锁流程

例如一条Upadte语句

只涉及到主键索引

  1. update students set score = 100 where id = 24;

这是操作单条数据，首先会在Id = 24的主键索引上加上一把锁，然后执行修改操作

image.png

涉及二级索引

  1. update students set score = 100 where name = 'Tom'

如果设计到二级索引，那么会在二级索引和一级索引上同时加锁

常见锁的类型 - 图2

如果同时涉及到多条数据

会依次查询出数据，然后每条数据都执行一个加锁动作，然后一条一条更新
常见锁的类型 - 图3

行锁类型

行锁类型分为4种

  • LOCK_ORDINARY：也称为 Next-Key Lock，这是间隙锁和记录锁的组合，锁一条记录及其之前的间隙，这是 RR 隔离级别用的最多的锁，从名字也能看出来；
  • LOCK_GAP：间隙锁，锁两个记录之间的 GAP，防止记录插入；
  • LOCK_REC_NOT_GAP：只锁记录；

  • LOCK_INSERT_INTENSION：插入意向 GAP 锁，插入记录时使用，是 LOCK_GAP 的一种特例。

    Mysql下的锁模式

    Mysql 讲锁分为锁类型和锁模式，上面讲的行锁和表锁都是讲的锁类型，描述的是锁的粒度，把锁加载哪个位置。而锁模式是指该怎么加这个锁。
    Mysql中锁模式分为以下几种

    读写意向锁

    数据库在加表锁之前必须判断在表中没有行锁，为了避免一行一行判断是否有行锁，所有有了意向锁。当事务需要读写某一条记录时，会首先在表上加上意向锁，然后在相关记录进行锁定

  • LOCK_IS：读意向锁;

  • LOCK_IX：写意向锁；

    读写锁

  • LOCK_S：读锁；简称S锁，读锁与读锁之间互相不冲突，写锁和读锁之间冲突。

  • LOCK_X：写锁；排他锁，加了写锁之后只有拥有锁的事务才可以进行修改。

自增锁

  • LOCK_AUTO_INC：自增锁；用于进行自增的锁，数据库在需要自动生成自增值的时候，会为该表加上AUTO_INC表锁。阻止其他事务插入操作，用于保证拿到的锁是唯一递增的

    自增锁有以下特点

  1. 自增锁在同一张表中互不兼容
  2. 自增锁不遵循二阶段提交协议，不是在事务结束后释放，而是在Insert 之后直接释放，所以如果在Insert之后事务回滚，那么就自增值也不会减回去

    Mysql自增锁的种类

    按照标准数据库自增锁的逻辑，加锁的时候需要锁表，那么大大降低并发的插入效率。Mysql为了提升这个效率新增了几种类型的锁，可以通过innodb_autoinc_lock_mode来指定。
    innodb_autoinc_lock_mode可以取值为
  • 0传统的自增锁会锁住整张表效率比较慢
  • 1

Mysql将插入语句分为3类。

  1. Simple inserts 能够确定插入数量，提前分配好自增值，只需要使用mutex提前锁好预分配的自增值
  2. Bulk inserts 不确定插入数量，直接锁住整张表。（例如 INSERT INTO table SELECT 或 LOAD DATA 等 ）
  3. Mixed-mode inserts。不确定插入数量，但是可以通过分析的方式计算出来最坏情况的插入数量，一次性分配足够的自增值。（例如 INSERT … ON DUPLICATE KEY UPDATE）
  • 2 ：全部都用轻量级锁 mutex，并发性能最高，按顺序依次分配自增值，不会预分配。

    缺点是不能保证同一条 INSERT 语句内的自增值是连续的，这样在复制（replication）时，如果 binlog_format 为 statement-based（基于语句的复制）就会存在问题，因为是来一个分配一个，同一条 INSERT 语句内获得的自增值可能不连续，主从数据集会出现数据不一致。所以在做数据库同步时要特别注意这个配置。

锁的冲突规则

  • 意向锁之间互不冲突；
  • S 锁只和 S/IS 锁兼容，和其他锁都冲突；
  • X 锁只和意向锁兼容，和其他所有锁都冲突；
  • AI 锁只和意向锁兼容；

    Mysql 锁类型

    记录锁（Record Locks）

    在索引中对指定的数据加上记录锁。

    注意：如果 SQL 语句无法使用索引时会走主索引实现全表扫描，这个时候 MySQL 会给整张表的所有数据行加记录锁。如果一个 WHERE 条件无法通过索引快速过滤，存储引擎层面就会将所有记录加锁后返回，再由 MySQL Server 层进行过滤。不过在实际使用过程中，MySQL 做了一些改进，在 MySQL Server 层进行过滤的时候，如果发现不满足，会调用 unlock_row 方法，把不满足条件的记录释放锁（显然这违背了二段锁协议）。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。可见在没有索引时，不仅会消耗大量的锁资源，增加数据库的开销，而且极大的降低了数据库的并发性能，所以说，更新操作一定要记得走索引。

间隙锁（Gap Locks）

间隙锁， 用于解决幻读，采用加载两个索引之间的锁，又可以称之为范围锁，可以确保在上锁期间不会有其他事务可以进行插入，从而避免了幻读。

Next-Key Locks

这是间隙锁和记录锁的组合

Next-key 锁 是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。假设一个索引包含 10、11、13 和 20 这几个值，可能的 Next-key 锁如下：

  • (-∞, 10]
  • (10, 11]
  • (11, 13]
  • (13, 20]
  • (20, +∞)

通常我们都用这种左开右闭区间来表示 Next-key 锁，其中，圆括号表示不包含该记录，方括号表示包含该记录。前面四个都是 Next-key 锁，最后一个为间隙锁。和间隙锁一样，在 RC 隔离级别下没有 Next-key 锁，只有 RR 隔离级别才有。继续拿上面的 SQL 例子来说，如果 id 不是主键，而是二级索引，且不是唯一索引，那么这个 SQL 在 RR 隔离级别下会加什么锁呢？答案就是 Next-key 锁，如下：

  • (a, 5]
  • (5, b)

其中，a 和 b 是 id = 5 前后两个索引，我们假设 a = 1、b = 10，那么此时如果插入一条 id = 3 的记录将会阻塞住。之所以要把 id = 5 前后的间隙都锁住，仍然是为了解决幻读问题，因为 id 是非唯一索引，所以 id = 5 可能会有多条记录，为了防止再插入一条 id = 5 的记录，必须将下面标记 ^ 的位置都锁住，因为这些位置都可能再插入一条 id = 5 的记录：

1 ^ 5 ^ 5 ^ 5 ^ 10 11 13 15

可以看出来，Next-key 锁确实可以避免幻读，但是带来的副作用是连插入 id = 3 这样的记录也被阻塞了，这根本就不会引起幻读问题的。

关于 Next-key 锁，有一个比较有意思的问题，比如下面这个 orders 表（id 为主键，order_id 为二级非唯一索引）：

+-----+----------+
`		 id order_id <br />+——-+—————+<br /> 1 1 <br /> 3 2 <br /> 5 5 <br /> 7 5 <br /> 10 9 <br />+——-+—————+`

事务 A 执行下面的 SQL：

mysql> begin;
mysql> select * from orders where order_id = 5 for update;
+-----+----------+
`		 id order_id <br />+——-+—————+<br /> 5 5 <br /> 7 5 <br />+——-+—————+<br />2 rows in set (0.00 sec)`

这个时候不仅 order_id = 5 这条记录会加上 X 记录锁，而且这条记录前后的间隙也会加上锁，加锁位置如下：

1 2 ^ 5 ^ 5 ^ 9

可以看到 (2, 9) 这个区间都被锁住了，这个时候如果插入 order_id = 4 或者 order_id = 8 这样的记录肯定会被阻塞，这没什么问题，那么现在问题来了，如果插入一条记录 order_id = 2 或者 order_id = 9 会被阻塞吗？答案是可能阻塞，也可能不阻塞，这取决于插入记录主键的值，感兴趣的读者可以参考这篇博客。

插入意向锁

插入意向锁 是一种特殊的间隙锁（所以有的地方把它简写成 II GAP），这个锁表示插入的意向，只有在 INSERT 的时候才会有这个锁。注意，这个锁虽然也叫意向锁，但是和上面介绍的表级意向锁是两个完全不同的概念，不要搞混淆了。插入意向锁和插入意向锁之间互不冲突，所以可以在同一个间隙中有多个事务同时插入不同索引的记录。譬如在上面的例子中，id = 1 和 id = 5 之间如果有两个事务要同时分别插入 id = 2 和 id = 3 是没问题的，虽然两个事务都会在 id = 1 和 id = 5 之间加上插入意向锁，但是不会冲突。

插入意向锁只会和间隙锁或 Next-key 锁冲突，正如上面所说，间隙锁唯一的作用就是防止其他事务插入记录造成幻读，那么间隙锁是如何防止幻读的呢？正是由于在执行 INSERT 语句时需要加插入意向锁，而插入意向锁和间隙锁冲突，从而阻止了插入操作的执行。