innodb锁-意向锁

前言：
Intention Locks译称意向锁，Insert Intention Locks译称插入意向锁，本文参考官方文档进行学习说明

数据库版本：

SELECT VERSION();
±-----------+
| version() |
±-----------+
| 5.6.34-log |
±-----------+

数据库引擎：

show variables like ‘%engine%’;
±---------------------------±-------+
| Variable_name | Value |
±---------------------------±-------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| storage_engine | InnoDB |
±---------------------------±-------+

根据版本信息参考官方文档：

1.MySQL 5.6 Reference Manual-InnoDB Locking-Intention Locks(意向锁)

意向锁

InnoDB为了支持多粒度(表锁与行锁)的锁并存，引入意向锁。
意向锁是表级锁，可分为意向共享锁(IS锁)和意向排他锁(IX锁)。

InnoDB supports multiple granularity locking which permits coexistence of row locks and table locks. For example, a statement such as LOCK TABLES … WRITE takes an exclusive lock (an X lock) on the specified table. To make locking at multiple granularity levels practical, InnoDB uses intention locks. Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table. There are two types of intention locks

事务在请求S锁和X锁前，需要先获得对应的IS、IX锁

Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS lock or stronger on the table.
Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table.

意向锁产生的主要目的是为了处理行锁和表锁之间的冲突，用于表明某个事务正在某一行上持有了锁，或者准备去持有锁

The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table

通过"show engine innodb status"输出innodb监控可以查看到意向锁的信息：

TABLE LOCK table test.t trx id 10080(示例事务id) lock mode IX

意向锁类型兼容性矩阵：

	X	IX	S	IS
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
IS	Conflict	Compatible	Compatible	Compatible

个人总结：

1.意向锁之所以存在是为了提高事务加表锁时的检测性能，试想事务A对某一行加了锁(不管是共享锁还是排他锁)，事务B来尝试加表锁，这时就要检测，如果是遍历检测每一行显然是性能极低的，所以引入意向锁，这样的话只要事务A尝试对行/表加锁，先加意向锁，然后事务B来加表锁时，只需要判断和当前表的意向锁是否兼容即可，兼容则可以加表锁，不兼容则进入阻塞状态