1.4 MySQL事务基础
在互联网领域，MySQL数据库是使用最多的关系型数据库之一，也是一种典型的支持事务的关系型数据库，因此我们有必要对MySQL数据库的事务进行简单介绍。
1.4.1 并发事务带来的问题
数据库一般会并发执行多个事务，而多个事务可能会并发地对相同的数据进行增加、删除、修改和查询操作，进而导致并发事务问题。并发事务带来的问题包括更新丢失（脏写）、脏读、不可重复读和幻读，如图1-3所示。
图1-3 并发事务带来的问题
1.更新丢失（脏写）
当两个或两个以上的事务选择数据库中的同一行数据，并基于最初选定的值更新该行数据时，因为每个事务之间都无法感知彼此的存在，所以会出现最后的更新操作覆盖之前由其他事务完成的更新操作的情况。也就是说，对于同一行数据，一个事务对该行数据的更新操作覆盖了其他事务对该行数据的更新操作。
例如，张三的账户余额是100元，当前有事务A和事务B两个事务，事务A是将张三的账户余额增加100元，事务B是将张三的账户余额增加200元。起初，事务A和事务B同时读取到张三的账户余额为100元。然后，事务A和事务B将分别更新张三的银行账户余额，假设事务A先于事务B提交，但事务A和事务B都提交后的结果是张三的账户余额是300元。也就是说，后提交的事务B覆盖了事务A的更新操作。
更新丢失（脏写）本质上是写操作的冲突，解决办法是让每个事务按照串行的方式执行，按照一定的顺序依次进行写操作。
2.脏读
一个事务正在对数据库中的一条记录进行修改操作，在这个事务完成并提交之前，当有另一个事务来读取正在修改的这条数据记录时，如果没有对这两个事务进行控制，则第二个事务就会读取到没有被提交的脏数据，并根据这些脏数据做进一步的处理，此时就会产生未提交的数据依赖关系。我们通常把这种现象称为脏读，也就是一个事务读取了另一个事务未提交的数据。
例如，当前有事务A和事务B两个事务，事务A是向张三的银行账户转账100元，事务B是查询张三的账户余额。事务A执行转账操作，在事务A未提交时，事务B查询到张三的银行账户多了100元，后来事务A由于某些原因，例如服务超时、系统异常等因素进行回滚操作，但事务B查询到的数据并没有改变。此时，事务B查询到的数据就是脏数据。
脏读本质上是读写操作的冲突，解决办法是先写后读，也就是写完之后再读。
3.不可重复读
一个事务读取了某些数据，在一段时间后，这个事务再次读取之前读过的数据，此时发现读取的数据发生了变化，或者其中的某些记录已经被删除，这种现象就叫作不可重复读。即同一个事务，使用相同的查询语句，在不同时刻读取到的结果不一致。
例如，当前有事务A和事务B两个事务，事务A是向张三的银行账户转账100元，事务B是查询张三的账户余额。第一次查询时，事务A还没有转账，第二次查询时，事务A已经转账成功，此时，就会导致事务B两次查询结果不一致。
不可重复读本质上也是读写操作的冲突，解决办法是先读后写，也就是读完之后再写。
4.幻读
一个事务按照相同的查询条件重新读取之前读过的数据，此时发现其他事务插入了满足当前事务查询条件的新数据，这种现象叫作幻读。即一个事务两次读取一个范围的数据记录，两次读取到的结果不同。
例如，当前有事务A和事务B两个事务，事务A是两次查询张三的转账记录，事务B是向张三的银行账户转账100元。事务A第一次查询时，事务B还没有转账，事务A第二次查询时，事务B已经转账成功，此时，就会导致事务A两次查询的转账数据不一致。
幻读本质上是读写操作的冲突，解决办法是先读后写，也就是读完之后再写。
很多人不懂不可重复读和幻读到底有何区别。这里，我们简单介绍一下。
1）不可重复读的重点在于更新和删除操作，而幻读的重点在于插入操作。
2）使用锁机制实现事务隔离级别时，在可重复读隔离级别中，SQL语句第一次读取到数据后，会将相应的数据加锁，使得其他事务无法修改和删除这些数据，此时可以实现可重复读。这种方法无法对新插入的数据加锁。如果事务A读取了数据，或者修改和删除了数据，此时，事务B还可以进行插入操作，导致事务A莫名其妙地多了一条之前没有的数据，这就是幻读。
3）幻读无法通过行级锁来避免，需要使用串行化的事务隔离级别，但是这种事务隔离级别会极大降低数据库的并发能力。
4）从本质上讲，不可重复读和幻读最大的区别在于如何通过锁机制解决问题。
另外，除了可以使用悲观锁来避免不可重复读和幻读的问题外，我们也可以使用乐观锁来处理，例如，MySQL、Oracle和PostgreSQL等数据库为了提高整体性能，就使用了基于乐观锁的MVCC（多版本并发控制）机制来避免不可重复读和幻读。
1.4.2 MySQL事务隔离级别
按照SQL：1992事务隔离级别，InnoDB默认是可重复读的。MySQL中的InnoDB储存引擎提供SQL标准所描述的4种事务隔离级别，分别为读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）和串行化（Serializable），如图1-4所示。
图1-4 事务隔离级别
可以在命令行用--transaction-isolation选项或在MySQL的配置文件my.cnf、my.ini里，为所有连接设置默认的事务隔离级别。
例如，可以在my.cnf或者my.ini文件中的mysqld节点下面配置如下选项。
transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}
也可以使用SET TRANSACTION命令改变单个或者所有新连接的事务隔离级别，基本语法如下所示。
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
如果使用SET TRANSACTION命令来设置事务隔离级别，需要注意以下几点。
1）不带SESSION或GLOBAL关键字设置事务隔离级别，指的是为下一个（还未开始的）事务设置隔离级别。
2）使用GLOBAL关键字指的是对全局设置事务隔离级别，也就是设置后的事务隔离级别对所有新产生的数据库连接生效。
3）使用SESSION关键字指的是对当前的数据库连接设置事务隔离级别，此时的事务隔离级别只对当前连接的后续事务生效。
4）任何客户端都能自由改变当前会话的事务隔离级别，可以在事务中间改变，也可以改变下一个事务的隔离级别。
使用如下命令可以查询全局级别和会话级别的事务隔离级别。
SELECT @@global.tx_isolation; SELECT @@session.tx_isolation; SELECT @@tx_isolation;
1.4.3 MySQL中各种事务隔离级别的区别
4种事务隔离级别对于并发事务带来的问题的解决程度不一样，具体如表1-1所示。
表1-1 不同事务隔离级别对问题的解决程度对比
1）读未提交允许脏读，即在读未提交的事务隔离级别下，可能读取到其他会话未提交事务修改的数据。这种事务隔离级别下存在脏读、不可重复读和幻读的问题。
2）读已提交只能读取到已经提交的数据。Oracle等数据库使用的默认事务隔离级别就是读已提交。这种事务隔离级别存在不可重复读和幻读的问题。
3）可重复读就是在同一个事务内，无论何时查询到的数据都与开始查询到的数据一致，这是MySQL中InnoDB存储引擎默认的事务隔离级别。这种事务隔离级别下存在幻读的问题。
4）串行化是指完全串行地读，每次读取数据库中的数据时，都需要获得表级别的共享锁，读和写都会阻塞。这种事务隔离级别解决了并发事务带来的问题，但完全的串行化操作使得数据库失去了并发特性，所以这种隔离级别往往在互联网行业中不太常用。
接下来，为了让大家更好地理解MySQL的事务隔离级别，列举几个实际案例。
1.4.4 MySQL事务隔离级别最佳实践
在MySQL中创建一个test数据库，在test数据库中创建一个account数据表作为测试使用的账户数据表，如下所示。
mysql> create database test;
Query OK, 1 row affected (0.02 sec)
mysql> use test;
Database changed
mysql>
mysql> create table account(
-> id int not null auto_increment,
-> name varchar(30) not null default '',
-> balance int not null default 0,
-> primary key(id)
-> ) engine=InnoDB default charset=utf8mb4;
Query OK, 0 rows affected (0.02 sec)
创建完数据库和数据表之后，向account数据表中插入几条测试数据，如下所示。
mysql> insert into
-> test.account(name, balance)
-> values
-> ('张三', 300),
-> ('李四', 350),
-> ('王五', 500);
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
此时，account数据表中有张三、李四和王五的账户信息，账户余额分别为300元、350元和500元。
准备工作完成了，接下来，我们一起来看MySQL中每种事务隔离级别下数据的处理情况。
1.读未提交
第一步：打开服务器终端A，登录MySQL，将当前终端的事务隔离级别设置为read uncommitted，也就是读未提交，如下所示。
mysql> set session transaction isolation level read uncommitted; Query OK, 0 rows affected (0.01 sec)
在终端A开启事务并查询account数据表中的数据，如下所示。
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 300 | | 2 | 李四 | 350 | | 3 | 王五 | 500 | +----+--------+---------+ 3 rows in set (0.00 sec)
此时，可以看到数据表中张三、李四和王五的账户余额分别为300元、350元和500元。
第二步：在终端A的事务提交之前，打开服务器的另一个终端B，连接MySQL，将当前事务模式设置为read uncommitted并更新account表的数据，将张三的账户余额加100元。
mysql> set session transaction isolation level read uncommitted; Query OK, 0 rows affected (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update account set balance = balance + 100 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
在终端B查询account数据表中的数据，如下所示。
mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 400 | | 2 | 李四 | 350 | | 3 | 王五 | 500 | +----+--------+---------+ 3 rows in set (0.00 sec)
可以看到，在终端B中，当前事务未提交时，张三的账户余额变为更新后的值，即400元。
第三步：在终端A查看account数据表的数据，如下所示。
mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 400 | | 2 | 李四 | 350 | | 3 | 王五 | 500 | +----+--------+---------+ 3 rows in set (0.00 sec)
可以看到，虽然终端B的事务并未提交，但是终端A可以查询到终端B已经更新的数据。
第四步：如果终端B的事务由于某种原因执行了回滚操作，那么终端B中执行的所有操作都会被撤销。也就是说，终端A查询到的数据其实就是脏数据。
在终端B执行事务回滚操作，并查询account数据表中的数据，如下所示。
mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 300 | | 2 | 李四 | 350 | | 3 | 王五 | 500 | +----+--------+---------+ 3 rows in set (0.00 sec)
可以看到，在终端B执行了事务的回滚操作后，张三的账户余额重新变为300元。
第五步：在终端A将张三的账户余额减100元，再次查询account数据表的数据，可以发现张三的账户余额变为200元，而不是300元，如下所示。
mysql> update account set balance = balance - 100 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 200 | | 2 | 李四 | 350 | | 3 | 王五 | 500 | +----+--------+---------+ 3 rows in set (0.00 sec)
执行第三步时读取到张三的账户余额为400元，然后将张三的账户余额减100元，因为在应用程序中并不知道其他会话回滚了事务，所以更新张三的账户余额就变为300元了，这就是脏读的问题。可以采用读已提交的事务隔离级别解决这个问题。
2.读已提交
第一步：打开一个终端A，将当前终端的事务隔离级别设置为read committed，也就是读已提交，如下所示。
mysql> set session transaction isolation level read committed; Query OK, 0 rows affected (0.00 sec)
在终端A开启事务并查询account数据表中的数据，如下所示。
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select* from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 300 | | 2 | 李四 | 350 | | 3 | 王五 | 500 | +----+--------+---------+ 3 rows in set (0.00 sec)
可以看到，张三、李四和王五的账户余额分别为300元、350元和500元。
第二步：在终端A的事务提交之前，打开终端B，将当前终端的事务隔离级别设置为read committed，开启事务并更新account数据表中的数据，将张三的账户余额增加100元，如下所示。
mysql> set session transaction isolation level read committed; Query OK, 0 rows affected (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update account set balance = balance + 100 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
在终端B查询account数据表中的数据，如下所示。
mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 400 | | 2 | 李四 | 350 | | 3 | 王五 | 500 | +----+--------+---------+ 3 rows in set (0.00 sec)
可以看到，在终端B的查询结果中，张三的账户余额已经由原来的300元变成400元。
第三步：在终端B的事务提交之前，在终端A中查询account数据表中的数据，如下所示。
mysql> select* from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 300 | | 2 | 李四 | 350 | | 3 | 王五 | 500 | +----+--------+---------+ 3 rows in set (0.00 sec)
可以看到，在终端A查询出来的张三的账户余额仍为300元，说明此时已经解决了脏读的问题。
第四步：在终端B提交事务，如下所示。
mysql> commit; Query OK, 0 rows affected (0.00 sec)
第五步：在终端B提交事务后，在终端A再次查询account数据表中的数据，如下所示。
mysql> select* from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 400 | | 2 | 李四 | 350 | | 3 | 王五 | 500 | +----+--------+---------+ 3 rows in set (0.01 sec)
可以看到，终端A在终端B的事务提交前和提交后读取到的account数据表中的数据不一致，产生了不可重复读的问题。要想解决这个问题，就需要使用可重复读的事务隔离级别。
3.可重复读
第一步：打开终端A，登录MySQL，将当前终端的事务隔离级别设置为repeatable read，也就是可重复读。开启事务并查询account数据表中的数据，如下所示。
mysql> set session transaction isolation level repeatable read; Query OK, 0 rows affected (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 300 | | 2 | 李四 | 350 | | 3 | 王五 | 500 | +----+--------+---------+ 3 rows in set (0.00 sec)
可以看到，此时张三、李四、王五的账户余额分别为300元、350元、500元。
第二步：在终端A的事务提交之前，打开终端B，登录MySQL，将当前终端的事务隔离级别设置为可重复读。开启事务，将张三的账户余额增加100元，随后提交事务，如下所示。
mysql> set session transaction isolation level repeatable read; Query OK, 0 rows affected (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update account set balance = balance + 100 where id = 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.00 sec)
接下来，在终端B查询account数据表中的数据，如下所示。
mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 400 | | 2 | 李四 | 350 | | 3 | 王五 | 500 | +----+--------+---------+ 3 rows in set (0.00 sec)
可以看到，在终端B查询的结果中，张三的账户余额已经由原来的300元变成400元。
第三步：在终端A查询account数据表中的数据，如下所示。
mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 300 | | 2 | 李四 | 350 | | 3 | 王五 | 500 | +----+--------+---------+ 3 rows in set (0.00 sec)
可以看到，在终端A查询的结果中，张三的账户余额仍为300元，并没有出现不可重复读的问题，说明可重复读的事务隔离级别解决了不可重复读的问题。
第四步：在终端A为张三的账户增加100元，如下所示。
mysql> update account set balance = balance + 100 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
接下来，在终端A查询account数据表中的数据，如下所示。
mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 500 | | 2 | 李四 | 350 | | 3 | 王五 | 500 | +----+--------+---------+ 3 rows in set (0.00 sec)
可以看到，此时张三的账户余额变成500元，而不是400元，数据的一致性没有遭到破坏。这是因为在终端A为张三的账户余额增加100元之前，终端B已经为张三的账户余额增加了100元，共计增加了200元，所以最终张三的账户余额是500元。
可重复读的隔离级别使用了MVCC（Multi-Version Concurrency Control，多版本并发控制）机制，数据库中的查询（select）操作不会更新版本号，是快照读，而操作数据表中的数据（insert、update、delete）则会更新版本号，是当前读。
第五步：在终端B开启事务，插入一条数据后提交事务，如下所示。
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into account(name, balance) values('赵六', 100);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
在终端B查询account数据表中的数据，如下所示。
mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 400 | | 2 | 李四 | 350 | | 3 | 王五 | 500 | | 4 | 赵六 | 100 | +----+--------+---------+ 4 rows in set (0.00 sec)
可以看到，在终端B查询的结果中，已经显示出新插入的赵六的账户信息了。
第六步：在终端A查询account数据表的数据，如下所示。
mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 500 | | 2 | 李四 | 350 | | 3 | 王五 | 500 | +----+--------+---------+ 3 rows in set (0.00 sec)
可以看到，在终端A查询的数据中，并没有赵六的账户信息，说明没有出现幻读。
第七步：在终端A为赵六的账户增加100元，如下所示。
mysql> update account set balance = balance + 100 where id = 4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
SQL语句执行成功。接下来，在终端A查询account数据表中的数据，如下所示。
mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 500 | | 2 | 李四 | 350 | | 3 | 王五 | 500 | | 4 | 赵六 | 200 | +----+--------+---------+ 4 rows in set (0.00 sec)
可以看到，在终端A执行完数据更新操作后，查询到赵六的账户信息，出现了幻读的问题。如何解决该问题呢？答案是使用可串行化的事务隔离级别或者间隙锁和临键锁。
4.串行化
第一步：打开终端A，登录MySQL，将当前终端的事务隔离级别设置为serializable，开启事务，然后查询account数据表中id为1的数据，如下所示。
mysql> set session transaction isolation level serializable; Query OK, 0 rows affected (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from account where id = 1; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 300 | +----+--------+---------+ 1 row in set (0.00 sec)
可以看到，张三、李四和王五的账户余额分别为300元、350元和500元。
第二步：打开终端B，登录MySQL，将当前终端的事务隔离级别设置为serializable，开启事务，修改account数据表中id为1的数据，如下所示。
mysql> set session transaction isolation level serializable; Query OK, 0 rows affected (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update account set balance = balance + 100 where id = 1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql>
可以看到，在终端B中对account数据表中id为1的数据执行更新操作时，会发生阻塞，锁超时后会抛出“ERROR 1205(HY000):Lock wait timeout exceeded:try restarting transaction”错误，避免了幻读。
另外，在可重复的事务隔离级别下，如果终端A执行的是一个范围查询，那么该范围内的所有行（包括每行记录所在的间隙区间范围，如果某行记录还未被插入数据，这行记录也会被加锁，这是一种间隙锁，后文会详细讲解）都会被加锁。此时终端B在此范围内插入数据，就会被阻塞，从而避免了幻读。
本节使用start transaction命令来开启事务，也可以使用begin命令来开启事务。
终端A是一个范围查询的串行化示例，大家可自行验证，笔者不再赘述。
1.4.5 MySQL中锁的分类
从本质上讲，锁是一种协调多个进程或多个线程对某一资源的访问的机制，MySQL使用锁和MVCC机制实现了事务隔离级别。接下来简单介绍MySQL中锁的分类。
MySQL中的锁可以从以下几个方面进行分类，如图1-5所示。
图1-5 锁的分类
1）从性能上看，MySQL中的锁可以分为悲观锁和乐观锁，这里的乐观锁是通过版本对比来实现的。
2）从对数据库的操作类型上看，MySQL中的锁可以分为读锁和写锁，这里的读锁和写锁都是悲观锁。
3）从操作数据的粒度上看，MySQL中的锁可以分为表锁、行锁和页面锁。
4）从更细粒度上看，MySQL中的锁可以分为间隙锁和临键锁。
下面分别对这些锁的分类进行详细的介绍。
1.悲观锁和乐观锁
（1）悲观锁
顾名思义，悲观锁对于数据库中数据的读写持悲观态度，即在整个数据处理的过程中，它会将相应的数据锁定。在数据库中，悲观锁的实现需要依赖数据库提供的锁机制，以保证对数据库加锁后，其他应用系统无法修改数据库中的数据。
在悲观锁机制下，读取数据库中的数据时需要加锁，此时不能对这些数据进行修改操作。修改数据库中的数据时也需要加锁，此时不能对这些数据进行读取操作。
（2）乐观锁
悲观锁会极大地降低数据库的性能，特别是对长事务而言，性能的损耗往往是无法承受的。乐观锁则在一定程度上解决了这个问题。
顾名思义，乐观锁对于数据库中数据的读写持乐观态度，即在整个数据处理的过程中，大多数情况下它是通过数据版本记录机制实现的。
实现乐观锁的一种常用做法是为数据增加一个版本标识，如果是通过数据库实现，往往会在数据表中增加一个类似version的版本号字段。在查询数据表中的数据时，会将版本号字段的值一起读取出来，当更新数据时，会令版本号字段的值加1。将提交数据的版本与数据表对应记录的版本进行对比，如果提交的数据版本号大于数据表中当前要修改的数据的版本号，则对数据进行修改操作。否则，不修改数据表中的数据。
2.读锁和写锁
（1）读锁
读锁又称为共享锁或S锁（Shared Lock），针对同一份数据，可以加多个读锁而互不影响。
（2）写锁
写锁又称为排他锁或X锁（Exclusive Lock），如果当前写锁未释放，它会阻塞其他的写锁和读锁。
需要注意的是，对同一份数据，如果加了读锁，则可以继续为其加读锁，且多个读锁之间互不影响，但此时不能为数据增加写锁。一旦加了写锁，则不能再增加写锁和读锁。因为读锁具有共享性，而写锁具有排他性。
3.表锁、行锁和页面锁
（1）表锁
表锁也称为表级锁，就是在整个数据表上对数据进行加锁和释放锁。典型特点是开销比较小，加锁速度快，一般不会出现死锁，锁定的粒度比较大，发生锁冲突的概率最高，并发度最低。
在MySQL中，有两种表级锁模式：一种是表共享锁（Table Shard Lock）；另一种是表独占写锁（Table Write Lock）。
当一个线程获取到一个表的读锁后，其他线程仍然可以对表进行读操作，但是不能对表进行写操作。当一个线程获取到一个表的写锁后，只有持有锁的线程可以对表进行更新操作，其他线程对数据表的读写操作都会被阻塞，直到写锁被释放为止。
可以在MySQL的命令行通过如下命令手动增加表锁。
lock table 表名称 read(write),表名称2 read(write);
例如，为account数据表增加表级读锁，如下所示。
mysql> lock table account read; Query OK, 0 rows affected (0.00 sec)
为account数据表增加表级写锁，如下所示。
mysql> lock table account write; Query OK, 0 rows affected (0.00 sec)
使用如下命令可以查看数据表上增加的锁。
show open tables;
例如，查看account数据表上增加的锁，如下所示。
mysql> show open tables; +--------------------+---------------------------+--------+-------------+ | Database | Table | In_use | Name_locked | +--------------------+---------------------------+--------+-------------+ | test | account | 1 | 0 | ##########################省略其他信息################ +--------------------+---------------------------+--------+-------------+ 50 rows in set (0.00 sec)
使用如下命令可以删除表锁。
unlock tables;
例如，删除account数据表中手动添加的表锁，如下所示。
mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)
（2）行锁
行锁也称为行级锁，就是在数据行上对数据进行加锁和释放锁。典型特点是开销比较大，加锁速度慢，可能会出现死锁，锁定的粒度最小，发生锁冲突的概率最小，并发度最高。
在InnoDB存储引擎中，有两种类型的行锁：一种是共享锁，另一种是排他锁。共享锁允许一个事务读取一行数据，但不允许一个事务对加了共享锁的当前行增加排他锁。排他锁只允许当前事务对数据行进行增删改查操作，不允许其他事务对增加了排他锁的数据行增加共享锁和排他锁。
使用行锁时，需要注意以下几点。
1）行锁主要加在索引上，如果对非索引的字段设置条件进行更新，行锁可能会变成表锁。
2）InnoDB的行锁是针对索引加锁，不是针对记录加锁，并且加锁的索引不能失效，否则行锁可能会变成表锁。
3）锁定某一行时，可以使用lock in share mode命令来指定共享锁，使用for update命令来指定排他锁，例如下面的SQL语句。
select * from account where id = 1 for update;
（3）页面锁
页面锁也称为页级锁，就是在页面级别对数据进行加锁和释放锁。对数据的加锁开销介于表锁和行锁之间，可能会出现死锁，锁定的粒度大小介于表锁和行锁之间，并发度一般。
接下来，我们总结一下表锁、行锁和页面锁的特点，如表1-2所示。
表1-2 表锁、行锁和页面锁的特点
4.间隙锁和临键锁
（1）间隙锁
在MySQL中使用范围查询时，如果请求共享锁或排他锁，InnoDB会给符合条件的已有数据的索引项加锁。如果键值在条件范围内，而这个范围内并不存在记录，则认为此时出现了“间隙（也就是GAP）”。InnoDB存储引擎会对这个“间隙”加锁，而这种加锁机制就是间隙锁（GAP Lock）。
说得简单点，间隙锁就是对两个值之间的间隙加锁。MySQL的默认隔离级别是可重复读，在可重复读隔离级别下会存在幻读的问题，而间隙锁在某种程度下可以解决幻读的问题。
例如，account数据表中存在如下数据。
mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 300 | | 2 | 李四 | 350 | | 3 | 王五 | 500 | | 15 | 赵六 | 100 | | 20 | 田七 | 360 | +----+--------+---------+ 5 rows in set (0.00 sec)
此时，account数据表中的间隙包括id为(3,15]、(15,20]、(20，正无穷]的三个区间。
如果执行如下命令，将符合条件的用户的账户余额增加100元。
update account set balance = balance + 100 where id > 5 and id <16;
则其他事务无法在(3,20]这个区间内插入或者修改任何数据。
这里需要注意的是，间隙锁只有在可重复读事务隔离级别下才会生效。
（2）临键锁
临键锁（Next-Key Lock）是行锁和间隙锁的组合，例如上面例子中的区间(3,20]就可以称为临键锁。
1.4.6 死锁的产生和预防
虽然锁在一定程度上能够解决并发问题，但稍有不慎，就可能造成死锁。发生死锁的必要条件有4个，分别为互斥条件、不可剥夺条件、请求与保持条件和循环等待条件，如图1-6所示。
图1-6 死锁的必要条件
（1）互斥条件
在一段时间内，计算机中的某个资源只能被一个进程占用。此时，如果其他进程请求该资源，则只能等待。
（2）不可剥夺条件
某个进程获得的资源在使用完毕之前，不能被其他进程强行夺走，只能由获得资源的进程主动释放。
（3）请求与保持条件
进程已经获得了至少一个资源，又要请求其他资源，但请求的资源已经被其他进程占有，此时请求的进程就会被阻塞，并且不会释放自己已获得的资源。
（4）循环等待条件
系统中的进程之间相互等待，同时各自占用的资源又会被下一个进程所请求。例如有进程A、进程B和进程C三个进程，进程A请求的资源被进程B占用，进程B请求的资源被进程C占用，进程C请求的资源被进程A占用，于是形成了循环等待条件，如图1-7所示。
图1-7 死锁的循环等待条件
需要注意的是，只有4个必要条件都满足时，才会发生死锁。
处理死锁有4种方法，分别为预防死锁、避免死锁、检测死锁和解除死锁，如图1-8所示。
图1-8 处理死锁的方法
1）预防死锁：处理死锁最直接的方法就是破坏造成死锁的4个必要条件中的一个或多个，以防止死锁的发生。
2）避免死锁：在系统资源的分配过程中，使用某种策略或者方法防止系统进入不安全状态，从而避免死锁的发生。
3）检测死锁：这种方法允许系统在运行过程中发生死锁，但是能够检测死锁的发生，并采取适当的措施清除死锁。
4）解除死锁：当检测出死锁后，采用适当的策略和方法将进程从死锁状态解脱出来。
在实际工作中，通常采用有序资源分配法和银行家算法这两种方式来避免死锁，大家可自行了解。
1.4.7 MySQL中的死锁问题
在MySQL 5.5.5及以上版本中，MySQL的默认存储引擎是InnoDB。该存储引擎使用的是行级锁，在某种情况下会产生死锁问题，所以InnoDB存储引擎采用了一种叫作等待图（wait-for graph）的方法来自动检测死锁，如果发现死锁，就会自动回滚一个事务。
接下来，我们看一个MySQL中的死锁案例。
第一步：打开终端A，登录MySQL，将事务隔离级别设置为可重复读，开启事务后为account数据表中id为1的数据添加排他锁，如下所示。
mysql> set session transaction isolation level repeatable read; Query OK, 0 rows affected (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from account where id =1 for update; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 300 | +----+--------+---------+ 1 row in set (0.00 sec)
第二步：打开终端B，登录MySQL，将事务隔离级别设置为可重复读，开启事务后为account数据表中id为2的数据添加排他锁，如下所示。
mysql> set session transaction isolation level repeatable read; Query OK, 0 rows affected (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from account where id =2 for update; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 2 | 李四 | 350 | +----+--------+---------+ 1 row in set (0.00 sec)
第三步：在终端A为account数据表中id为2的数据添加排他锁，如下所示。
mysql> select * from account where id =2 for update;
此时，线程会一直卡住，因为在等待终端B中id为2的数据释放排他锁。
第四步：在终端B中为account数据表中id为1的数据添加排他锁，如下所示。
mysql> select * from account where id =1 for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
此时发生了死锁。通过如下命令可以查看死锁的日志信息。
show engine innodb status\G
通过命令行查看LATEST DETECTED DEADLOCK选项相关的信息，可以发现死锁的相关信息，或者通过配置innodb_print_all_deadlocks（MySQL 5.6.2版本开始提供）参数为ON，将死锁相关信息打印到MySQL错误日志中。
在MySQL中，通常通过以下几种方式来避免死锁。
1）尽量让数据表中的数据检索都通过索引来完成，避免无效索引导致行锁升级为表锁。
2）合理设计索引，尽量缩小锁的范围。
3）尽量减少查询条件的范围，尽量避免间隙锁或缩小间隙锁的范围。
4）尽量控制事务的大小，减少一次事务锁定的资源数量，缩短锁定资源的时间。
5）如果一条SQL语句涉及事务加锁操作，则尽量将其放在整个事务的最后执行。
6）尽可能使用低级别的事务隔离机制。
1.4.8 InnoDB中的MVCC原理
1.4.4节提到了可重复读隔离级别使用了MVCC机制，本节将具体介绍InnoDB存储引擎中的MVCC原理。
在MVCC机制中，每个连接到数据库的读操作，在某个瞬间看到的都是数据库中数据的一个快照，而写操作的事务提交之前，读操作是看不到这些数据的变化的。
MVCC机制能够大大提升数据库的读写性能，很多数据库厂商的事务性存储引擎都实现了MVCC机制，包含MySQL、Oracle、PostgreSQL等。虽然不同数据库实现MVCC机制的细节不同，但大多实现了非阻塞的读操作，写操作也只会锁定必要的数据行。
从本质上讲，MVCC机制保存了数据库中数据在某个时间点上的数据快照，这意味着同一个读操作的事务，按照相同的条件查询数据，无论查询多少次，结果都是一样的。从另一个角度来讲，这也意味着不同的事务在同一时刻看到的同一张表的数据可能不同。
在InnoDB存储引擎中，MVCC机制是通过在每行数据表记录后面保存两个隐藏的列来实现的，一个列用来保存行的创建版本号，另一个列用来保存行的过期版本号。每当有一个新的事务执行时，版本号就会自动递增。事务开始时刻的版本号作为事务的版本号，用于和查询到的每行记录的版本号做对比。
接下来，我们来看在可重复读事务隔离级别下，MVCC机制是如何完成增删改查操作的。
1.查询操作
在查询操作中，InnoDB存储引擎会根据下面两个条件检查每行记录。
1）InnoDB存储引擎只会查找不晚于当前事务版本的数据行，也就是说，InnoDB存储引擎只会查找版本号小于或者等于当前事务版本的数据行。这些数据行要么在事务开始前就已经存在，要么就是事务本身插入或者更新的数据行。
2）数据行删除的版本要么还没有被定义，要么大于当前事务的版本号，只有这样才能确保事务读取到的行，在事务开始之前没有被删除。
这里需要注意的是，只有符合上面两个条件的数据行，才会被返回作为查询的结果数据。
例如，存在事务A和事务B两个事务，事务A中存在两条相同的select语句，事务B中存在一条update语句。事务A中的第一条select语句在事务B提交之前执行，第二条select语句在事务B提交之后执行。事务A如下所示。
start transaction; select * from account where id = 1; //在事务B提交之前执行 select * from account where id = 1; //在事务B提交之后执行 commit;
事务B如下所示。
start transaction; update account set balance = balance + 100 where id = 1; commit;
如果不使用MVCC机制，则事务A中的第一条select语句读取的数据是修改前的数据，而第二条select语句读取的是修改后的数据，两次读取的数据不一致。如果使用了MVCC机制，则无论事务B如何修改数据，事务A中的两条select语句查询出来的结果始终是一致的。
2.插入操作
在插入操作中，InnoDB存储引擎会将新插入的每一行记录的当前系统版本号保存为行版本号。
例如向account数据表中插入一条数据，同时假设MVCC的两个版本号分别为create_version和delete_version：create_version代表创建行的版本号；delete_version代表删除行的版本号。为了更好地展示效果，再增加一个描述事务版本号的字段trans_id。向account数据表插入数据的SQL语句如下所示。
insert into account(id, name, balance) values (1001, '冰河', 100);
对应的版本号信息如表1-3所示。
表1-3 MVCC机制下的插入操作
从表1-3中可以看出，当向数据表中新增记录时，需要设置保存行的版本号，而删除行的版本号未定义。
3.更新操作
在更新操作中，InnoDB存储引擎会插入一行新记录，并保存当前系统的版本号作为新记录行的版本号，同时保存当前系统的版本号到原来的数据行作为删除标识。
例如，将account数据表中id为1001的用户的账户余额增加100元，SQL语句如下所示。
update account set balance = balance + 100 where id = 1001;
执行SQL语句成功后，再次查询account数据表中的数据，存在版本号和事务编号不同的两条记录，如表1-4所示。
表1-4 MVCC机制下的更新操作
从表1-4可以看出，执行更新操作时，MVCC机制是先将原来的数据复制一份，将balance字段的值增加100后，再将create_version字段的值设置为当前系统的版本号，而delete_version字段的值未定义。除此之外，MVCC机制还会将原来行的delete_version字段的值设置为当前的系统版本号，以标识原来行被删除。
这里需要注意的是，原来的行会被复制到Undo Log中。
4.删除操作
在删除操作中，InnoDB存储引擎会保存删除的每一个行记录当前的系统版本号，作为行删除标识。
例如，删除account数据表中id为1001的数据，SQL语句如下所示。
delete from account where id = 1001;
对应的版本号信息如表1-5所示。
表1-5 MVCC机制下的删除操作
从表1-5中可以看出，当删除数据表中的数据行时，MVCC机制会将当前系统的版本号写入被删除数据行的删除版本字段delete_version中，以此来标识当前数据行已经被删除。