70-MySQL-事务-表级锁

事务的隔离性是由锁来保证的

一、概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。当多个线程并发访问某个数据的时候，我们就需要保证这个数据在任何时刻最多只有一个线程在访问，保证数据的完整性和一致性。在开发过程中加锁是为了保证数据的一致性，这个思想在数据库领域中同样很重要。在数据库中，除传统的计算资源（CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。为了保证数据的一致性，需要对并发操作进行控制，因此产生了锁。同时锁机制也为实现MySQL的各个隔离级别提供了保证。锁冲突也是影响数据库并发访问性能的一个重要因素。

二、 MySQL并发事务访问相同记录

并发事务访问相同记录的情况大致可以划分为3种

2.1、读-读情况

读-读 情况，即并发事务相继 读取相同的记录 。读取操作本身不会对记录有任何影响，并不会引起什么问题，所以允许这种情况的发生

2.2 写-写情况

写-写 情况，即并发事务相继对相同的记录做出改动。

  • 1、在这种情况下会发生 脏写 的问题，任何一种隔离级别都不允许这种问题的发生。所以在多个未提交事务相继对一条记录做改动时，需要让它们 排队执行 ，这个排队的过程其实是通过 锁 来实现的。这个所谓的锁其实是一个 内存中的结构 ，在事务执行前本来是没有锁的，也就是说一开始是没有 锁结构 和记录进行关联的

    未加锁结构时数据.png

  • 2、当一个事务想对这条记录做改动时，首先会看看内存中有没有与这条记录关联的 锁结构 ，当没有的时候就会在内存中生成一个 锁结构 与之关联。比如，事务 T1 要对这条记录做改动，就需要生成一个 锁结构与之关联

    记录关联锁结构.png

  • 3、在锁结构里有很多信息，为了简化理解，只把两个比较重要的属性拿来说明

    • trx信息：代表这个锁结构是哪个事务生成的
    • is_waiting：代表当前事务是否在等待

  • 4、当事务 T1改动了这条记录后，就生成了一个锁结构与该记录关联，因为之前没有别的事务为这条记录加锁，所以is_waiting属性就是false，把这个场景就称之为获取锁成功，或者加锁成功，然后就可以继续执行操作了

  • 5、在事务T1提交之前，另一个事务T2也想对该记录做改动，那么先看看有没有锁结构与这条记录关联，发现有一个锁结构与之关联后，然后也生成了一个锁结构与这条记录关联，不过该锁结构的is_waiting属性值为true，表示当前事务需要等待，我们把这个场景就称之为获取失败，或者加锁失败

    事务获取锁，锁结构.png

  • 6、事务T1提交之后，就会把该事务生成的锁结构释放掉，然后看看还有没有别的事务在等待获取锁，发现了事务T2还在等待获取锁，所以把事务T2对应的锁结构的is_waiting属性值设置为false，然后把该事务对应的线程唤醒，让它继续执行，此时事务T2就算获取到了锁

    image.png

小结

  • 不加锁：不需要在内存中生成对应的 锁结构 ，可以直接执行操作

  • 获取锁成功，或者加锁成功：在内存中生成了对应的 锁结构 ，而且锁结构的 is_waiting 属性为 false ，也就是事务可以继续执行操作

  • 获取锁失败，或者加锁失败，或者没有获取到锁：在内存中生成了对应的 锁结构 ，不过锁结构的is_waiting 属性为 true ，也就是事务需要等待，不可以继续执行操作

2.3 读-写或写-读情况

读-写 或 写-读 ，即一个事务进行读取操作，另一个进行改动操作。这种情况下可能发生 脏读 、 不可重复读 、 幻读 的问题。各个数据库厂商对 SQL标准 的支持都可能不一样。比如MySQL在 REPEATABLE READ 隔离级别上就已经解决了 幻读 问题

2.4 并发问题的解决方案

通过两种可选的解决方案来解决 脏读 、 不可重复读 、 幻读 这些问题

2.4.1、方案一：读操作利用多版本并发控制（MVCC），写操作进行 加锁 。

所谓MVCC，就是生成一个ReadView，通过ReadView找到符合条件的记录版本（历史版本由 undo日志构建）。查询语句只能读到在生成ReadView之前已提交事务所做的更改，在生成ReadView之前未提交的事务或者之后才开启的事务所做的更改是看不到的。而写操作肯定针对的是最新版本的记录，读记录的历史版本和改动记录的最新版本本身并不冲突，也就是采用MVCC时，读-写操作并不冲突。

  • 普通的SELECT语句在READ COMMITTED和REPEATABLE READ隔离级别下会使用到MVCC读取记录
    • 在 READ COMMITTED 隔离级别下，一个事务在执行过程中每次执行SELECT操作时都会生成一个ReadView，ReadView的存在本身就保证了 事务不可以读取到未提交的事务所做的更改 ，也就是避免了脏读现象
    • 在 REPEATABLE READ 隔离级别下，一个事务在执行过程中只有 第一次执行SELECT操作 才会生成一个ReadView，之后的SELECT操作都 复用 这个ReadView，这样也就避免了不可重复读和幻读的问题

2.4.2、方案二：读、写操作都采用 加锁 的方式

如果一些业务场景不允许读取记录的旧版本，而是每次都必须去读取记录的最新版本。如在银行存款的业务中，你需要先把账户的余额读出来，然后将其加上本次存款的数额，最后再写到数据库中。在将账户余额读取出来后，就不想让别的事务再访问该余额，直到本次存款事务执行完成，其他事务才可以访问账户的余额。这样在读取记录的时候就需要对其进行加锁操作，这样也就意味着读操作和写操作也像写-写操作那样排队执行

  • 脏读的产生是因为当前事务读取了另一个事务未提交的一条记录，如果另一个事务再写记录的时候就给这条记录加锁，那么当前事务就无法继续读取该记录了，所以也就不会有脏读问题的产生了

  • 不可重复读的产生是因为当前事务先读取一条记录，另外一个事务对该记录做了改动之后并提交之后，当前事务再次读取时会获得不同的值，如果在当前事务读取记录时就给改记录加锁，那么另一个事务就无法修改改记录，自然也不会发生不可重复读了。

  • 幻读问题的产生是因为当前事务读取了一个范围的记录，然后另外的事务向该范围内插入了新记录，当前事务再次读取该范围的记录时发现了新插入的新记录。采用加锁的方式解决幻读问题就有一些麻烦，因为当前事务在第一次读取记录时幻读记录并不存在，所以读取的时候加锁就有点尴尬（因为并不知道给谁加锁）

小结

  • 采用 MVCC 方式的话，读-写 操作彼此并不冲突，性能更高
  • 采用 加锁 方式的话，读-写 操作彼此需要 排队执行 ，影响性能
  • 一般情况下我们当然愿意采用 MVCC 来解决 读-写 操作并发执行的问题，但是业务在某些特殊情况下，要求必须采用 加锁 的方式执行

三、锁的不同角度分类

image.png

1、从数据操作的类型划分：读锁、写锁

对于数据库中并发事务的读-读情况并不会引起什么问题。对于写-写、读-写或写-读这些情况可能会引起一些问题，需要使用MVCC或者加锁的方式来解决它们。在使用加锁的方式解决问题时，由于既要允许读-读情况不受影响，又要使写-写、读-写或写-读这些情况中的操作相互阻塞，所以 MySQL 实现一个由两种类型的锁组成的锁系统来解决。这两种类型的锁通常被称为共享锁（Shared Lock，S Lock）和 排它锁（Exclusive Lock，X Lock）也叫读锁（ReadLock）和写锁（Write Lock）

  • 读锁 ：也称为 共享锁 、英文用 S 表示。针对同一份数据，多个事务的读操作可以同时进行而不会互相影响，相互不阻塞的

  • 写锁 ：也称为 排他锁 、英文用 X 表示。当前写操作没有完成前，它会阻断其他写锁和读锁。这样就能确保在给定的时间里，只有一个事务能执行写入，并防止其他用户读取正在写入的同一资源

  • 需要注意的是对于 InnoDB 引擎来说，读锁和写锁可以加在表上，也可以加在行上。

对于行级读写锁来说：如果一个事务T1已经获得了某个行 r 的读锁，那么此时另外的一个事务T2是可以去获得这个行 r 的读锁，因为读取操作并没有改变行 r 的数据；但是，如果某个事务T3想获取行 r 的写锁，则它必须等待事务T1、T2释放掉行 r 上的读锁才行

读写锁总结.png

1.1、锁定读

在采用加锁方式解决脏读、不可重复读、幻读这些问题时，读取一条记录时需要获取记录的S锁，其实是不严谨的，有时候需要在读取记录时就获取记录的X锁来禁止别的事务读写该记录，为此MySQL提出了两种比较特殊的 SELECT语句格式

1.1.1、对读取的记录加S锁

在普通的 SELECT 语句后边加LOCK IN SHARE MODE，如果当前事务执行了该语句，那么它会为读取到的记录加S锁，这样允许别的事务继续获取这些记录的S 锁（比方说别的事务也使用SELECT . . . LOCK IN SHARE MODE语句来读取这些记录），但是不能获取这些记录的X 锁（比如使用 SELECT . . . FOR UPDATE 语句来读取这些记录，或者直接修改这些记录）。如果别的事务想要获取这些记录的X 锁，那么它们会阻塞，直到当前事务提交之后将这些记录上的S 锁释放掉

SELECT . . . LOCK IN SHARE MODE;
#  或
SELECT . . . FOR SHARE;  #(8.0新增语法)
1.1.2、对读取的记录加X 锁

在普通的 SELECT 语句后边加 FOR UPDATE ，如果当前事务执行了该语句，那么它会为读取到的记录加X 锁，这样既不允许别的事务获取这些记录的S 锁（比方说别的事务使用 SELECT . . . LOCK IN SHARE MODE语句来读取这些记录），也不允许获取这些记录的X 锁（比如使用 SELECT . . . FOR UPDATE语句来读取这些记录，或者直接修改这些记录）。如果别的事务想要获取这些记录的 S 锁 或者 X 锁，那么它们会阻塞，直到当前事务提交之后将这些记录上的X 锁释放掉

SELECT . . . FOR UPDATE;
1.1.3、MySQL8.0新特性

在 5.7及之前的版本，SELECT . . . FOR UPDATE，如果获取不到锁，会一直等待，直到innodb_lock_wait_timeout超时。在8.0版本中，SELECT . . . FOR UPDATE，SELECT . . . FOR SHARE 添加NOWAIT、SKIP LOCKED语法，跳过锁等待，或者跳过锁定

  • 通过添加NOWAIT、SKIP LOCKED语法，能够立即返回。如果查询的行已经加锁
    • 那么NOWAIT会立即报错返回
    • 那么SKIP LOCKED也会立即返回，只是返回的结果中不包含被锁定的行
1.1.4、实战
  • session1 获取X 锁 
#  开启事务
begin;

#  获取  X 锁
select * from account where id = 2 for update;
  • session2 一直等待，直到报错
select * from account where id = 2 for update;
一直等待.png
等待超时，报错.png
  • session3 获取锁时使用 nowait 
select * from account where id = 2 for update nowait;
获取锁时使用 nowait.png
  • session4 获取锁时使用skip locked 
select * from account where id = 2 for update skip locked;
获取锁时使用skip locked.png

1.2、写操作

写操作无非是DELETE、UPDATE、INSERT三种

  • DELETE：对一条记录做 DELETE 操作的过程其实是先在B+树中定位到这条记录的位置，然后获取这条记录的X锁，再执行delete mark操作。也可以把这个定位待删除记录在B+树中位置的过程看成是一个获取X锁的锁定读

  • UPDATE：在对一条记录做UPDATE操作时分为三种情况

    • 情况1：未修改该记录的键值（聚簇索引，一般就是主键id），并且被更新的列占用的存储空间在修改前后未发生变化。则先在B+树中定位到这条记录的位置，然后再获取一下记录的X锁，最后在原记录的位置进行修改操作。也可以把这个定位待修改记录在B+树中位置的过程看成是一个获取X锁的锁定读。

    • 情况2：未修改该记录的键值（聚簇索引，一般就是主键id），并且至少有一个被更新的列占用的存储空间在修改前后发生变化。则先在B+树中定位到这条记录的位置，然后获取一下记录的X 锁，将该记录彻底删除掉（就是把记录彻底移入垃圾链表），最后再插入一条新纪录。这个定位待修改记录在B+树中位置的过程看成是一个获取X锁的锁定读，新插入的记录由INSERT操作提供的隐式锁进行保护

    • 情况3：修改了该记录的键值，则相当于在原记录上做DELETE操作之后再来一次INSERT操作，加锁操作就需要按照DELETE和INSERT的规则进行了

  • INSERT：一般情况下，新插入一条记录的操作并不加锁，通过一种称之为隐式锁的结构来保护这条新插入的记录在本事务提交前不被别的事务访问

2、从数据操作的粒度划分：表级锁、页级锁、行锁

为了尽可能提高数据库的并发能力，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很消耗资源的事情（涉及获取、检查、释放锁等动作）。因此数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了锁粒度（Lock Granularity）的概念。对一条记录加锁影响的也只是这条记录而已，就说这个锁的粒度比较细；其实一个事务也可以在表级别进行加锁，自然就被称之为表级锁或者表锁，对一个表加锁影响整个表中的记录，我们就说这个锁的粒度比较粗。锁的粒度主要分为表级锁、页级锁和行锁

2.1、表锁（Table Lock）

表锁会锁定整张表，它是 MySQL 中最基本的锁策略，并不依赖于存储引擎（不管你是 MySQL 的什么存储引擎，对于表锁的策略都是一样的），并且表锁是开销最小的策略（因为粒度比较大）。由于表级锁一次会将整个表锁定，所以可以很好的避免死锁问题。当然，锁的粒度大所带来最大的负面影响就是出现锁资源竞争的概率升高，导致并发率大打折扣

2.1.1、表级别的S锁（共享锁）、X锁（排他锁）

在对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，InnoDB存储引擎是不会为这个表添加表级别的 S锁 或者 X锁 的。在对某个表执行一些诸如 ALTER TABLE 、 DROP TABLE 这类的 DDL 语句时，其他事务对这个表并发执行诸如SELECT、INSERT、DELETE、UPDATE的语句会发生阻塞。同理，某个事务中对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，在其他会话中对这个表执行 DDL 语句也会发生阻塞。这个过程其实是通过在 server层 使用一种称之为 元数据锁 （英文名： Metadata Locks ，简称 MDL ）结构来实现的。一般情况下，不会使用InnoDB存储引擎提供的表级别的 S锁 和 X锁 。只会在一些特殊情况下，比方说 崩溃恢复 过程中用到。比如，在系统变量 autocommit=0，innodb_table_locks = 1 时， 手动 获取InnoDB存储引擎提供的表t 的 S锁 或者 X锁 可以这么写

  • LOCK TABLES t READ ：InnoDB存储引擎会对表 t 加表级别的 S锁 。

  • LOCK TABLES t WRITE ：InnoDB存储引擎会对表 t 加表级别的 X锁

  • MySQL的表级锁有两种模式：（以MyISAM表进行操作的演示）

    • 表共享读锁（Table Read Lock）
    • 表独占写锁（Table Write Lock）

  • 实战

尽量避免在使用InnoDB存储引擎的表上使 LOCK TABLES 这样的手动锁表语句，它们并不会提供什么额外的保护，只是会降低并发能力而已。InnoDB的厉害之处还是实现了更细粒度的 行锁 ，关于InnoDB表级别的 S锁 和 X锁 了解一下就可以了。

  • 1、创建存储引擎为 myisam 的表
CREATE TABLE mylock
(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
) ENGINE myisam;
  • 2、插入数据
INSERT INTO mylock(name) VALUES ('a');
image.png
  • 3、查看表上是否加锁
SHOW OPEN TABLES ; #  主要关注 In_use 字段的值
或者
SHOW OPEN TABLES WHERE in_use > 0;
  • 4、手动增加表锁
LOCK TABLES mylock READ ;  #  存储引擎会对表 mylock 加 表级别的共享锁
LOCK TABLES mylock WRITE ;  #  存储引擎会对表 mylock 加 表级别的排他锁
  • 5、加锁后查看
LOCK TABLES mylock WRITE ; 
SHOW OPEN TABLES WHERE in_use > 0;
  • 6、释放表锁
UNLOCK TABLES ;
image.png
2.1.1.1、实战-加表读锁

为 mylock 表加 Read 锁，观察阻塞情况

  • 1、session1 获得表 mylock 的Read 锁
LOCK TABLES mylock READ ;
  • 2、当前 session1 可以查询该表记录
SELECT *
FROM mylock;
  • 3、当前 session1 不能查询其他没有锁定的表
SELECT *
FROM account;
当前 session1 不能查询其他没有锁定的表.png
  • 4、当前 session1 中插入或者更新锁定的表都会提示错误
INSERT INTO mylock(name) VALUES ('b');
当前 session1 中插入或者更新锁定的表都会提示错误.png
  • 5、session2 也可以查询该表记录
SELECT *
FROM mylock;
  • 6、session2 也可以查询或者更新未锁定表
INSERT INTO account(id, balance) VALUES (5,500);
  • 7、session2 插入或者更新锁定表会一直等待获得锁
2.1.1.2、实战-加表写锁

为 mylock 表加 write 锁，观察阻塞的情况

  • 1、session1 获取 mylock 的 WRITE 锁定
LOCK TABLES mylock WRITE ;
  • 2、当前 session1 对锁定表的查询 + 更新 + 插入操作都可以
SELECT *
FROM mylock;

UPDATE mylock
SET name = 'c'
WHERE id = 3;


INSERT INTO mylock(name) VALUES ('d');
  • 3、session2 对锁定表的查询被阻塞，需要等待锁被释放
2.1.1.3、小结

MyISAM在执行查询语句（SELECT）前，会给涉及的所有表加读锁，在执行增删改操作前，会给涉及的表加写锁。InnoDB存储引擎是不会为这个表添加表级别的读锁或者写锁

image.png

2.1.2、意向锁 （intention lock）

InnoDB 支持多粒度锁（multiple granularity locking），它允许行级锁与表级锁共存，而意向锁就是其中的一种表锁
1、意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁 与 行锁）的锁并存
2、意向锁是一种 不与行级锁冲突的表级锁
3、表明某个事务正在某些行持有了锁或该事务准备去持有锁

2.1.2.1、意向锁种类
  • 意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁） 
-- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。 
SELECT column FROM table ... LOCK IN SHARE MODE;
  • 意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁） 
-- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。
SELECT column FROM table ... FOR UPDATE;
  • 意向锁是由存储引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InnoDB会先获取该数据行所在数据表的对应意向锁
2.1.2.2、意向锁要解决的问题

现在有两个事务，分别是 T1 和 T2，其中T2试图在该表级别上应用共享或排他锁，如果没有意向锁存在，那么T2就需要去检查各个页或行是否存在锁；如果存在意向锁，那么此时就会受到由T1控制的表级别意向锁的阻塞。T2在锁定该表前不必检查各个页或行锁，而只需检查表上的意向锁。简单来说就是给更大一级别的空间示意里面是否已经上过锁。在数据表的场景中，如果我们给某一行数据加上了排他锁，数据库会自动给更大一级的空间，比如数据页或数据表加上意向锁，告诉其他人这个数据页或数据表已经有人上过排他锁了，这样当其他人想要获取数据表排他锁的时候，只需要了解是否有人已经获取了这个数据表的意向排他锁即可

  • 如果事务想要获得数据表中某些记录的共享锁，就需要在数据表上添加意向共享锁
  • 如果事务想要获得数据表中某些记录的排他锁，就需要在数据表上添加意向排他锁
2.1.2.3、意向锁实战
  • 创建表 teacher
CREATE TABLE teacher (
    id INT NOT NULL ,
    name VARCHAR(200) NOT NULL ,
    PRIMARY KEY (id)
);
  • 插入数据
INSERT INTO teacher (id, name)
VALUES (1, 'zs'),
       (2, 'ls'),
       (3, 'ww'),
       (4, 'zl');
  • 事务 A 获取某一行的排他锁，并未提交
BEGIN ;

SELECT *
FROM teacher
WHERE id = 3 FOR UPDATE ;
  • 事务 B 试图获取 teacher 表的表读锁 
BEGIN ;

LOCK TABLES teacher READ ;
一直处于等待状态.png
  • 结果：因为共享锁与排他锁互斥，所以事务 B 在试图对 teacher 表加共享锁的时候，必须保证两个条件
    • 当前没有其他事务持有 teacher 表的排他锁
    • 当前没有其他事务持有 teacher 表中任意一行的排他锁
意向锁之间的兼容互斥性.png

意向锁与普通锁互斥性.png
  • 实战小结

事务B检测 事务A持有 teacher 表的意向排他锁，就可以得知事务A 必然持有该表中某些数据行的排他锁，那么事务B 对 teacher 表的加锁请求就会被排斥（阻塞），而无需去检测表中的每一行数据是否存在排他锁

2.1.2.4、意向锁的并发性

意向锁不会与行级的共享 / 排他锁互斥！正因为如此，意向锁并不会影响到多个事务对不同数据行加排他锁时的并发性。（不然我们直接用普通的表锁就行了）

  • 上述实战阻塞原因：事务A对表teacher中的id=3的记录加了排他锁，也就是说表 teacher 中已经有了意向排他锁，事务B想对表teacher加表级的读锁造成阻塞

  • 事务 C 对表 teacher 其他行添加 排他锁是完全OK的

SELECT *
FROM teacher
WHERE id = 4 FOR UPDATE ;
  • 小结：事务C 申请 teacher 表的意向排他锁。事务C检测到事务A持有 teacher表的意向排他锁。因为意向锁之间并不互斥，所以事务C获取到了 teacher 表的意向排他锁。因为 id = 4 的数据行上不存在任何排他锁，最终事务C成功获取到了该数据行上的排他锁
2.1.2.5、结论
    1. InnoDB 支持 多粒度锁 ，特定场景下，行级锁可以与表级锁共存。
  • 2.意向锁之间互不排斥，但除了 IS 与 S 兼容外， 意向锁会与 共享锁 / 排他锁互斥 。
    1. IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。
    1. 意向锁在保证并发性的前提下，实现了 行锁和表锁共存 且满足事务隔离性 的要求。

2.1.3、自增锁（AUTO-INC锁）

2.1.3.1、在使用MySQL过程中，我们可以为表的某个列添加 AUTO_INCREMENT 属性
CREATE TABLE `teacher`
(
    `id`   INT          NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;
2.1.3.2、 Simple inserts （简单插入）

可以 预先确定要插入的行数 （当语句被初始处理时）的语句。包括没有嵌套子查询的单行和多行INSERT...VALUES() 和 REPLACE 语句。

  • 例如
INSERT INTO `teacher` (name) VALUES ('zhangsan'), ('lisi');
2.1.3.3、Bulk inserts （批量插入）

事先不知道要插入的行数 （和所需自动递增值的数量）的语句。比如 INSERT ... SELECT， REPLACE ... SELECT 和 LOAD DATA 语句，但不包括纯INSERT。 InnoDB在每处理一行，为AUTO_INCREMENT列分配一个新值。

2.1.3.4、 Mixed-mode inserts （混合模式插入）

这些是 Simple inserts 语句但是指定部分新行的自动递增值。例如 INSERT INTO teacher (id,name) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d'); 只是指定了部分id的值。另一种类型的混合模式插入是 INSERT ... ON DUPLICATE KEY UPDATE 。对于上面数据插入的案例，MySQL中采用了自增锁的方式来实现，AUTO-INC锁是向使用含有 AUTO_INCREMENT 列的表中插入数据时需要获取的一种特殊的表级锁，在执行插入语句时就在表级别加一个AUTO-INC锁，然后为每条待插入记录的 AUTO_INCREMENT 修饰的列分配递增的值，在该语句执行结束后，再把AUTO-INC锁释放掉。一个事务在持有 AUTO-INC 锁的过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增值是连续的。也正因为此，其并发性不高，当我们向一个有 AUTO_INCREMENT 关键字的主键插入值的时候，每条语句都要对这个表锁进行竞争，这样的并发潜力其实是很低的，所以InnoDB通过innodb_autoinc_lock_mode的不同取值来提供不同的锁定机制，来显著提高 SQL 语句的可伸缩性和性能。innodb_autoinc_lock_mode有三种取值，分别对应与不同锁定模式

  • （1）innodb_autoinc_lock_mode = 0(“传统”锁定模式)：在此锁定模式下，所有类型的insert语句都会获得一个特殊的表级AUTO-INC锁，用于插入具有AUTO_INCREMENT列的表。这种模式其实就如我们上面的例子，即每当执行insert的时候，都会得到一个表级锁(AUTO-INC锁)，使得语句中生成的auto_increment为顺序，且在binlog中重放的时候，可以保证master与slave中数据的auto_increment是相同的。因为是表级锁，当在同一时间多个事务中执行insert的时候，对于AUTO-INC锁的争夺会 限制并发 能力

  • （2）innodb_autoinc_lock_mode = 1(“连续”锁定模式)：在 MySQL 8.0 之前，连续锁定模式是 默认 的。在这个模式下，bulk inserts仍然使用AUTO-INC表级锁，并保持到语句结束。这适用于所有INSERT ... SELECT，REPLACE ... SELECT和LOAD DATA语句。同一时刻只有一个语句可以持有AUTO-INC锁。对于Simple inserts（要插入的行数事先已知），则通过在mutex（轻量锁） 的控制下获得所需数量的自动递增值来避免表级AUTO-INC锁， 它只在分配过程的持续时间内保持，而不是直到语句完成。不使用表级AUTO-INC锁，除非AUTO-INC锁由另一个事务保持。如果另一个事务保持AUTO-INC锁，则Simple inserts等待AUTO-INC锁，如同它是一个bulk inserts。

  • （3）innodb_autoinc_lock_mode = 2(“交错”锁定模式)：从 MySQL 8.0 开始，交错锁模式是 默认 设置。在此锁定模式下，自动递增值 保证 在所有并发执行的所有类型的insert语句中是 唯一 且 单调递增 的。但是，由于多个语句可以同时生成数字（即，跨语句交叉编号），为任何给定语句插入的行生成的值可能不是连续的

2.1.4、元数据锁（MDL锁）

MySQL5.5引入了meta data lock，简称MDL锁，属于表锁范畴。MDL 的作用是，保证读写的正确性。比如，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个 表结构做变更 ，增加了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。因此，当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写锁。读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更结构操作的安全性，解决了 DML 和 DDL操作之间的一致性问题。无需显示使用，在访问一个表的时候会被自动加上。

2.1.4.1、会话A-从表中查询数据
BEGIN ;

SELECT *
FROM teacher;
2.1.4.2、会话B-修改表结构，增加新列
BEGIN ;

ALTER TABLE teacher ADD age INT NOT NULL ;
2.1.4.3、查看当前MySQL的进程
SHOW PROCESSLIST ;
image.png
  • 通过会话C可以看出会话B被阻塞，这是由于会话A拿到了 teacher 表的元数据读锁，会话B想申请 teacher 表的元数据写锁，由于读写锁互斥，会话B需要等待会话A释放元数据锁才能执行。
2.1.4.4、小结

会话A会对表teacher 加一个MDL 读锁，之后会话B要加MDL 写锁会被 Blocked，因为 会话A的MDL 读锁还没有释放，而 会话C要在表teacher上申请 MDL 读锁的请求也会被会话 B阻塞。因为所有对表的增删改查操作都需要先申请 MDL 读锁，就都被 会话B 阻塞，等于这个时候完全不可读写了

image.png
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 212,542评论 6赞 493
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,596评论 3赞 385
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 158,021评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,682评论 1赞 284
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,792评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,985评论 1赞 291
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,107评论 3赞 410
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,845评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,299评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,612评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,747评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,441评论 4赞 333
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,072评论 3赞 317
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,828评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,069评论 1赞 267
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,545评论 2赞 362
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,658评论 2赞 350

推荐阅读更多精彩内容