Mysql判断sql语句触发行锁还是表锁

前言
我们前几篇讲了索引是什么，如何使用explain分析索引使用情况，如何去优化索引，以及show profiles分析SQL语句执行资源消耗的学习。今天我们来讲讲MySQL的各种锁，这里存储引擎我们使用InnoDB；

准备工作
创建表 tb_innodb_lock
drop table if exists test_innodb_lock;
CREATE TABLE test_innodb_lock (
    a INT (11),
    b VARCHAR (20)
) ENGINE INNODB DEFAULT charset = utf8;
insert into test_innodb_lock values (1,'a');
insert into test_innodb_lock values (2,'b');
insert into test_innodb_lock values (3,'c');
insert into test_innodb_lock values (4,'d');
insert into test_innodb_lock values (5,'e');
创建索引
create index idx_lock_a on test_innodb_lock(a);
create index idx_lock_b on test_innodb_lock(b);
MySQL 各种锁演示
先将自动提交事务改成手动提交：set autocommit=0;
我们启动两个会话窗口 A 和 B，模拟一个抢到锁，一个没抢到被阻塞住了。

行锁：

在MySQL的InnoDB引擎支持行锁，与Oracle不同，MySQL的行锁是通过索引加载的，也就是说，行锁是加在索引响应的行上的，要是对应的SQL语句没有走索引，则会全表扫描，行锁则无法实现，取而代之的是表锁，此时其它事务无法对当前表进行更新或插入操作。

for update

如果在一条select语句后加上for update，则查询到的数据会被加上一条排它锁，其它事务可以读取，但不能进行更新和插入操作。

-- A用户对id=1的记录进行加锁
select * from user where id=1 for update;
 
-- B用户无法对该记录进行操作
update user set count=10 where id=1;
 
-- A用户commit以后则B用户可以对该记录进行操作
行锁的实现需要注意：

行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。
两个事务不能锁同一个索引。
insert，delete，update在事务中都会自动默认加上排它锁。

行锁（写&读）

A 窗口执行
update test_innodb_lock set b='a1' where a=1;
SELECT * from test_innodb_lock;

我们可以看到 A 窗口可以看到更新后的结果；

B 窗口执行
SELECT * from test_innodb_lock;
 

 我们可以看到 B 窗口不能看到更新后的结果，看到的还是老数据，这是因为 a = 1 的这行记录被 A 窗口执行的 SQL 语句抢到了锁，并且没有执行 commit 提交操作。所以窗口 B 看到的还是老数据。这就是 MySQL 隔离级别中的"读已提交"。

窗口 A 执行 commit 操作
COMMIT;
窗口 B 查询
SELECT * from test_innodb_lock;

 这个时候我们发现窗口 B 已经读取到最新数据了。

行锁（写&写）

窗口 A 执行更新 a = 1 的记录
update test_innodb_lock set b='a2' where a=1;
这时候并没有 commit 提交，锁是窗口 A 持有。

窗口 B 也执行更新 a = 1 的记录
update test_innodb_lock set b='a3' where a=1;

可以看到，窗口 B 一直处于阻塞状态，因为窗口 A 还没有执行 commit，还持有锁。窗口 B 抢不到 a = 1 这行记录的锁，所以一直阻塞等待。

窗口 A 执行 commit 操作
COMMIT;
窗口 B 的变化

可以看到这个时候窗口 B 已经执行成功了。

表锁

当索引失效的时候，行锁会升级成表锁，索引失效的其中一个方法是对索引自动 or 手动的换型。a 字段本身是 integer，我们加上引号，就变成了 String，这个时候索引就会失效了。

窗口 A 更新 a = 1 的记录
update test_innodb_lock set b='a4' where a=1 or a=2;

窗口 B 更新 a = 3 的记录
update test_innodb_lock set b='b1' where a=3;

这个时候发现，虽然窗口 A 和 B 更新的行不一样，但是窗口 B 还是被阻塞住了，就是因为窗口 A 的索引失效，导致行锁升级成了表锁，把整个表锁住了，索引窗口 B 被阻塞了。

窗口 A 执行 commit 操作
可以看到这个时候窗口 B 已经执行成功了。

间隙锁

间隙锁是innodb中行锁的一种， 但是这种锁锁住的却不止一行数据，他锁住的是多行，是一个数据范围。间隙锁的主要作用是为了防止出现幻读，但是它会把锁定范围扩大，有时候也会给我们带来麻烦，我们就遇到了。 在数据库参数中， 控制间隙锁的参数是：innodb_locks_unsafe_for_binlog， 这个参数默认值是OFF， 也就是启用间隙锁， 他是一个bool值， 当值为true时表示disable间隙锁。那为了防止间隙锁是不是直接将innodb_locaks_unsafe_for_binlog设置为true就可以了呢？ 不一定！而且这个参数会影响到主从复制及灾难恢复， 这个方法还尚待商量。

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

什么是间隙锁
当我们采用范围条件查询数据时，InnoDB 会对这个范围内的数据进行加锁。比如有 id 为：1、3、5、7 的 4 条数据，我们查找 1-7 范围的数据。那么 1-7 都会被加上锁。2、4、6 也在 1-7 的范围中，但是不存在这些数据记录，这些 2、4、6 就被称为间隙。

间隙锁的危害
范围查找时，会把整个范围的数据全部锁定住，即便这个范围内不存在的一些数据，也会被无辜的锁定住，比如我要在 1、3、5、7 中插入 2，这个时候 1-7 都被锁定住了，根本无法插入 2。在某些场景下会对性能产生很大的影响

间隙锁演示
我们先把字段 a 的值修改成 1、3、5、7、9

窗口 A 更新 a = 1~7 范围的数据
update test_innodb_lock set b='b5' where a>1 and a<7;

窗口 B 在 a = 2 的位置插入数据
insert into test_innodb_lock values(2, "b6");

 这个时候发现窗口 B 更新 a = 2 的操作一直在等待，因为 1~7 范围的数据被间隙锁，锁住了。只有等窗口 A 执行 commit，窗口 B 的 a = 2 才能更新成功。

 参考资料：https://segmentfault.com/a/1190000037534778

建议：

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能减少索引条件，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度