InnoDB锁问题（1）

目录

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）

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种隔离级别比较：

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

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

2. 获取InnoDB行锁争用情况

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

mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 145770 |
| Innodb_row_lock_time_avg | 20824 |
| Innodb_row_lock_time_max | 51004 |
| Innodb_row_lock_waits | 7 |
+-------------------------------+--------+
5 rows in set (0.00 sec)

如果发现锁争用比较严重，如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行锁模式兼容性列表：

当前锁模式\是否兼容\请求锁模式	X	IX	S	IS
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	冲突	兼容
S	冲突	冲突	兼容	兼容
IS	冲突	兼容	兼容	兼容

如果一个事务请求的锁模式与当前的锁兼容，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的执行计划。