锁的粒度

InnoDB 里面既有行级别的锁 ，又有表级别的锁 ，我们先来分析一下这两种锁定粒度的一些差异。

表锁顾名思义 ，是锁住一张表； 行锁就是锁住表里面的一行数据。

锁定粒度 ，表锁肯定是大于行锁的。那么加锁效率 ，表锁应该是大于行锁还是小于行锁呢？大于。为什么？表锁只需要直接锁住这张表就行了 ，而行锁 ，还需要在表里面去检索这一行数据 ，所以表锁的加锁效率更高。

第二个冲突的概率？表锁的冲突概率比行锁大 ，还是小？大于 ，因为当我们锁住一张表的时候 ，其他任何一个事务都不能操作这张表。但是我们锁住了表里面的一行数据的时候 ，其他的事务还可以来操作表里面的其他没有被锁定的行 ，所以表锁的冲突概率更大。表锁的冲突概率更大 ，所以并发性能更低 ，这里并发性能就是小于。

InnoDB 里面我们知道它既支持表锁又支持行锁 ，另一个常用的存储引擎 MyISAM 支 持什么粒度的锁？这是第一个问题。第二个就是 InnoDB 已经支持行锁了 ，那么它也可以通过把表里面的每一行都锁住来实现表锁 ，为什么还要提供表锁呢？要搞清楚这个问题 ，我们就要来了解一下 InnoDB 里面的基本的锁的模式（lockmode ），这里面有两个行锁和两个表锁。

共享锁

第一个行级别的锁就是我们在官网看到的 Shared Locks （共享锁） ，我们获取了一行数据的读锁以后 ，可以用来读取数据 ，所以它也叫做读锁 ，注意不要在加上了读锁以后去写数据 ，不然的话可能会出现死锁的情况。而且多个事务可以共享一把读锁。那怎么给一行数据加上读锁呢？

我们可以用 select …… lock in share mode; 的方式手工加上一把读锁。

释放锁有两种方式 ，只要事务结束 ，锁就会自动事务 ，包括提交事务和结束事务。

我们也来验证一下 ，看看共享锁是不是可以重复获取。

排它锁

第二个行级别的锁叫做 Exclusive Locks（排它锁） ，它是用来操作数据的 ，所以又叫做写锁。只要一个事务获取了一行数据的排它锁 ，其他的事务就不能再获取这一行数据的共享锁和排它锁。

排它锁的加锁方式有两种:

• 第一种是自动加排他锁。我们在操作数据的时候 ，包括增删改 ，都会默认加上一个排它锁。
• 还有一种是手工加锁 ，我们用一个 FOR UPDATE 给一行数据加上一个排它锁 ，这个无论是在我们的代码里面还是操作数据的工具里面 ，都比较常用。释放锁的方式跟前面是一样的。

排他锁的验证：

*Transaction* *1* *Transaction* *2* begin; UPDATE student SET sname = '猫老公 555' WHERE id=1; begin; SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;// BLOCKEDSELECT * FROM student where id=1 FOR UPDATE; // BLOCKED DELETE FROM student where id=1 ; // BLOCKED

这个是两个行锁 ，接下来就是两个表锁。

意向锁

意向锁是什么呢？我们好像从来没有听过 ，也从来没有使用过 ，其实他们是由数据库自己维护的。

也就是说 ，当我们给一行数据加上共享锁之前 ，数据库会自动在这张表上面加一个意向共享锁。当我们给一行数据加上排他锁之前 ，数据库会自动在这张表上面加一个意向排他锁。

反过来说 ：如果一张表上面至少有一个意向共享锁 ，说明有其他的事务给其中的某些数据行加上了共享锁。如果一张表上面至少有一个意向排他锁 ，说明有其他的事务给其中的某些数据行加上了排他锁。

select * from t2  where id =4 for update;

TABLE LOCK table `gupao`.`t2` trx id 24467 lock mode IX

RECORD LOCKS space id 64 page no 3 n bits 72 index PRIMARY of table `gupao`.`t2` trx id 24467 lock_mode X locks rec but not gap

那么这两个表级别的锁存在的意义是什么呢？第一个 ，我们有了表级别的锁 ，在InnoDB 里面就可以支持更多粒度的锁。它的第二个作用 ，我们想一下 ，如果说没有意向锁的话 ，当我们准备给一张表加上表锁的时候 ，我们首先要做什么？是不是必须先要去判断有没其他的事务锁定了其中了某些行？如果有的话 ，肯定不能加上表锁。那么这个时候我们就要去扫描整张表才能确定能不能成功加上一个表锁 ，如果数据量特别大 ，比如有上千万的数据的时候 ，加表锁的效率是不是很低？

但是我们引入了意向锁之后就不一样了。我只要判断这张表上面有没有意向锁 ，如果有 ，就直接返回失败。如果没有 ，就可以加锁成功。所以 InnoDB 里面的表锁 ，我们可以把它理解成一个标志。就像火车上厕所有没有人使用的灯 ，是用来提高加锁的效率的。

以上就是 MySQL 里面的 4 种基本的锁的模式，或者叫做锁的类型。 到这里我们要思考两个问题，首先，锁的作用是什么？它跟 Java 里面的锁是一样的，是为了解决资源竞争的问题，Java 里面的资源是对象，数据库的资源就是数据表或者数据行。

所以锁是用来解决事务对数据的并发访问的问题的。

锁的算法

我们先来看一下我们测试用的表，这张表有一个主键索引。我们插入了 4 行数据，主键值分别是 1、4、7、10。因为我们用主键索引加锁，我们这里的划分标准就是主键索引的值。

这些数据库里面存在的主键值，我们把它叫做 Record，记录，那么这里我们就有 4个 Record.

根据主键，这些存在的 Record 隔开的数据不存在的区间，我们把它叫做 Gap，间隙，它是一个左开右开的区间。

最后一个，间隙（Gap）连同它左边的记录（Record），我们把它叫做临键的区间，它是一个左开右闭的区间.

t2 的主键索引，它是整型的，可以排序，所以才有这种区间。如果我的主键索引不是整形，是字符怎么办呢？字符可以排序吗？ 用 ASCII 码来排序。

我们已经弄清楚了三个范围的概念，下面我们就来看一下在不同的范围下，行锁是怎么表现的.

记录锁

第一种情况，当我们对于唯一性的索引（包括唯一索引和主键索引）使用等值查询，精准匹配到一条记录的时候，这个时候使用的就是记录锁。

比如 where id = 1 4 7 10 。

这个演示我们在前面已经看过了。我们使用不同的 key 去加锁，不会冲突，它只锁住这个 record

间隙锁

第二种情况，当我们查询的记录不存在，没有命中任何一个 record，无论是用等值查询还是范围查询的时候，它使用的都是间隙锁。

举个例子，

where id >4 and id <7，where id = 6。

*Transaction* *1* *Transaction* *2* begin; INSERT INTO t2 (id, name) VALUES (5, '5'); // BLOCKED INSERT INTO t2 (id, name) VALUES (6, '6'); // BLOCKED select * from t2 where id =6 for update; // OK select * from t2 where id >20 for update; INSERT INTO t2 (id, name) VALUES (11, '11'); // BLOCKED

注意 ，间隙锁主要是阻塞插入 insert。相同的间隙锁之间不冲突。

临键锁

当我们使用了范围查询 ，不仅仅命中了 Record 记录 ，还包含了 Gap 间隙 ，在这种情况下我们使用的就是临键锁 ，它是 MySQL 里面默认的行锁算法 ，相当于记录锁加上间隙锁。

其他两种退化的情况 ：

• 唯一性索引 ，等值查询匹配到一条记录的时候 ，退化成记录锁。
• 没有匹配到任何记录的时候 ，退化成间隙锁。

比如我们使用>5 <9 ， 它包含了记录不存在的区间 ，也包含了一个 Record 7。

*Transaction* *1* *Transaction* *2* begin; select * from t2 where id >5 andid < 9 for update; begin; select * from t2 where id =4 for update; // OKINSERT INTO t2 (id, name) VALUES (6, '6'); // BLOCKED INSERT INTO t2 (id, name) VALUES (8, '8'); // BLOCKED select * from t2 where id =10 for update; // BLOCKED

临键锁，锁住最后一个 key 的下一个左开右闭的区间.

select * from t2 where id >5 and id <=7 for update; -- 锁住(4,7]和(7,10]
select * from t2 where id >8 and id <=10 for update; -- 锁住 (7,10]，(10,+∞)

为什么要锁住下一个左开右闭的区间？——就是为了解决幻读的问题

死锁

锁什么时候释放？

事务结束（commit，rollback）；客户端连接断开

如果一个事务一直未释放锁，其他事务会被阻塞多久？会不会永远等待下去？如果 是，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占 用大量计算机资源，造成严重性能问题，甚至拖跨数据库。 [Err] 1205 - Lock wait timeout exceeded; try restarting transaction MySQL 有一个参数来控制获取锁的等待时间，默认是 50 秒。

show VARIABLES like 'innodb_lock_wait_timeout';

对于死锁，是无论等多久都不能获取到锁的，这种情况，也需要等待 50 秒钟吗？那不是白白浪费了 50 秒钟的时间吗？

我们先来看一下什么时候会发生死锁

死锁的发生和检测

死锁演示：

*Session* *1* *Session* *2* begin;select * from t2 where id =1 for update; begin;delete from t2 where id =4 ; update t2 set name= '4d' where id =4 ;

在第一个事务中 ，检测到了死锁 ，马上退出了 ，第二个事务获得了锁 ，不需要等待50 秒 ：

[ Err] 1213 - Deadlock found when trying to get lock; try restarting transaction

为什么可以直接检测到呢？是因为死锁的发生需要满足一定的条件 ，所以在发生死锁时 ，InnoDB 一般都能通过算法（ wait-for graph）自动检测到。

那么死锁需要满足什么条件？死锁的产生条件 ：

• 同一时刻只能有一个事务持有这把锁 ，
• 其他的事务需要在这个事务释放锁之后才能获取锁 ，而不可以强行剥夺 - 当多个事务形成等待环路的时候 ，即发生死锁。

如果锁一直没有释放 ，就有可能造成大量阻塞或者发生死锁 ，造成系统吞吐量下降 ，这时候就要查看是哪些事务持有了锁。

show status like 'innodb_row_lock_%';

Innodb_row_lock_current_waits：当前正在等待锁定的数量；
Innodb_row_lock_time  ：从系统启动到现在锁定的总时间长度，单位 ms；
Innodb_row_lock_time_avg  ：每次等待所花平均时间；
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间；
Innodb_row_lock_waits  ：从系统启动到现在总共等待的次数。

SHOW 命令是一个概要信息。InnoDB 还提供了三张表来分析事务与锁的情况 ：

select * from information_schema.INNODB_TRX;    --  当前运行的所有事务 ，还有具体的语句

select * from information_schema.INNODB_LOCKS;    --  当前出现的锁

select * from information_schema.INNODB_LOCK_WAITS;    --  锁等待的对应关系

找出持有锁的事务之后呢？

如果一个事务长时间持有锁不释放，可以kill事务对应的线程ID，也就是INNODB_TRX 表中的 trx_mysql_thread_id ，例如执行 kill 4 ，kill 7 ，kill 8。

当然 ，死锁的问题不能每次都靠 kill 线程来解决 ，这是治标不治本的行为。我们应该尽量在应用端 ，也就是在编码的过程中避免。

有哪些可以避免死锁的方法呢？

1. 在程序中 ，操作多张表时 ，尽量以相同的顺序来访问（避免形成等待环路）；
2. 批量操作单张表数据的时候 ，先对数据进行排序（避免形成等待环路）
3. 申请足够级别的锁 ，如果要操作数据 ，就申请排它锁；
4. 尽量使用索引访问数据 ，避免没有 where 条件的操作 ，避免锁表；
5. 如果可以 ，大事务化成小事务；
6. 使用等值查询而不是范围查询查询数据 ，命中记录 ，避免间隙锁对并发的影响。