MySQL 原理与优化：意向锁，IS，IX

先来看一种应用场景，当有两个线程 A 和 B 分别访问一张表。

线程 A ：针对表中的一条语句进行 update 操作，假设根据记录的 id 更新记录，此时开启的事务会对这条记录加行锁。

线程 B：如果需要进行锁表的操作，例如：lock tables [table_name] read/write，也就是对表加上读写锁。在加表锁之前需要检查行记录是否加锁，如果有加锁就需要等待锁释放以后再进行表锁的后续操作。此时检查行锁的操作，就需要从表的第一行向下逐一进行，直到最后一行记录。

大家知道对表进行扫描操作的效率是非常低的，此时就引入了意向锁。

意向锁就是避免 DML 在执行时，加的行锁和表锁发生冲突而引入的，使得表锁不用加茶妹行数据是否加锁，使用意向锁来减少表锁的检查。

如上图所示，在引入意向锁之后按照这个步骤进行加锁

1. 线程 A 对表中的某条记录加行锁。

2. 同时对表加上意向锁。

3. 当线程 B 对表加表锁的时候，发现线程 A 加的意向锁，会将表锁与意向锁进行对比。如果两锁互斥：等待意向锁释放执行表锁的操作，如果两锁不互斥：就执行表锁的操作。

按照这个执行步骤，线程 B 就不用去对整张表进行全表扫描了。

意向锁分为两类：

意向共享锁：IS，select ... lock in share mode

意向排他锁：IX，insert 、update、delete、select ... for update

从上面的语句可以看出意向共享锁主要对应查询操作，意向排他锁对应更新操作。

意向锁与表锁的互斥情况：

意向共享锁：与表共享锁（read）兼容，与表锁排他锁（write）互斥。

意向排他锁：与表共享锁（read）以及排他锁（write）都互斥。意向锁之间不会互斥。

意向锁对记录进行读操作的时候，表锁可以加读锁，也就是其他的线程可以读表，但是不能写表。当意向锁对记录进行写入操作的时候，表锁线程不能对表的数据进行读和写的操作，需要等到意向排他锁对应的事务提交以后才能，进行后续操作。

下面来看两个例子

第一个例子

开启线程，通过在 sql 语句后面加上 lock in share mode，表示对表加上意向共享锁

begin;

select * from course where id =2 lock in share mode;

通过 sql 语句查询意向锁的情况

select object_schema, object_name ,index_name, lock_type ,lock_mode, lock_data from performance_schema.data_locks;

从上图可以看到 select 语句在 record（行）上加了共享锁（read），在 table（表）上加了 IS 共享锁。

打开另外一个客户端执行如下语句

lock tables course read;

上图可见，此时加锁是成功的说明意向共享锁和表共享锁（read）是兼容的。

接着加上表的互斥锁 write

lock tables course write;

发现光标闪动，说明线程阻塞了。因为意向共享锁和表排他锁（write）是互斥的

回到第一个事务，将其 commit；

commit；

此时 回到第二个客户端，看到 lock tables course write; 语句得以顺利执行。

第二个例子

开启一个事务

begin;

update course set name = 'Json' where id =3;

通过 sql 语句查询意向锁的情况

select object_schema, object_name ,index_name, lock_type ,lock_mode, lock_data from performance_schema.data_locks;

从查询结果来看，针对表 course ，update 语句在行锁上面加了一个排他锁，在表的级别加上了一个意向排他锁。

此时切换到第二个客户端，执行锁表的操作,执行表共享锁（read）

lock tables course read;

由于表共享锁与意向排他锁有互斥，因此 lock 语句光标停留不动，线程阻塞。

接着通过 unlock tables;释放掉这个 read lock

unlock tables;

然后尝试加上表互斥锁（write）

lock tables course write;

由于表互斥锁与意向排他锁有互斥，因此 lock 语句光标停留不动，线程阻塞。