【MySQL进阶之路 | 高级篇】表级锁之S锁，X锁，意向锁

1. 从数据操作的粒度划分：表级锁，页级锁，行锁

为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很耗资源的事情（涉及获取、检查、释放锁等动作)。因此数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了‘锁粒度’的概念。

对一条记录加锁影响的也只是这条记录而已，我们就说这个锁的粒度比较细;其实一个事务也可以在表级别进行加锁，自然就被称之为表级锁或者表锁，对一个表加锁影响整个表中的记录，我们就说这个锁的粒度比较粗。锁的粒度主要分为表级锁、页级锁和行锁。

1.1 表锁（Table Lock）

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

a. 表级别的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锁。

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

我们可以在MISAM存储引擎的表中测试表级锁：

# 添加表共享读锁
lock tables mylock read;

# 查看锁
show open  tables where ...

# 释放锁
unlock tables;

小结：

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

b. 意向锁

InnoDB支持多粒度锁(multiple granularity locking)，它允许行级锁与表级锁共存，而意向锁就是其中的一种表锁。

意向锁的存在是为了协调行锁和表锁的关系，支持多粒度(表锁与行锁)的锁并存。
意向锁是一种不与行级锁冲突表级锁，这一点非常重要。
表明“某个事务正在某些行持有了锁或该事务准备去持有锁

意向锁分为两种:

意向共享锁（intention shared lock,IS):事务有意向对表中的某些行加共享锁（s锁)

--事务要获取某些行的S锁，必须先获得表的IS 锁。

SELECT column FROM table ... LOCK IN SHARE MODE;

意向排他锁（intention exclusive lock, lX)︰事务有意向对表中的某些行加排他锁(x锁)

--事务要获取某些行的×锁，必须先获得表的工X锁。

SELECT column FROM table ... FOR UPDATE;

即:意向锁是由存储引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InooDB会先获取该数据行所在数据表的对应意向锁。

意向锁要解决的问题

现在有两个事务，分别是T1和T2，其中T2试图在该表级别上应用共享或排它锁，如果没有意向锁存在，那么T2就需要去检查各个页或行是否存在锁;如果存在意向锁，那么此时就会受到由T1控制的表级别意向锁的阻塞。T2在锁定该表前不必检查各个页或行锁，而只需检查表上的意向锁。简单来说就是给更大一级别的空间示意里面是否已经上过锁。

在数据表的场景中，如果我们给某一行数据加上了排它锁，数据库会自动给更大一级的空间，比如数据页或数据表加上意向锁，告诉其他人这个数据页或数据表已经有人上过排它锁了，这样当其他人想要获取数据表排它锁的时候扌只需要了解是否有人已经获取了这个数据表的意向排他锁即可。

如果事务想要获得数据表中某些记录的共享锁，就需要在数据表上添加意向共享锁。
如果事务想要获得数据表中某些记录的排他锁，就需要在数据表上添加意向排他锁。这时，意向锁会告诉其他事务已经有人锁定了表中的某些记录。

我们需要知道意向锁之间的兼容互斥性，如图：

即意向锁之间是相互兼容的，虽然意向锁和自家兄弟互相兼容，但还是它会与普通的排他锁/共享锁互斥。

这里的排他锁/共享锁指的都是表锁，意向锁不会与行级的共享/排他锁互斥。

结论：

InnoDB支持多粒度锁，特定场景下，行级锁可以与表级锁共存。
意向锁之间互不排斥，但除了IS与S兼容外，意向锁会与共享锁/排他锁互斥。
IS，IX是表级锁，不会和行级的X锁和S锁发生冲突。只会和表级的X锁，S锁发生冲突。
意向锁在保证并发性的前提下，实现了行锁和表锁共存且满足事务隔离性的要求。

c. 自增锁（AUTO-INC锁）

在使用MySQL过程中，我们可以为表的某个列添加AUTO_INCREAMENT属性。

MySQL中采用了自增锁的方式来实现，AUTO-INC锁是当向使用含有AUTO_INCREMENT列的表中插入数据时需要获取的一种特殊的表级锁，在执行插入语句时就在表级别加一个AUTO-INC锁，然后为每条待插入记录的AUTO_INCREMENT修饰的列分配递增的值，在该语句执行结束后，再把锁释放掉。一个事务在持有AUTO-INC锁过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增的值是连续的。也正因为如此其并发性能不高。当我们向有一个auto_increament关键字的主键插入值时，每条语句都要对这个表锁进行竞争，这样的并发潜力其实是很低下的，所以innodb通过innodb_autoinc_lock_mode的不同取值来提供不同的锁定机制，来显著提高sQL语句的可伸缩性和性能。

innodb_autoinc_lock_mode有三种取值，分别对应与不同锁定模式:

(1) innodb_autoinc_lock_mode = e(“传统"锁定模式)

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

(2) innodb_autoinc_lock_mode = 1(“连续"锁定模式)

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

(3 ) innodb_autoinc_lock_mode = 2(“交错"锁定模式)

从MySQL 8.o开始，交错锁模式是默认设置。在这种锁定模式下，所有类INSERT语句都不会使用表级AUTO-INC锁，并且可以同时执行多个语句。这是最快和最可扩展的锁定模式，但是当使用基于语句的复制或恢复方案时，从二进制日志重播SQL语句时，这是不安全的。在此锁定模式下，自动递增值保证在所有并发执行的所有类型的insert语句中是唯一且单调递增的。但是，由于多个语句可以同时生成数字（即，跨语句交叉编号），为任何给定语句插入的行生成的值可能不是连续的。如果执行的语句是"simple inserts”，其中要插入的行数已提前知道，除了“Mixed-mode inserts"之外，为单个语句生成的数字不会有间隙。然而，当执行“bulk inserts"时，在由任何给定语句分配的自动递增值中可能存在间隙。

d. 元数据锁（MDL）

MySQL5.5引入了meta data lock，简称MDL锁，属于表锁范畴。MDL的作用是，保证读写的正确性。比如，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，增加了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

因此，当对一个表做增删改查操作的时候，加MDL读锁;当要对表做结构变更操作的时候，加MDL写锁.

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性，解决了DML和DDL操作之间的一致性问题。不需要显式使用，在访问一个表的时候会被自动加上。

标签：语句,进阶,AUTO,意向锁,表锁,表级,INC
From： https://blog.csdn.net/2301_80912559/article/details/140702544