锁系列三之表锁

 表锁（Table Lock）  

是mysql中最基本的锁策略，并不依赖存储引擎，表锁是开销最小的策略（因为粒度比较大）。由于表锁是锁住整张表，所以可以避免死锁。不过最大的负面问题是锁竞争概率最高。并发性最差。

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锁 可以这么写：

案例

使用show open tables查看表锁使用情况

 加表S锁

释放表S锁

 

 加表X锁

释放表X锁

 

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

2、意向锁 （intention lock） 

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

1）意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁和行锁）的锁并存

2）意向锁是一种不与行级别锁冲突的表级锁。

意向锁要解决的问题

现在有两个事务，T1和T2，其中T2试图在该表级别上应用共享或者排他锁，如果没有意向锁存在，那么T2需要检查各个页或者行是否存在锁。如果存在意向锁，那么此时就会受到由T1控制的表级别意向锁的阻塞，T2在锁定该表前不必检查各个页或者行锁，而只需要检查表上的意向锁。

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

如果事务想要获得数据表中某些记录的共享锁，就需要在数据表上添加意向共享锁

如果事务想要获得数据表中某些记录的排他锁，就需要在数据表中添加意向排他锁

1）意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁）

 2）意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁）

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

因为共享和排他锁互斥，所以事务试图对table表加共享锁，必须满足两个条件

（1）当前没有其他事务持有table表的排他锁

（2）当前没有其他事务持有table表的任意一行的排他锁

为了检测是否满足第二个条件，事务必须在确保table表不存在任何排他锁的前提下，去检测表中的每一行是否存在排他锁。很明显这是一个效率很差的做法，但是有了意向锁，情况就不一样了。

案例

事务1：对teacher表的id=6加X锁，此时在表级别会有一个意向锁IX

事务2：对teacher表的id=5加X锁，此时在表级别会有一个意向锁IX

 

 事务2可以成功，原因是InnoDB采用行级别锁，不冲突，但是表级别都有意向锁IX，但是不冲突。

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

 

意向锁的并发性

意向锁不会与行级的共享 / 排他锁互斥！正因为如此，意向锁并不会影响到多个事务对不同数据行加排他锁时的并发性。

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

3、自增锁（AUTO-INC锁）

CREATE TABLE `teacher` (
 `id` int NOT NULL   AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

插入数据的方式总共分为三类

（1）“Simple inserts” （简单插入）

INSERT INTO `teacher` (name) VALUES ('zhangsan'), ('lisi');

（2）“Bulk inserts” （批量插入）

事先不知道要插入的行数 （和所需自动递增值的数量）的语句

INSERT ... SELECT 

或者

 LOAD DATA

（3）“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语句中是 唯一 且 单调递增 的。但是，由于多个语句可以同时生成数字（即，跨语句交叉编号），为任何给定语句插入的行生成的值可能不是连续的。

4、元数据锁（MDL锁）

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

案例

此时被阻塞

查看锁情况如下