MySQL事务与锁

MySQL事务与锁是紧密相连的整体，需要互相配合使用，本文介绍了事务的隔离级别、共享锁、独占锁、间隙锁、记录锁、next-key锁，索引与锁的关系、以及MySQL默认隔离级别选择可重复读的原因等。

事务是一组原子性的SQL语句，或者说是一个独立的工作单元。

• 如果数据库引擎（比如InnoDB）能够成功地对数据库应用这组SQL语句，那么就执行；
• 如果其中有任何一条语句因为崩溃或其他原因无法执行，那么所有语句都不会执行，也就是说，事务内的语句，要么全部执行成功，要么全部执行失败。

开始一个事务使用begin或者start transaction，结束事务使用commit或rollback。

InnoDB的锁

事务是基于MVCC实现的多版本并发读控制系统，锁是MySQL提供的另一种安全性的保证。在了解事务前，先了解Mysql的锁。

准备工作

首先，初始化一张表以供后续测试，打开两个mysql的命令行终端，分别称为terminalA、terminalB。在其中任意一个终端中建立一张表，插入1条初始的数据：

create database testdb;
use testdb;
CREATE TABLE IF NOT EXISTS `test_tbl`(
   `test_id` INT UNSIGNED AUTO_INCREMENT,
   `test_title` VARCHAR(100) NOT NULL,
   `test_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `test_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

随便插入几条数据，表中目前有以下数据。

mysql> select * from test_tbl ;
+---------+-------------+-------------+-----------------+
| test_id | test_title  | test_author | submission_date |
+---------+-------------+-------------+-----------------+
|      12 | testX       | txB         | NULL            |
|      13 | testX       | txB         | NULL            |
|      14 | testX       | txB         | NULL            |
|      15 | testX       | txB         | NULL            |
|      16 | testX       | txB         | NULL            |
|      18 | testX       | txB         | NULL            |
|      19 | testX       | txB         | NULL            |
|      20 | testPlus    | txA         | NULL            |
|      21 | testPlus    | txA         | NULL            |
|      22 | testPlus    | txA         | NULL            |
|      23 | testPlusMax | admin       | NULL            |
|      24 | testPlusMax | admin       | NULL            |
|      25 | testPlusMax | admin       | NULL            |
+---------+-------------+-------------+-----------------+
13 rows in set (0.00 sec)

索引避免“表锁”

InnoDB行锁是基于索引实现的。行锁锁定的是索引，而不是记录本身。

一般常说的共享锁与排他锁都是行锁，只有被锁定的时的where条件没有对应索引时，会锁定整个主键索引树，相当于对所有的行都加了共享锁或排他锁，产生类似于表锁的效果。为了避免出现这种“表锁”，影响测试，创建两个索引。

alter table test_tbl add index title (test_title);
alter table test_tbl add index author (test_author);

“表锁”的弊端：假设查询以title为查询条件，且未建立title的索引，当事务A对title=“testX”的这些行加锁后，事务B如果需要找到title=“testX”的这些行，就需要通过一级索引全表扫描，但因为事务A已经对这些行加了排他锁，导致事务B全表扫描执行不下去，事务B阻塞。

测试用例如下：在事务B中查询其它行，阻塞了，无法加共享锁，证明了整张表都被事务A加了排他锁。

// 删除索引title
drop index title on test_tbl;
// 事务A
begin;
update test_tbl set test_title="viptest" where test_title="testX";
// 事务B
begin;
select * from test_tbl where test_title="testPlus" lock in share mode;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

但是如果将查询条件换成有索引的author字段，就不会阻塞：

// 事务A
rollback;
begin;
update test_tbl set test_author="auth3" where test_author="txA";

// 事务B
rollback;
begin;
select * from test_tbl where test_author="txB" lock in share mode;
+---------+------------+-------------+-----------------+
| test_id | test_title | test_author | submission_date |
+---------+------------+-------------+-----------------+
|      12 | testX      | txB         | NULL            |
|      13 | testX      | txB         | NULL            |
|      14 | testX      | txB         | NULL            |
|      15 | testX      | txB         | NULL            |
|      16 | testX      | txB         | NULL            |
|      18 | testX      | txB         | NULL            |
|      19 | testX      | txB         | NULL            |
+---------+------------+-------------+-----------------+

共享锁

本节的共享锁仅针对行级锁，下一节的排他锁也仅针对行级锁，同时，都是在默认隔离级别RR级别下进行测试。

语句：select ... lock in share mode

• 不允许其它事务加入排他锁
• 不允许其它事务更新、删除被共享锁锁定的行
• 允许其它事务加共享锁
• 允许其它事务读整张表
• 允许其它事务删除、更新未被锁定的行
• 允许其它事务插入，但是其它事务插入后，当前事务就不能再次执行select ... lock in share mode了。

示例：

首先在事务A中，对test_author=txB的行加共享锁，但是不提交，分别验证上述几个点。

// 事务A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_tbl where test_author="txB" lock in share mode;
+---------+------------+-------------+-----------------+
| test_id | test_title | test_author | submission_date |
+---------+------------+-------------+-----------------+
|      12 | testX      | txB         | NULL            |
|      13 | testX      | txB         | NULL            |
|      14 | testX      | txB         | NULL            |
|      15 | testX      | txB         | NULL            |
|      16 | testX      | txB         | NULL            |
|      18 | testX      | txB         | NULL            |
|      19 | testX      | txB         | NULL            |
+---------+------------+-------------+-----------------+
7 rows in set (0.00 sec)

1、不允许其他事物加排他锁

在事务B，尝试使用for update添加排他锁，失败。

// 事务B
mysql