MySQL中的“锁”事

乐观锁和悲观锁

• 乐观锁：类似Java中的CAS算法，每次读数据的时候都认为别人不会修改数据，所以不会上锁，只有在更新的时候去判断数据是否被修改过，一般都会使用版本号机制实现。
• 悲观锁：类似Java中的synchronized和lock锁，每次都认为别人会修改数据，所以每次读数据时都会上锁，这样别人想修改数据时就会被阻塞。MySQL中的行锁，表锁等都是悲观锁。

共享锁和排他锁

在InnoDB存储引擎中两种行级锁：

• 共享锁（S lock）：允许事务读一行数据
• 排他锁（X lock）; 允许事务更新或删除一行数据

如果一个事务已经获取行的S锁，那么其它事务也可以获取该行的S锁，因为S锁并没有改变数据。但是其他事务不能获取该行的X锁，必须等S锁释放后才能获取X锁。
下面展示了S锁和X锁的兼容性（可见只有S锁与S锁是兼容的，也就是说一个行可以被多个事务同时拥有S锁）：

	S锁	X锁
S锁	兼容	不兼容
X锁	不兼容	不兼容

先来建一张表用于后面的示例：

Create Table: CREATE TABLE `test` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `name` varchar(8) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

然后插入两条数据：

insert into test(name) values("小熊"), ("小杰");

在MySQL中可以显示上锁，FOR UPDATE用于X锁，LOCK IN SHARE MODE 用于S锁。

共享锁

上面已经说了，共享锁就是读锁，也就是读数据的时候上锁，因为读数据不会改变数据，所以允许多个事务同时拥有读锁，下面来验证一下：
第一个事务：

BEGIN;
// 显示上S锁
select * from test where id = 5 lock in share mode;
// 延迟30s执行
select sleep(30);
// 提交事务1
COMMIT;

第二个事务：

BEGIN;
// 显示上S锁
select * from test where id = 5 lock in share mode;
COMMIT;

事务1与事务2执行结果一，因为在事务2执行的时候，事务1还没有提交。说明两个事务可以同时对某一行拥有S锁。

+----+--------+
| id | name   |
+----+--------+
|  5 | 小熊   |
+----+--------+

但是在事务1拥有读锁的时候，事务2能不能对该行进行修改呢。答案当然是不能的，因为X锁获取的前提是S锁已经释放了，还是以上面的例子看。
在第二个事务的查询语句后面再执行一句update test set name = '小熊' where id = 5;（这里没有显示上锁，但是MySQL会自动给数据加锁，这里加的是X锁，这就是隐式锁），同时事务1一直不提交。执行结果为：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

由于事务1一直没有提交，也就是一直持有着S锁，那么事务2将无法得到该行的X锁，所以就会造成锁等待超时，这条语句将无法得到执行。
这时提交事务1，再次执行上面的更新语句，将会执行成功。虽然这时该行还是有个S锁，但因为他们是在同一个事务中，不存在并发问题，所以可以进行修改操作。

排他锁

排它锁其实就是写锁，因为在写数据的时候会修改数据，所以为了保证数据的一致性，只能允许在同一时刻只能有一个事务拥有该行的X锁，下面验证一下：
事务1，执行一条语句，上了X锁，但事务先不提交

BEGIN;
// 显示上X锁
select * from test where id = 5 for update;

事务1执行结果：

+----+--------+
| id | name   |
+----+--------+
|  5 | 小熊   |
+----+--------+
1 row in set (0.00 sec)

事务2，执行一条语句，也上X锁

BEGIN;
// 显示上X锁
select * from test where id = 5 for update;

事务2执行结果（因为事务1没有提交，事务1的X锁不会释放，所以事务2得不到X锁，所以执行失败）

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

这时提交事务1，再次执行事务2的查询，则执行成功（事务1的X锁已经释放，事务2可以成功获取到X锁）。
这里可以看到一个问题，如果事务1一直迟迟不提交事务（可能由于业务繁忙等原因），将会导致事务2的锁等待超时，使事务2的语句没法执行，但是这时事务2并不会回滚。

自增锁

在InnoDB存储引擎中，对每个含有自增长值的表都有一个自增长计数器。插入操作会根据这个自增长计数器的值加1，然后赋值给自增长的列，从而实现自增。而计数器值的获取是通过加锁获取的：select max(auto_inc_col) from t for update;所以这种自增的实现方式称为AUTO-INC Locking。这种锁其实是采用一种特殊的表锁机制，为了提高插入的性能，会在插入完成后立即释放锁，而不是等事务结束。下面来验证一下：
事务1，开启一个事务，并插入一条数据，事务不提交。
事务2，也开始一个事务，插入一条数据，可以插入成功，说明自增锁是在插入完成后就释放锁，而不是在事务结束后释放锁。

行锁算法

InnoDB存储引擎有三种行锁的算法（这些都是排它锁），分别是：

• 记录锁（Record Lock）：单个行记录上的锁，这也是最常用的锁。
• 间隙锁（Gap Lock）：锁定一个范围，但不包含记录本身。（就是把某个范围内的间隔锁住）
• 临键锁（Next-Key Lock）：锁定一个范围，但包含记录本身。（其实就是记录锁+间隙锁）

记录锁就不用说了，下面主要分析一下间隙锁，临键锁类似间隙锁，只是包含了当前记录 。
间隙锁是对索引记录之间间隙的锁定（也就是该记录之前或之后间隙的锁定）。间隙锁只能用于repeatable read（可重复读）事务级别。它的目的就是为了防止其它事务将数据插入记录的间隙。
通过下面两种方式可以显示地关闭间隙锁：

• 将事务的隔离级别设置为read committed（读提交）
• 将参数innodb_locks_unsafe_for_binlog设置为1（现已弃用）
  在唯一索引和普通索引下产生间隙锁的情况是不一样的。

唯一索引的间隙锁

如果查询的索引是唯一索引，且只对一行记录进行查询时，间隙锁会降为记录锁。下面来验证一下：
当前表中的数据如下:

+----+--------+
| id | name   |
+----+--------+
|  5 | 小熊   |
|  7 | 小明   |
|  9 | 小红   |
+----+--------+

事务1，

begin;
// 查询id为7的记录，并加上一个X锁
select * from test where id = 7 for update;

事务2，在7和9的间隙中插入记录8，可以执行成功。

insert into test value (8, '小张');

如果对唯一索引的多个记录或是一个范围进行查询的时候，就会使用间隙锁。下面再来验证一下，还是使用上面一样的数据：
事务1查询两个记录之间的范围，并上锁。

begin;
// 查询id为5和7的记录
select * from test where id between 5 adn 7 for update;

事务2，插入记录6和8，都插入失败（锁等待超时，因为我事务1没有提交，所以间隙锁一直持有）

begin;
// 尝试插入记录6和8
insert into test value (6, '小磊');
insert into test value (8, '小张');

这里可以看到间隙锁把记录5和7之间、之前、之后的间隙都上了锁。也就是区间（5,7），（7，9）。（实际验证时，，好像全部范围都被锁死了？？疑惑，，，）
当查询的行记录不存在时，也会使用间隙锁。这里不再验证。

普通索引的间隙锁

先修改一下表结构，加入一个普通索引num：

alter table test add num int(2) not null;
alter table test add index num(num);

清空之前的数据，重新插入三条记录：

insert into test(id,num,name) values(1, 1, '小熊');
insert into test(id,num,name) values(3, 3, '小红');
insert into test(id,num,name) values(5, 5, '小张');

现在的表数据如下：

+----+--------+-----+
| id | name   | num |
+----+--------+-----+
|  1 | 小熊   |   1 |
|  3 | 小红   |   3 |
|  5 | 小张   |   5 |
+----+--------+-----+

下面开启一个事务来加锁查询单条记录（查询索引是普通索引num）：

begin;
select * from test where num = 3 for update;

再在事务2中尝试插入num为4的记录：

begin;
insert into test(id,num,name) values(4, 4, '小杰');

这里的事务2中的插入语句执行结果失败，因为事务1没有提交，由于事务1查询的索引是普通索引，所以会优先使用间隙锁。锁住了区间(1,3)和（3,5）。（好像这时查询唯一索引不会产生间隙锁了？？？疑惑，，）
间隙锁的作用就是在repeatable read事务级别可以避免幻读（多次执行事务1中的查询语句，所得到的查询结果都是相同的，因为其它事务不能无法再次插入一条num为3的记录）。