思考并回答以下问题：

只有通过索引字段检索数据的查询语句或者更新语句，才可能施加行级锁，否则InnoDB将使用表级锁。怎么理解？
给MyISAM表施加表级锁不会导致死锁问题的发生，给InnoDB表施加行级锁可能导致死锁问题的发生。为什么？
MySQL为什么会存在内存中的数据与外存中的数据不同步？
for update必须写在事务吗？
InnoDB存储引擎支持行级锁和表级锁吗？
为什么读锁也称为共享锁，写锁也称为排他锁或者独占锁？
lock tables account read;是什么意思？
使用low_priority write选项降低写锁的优先级，以便MySQL客户机A及时取得读锁，不被饿死。怎么理解？

在同一时刻，如果数据库仅仅为单个MySQL客户机提供服务，仅通过事务机制即可实现数据库的数据一致性。但更多时候，在同一时刻，多个并发用户往往需要同时访问（检索或者更新）数据库中的同一个数据，此时仅仅通过事务机制无法保证多用户同时访问同一数据的数据一致性，因此有必要引入另一种机制实现数据的多用户并发访问。锁机制是MySQL实现多用户并发访问的基石。

锁机制的必要性

MySQL客户机A与MySQL客户机B执行同一条SQL语句“select * from account;”时产生的结果截然不同，继而产生数据不一致问题。这种数据不一致问题产生的深层次原因在于，内存中的数据与外存中的数据不同步造成的（或者说是由内存中的表记录与外存中的表记录之间存在“同步延迟”造成的）。

MySQL客户机A访问数据时，如果能够对该数据“加锁”，阻塞（或者延迟）MySQL客户机B对该数据访问，直到MySQL客户机A数据访问结束，内存与外存中的数据同步后，MySQL客户机A对该数据“解锁”，“解锁”后，被阻塞的MySQL客户机B“被唤醒”，继而可以继续访问该数据，这样就可以实现多用户下数据的并发访问，如图1所示。

图1 多用户下数据并发访问的实现原理

简言之，内存数据与外存数据之间的同步延迟，可以通过锁机制将“并发访问”延迟，进而实现数据的一致性访问以及并发访问。

当然，单条更新语句运行期间也会产生同步延迟。update语句的执行过程可以简单描述为如下步骤，如图2所示。

1	update account set balance = balance + 800 where account_no = 2;

图2 单条更新语句的延迟无法避免

步骤1：使用索引查询是否存在“account_no=2”的账户信息。

步骤2：若存在，将该账户信息从外存加载到内存，在内存中生成old记录。

步骤3：修改old记录中的balance字段值，在内存中生成new记录。

步骤4：将内存中的new记录写入到外存，完成update操作。

上述每一个步骤的执行都需要一定的时间间隔（虽然短暂）。单个update语句运行期间，从步骤1运行到步骤4同样会产生延迟，这种延迟根本就无法避免，数据库开发人员也无需理会这种延迟，毕竟单条SQL语句运行期间会作为一个“原子”操作运行。数据库开发人员需要考虑的问题是:如何借助锁机制，解决多用户并发访问可能引起的数据不一致问题？

MySQL锁机制的基础知识

简单地说，MySQL锁机制涉及的内容包括：锁的粒度、隐式锁与显式锁、锁的类型、锁的钥匙以及锁的生命周期等。

锁的粒度

锁的粒度是指锁的作用范围。就像读者有了防盗门的钥匙就可以回到“家”中，有了卧室的钥匙就可以进到卧室，有了保险柜的钥匙就可以打开保险柜，每一种“资源”存在一个与之对应“粒度”的锁，数据库亦是如此。对于MySQL而言，锁的粒度可以分为服务器级锁（server-level locking）和存储引擎级锁（storage-engine-level locking）。

服务器级锁是以服务器为单位进行加锁，它与表的存储引擎无关。在MySQL基础知识章节中讲解数据库备份时，为了保证数据备份过程中不会有新的数据写入，使用MySQL命令“flush tables with read lock;”锁定了当前MySQL服务实例，该锁是服务器级锁，并且是服务器级“读锁”。

也就是说， MySQL客户机A执行了MySQL命令“flush tables with read lock;”，锁定了当前MySQL服务实例后，MySQL客户机A针对服务器的写操作（例如insert，update，delete以及create等语句）抛出如下错误信息。

1	ERROR 1223（HY000）: Can't execute the query because you have a conflicting read lock

其他MySQL客户机（例如MySQL客户机B）针对服务器的写操作（例如insert，update，delete以及create等语句）被阻塞。

只有MySQL客户机A执行“unlock tables;”命令或者关闭MySQL客户机A的服务器连接，释放服务器级读锁后，才会“唤醒”MySQL客户机B的写操作，MySQL客户机B的写操作才能得以继续执行。MySQL客户机A施加的服务器级锁，只有MySQL客户机A才能解锁。

例如，在MySQL客户机A上锁定了当前MySQL服务实例后，在MySQL客户机B上创建视图test view将被阻塞，而在MySQL客户机A上创建视图test view将产生错误信息（ERROR 1223）。MySQL客户机A解锁后，MySQL客户机B才能成功创建视图test view，如图3所示（注意图中的粗体字）。从执行结果可以看出，MySQL客户机A施加服务器级锁后，该锁对MySQL客户机A的后续操作以及对MySQL客户机B的后续操作产生的效果并不相同。

图3 服务器级读锁的使用

存储引擎级锁分为表级锁以及行级锁。表级锁是以表为单位进行加锁，MyISAM与InnoDB存储引擎的表都支持表级锁。行级锁是以记录为单位进行加锁，在MyISAM与InnoDB存储引擎中，只有InnoDB存储引擎支持行级锁。

小结：服务器级锁的粒度最大，表级锁的粒度次之，行级锁的粒度最小。锁粒度越小，并发访问性能就越高，越适合做并发更新操作（InnoDB表更适合做并发更新操作）；锁粒度越大，并发访问性能就越低，越适合做并发查询操作（MyISAM表更适合做并发查询操作）。另外，锁粒度越小，完成某个功能时所需要的加锁、解锁的次数就会越多，反而会消耗较多的服务器资源，甚至会出现资源的恶性竞争，甚至发生死锁问题。

对于“选课系统”而言，系统需要为上百名学生，甚至几百名学生同时提供选课、调课、退课服务。为了提高并发性能，“选课系统”将选用行级锁，这也是“选课系统”的各个数据库表使用InnoDB存储引擎的原因（InnoDB存储引擎支持行级锁）。

隐式锁与显式锁

MySQL锁分为隐式锁以及显式锁。多个MySQL客户机并发访问同一个数据时，为保证数据的一致性，数据库管理系统会自动地为该数据加锁、解锁，这种锁称为隐式锁。隐式锁无需数据库开发人员维护（包括粒度、加锁时机、解锁时机等）。

如果应用系统存在多用户并发访问数据的行为，有时单靠隐式锁无法实现数据的一致性访问要求（例如多个学生同时选修同一门课程），此时需要数据库开发人员手动地加锁、解锁，这种锁称为显式锁。对于显式锁而言，数据库开发人员不仅需要确定锁的粒度，还需要确定锁的加锁时机（何时加锁）、解锁时机（何时解锁）以及锁的类型。

锁的类型

锁的类型包括读锁（read lock）和写锁（write lock），其中读锁也称为共享锁，写锁也称为排他锁或者独占锁。

读锁（read lock）：如果MySQL客户机A对某个数据施加了读锁，加锁期间允许其他MySQL客户机（例如MySQL客户机B）对该数据施加读锁，但会阻塞其他MySQL客户机（例如MySQL客户机C）对该数据施加写锁，除非MySQL客户机A释放该数据的读锁。简言之，读锁允许其他MySQL客户机对数据同时“读”，但不允许其他MySQL客户机对数据任何“写”（如图4所示）。如果“数据”是表，则该读锁是表级读锁；如果“数据”是记录，则该读锁是行级读锁。

图4 读锁的使用

写锁（write lock）：如果MySQL客户机A对某个数据施加了写锁，加锁期间会阻塞其他MySQL客户机（例如MySQL客户机B）对该数据施加读锁以及写锁，除非MySQL客户机A释放该数据的写锁。简言之，写锁不允许其他MySQL客户机对数据同时“读”，也不允许其他MySQL客户机对数据同时“写”（见图5）。如果“数据”是表，则该写锁是表级写锁；如果“数据”是记录，则该写锁是行级写锁。

图5 写锁的使用

锁的钥匙

多个MySQL客户机并发访问同一个数据时，如果MySQL客户机A对该数据成功地施加了锁，那么只有MySQL客户机A拥有这把锁的“钥匙”，也就是说，只有MySQL客户机A能够对该锁进行解锁操作。

锁的生命周期

锁的生命周期是指在同一个MySQL会话内，对数据加锁到解锁之间的时间间隔。锁的生命周期越长，并发访问性能就越低；锁的生命周期越短，并发访问性能就越高。另外，锁是数据库管理系统重要的数据库资源，需要耗费一定的服务器内存，锁的生命周期越长，该锁占用服务器内存的时间间隔就越长；锁的生命周期越短，该锁占用服务器内存的时间间隔就越短。因此为了节省服务器资源，数据库开发人员必须尽可能的缩短锁的生命周期，尽可能早地释放锁资源。

小结：不恰当的锁粒度、锁生命周期不仅会影响数据库的并发性能，还会造成锁资源的浪费。

MyISAM表的表级锁

对MyISAM存储引擎的表进行检索（select）操作时，select语句执行期间（时间间隔虽然短暂），MyISAM存储引擎会自动地给涉及到的MyISAM表施加“隐式读锁”；select语句执行完毕后，MyISAM存储引擎会自动地为这些表进行“解锁”。因此select语句的执行时间就是“隐式读锁”的生命周期。

对MyISAM存储引擎的表进行更新（insert、update以及delete）操作时，更新语句（例如insert、update以及delete）执行期间（时间间隔虽然短暂），MyISAM存储引擎会自动地给涉及到的MyISAM表施加“隐式写锁”；更新语句执行完毕后，MyISAM存储引擎会自动地为这些表进行解锁，更新语句的执行时间就是“隐式写锁”的生命周期。

可以看到，任何针对MyISAM表的查询操作或者更新操作，都会隐式地施加表级锁。隐式锁的生命周期非常短暂，且不受数据库开发人员的控制。

有时，应用系统要求数据库开发人员延长MyISAM表级锁的生命周期，MySQL为数据库开发人员提供了显式地施加表级锁以及显式地解锁的MySQL命令，以便数据库开发人员能够控制MyISAM表级锁的生命周期，MySQL客户机A施加表级锁以及解锁的MySQL命令的语法格式如图6所示。

图6 表级锁的使用

注意事项：

（1）上述语法格式主要针对MyISAM表显式地施加表级锁以及解锁，该语法格式同样适用于InnoDB表。只不过因为InnoDB表支持行级锁，在InnoDB表中表级锁的概念比较淡化。

（2）read与write选项的功能在于说明施加表级读锁还是表级写锁。对表施加读锁后，MySQL客户机A对该表的后续更新操作将出错，错误信息如上图所示；MySQL客户机B对该表的后续查询操作可以继续进行，而对该表的后续更新操作将被阻塞。出错与阻塞是两个不同的概念。

MySQL客户机A对表施加写锁后，MySQL客户机A的后续查询操作以及后续更新操作都可以继续进行；MySQL客户机B对该表的后续查询操作以及后续更新操作都将被阻塞。

MySQL客户机A为某个表加锁后，加锁期间MySQL客户机A对该表的后续操作，MySQL客户机B对该表的后续操作以及MySQL客户机B对该表加锁之间的关系如表1所示。

表1 表级锁与后续操作之间的关系

（3）MySQL客户机A使用lock tables命令可以同时为多个表施加表级锁（包括读锁或者写锁），并且加锁期间，MySQL客户机A不能对“没有锁定的表”进行更新及查询操作，否则将抛出“表未被锁定”的错误信息。例如，在MySQL客户机A上运行下面的MySQL代码，对account表施加读锁，加锁期间对book表的查询操作将抛出错误信息，如图7所示。读者可以自行分析，使用显式锁后，锁的生命周期是否延长。

alter table account engine=MyISAM;
alter table book engine=MyISAM;
lock tables account read;
select * from account; 
select * from book;
unlock tables;

图7 表级锁使用的注意事项（1）

（4）如果需要为同一个表同时施加读锁与写锁，那么需要为该表起两个别名，以区分读锁与写锁。

例如，下面的MySQL代码首先将account表的存储引擎设置为MyISAM。然后向account表同时施加读锁（account表的别名为a）以及写锁（account表的别名为b）。接着将account表重命名为a进行查询操作，将account表重命名为b进行查询操作。如果直接查询account表中的所有记录，则将抛出错误信息，原因是并没有为account表施加一个名字为account的锁，抛出错误信息”account表未被锁定”也在情理之中，执行结果如图8所示。读者可以自行分析，使用显式锁后，锁的生命周期是否延长。

alter table account engine=MyISAM;
lock tables account as a read，account as b write;
select * from account as a;
select * from account as b;
select * from account; 
unlock tables;

图8 表级锁使用的注意事项（2）

说明
为了便于理解，读者可以认为每个表的锁必须有锁名，且默认情况下锁名就是表名。当某个表既存在读锁又存在写锁时，需要为表名起多个别名，且每个别名对应一个锁名。

（5）read local与read选项之间的区别在于，如果MySQL客户机A使用read选项为某个MyISAM表施加读锁，加锁期间，MySQL客户机A以及MySQL客户机B都不能对该表进行插入操作。如果MySQL客户机A使用read local选项为某个MyISAM表施加读锁，加锁期间，MySQL客户机B可以对该表进行插入操作，前提是新记录必须插入到表的末尾。对InnoDB表施加读锁时，read local选项与read选项的功能完全相同。

场景描述1：read local与read选项之间的区别。

首先在MySQL客户机A上执行下面的MySQL命令，并为account表施加local读锁。

1 2	alter table account engine=MyISAM; lock tables account read local;

然后打开MySQL客户机B，在MySQL客户机B上执行下面的insert语句，向account表中添加一条记录，执行结果如图9所示。从执行结果可以看出，MySQL客户机A为account表施加local读锁后，MySQL客户机B可以向account表中添加记录。local关键字使得MyISAM表最大限度地支持查询和插入的并发操作。

1	insert into account values（null， '丁'， 1000）;

图9 read local选项的使用

最后在MySQL客户机A上执行下面的MySQL命令，为account表解锁。

1	unlock tables;

（6）MySQL客户机A对某个表施加读锁的同时， MySQL客户机B对该表施加写锁，默认情况下会优先施加写锁，这是因为更新操作比查询操作更为重要。如果MySQL客户机C…Z对该表同时也施加了写锁，可能造成读锁“饿死”。为了避免读锁“饿死”，MySQL客户机B….Z可以使用low_priority write选项降低写锁的优先级，以便MySQL客户机A及时取得读锁，不被饿死。

（7）unlock tables用于解锁，它会解除当前MySQL服务器连接中所有MyISAM表的所有锁。

（8）lock tables与unlock tables语句会引起事务的隐式提交。

（9）MySQL客户机一旦关闭， unlock tables语句将会被隐式地执行。因此，如果要让表锁定生效就必须一直保持MySQL服务器连接。

InnoDB表的行级锁

InnoDB表的锁比MyISAM表的锁更为复杂，原因在于InnoDB表既支持表级锁，又支持行级锁，又存在意向锁，再把事务掺入其中，会给初学者的学习带来不少麻烦。使用lock tables命令为InnoDB表施加表级锁与使用lock tables命令为MyISAM表施加表级锁的用法基本相同，不再赘述，这里主要讨论InnoDB行级锁以及意向锁的用法。

InnoDB提供了两种类型的行级锁，分别是共享锁（S）以及排他锁（X），其中共享锁也叫读锁，排他锁也叫写锁。InnoDB行级锁的粒度仅仅是受查询语句或者更新语句影响的那些记录。在查询（select）语句或者更新（ insert，update以及delete）语句中，为受影响的记录施加行级锁的方法也非常简单。

方法1：在查询（select）语句中，为符合查询条件的记录施加共享锁，语法格式如下所示。

select from 表 where 条件语句 *lock in share mode;

方法2：在查询（select）语句中，为符合查询条件的记录施加排他锁，语法格式如下所示。

select from 表 where 条件语句 *for update;

方法3：在更新（insert，update以及delete）语句中，InnoDB存储引擎将符合更新条件的记录自动施加排他锁（隐式锁），即InnoDB存储引擎自动地为更新语句影响的记录施加隐式排他锁。

说明
方法1与方法2是显式地施加行级锁，方法3是隐式地施加行级锁。这3种方法施加的行级锁的生命周期非常短暂，为了延长行级锁的生命周期，最为通用的做法是开启事务。事务提交或者回滚后，行级锁才被释放，这样就可以延长行级锁的生命周期，此时事务的生命周期就是行级锁的生命周期。

场景描述2：通过事务延长行级锁的生命周期。

步骤1：在MySQL客户机A上执行下面的MySQL语句，开启事务，并为student表施加行级写锁。

1
2
3

use choose;
start transaction;
select * from student for update;

步骤2：打开MySQL客户机B ，在MySQL客户机B上执行下面的MySQL语句，开启事务，并为student表施加行级写锁。此时，MySQL客户机B被阻塞。

1
2
3

use choose;
start transaction;
select * from student for update;

步骤3：在MySQL客户机A上执行下面的MySQL命令，为student表解锁。此时，MySQL客户机A释放了student表的行级写锁，MySQL客户机B被“唤醒” ，得以继续执行。

commit;

可以看到，通过事务延长了MySQL客户机A针对student表的行级锁的生命周期。

结论：事务中的行级共享锁（S）以及行级排他锁（X）的生命周期从加锁开始，直到事务提交或者回滚，行级锁才会释放。

MySQL客户机A使用“select * from 表 where 条件语句 lock in share mode;”为InnoDB表中符合条件语句的记录施加共享锁后，加锁期间，MySQL客户机A可以对该表的所有记录进行查询以及更新操作。加锁期间，MySQL客户机B可以查询该表的所有记录（甚至施加共享锁），可以更新不符合条件语句的记录，然而为符合条件语句的记录施加排他锁时将被阻塞。

MySQL客户机A使用“select * from 表 where 条件语句 for update;”或者更新语句（例如insert，update以及delete）为InnoDB表中符合条件语句的记录施加排他锁后，加锁期间，MySQL客户机A可以对该表的所有记录进行查询以及更新操作。加锁期间，MySQL客户机B可以查询该表的所有记录，可以更新不符合条件语句的记录，然而为符合条件语句的记录施加共享锁或者排他锁时将被阻塞。

为了便于读者更好地理解共享锁以及排他锁之间的关系，可以参看表2所示的内容。

表2 行级锁与后续操作之间的关系

“选课系统”中的行级锁

场景描述3：实现调课功能的存储过程replace_course_proc()存在功能缺陷。考虑这样的场景：张三与李四“同时”选择同一门目标课程，且目标课程就剩下一个席位（此时目标课程available的字段值为1）。张三以及李四为了实现调课功能，“同时”调用存储过程replace_course_proc()，假设两人“同时”执行存储过程中的select语句“查询目标课程available字段值”：

1	select available into s from course where course_no=c_after;

张三以及李四可能都读取到available的值为1（大于零），最后的结果是张三与李四都选择了目标课程，如图10所示。

图10 存储过程replace_course_proc()存在功能缺陷

可以看出，存储过程replace_course_proc()读取课程的available字段值时，有必要为张三与李四选择相同的目标课程施加排他锁，避免多名学生同时读取同一门课程的available字段值。将存储过程replace_course_proc()中的代码片段:

1	select available into s from course where course_no=c after;

修改为如下的代码片段：

1	select available into s from course where course_no=c_after for update;

说明
为了延长行级排他锁的生命周期，将该select语句写在了start transaction语句后，封装到事务中。

此时，当张三、李四以及其他更多的学生同时“争夺”同一门目标课程的最后一个席位时，可以保证只有一个学生能够读取该席位，其他学生将被阻塞（如图11所示）。这样就可以防止张三与李四都选择了目标课程的最后一个席位。很多读者可能觉得：多个学生同时选择“最后一个席位”的可能性微乎其微，但如果最后的一个“席位”是春运期间某趟列车的最后一张火车票呢？现实生活中，类似的“资源竞争”问题还有很多（例如团购、秒杀等），使用锁机制可以有效解决此类“资源竞争”问题。

图11 解决存储过程replace_course_proc()的功能缺陷

同样的道理，在前面的章节中，实现选课功能的存储过程choose_proc()也需要进行相应的修改（粗体字部分为代码改动部分）。删除存储过程choose_proc()，并重建该存储过程。

drop procedure choose_proc;
delimiter $$
create procedure choose_proc(in s_no char(11),in c_no int,out state int)
modifies sql data
begin
    declare s1 int;
    declare s2 int;
    declare s3 int;
    declare status char(8);
    set state= 0;
    set status='未审核';
    select count(*) into s1 from choose where student_no=s_no and course_no=c_no ;
    if(s1>=1) then
        set state = -1;
    else
        select count(*) into s2 from choose where student_no=s_no;
        if(s2>=2) then
            set state = -2;
        else
            start transaction;
            select state into status from course where course_no=c_no;
            select available into s3 from course where course_no=c_no for update;
            if(s3=0 || status='未审核') then
                set state = -3;
            else
                insert into choose values(null,s_no,c_no,null,now());
                set state = last_insert_id();
            end if;
            commit;
        end if;
    end if;
end
$$
delimiter ;

InnoDB表的意向锁

InnoDB表既支持行级锁，又支持表级锁。考虑如下场景：MySQL客户机A获得了某个InnoDB表中若干条记录的行级锁，此时，MySQL客户机B出于某种原因需要向该表显式地施加表级锁（使用lock tables命令即可），为了获得该表的表级锁，MySQL客户机B需要逐行检测表中是否存在行级锁，而这种检测需要耗费大量的服务器资源。

试想：如果MySQL客户机A获得该表若干条记录的行级锁之前，MySQL客户机A直接向该表施加一个“表级锁”（这个表级锁是隐式的，也叫意向锁），MySQL客户机B仅仅需要检测自己的表级锁与该意向锁是否兼容，无需逐行检测该表是否存在行级锁，这样就会节省不少服务器资源，如图12所示。

图12 InnoDB表的意向锁

由此可以看出，引入意向锁的目的是为了方便检测表级锁与行级锁之间是否兼容。意向锁是隐式的表级锁，数据库开发人员向InnoDB表的某些记录施加行级锁时，InnoDB存储引擎首先会自动地向该表施加意向锁，然后再施加行级锁，意向锁无需数据库开发人员维护。MySQL提供了两种意向锁：意向共享锁（IS）和意向排他锁（IX）。

意向共享锁（IS）：向InnoDB表的某些记录施加行级共享锁时，InnoDB存储引擎会自动地向该表施加意向共享锁（IS）。也就是说，执行“select * from 表 where 条件语句 lock in share mode;”后，InnoDB存储引擎在为表中符合条件语句的记录施加共享锁前，InnoDB会自动地为该表施加意向共享锁（IS）。

意向排他锁（IX）：向InnoDB表的某些记录施加行级排他锁时，InnoDB存储引擎会自动地向该表施加意向排他锁（IX）。也就是说，执行更新语句（例如insert、update或者delete语句）或者“select * from 表 where 条件语句 for update;”后，InnoDB存储引擎在为表中符合条件语句的记录施加排他锁前，InnoDB会自动地为该表施加意向排他锁（IX）。

说明
意向锁虽是表级锁，但是却表示事务正在查询或更新某一行记录，而不是整个表，因此意向锁之间不会产生冲突。

每执行一条“select…lock in share mode”语句，该select语句在执行期间自动地施加意向共享锁，执行完毕后，意向共享锁会自动解锁，因此意向共享锁的生命周期非常短暂，且不受人为控制；意向排他锁也是如此。

某个InnoDB表已经存在了行级锁，此时其他MySQL客户机再向该表施加表级锁时，可能引发意向锁与表级锁之间的冲突。意向锁与意向锁之间以及意向锁与表级锁之间的关系如表3所示。

表3 意向锁与表级锁之间的关系

InnoDB行级锁与索引之间的关系

InnoDB表的行级锁是通过对“索引”施加锁的方式实现的，这就意味着，只有通过索引字段检索数据的查询语句或者更新语句，才可能施加行级锁，否则InnoDB将使用表级锁，而使用表级锁势必会降低InnoDB表的并发访问性能。

场景描述4：索引设置不当，降低InnoDB表的并发访问性能。

步骤1：打开MySQL客户机A，在MySQL客户机A上执行下面的MySQL命令，首先将account账户表的存储引擎设置为InnoDB，接着关闭MySQL自动提交，最后对账户名为“甲”的记录施加行级排他锁，执行结果如图13所示。

1
2
3

alter table account engine=InnoDB;
set autocommit=0;
select * from account where account_name='甲' for update;

图13 对账户名为“甲”的记录施加行级排他锁

步骤2：打开MySQL客户机B，在MySQL客户机B上执行下面的MySQL命令，首先关闭MySQL自动提交，对账户名为“乙”的记录施加行级排他锁时被阻塞，执行结果如图14所示。从MySQL客户机B的执行结果可以看出，MySQL客户机B对“乙”账户施加排他锁时，出现了“锁等待”现象（被阻塞）。

1 2	set autocommit=0; select * from account where account_name='乙' for update;

图14 对账户名为“乙”的记录施加行级排他锁

按理MySQL客户机A仅仅对“甲”账户施加了排他锁，不会影响MySQL客户机B对“乙”账户施加排他锁，然而事实并非如此。原因在于，查询语句或者更新语句施加行级锁时，如果没有使用索引，查询语句或者更新语句会自动地对 InnoDB 表施加表级锁，最终导致出现了“锁等待”现象，降低了InnoDB表的并发访问性能。

说明
使用MySQL命令“show variables like ‘innodb_lock_wait_timeout’;”可以查看锁InnoDB等待超时的时间（默认值为50秒，如图15所示）。当InnoDB锁等待的时间超过参数innodb_lock_wait_timeout的值时，将引发InnoDB锁等待超时错误异常（如图14所示）。
图15 查看锁等待超时时间

步骤3：锁等待期间，在MySQL客户机A上执行MySQL命令“show full processlist\G”可以查看当前MySQL服务实例上正在运行的MySQL线程的状态信息，如图16所示。各个状态信息说明如下。

图16 查看MySQL服务实例上正在运行的MySQL线程

Id列：是一个标识，唯一标记了一个MySQL线程或者一个MySQL服务器连接。
User列：显示了当前的MySQL账户名。
Host列：显示每条SQL语句或者MySQL命令是从哪个MySQL客户机的哪个端口上发出。
db列：显示当前的MySQL线程操作的是哪一个数据库。
Command 列：显示该线程的命令类型，命令类型的取值一般是休眠（sleep）、查询（query）或者连接（connect）。例如，命令类型的取值是Sleep时，表示当前的线程正在等待MySQL客户机向它发送一条新语句。
Time列：显示了该线程执行时的持续时间，单位是秒。例如，time=48时，意味着该线程执行的持续时间为48秒。
State列：显示了该线程的状态，状态取值一般是init、update、sleep、sending data、空字符串或者waiting for锁类型lock。例如，当状态取值是Waiting for table metadata lock时，表示当前的线程正在等待MySQL客户机获得元数据锁，即发生了锁等待现象;当状态取值是sending data时，表示当前的线程正在向MySQL服务器发送数据。如果处于某种状态（例如sending data）的持续时间较长（例如48秒），可能出现了锁等待现象。
Info列：显示了SQL语句，因为长度有限，所以长的SQL语句仅仅显示一部分。

步骤4：使用MySQL命令“kill 49;”即可杀死图16中状态持续时间较长的线程49，并关闭与之对应的MySQL服务器连接。

对于数据库开发人员而言，如果不了解InnoDB行级锁是基于索引实现的这一特性，可能导致大量的锁冲突，从而影响并发性能：当“甲”账户在银行柜台前办理存款或者取款业务时，其他账户无法同时办理存款或者取款业务。

解决办法：使用下面的SQL语句为account表的account_name字段添加索引（索引名为account_name_index）。添加索引后，读者可以再次尝试：MySQL客户机A对“甲”账户施加了排他锁后，MySQL客户机B对“乙”账户施加排他锁时，是否还会产生“InnoDB锁等待”现象（被阻塞）。

1	alter table account add index account_name_index(account_name);

结论：InnoDB表的行级锁是通过对索引施加锁的方式实现的，了解InnoDB行级锁的实现方式后，很多问题都可以找到答案。例如，当检索条件为某个区间（例如account_no between 1 and 100）范围时，对该区间范围施加共享锁或排他锁后，满足该区间范围的记录（例如account_no=1或者account_no=2的记录）存在共享锁或排他锁；满足该区间范围，但表中不存在的记录（例如account_no=50 或者 account_no=100 的记录）也会存在共享锁或排他锁，即行级锁会锁定相邻的键（next-key locking），这种锁机制就是所谓的间隙锁（next-key锁），可以看出，间隙锁与索引密切相关。如果间隙锁使用得当，可以避免幻读现象；如果间隙锁使用不当，可能导致死锁问题，有关间隙锁的使用请参看本章后续内容。

说明
当事务的隔离级别设置为repeatable read（这是MySQL默认的事务隔离级别），此时为InnoDB表施加行级锁，默认情况下使用间隙锁。当事务的隔离级别设置为read uncommitted或者read committed，此时为InnoDB表施加行级锁，默认情况下使用记录锁（record lock）。与间隙锁不同，记录锁仅仅为满足该查询范围的记录施加共享锁或排他锁。

数据库开发人员可以使用explain命令对查询语句进行分析，从而判断该查询语句是否使用了索引。虽然explain命令只能搭配select类型语句使用，如果想查看更新语句（例如update、delete语句）的索引效果，则保持更新条件不变，把更新语句替换成select即可。

即便在条件中使用了索引关键字，MySQL最终是根据执行计划决定是否使用索引。

间隙锁与死锁

场景描述5：MySQL默认的事务隔离级别是repeatable read（稍后介绍），此时如果MySQL客户机A与MySQL客户机B针对“符合查询条件但不存在记录”施加了共享锁或者排他锁（此时的锁实际上是间隙锁），那么MySQL客户机A与MySQL客户机B都会加锁成功。加锁期间，如果MySQL客户机A与MySQL客户机B都试图添加一条“符合查询条件的记录”，此时会进入死锁状态。

步骤1：打开MySQL客户机A，执行下面的SQL语句，首先将account表的存储引擎设置为InnoDB，接着开启事务，查询account表中account_no=20的账户信息，并对该账户信息施加共享锁，执行结果如图17所示。从执行结果可以得知，account表中不存在account_no=20的账户信息。

1
2
3

alter table account engine=InnoDB;
start transaction;
select * from account where account_no=20 lock in share mode;

图17 对account_no=20的账户施加共享锁（1）

步骤2：打开MySQL客户机B，执行下面的SQL语句，然后开启事务，接着查询account表中account_no=20账户信息，并对该账户信息施加共享锁，执行结果如图18所示。从执行结果可以得知，account表中不存在account_no=20的账户信息。

1 2	start transaction; select * from account where account_no=20 lock in share mode;

图18 对account_no=20的账户施加共享锁（2）

步骤3：由于MySQL客户机A已经得知，account表中不存在account_no=20的账户信息，因此MySQL客户机A可以使用下面的insert语句，向account表中添加一条account_no=20的账户信息。此时该insert语句被阻塞，进入锁等待状态。

1	insert into account values(20,'戊',5000);

步骤4：由于MySQL客户机B已经得知，account表中不存在account_no=20的账户信息，因此，MySQL客户机B可以使用下面的insert语句，向account表中添加一条account_no=20的账户信息。但此时该insert语句导致死锁问题的发生，执行结果如图19所示。

1	insert into account values(20,'戊',6000);

图19 间隙锁导致死锁问题说明

默认情况下InnoDB存储引擎会自动检测死锁，通过比较参与死锁问题的事务权重，继而选择权重值最小的事务进行回滚，并释放锁，以便其他事务获得锁，继续完成事务。每个事务的权重值存储在information_schema 数据库的INNODB_TRX表的trx_weight字段中。

步骤5：如果MySQL客户机A获得锁，此时MySQL客户机A上的insert语句成功执行，如图20所示（注意观察insert语句的执行时间）。

图20 MySQL客户机A获得锁，继续完成事务

步骤6：在MySQL客户机A上执行commit命令，提交insert语句，并解锁。然后执行select语句查询account表的所有记录，执行结果如图21所示。

图21 查询account表的所有记录

死锁与锁等待

给MyISAM表施加表级锁不会导致死锁问题的发生，这是由于MyISAM总是一次性地获得SQL语句的全部锁。给InnoDB表施加行级锁可能导致死锁问题的发生，这是由于执行SQL语句期间，可以继续施加行级锁。因此，这里讨论的死锁问题主要是InnoDB行级锁产生的死锁问题。

上面的死锁问题由间隙锁产生，间隙锁如果使用不当，可能导致死锁问题。不仅仅是间隙锁可以导致死锁问题，错误的加锁时机也会导致死锁问题的发生。

场景描述6：如果account账户表的存储引擎为InnoDB，“甲”在银行柜台前通过MySQL客户机A将“甲”账户（account_no=1）的部分金额（例如1000元）转账给“乙”账户的“同时”，“乙”在银行柜台前通过MySQL客户机B将“乙”账户（account_no=2）的部分金额（例如500元）转账给“甲”账户，通过MySQL客户机A以及MySQL客户机B实现转账业务时都需要调用transfer_proc()存储过程。假设甲的转账存储过程与乙的转账存储过程的执行过程如图22所示，两个transfer_proc()存储过程正在分时、并发、交替运行，请读者注意每条语句执行的先后顺序。

注意：现实生活中，这种假设存在的可能性微乎其微，但即便这样，数据库开发人员也需要应对这种低概率事件的发生。

图22 两个账户并发、互相转账

步骤3后，MySQL客户机A首先获得了“乙”账户的排他锁，如图9-39所示（注意箭头的指向）；步骤4后，MySQL客户机B获得了“甲”账户的排他锁。为了实现转账业务，MySQL客户机A接着申请“甲”账户的排他锁（步骤5），此时需要等待MySQL客户机B释放“甲”账户的排他锁，产生“锁等待”现象（被阻塞），注意：此时并没有产生死锁问题。为了实现转账业务，MySQL客户机B接着申请“乙”账户的排他锁（步骤6），当MySQL客户机B申请“乙”账户的排他锁时，形成一个“环路等待”，此时进入死锁状态。步骤6对应的update语句执行后将产生死锁问题，并抛出如下错误信息：

1	ERROR 1213 (40001)：Deadlock found when trying to get lock; try restarting transaction

从图23中可以看到，锁等待与死锁是两个不同的概念。锁等待是为了保证事务可以正常地并发运行，锁等待不一定导致死锁问题的发生。而死锁问题的发生一定伴随着锁等待现象。

图23 锁等待与死锁

默认情况下，InnoDB存储引擎会自动检测死锁，通过比较参与死锁问题的事务权重，继而选择权重值最小的事务进行回滚，并释放锁，以便其他事务获得锁，继续完成事务。但即便如此，对于数据库开发人员而言，显式地处理死锁异常是一个好的编程习惯。下面的MySQL代码，首先删除原有的transfer_proc()存储过程，然后重新创建transfer_proc()存储过程，并将代码修改为下面的代码（粗体字部分为代码改动部分，其他代码不变）。粗体字部分的代码主要用于处理死锁异常，发生死锁异常问题后，回滚整个事务。

drop procedure transfer_proc;

delimiter $$
create procedure transfer_proc(in from_account int,in to_account int,in money int)
modifies sql data
begin
declare continue handler for 1690
begin
    rollback;
end;
declare continue handler for 1213
begin
    rollback;
end;
start transaction;
update account set balance=balance+money where account_no=to_account;
update account set balance=balance-money where account_no=from_account;
commit;
end
$$
delimiter ;

说明
有些时候InnoDB并不能自动检测到死锁，可以通过设置InnoDB锁等待超时参数innodb_lock_wait_timeout的值，设置合适的锁等待超时阈值。当然锁等待超时参数innodb_lock_wait_timeout 并不只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获取所需的锁而被阻塞，会占用大量数据库服务器资源，降低数据库服务器性能，设置合适的锁等待超时阈值也可以解决锁占用时间过长等问题。

默认情况下，InnoDB存储引擎一旦出现锁等待超时异常，InnoDB存储引擎既不会提交事务，也不会回滚事务，而这是十分危险的。一旦发生锁等待超时异常，应用程序应该自定义错误处理程序，由程序开发人员选择是进一步提交事务还是回滚事务。