MySQL InnoDB锁

Intention Locks（意向锁）

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:
- An intention shared lock (IS) indicates that a transaction intends to set a shared lock on individual rows in a table.
- An intention exclusive lock (IX) indicates that that a transaction intends to set an exclusive lock on individual rows in a table.

For example, SELECT ... FOR SHARE sets an IS lock, and SELECT ... FOR UPDATE sets an IX lock.

行锁

InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。如果update操作没有命中索引，也无法使用行锁，将要退化为表锁。
ref:InnoDB，5项最佳实践，知其所以然？

共享锁

　　共享锁(S)表示对数据进行读操作。因此多个事务可以同时为一个对象加共享锁。（如果试衣间的门还没被锁上，顾客都能够同时进去参观）

产生共享锁的sql：select * from ad_plan lock in share mode;

ref：Mysql的排他锁和共享锁

Gap Lock

Gap locks in InnoDB are “purely inhibitive”, which means that their only purpose is to prevent other transactions from inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function.

插入意向锁

插入意向锁Mysql官方对其的解释:

An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

可以看出插入意向锁是在插入的时候产生的，插入意向锁是一种Gap Lock，不会被互相锁住，因为数据行并不冲突。
在多个事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

ref: 为什么开发人员必须要了解数据库锁？

索引与锁的关系

MySQL InnoDB锁机制全面解析分享

MySQL Gap Lock问题

关于查询中，什么时候用MVCC，什么时候用锁

查看官方文档。
1.关于MVCC读:

不显式加『lock in share mode』与『for update』的『select』操作都属于快照读。

---

15.5.2.3 Consistent Nonlocking Reads

A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by earlier statements within the same transaction. This exception causes the following anomaly: If you update some rows in a table, a SELECT sees the latest version of the updated rows, but it might also see older versions of any rows. If other sessions simultaneously update the same table, the anomaly means that you might see the table in a state that never existed in the database.

consistent read利用多版本查询数据库快照。

If the transaction isolation level is REPEATABLE READ (the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction. You can get a fresher snapshot for your queries by committing the current transaction and after that issuing new queries.

隔离级别是REPEATABLE READ，就会在同一事务中读取第一次查询的快照，来保证可重复读取。

With READ COMMITTED isolation level, each consistent read within a transaction sets and reads its own fresh snapshot.

隔离级别是READ COMMITTED，就会读取最新快照。

Consistent read is the default mode in which InnoDB processes SELECT statements in READ COMMITTED and REPEATABLE READ isolation levels. A consistent read does not set any locks on the tables it accesses, and therefore other sessions are free to modify those tables at the same time a consistent read is being performed on the table.

Consistent read是默认的查询方法（隔离级别是REPEATABLE READ还是READ COMMITTED）。通过不加锁提高了并发量。

Suppose that you are running in the default REPEATABLE READ isolation level. When you issue a consistent read (that is, an ordinary SELECT statement), InnoDB gives your transaction a timepoint according to which your query sees the database. If another transaction deletes a row and commits after your timepoint was assigned, you do not see the row as having been deleted. Inserts and updates are treated similarly.

指出一个事务中，只能看到第一次查询的快照（即使有其他事务对相关记录进行了操作，也不能看到修改后结果，保证了可重复读取）
2.关于锁读：

15.5.2.4 Locking Reads

If you query data and then insert or update related data within the same transaction, the regular SELECT statement does not give enough protection. Other transactions can update or delete the same rows you just queried. InnoDB supports two types of locking reads that offer extra safety:

在一个事务中，先查询记录，再插入/更新前面查询记录的相关数据，在这种场景下的查询语句会使用共享锁。典型场景是更新操作：

update table set ? where ?;
当Update SQL被发给MySQL后，MySQL Server会根据where条件，读取第一条满足条件的记录，然后InnoDB引擎会将第一条记录返回，并加锁，待MySQL Server收到这条加锁的记录之后，会再发起一个Update请求，更新这条记录。一条记录操作完成，再读取下一条记录，直至没有满足条件的记录为止。因此，Update操作内部，就包含了当前读。
ref: MySQL InnoDB锁机制全面解析分享

ref: MySQL官方文档

幻读的典型场景

线程1查询符合条件的记录数（可能很多，耗时长）
线程2插入一条记录，这条记录在线程1查询条件内。此时线程1已经扫描过了这条记录区域，导致线程1计数漏了这条。
ref：Mysql数据库事务的隔离级别和锁的实现原理分析

mysql命令行

INNODB_LOCKS表主要包含了InnoDB事务锁的具体情况，包括事务正在申请加的锁和事务加上的锁：

select * from information_schema.INNODB_LOCKS;
lock_data是事务锁住的主键值，若是表锁，则该值为null

INNODB_LOCK_WAITS表包含了被blocked的事务的锁等待的状态：

select * from information_schema.INNODB_LOCK_WAITS;

information_schema中Innodb相关表用于分析sql查询锁的使用情况介绍

显示innodb引擎的事务和锁的情况：

show engine innodb status;

mysql中查看sql语句的加锁信息

Ref

调试：MySQL Gap Lock问题
详解

MySQL InnoDB锁机制全面解析分享
MySQL锁系列（七）之 锁算法详解

MySQL锁详解