InnoDB锁问题（1）

我就是我 2023-10-06 17:00 31阅读 0赞

**目录**

0. 序

1. 基础知识

1.1 事务（Transaction）及其ACID属性

1.2 并发事务处理带来的问题

1.3 事务隔离级别

2. 获取InnoDB行锁争用情况

3. InnoDB的行锁模式及加锁方法

4. InnoDB行锁实现方式

4.1在不通过索引条件查询时，InnoDB会锁定表中的所有记录

4.2查询同一个索引值的不同记录时会出现锁冲突

4.3可以通过不同索引锁不同行，但通过多个索引锁定同一行会产生锁冲突

4.4需要检查实际执行SQL时是否用到了索引

--------------------

## 0. 序 ##

本文内容来自《深入浅出MySQL》，作者：唐汉明，出版社：人民邮电出版社  
在此感谢作者，把书本内容摘要到此处只是为了更好的做记录和整理，方便和大家讨论。  
本文实验均经过亲自实验，与书本略有不同，mysql使用内部版本，版本号为：

1.  `mysql Ver 14.14 Distrib 5.6.34, for Linux (x86_64) using EditLine wrapper`

--------------------

InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。行级锁与表级锁本来就有许多不同之处，另外，事务的引入也带来了一些新问题。

下面我们先介绍一点背景知识，然后详细讨论InnoDB的锁问题。

内容较多，分两篇文章讲述：

[InnoDB锁问题（2）][InnoDB_2]

## 1. 基础知识 ##

### 1.1 事务（Transaction）及其ACID属性 ###

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。

*  原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
 *  一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
 *  隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
 *  持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

银行转帐就是事务的一个典型例子。

### 1.2 并发事务处理带来的问题 ###

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多的用户。但并发事务处理也会带来一些问题，主要包括以下几种情况。

*  更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题－－最后的更新覆盖了由其他事务所做的更新。例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖另一个编辑人员所做的更改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题。
 *  脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做”脏读”。
 *  不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。
 *  幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

### 1.3 事务隔离级别 ###

在上面讲到的并发事务处理带来的问题中，“更新丢失”通常是应该完全避免的。但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。

“脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式，基本上可分为以下两种。

*  一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。
 *  另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（MultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上 “串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。

为了解决“隔离”与“并发”的矛盾，ISO/ANSI SQL92定义了4个事务隔离级别，每个级别的隔离程度不同，允许出现的副作用也不同，应用可以根据自己的业务逻辑要求，通过选择不同的隔离级别来平衡 “隔离”与“并发”的矛盾。下表很好地概括了这4个隔离级别的特性。

4种隔离级别比较：

<table> 
 <thead> 
  <tr> 
   <th> <p>隔离级别\读数据一致性及允许的并发副作用</p> </th> 
   <th> <p>读数据一致性</p> </th> 
   <th> <p>脏读</p> </th> 
   <th> <p>不可重复读</p> </th> 
   <th> <p>幻读</p> </th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>未提交读（Read uncommitted）</td> 
   <td>最低级别，只能保证不读取物理上损坏的数据</td> 
   <td>是</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>已提交读（Read committed）</td> 
   <td>语句级</td> 
   <td>否</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>可重复读（Repeatable read）</td> 
   <td>事务级</td> 
   <td>否</td> 
   <td>否</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>可序列化（Serializable）</td> 
   <td>最高级别，事务级</td> 
   <td>否</td> 
   <td>否</td> 
   <td>否</td> 
  </tr> 
 </tbody> 
</table>

最后要说明的是：各具体数据库并不一定完全实现了上述4个隔离级别，例如，Oracle只提供Read committed和Serializable两个标准隔离级别，另外还提供自己定义的Read only隔离级别；SQL Server除支持上述ISO/ANSI SQL92定义的4个隔离级别外，还支持一个叫做“快照”的隔离级别，但严格来说它是一个用MVCC实现的Serializable隔离级别。MySQL 支持全部4个隔离级别，但在具体实现时，有一些特点，比如在一些隔离级别下是采用MVCC一致性读，但某些情况下又不是，这些内容在后面的章节中将会做进一步介绍。

## 2. 获取InnoDB行锁争用情况 ##

可以通过检查InnoDB\_row\_lock状态变量来分析系统上的行锁的争夺情况：

如果发现锁争用比较严重，如InnoDB\_row\_lock\_waits和InnoDB\_row\_lock\_time\_avg的值比较高，还可以通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。

通过查询information\_schema数据库中的表了解锁等待情况：

mysql> use information_schema;
    mysql> select * from INNODB_LOCKS;

通过设置InnoDB Monitors观察锁冲突情况：

mysql> use innodb_study;
    mysql> create table innodb_monitor(a INT) engine = innodb;
    Query OK, 0 rows affected, 1 warning (0.01 sec)

然后就可以使用下面的语句来进行查看：

mysql> show engine innodb status \G;
    *************************** 1. row ***************************
     Type: InnoDB
     Name:
    Status:
    =====================================
    2017-07-19 01:12:46 40a1c960 INNODB MONITOR OUTPUT
    =====================================
    Per second averages calculated from the last 1 seconds
    -----------------
    BACKGROUND THREAD
    -----------------
    srv_master_thread loops: 20 srv_active, 0 srv_shutdown, 10509 srv_idle
    srv_master_thread log flush and writes: 10529
    ----------
    SEMAPHORES
    ----------
    OS WAIT ARRAY INFO: reservation count 36
    OS WAIT ARRAY INFO: signal count 38
    Mutex spin waits 4, rounds 7, OS waits 0
    RW-shared spins 36, rounds 1080, OS waits 36
    RW-excl spins 0, rounds 20, OS waits 0
    Spin rounds per wait: 1.75 mutex, 30.00 RW-shared, 20.00 RW-excl
    ------------
    TRANSACTIONS
    ------------
    Trx id counter 3959
    Purge done for trx's n:o < 3956 undo n:o < 0 state: running but idle
    History list length 78
    LIST OF TRANSACTIONS FOR EACH SESSION:
    ---TRANSACTION 0, not started
    MySQL thread id 5, OS thread handle 0x40a1c960, query id 206 localhost root init
    show engine innodb status
    ---TRANSACTION 3951, not started
    MySQL thread id 4, OS thread handle 0x4097a960, query id 116 localhost root cleaning up
    ---TRANSACTION 3924, not started
    MySQL thread id 1, OS thread handle 0x4c9fb960, query id 88 localhost root cleaning up
    --------
    FILE I/O
    --------
    I/O thread 0 state: waiting for i/o request (insert buffer thread)
    I/O thread 1 state: waiting for i/o request (log thread)
    I/O thread 2 state: waiting for i/o request (read thread)
    I/O thread 3 state: waiting for i/o request (read thread)
    I/O thread 4 state: waiting for i/o request (read thread)
    I/O thread 5 state: waiting for i/o request (read thread)
    I/O thread 6 state: waiting for i/o request (write thread)
    I/O thread 7 state: waiting for i/o request (write thread)
    I/O thread 8 state: waiting for i/o request (write thread)
    I/O thread 9 state: waiting for i/o request (write thread)
    Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
    ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
    Pending flushes (fsync) log: 0; buffer pool: 0
    397 OS file reads, 287 OS file writes, 196 OS fsyncs
    0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
    -------------------------------------
    INSERT BUFFER AND ADAPTIVE HASH INDEX
    -------------------------------------
    Ibuf: size 1, free list len 0, seg size 2, 0 merges
    merged operations:
    insert 0, delete mark 0, delete 0
    discarded operations:
    insert 0, delete mark 0, delete 0
    Hash table size 276707, node heap has 0 buffer(s)
    0.00 hash searches/s, 0.00 non-hash searches/s
    ---
    LOG
    ---
    Log sequence number 1847772
    Log flushed up to 1847772
    Pages flushed up to 1847772
    Last checkpoint at 1847772
    0 pending log writes, 0 pending chkp writes
    91 log i/o's done, 0.00 log i/o's/second
    ----------------------
    BUFFER POOL AND MEMORY
    ----------------------
    Total memory allocated 138739712; in additional pool allocated 0
    Dictionary memory allocated 67011
    Buffer pool size 8192
    Free buffers 7896
    Database pages 296
    Old database pages 0
    Modified db pages 0
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages made young 0, not young 0
    0.00 youngs/s, 0.00 non-youngs/s
    Pages read 242, created 54, written 267
    0.00 reads/s, 0.00 creates/s, 0.00 writes/s
    No buffer pool page gets since the last printout
    Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    LRU len: 296, unzip_LRU len: 0
    I/O sum[0]:cur[0], unzip sum[0]:cur[0]
    --------------
    ROW OPERATIONS
    --------------
    0 queries inside InnoDB, 0 queries in queue
    0 read views open inside InnoDB
    Main thread process no. 26388, id 1232554336, state: sleeping
    Number of rows inserted 13, updated 0, deleted 3, read 45
    0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
    ----------------------------
    END OF INNODB MONITOR OUTPUT
    ============================
    1 row in set (0.00 sec)

监视器可以通过发出下列语句来停止：

> 1.  `mysql> drop table innodb_monitor;`
> 2.  `Query OK, 0 rows affected (0.00 sec)`

设置监视器后，在show engine innodb status的显示内容中，会有详细的当前锁等待的信息，包括表名、锁类型、锁定记录的情况等，便于进行进一步的分析和问题的确定。`打开监视器以后，默认情况下每15秒会向日志中记录监控的内容，如果长时间打开会导致.err文件变得非常的巨大`，所以用户在确认问题原因之后，要记得删除监控表以关闭监视器，或者通过使用“—console”选项来启动服务器以关闭写日志文件。

## 3. InnoDB的行锁模式及加锁方法 ##

InnoDB实现了以下两种类型的行锁：

*  共享锁（S）：允许一个事务执行读操作，阻止其他事务获得相同数据集的排他锁。
 *  排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。
 *  意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
 *  意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

上述锁模式的兼容情况具体如下表所示。

InnoDB行锁模式兼容性列表：

<table> 
 <thead> 
  <tr> 
   <th> <p>当前锁模式\是否兼容\请求锁模式</p> </th> 
   <th> <p>X</p> </th> 
   <th> <p>IX</p> </th> 
   <th> <p>S</p> </th> 
   <th> <p>IS</p> </th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>X</td> 
   <td>冲突</td> 
   <td>冲突</td> 
   <td>冲突</td> 
   <td>冲突</td> 
  </tr> 
  <tr> 
   <td>IX</td> 
   <td>冲突</td> 
   <td>兼容</td> 
   <td>冲突</td> 
   <td>兼容</td> 
  </tr> 
  <tr> 
   <td>S</td> 
   <td>冲突</td> 
   <td>冲突</td> 
   <td>兼容</td> 
   <td>兼容</td> 
  </tr> 
  <tr> 
   <td>IS</td> 
   <td>冲突</td> 
   <td>兼容</td> 
   <td>兼容</td> 
   <td>兼容</td> 
  </tr> 
 </tbody> 
</table>

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。

意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X）；对于普通SELECT语句，InnoDB不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁。

>  *  共享锁（S）：SELECT \* FROM table\_name WHERE … LOCK IN SHARE MODE。
>  *  排他锁（X）：SELECT \* FROM table\_name WHERE … FOR UPDATE。

用SELECT … IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT… FOR UPDATE方式获得排他锁。

在如下表所示的例子中，使用了SELECT … IN SHARE MODE加锁后再更新记录，看看会出现什么情况，其中actor表的actor\_id字段为主键。

InnoDB存储引擎的共享锁例子：

//数据准备
    mysql> create table actor(actor_id int, first_name varchar(10), last_name varchar(10)) engine=innodb;
    Query OK, 0 rows affected (0.01 sec)
    mysql> alter table actor add key id(actor_id);
    Query OK, 0 rows affected (0.01 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    mysql> insert into actor values(1, 'PENELOPE', 'GUINESS');
    Query OK, 1 row affected (0.00 sec)
    //session_1
    mysql> set autocommit = 0;
    Query OK, 0 rows affected (0.00 sec)
    mysql> select actor_id, first_name, last_name from actor where actor_id = 1;
    +----------+------------+-----------+
    | actor_id | first_name | last_name |
    +----------+------------+-----------+
    | 1 | PENELOPE | GUINESS |
    +----------+------------+-----------+
    1 row in set (0.00 sec)
    //session_2
    mysql> set autocommit = 0;
    mysql> select actor_id, first_name, last_name from actor where actor_id = 1;
    +----------+------------+-----------+
    | actor_id | first_name | last_name |
    +----------+------------+-----------+
    | 1 | PENELOPE | GUINESS |
    +----------+------------+-----------+
    1 row in set (0.00 sec)
    //session_1
    //当前session对actor_id=178的记录加share mode 的共享锁：
    mysql> select actor_id, first_name, last_name from actor where actor_id = 1 lock in share mode;
    +----------+------------+-----------+
    | actor_id | first_name | last_name |
    +----------+------------+-----------+
    | 1 | PENELOPE | GUINESS |
    +----------+------------+-----------+
    1 row in set (0.00 sec)
    //session_2
    //其他session仍然可以查询记录，并也可以对该记录加share mode的共享锁：
    mysql> select actor_id, first_name, last_name from actor where actor_id = 1 lock in share mode;
    +----------+------------+-----------+
    | actor_id | first_name | last_name |
    +----------+------------+-----------+
    | 1 | PENELOPE | GUINESS |
    +----------+------------+-----------+
    1 row in set (0.00 sec)
    //session_1
    //当前session对锁定的记录进行更新操作，等待锁：
    mysql> update actor set last_name = 'MONROE T' where actor_id = 1;
    //等待
    //session_2
    //其他session也对该记录进行更新操作，则会导致死锁退出：
    mysql> update actor set last_name = 'MONROE T' where actor_id = 1;
    ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
    //session_1
    //获得锁后，可以成功更新：
    mysql> update actor set last_name = 'MONROE T' where actor_id = 1;
    Query OK, 0 rows affected (12.27 sec)
    Rows matched: 1 Changed: 0 Warnings: 0

当使用SELECT…FOR UPDATE加锁后再更新记录，出现如下表所示的情况：

//数据准备
    //session_1
    mysql> set autocommit = 0;
    mysql> select actor_id, first_name, last_name from actor where actor_id = 1;
    +----------+------------+-----------+
    | actor_id | first_name | last_name |
    +----------+------------+-----------+
    | 1 | PENELOPE | MONROE T |
    +----------+------------+-----------+
    1 row in set (0.00 sec)
    //session_2
    mysql> set autocommit = 0;
    mysql> select actor_id,first_name,last_name from actor where actor_id = 1;
    +----------+------------+-----------+
    | actor_id | first_name | last_name |
    +----------+------------+-----------+
    | 1 | PENELOPE | MONROE T |
    +----------+------------+-----------+
    1 row in set (0.00 sec)
    //session_1
    //当前session对actor_id=1的记录加for update的排它锁：
    mysql> select actor_id, first_name, last_name from actor where actor_id = 1 for update;
    +----------+------------+-----------+
    | actor_id | first_name | last_name |
    +----------+------------+-----------+
    | 1 | PENELOPE | MONROE T |
    +----------+------------+-----------+
    1 row in set (0.00 sec)
    //session_2
    //其他session可以查询该记录，但是不能对该记录加共享锁，会等待获得锁：
    mysql> select actor_id, first_name, last_name from actor where actor_id = 1;
    +----------+------------+-----------+
    | actor_id | first_name | last_name |
    +----------+------------+-----------+
    | 1 | PENELOPE | MONROE T |
    +----------+------------+-----------+
    1 row in set (0.00 sec)
    mysql> select actor_id, first_name, last_name from actor where actor_id = 1 for update;
    //等待
    //session_1
    //当前session可以对锁定的记录进行更新操作，更新后释放锁：
    mysql> update actor set last_name = 'GUINESS' where actor_id = 1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1 Changed: 1 Warnings: 0
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    //session_2
    //其他session获得锁，得到其他session提交的记录：
    mysql> select actor_id, first_name, last_name from actor where actor_id = 1 for update;
    +----------+------------+-----------+
    | actor_id | first_name | last_name |
    +----------+------------+-----------+
    | 1 | PENELOPE | GUINESS |
    +----------+------------+-----------+
    1 row in set (8.09 sec)

## 4. InnoDB行锁实现方式 ##

InnoDB行锁是通过给索引上的索引项来加锁实现的，如果没有索引，InnoDB将通过隐藏的聚族索引来对记录加锁。InnoDB行锁分为3种情形（`具体使用哪种锁请参考第7节`）：

*  Record Lock：对索引项加锁
 *  Gap Lock：对索引项之间的「间隙」加锁
 *  Next-key Lock：前两种索引的组合，即对一个索引项以及这个索引项到相邻索引项之间的间隙加锁

下面通过几个实验来说明InnoDB在处理SELECT \* FROM … FOR UPDATE的时候其行锁的特性。其中实验分别用session\_1和session\_2两个线程来模拟对锁的竞争（注意实验之间要提交未提交的事务，并删除相关的表）。

### 4.1在不通过索引条件查询时，InnoDB会锁定表中的所有记录 ###

实验：创建tab\_no\_index表，由两个session分别请求两行的排它锁

//数据准备
    mysql> create table tab_no_index(id int, name varchar(10)) engine=innodb;
    Query OK, 0 rows affected (0.01 sec)
    mysql> insert into tab_no_index values(1, '1'),(2, '2'),(3, '3'),(4, '4');
    Query OK, 4 rows affected (0.00 sec)
    Records: 4 Duplicates: 0 Warnings: 0
    //session_1:
    mysql> set autocommit=0;
    Query OK, 0 rows affected (0.00 sec)
    mysql> select * from tab_no_index where id = 1;
    +------+------+
    | id | name |
    +------+------+
    | 1 | 1 |
    +------+------+
    1 row in set (0.00 sec)
    //session_2
    mysql> set autocommit=0;
    Query OK, 0 rows affected (0.00 sec)
    mysql> select * from tab_no_index where id = 2;
    +------+------+
    | id | name |
    +------+------+
    | 2 | 2 |
    +------+------+
    1 row in set (0.00 sec)
    //session_1
    mysql> select * from tab_no_index where id = 1 for update;
    +------+------+
    | id | name |
    +------+------+
    | 1 | 1 |
    +------+------+
    1 row in set (0.00 sec)
    //session_2
    mysql> select * from tab_no_index where id = 2 for update;
    //等待

现象：session\_1给一行加了排它锁，但session\_2请求其他行排它锁时出现了锁等待。

原因：在没有索引的情况下，InnoDB将通过隐藏的聚族索引来对记录加锁，故实际效果类似与使用了表锁。

对比实验：创建tab\_with\_index表，id字段有普通索引

//数据准备
    mysql> create table tab_with_index(id int, name varchar(10)) engine=innodb;
    Query OK, 0 rows affected (0.01 sec)
    mysql> alter table tab_with_index add index id(id);
    Query OK, 0 rows affected (0.01 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    mysql> insert into tab_with_index values(1, '1'),(2, '2'),(3, '3'),(4, '4');
    Query OK, 4 rows affected (0.00 sec)
    Records: 4 Duplicates: 0 Warnings: 0
    //session_1
    mysql> set autocommit=0;
    Query OK, 0 rows affected (0.00 sec)
    mysql> select * from tab_with_index where id = 1;
    +------+------+
    | id | name |
    +------+------+
    | 1 | 1 |
    +------+------+
    1 row in set (0.00 sec)
    //session_2
    mysql> set autocommit=0;
    Query OK, 0 rows affected (0.01 sec)
    mysql> select * from tab_with_index where id = 2;
    +------+------+
    | id | name |
    +------+------+
    | 2 | 2 |
    +------+------+
    1 row in set (0.00 sec)
    //session_1
    mysql> select * from tab_with_index where id = 1 for update;
    +------+------+
    | id | name |
    +------+------+
    | 1 | 1 |
    +------+------+
    1 row in set (0.00 sec)
    //session_2
    mysql> select * from tab_with_index where id = 2 for update;
    +------+------+
    | id | name |
    +------+------+
    | 2 | 2 |
    +------+------+
    1 row in set (0.00 sec)

### 4.2查询同一个索引值的不同记录时会出现锁冲突 ###

实验：创建tab\_with\_index表，其中id字段有索引，name字段没索引，用两个session查询使用相同索引的不同记录：

//数据准备
    mysql> create table tab_with_index(id int, name varchar(10)) engine=innodb;
    Query OK, 0 rows affected (0.01 sec)
    mysql> alter table tab_with_index add index id(id);
    Query OK, 0 rows affected (0.01 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    mysql> insert into tab_with_index values(1, '1'),(1, '4');
    Query OK, 2 rows affected (0.01 sec)
    Records: 2 Duplicates: 0 Warnings: 0
    mysql> select * from tab_with_index where id = 1;
    +------+------+
    | id | name |
    +------+------+
    | 1 | 1 |
    | 1 | 4 |
    +------+------+
    2 rows in set (0.00 sec)
    //session_1
    mysql> set autocommit=0;
    Query OK, 0 rows affected (0.00 sec)
    mysql> select * from tab_with_index where id = 1 and name = '1' for update;
    +------+------+
    | id | name |
    +------+------+
    | 1 | 1 |
    +------+------+
    1 row in set (0.00 sec)
    //session_2
    //虽然session_2访问的是和session_1不同的记录，但是因为使用了相同的索引，所以需要等待锁:
    mysql> set autocommit=0;
    Query OK, 0 rows affected (0.00 sec)
    mysql> select * from tab_with_index where id = 1 and name = '4' for update;
    //等待

原因：InnoDB的行锁是针对索引加的，不是针对记录加的锁。上述实验虽然两个session访问的是不同行的记录，但是使用了相同的索引键，所以出现了锁冲突。

### 4.3可以通过不同索引锁不同行，但通过多个索引锁定同一行会产生锁冲突 ###

当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，不论使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。但是若不同索引锁定的是同一行，仍然会产生锁冲突。

实验：创建tab\_with\_index表，id字段设为主键索引，name字段设为普通索引

//准备数据
    mysql> create table tab_with_index(id int, name varchar(10)) engine=innodb;
    Query OK, 0 rows affected (0.00 sec)
    mysql> alter table tab_with_index add primary key(`id`);
    Query OK, 0 rows affected (0.01 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    mysql> alter table tab_with_index add index name(name);
    Query OK, 0 rows affected (0.01 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    mysql> insert into tab_with_index values(1, '1'),(2, '2'),(3, '3'),(4, '1');
    Query OK, 4 rows affected (0.00 sec)
    Records: 4 Duplicates: 0 Warnings: 0
    //session_1
    mysql> set autocommit=0;
    Query OK, 0 rows affected (0.00 sec)
    mysql> select * from tab_with_index where name = '1' for update;
    +----+------+
    | id | name |
    +----+------+
    | 1 | 1 |
    | 4 | 1 |
    +----+------+
    2 rows in set (0.00 sec)
    //session_2
    //session_2使用id的索引访问记录，因为记录没有被加锁，所以可以获得锁
    mysql> set autocommit=0;
    Query OK, 0 rows affected (0.00 sec)
    mysql> select * from tab_with_index where name = '2' for update;
    +----+------+
    | id | name |
    +----+------+
    | 2 | 2 |
    +----+------+
    1 row in set (0.00 sec)
    //由于访问记录已被session_1通过name索引锁住，即使通过id索引访问该记录也需要等待锁释放
    mysql> select * from tab_with_index where id = 4 for update;
    //等待

### 4.4需要检查实际执行SQL时是否用到了索引 ###

即便在条件中使用了索引字段，但是实际执行过程中是否使用索引来检索数据是由MySQL通过判断不同执行计划代价来决定的。可以通过`explain`命令来检查SQL的执行计划。

[InnoDB_2]: https://blog.csdn.net/LiZhen314/article/details/115181521?spm=1001.2014.3001.5501https://blog.csdn.net/LiZhen314/article/details/115181521?spm=1001.2014.3001.5501