MySQL 高级(4) 锁机制

锁是计算机协调多个进程或线程间并发访问某一资源的限制

在数据库中，除了传统的计算资源和存储资源的竞争外，数据更是一种要供许多用户共享的资源。于是如何利用锁保证数据并发访问的一致性，有效性显得尤为重要

但锁同样也带来了问题，如果锁发生了冲突，则会影响到数据库的并发访问性能

MySQL 中的锁

从对数据操作的类型分分为

• 读锁：读锁是一种共享锁，针对同一份数据，多个读操作可以同时进行而不会互相影响

• 写锁：写锁是一种排它锁，当某一线程的写操作没有完成时，其它线程对对应资源的写操作无法进行，同时也无法读取

从对数据操作的细粒度分分为

• 行锁

• 表锁

从锁模式分为

• 乐观锁

• 悲观锁

从算法上分为

• 临间锁

• 间隙锁

• 记录锁

从属性上分为

• 排他锁

• 共享锁

从状态上分为

• 意向共享锁
• 意向排他锁

表锁

MyISAM 对表锁的支持比较好，MyISAM 在执行 select 之前，会自动给涉及到的所有表加读锁；在执行增删改操作之前，会自动给涉及的表加写锁

表锁即锁一次就锁了一整张表，在表被锁定期间，不能对该表进行任何操作。表锁响应的是非索引字段，即全表扫描，全表扫描时锁定整张表，发生表锁

表锁的特点有：开销小，加锁快，无死锁，粒度大，发生锁冲突的几率高，并发度低

显示添加表锁

表锁中又分为读锁和写锁，为了分别进行测试，先建一张表并插入数据

create table mylock(
    id int not null primary key auto_increment,
    name varchar(20)
);
insert into mylock(name) 
values ("a"),("b"),("c"),("d"),("e");

增加表锁的语法为

lock table 表名 read[/write], 表名2 read[/write] ...;

当为 read 时，添加的是表级读锁，为 write 则是表级写锁

查看哪些表上有锁

show open tables;

可以发现此时 mylock 中没有任何锁

对 mylock 进行上锁，然后再次查询

lock table mylock read;

show open tables;

可以看见 In_use 字段变为了 1，表示有一个正在使用的锁

tips：如果想为多个表同时上锁，千万不要分开写 lock 语句，后执行的会生效，而先执行的会失效

释放锁的语句为，该语句会释放所有表锁

ublock tables;

表级读锁

读锁是共享锁，运行多个进程或线程同时访问读锁锁定的数据

在进行实践前，首先打开两个连接 MySQL 的会话

首先，在第一个会话中为 mylock 表添加 read 锁

LOCK TABLE mylock READ;

然后在第一个会话中尝试更新数据

update mylock set name="Q" where id = 1;

不出意外，因为上了读锁，所以更新时肯定不行了滴

然后尝试查询其他的表

select * from major;

结果非常的 amazing 啊！居然连读其它表都成了一种奢望。其实可以换一个角度去思考，在会话1中我们相当于用 mylock 这张表当成了锁 ( 或者当多张表都上了锁时，这些表就都成了锁，都能访问 )，限制了只能对 mylock 访问，把整个会话给锁住了，于是我们就只能访问这张表了，只有当这个表锁释放后才能在这个会话中进行其他的操作

而第二个会话则可以访问 mylock，同时也可以访问其他表，因为这个会话并没有被锁住

但是在第二个会话中是否可以进行更新？

UPDATE mylock SET NAME="Q" WHERE id = 1;

结果非常 amazing 啊！居然阻塞了

但是当我们在第一个会话中释放锁后，第二个会话阻塞的更新语句居然立马就执行了

总结

1. 加锁的会话，不能够访问作为表锁的表之外的表
2. 加锁的会话，不能够进行更新，直接报错
3. 没有加锁的会话，可以访问作为表锁的表和其他表
4. 没有加锁的会话，增删改作为表锁的表的字段时会被阻塞，在加锁的会话释放锁后会继续执行；而增删改作为表锁的表之外的表时无限制

表级写锁

写锁是排他锁，被锁定的数据不允许其他进程或者线程进行访问或更新

在第一个会话中，使用 mylock 表上一把写锁

lock table mylock write;

在第一个会话中，访问 mylock

select * from mylock;

在第一个会话中，修改 mylock 数据后再次查询

update mylock set name="a4" where id = 1;

在第一个会话中，查询作为表锁的表以外的表

在第二个会话中，查询作为表锁的表以外的表是可以的

在第二个会话中，查询作为表锁的表时会发生阻塞；对作为表锁的表进行增删改操作同样发生阻塞。上锁会话将表锁释放后则继续执行

总结

1. 加锁的会话，可以访问和更新作为表锁的表
2. 没有加锁的会话，不能对作为表锁的表进行增删改查操作，否则将会阻塞，直到加锁的会话释放锁

表锁状态量

通过命令

SHOW STATUS LIKE "table%";

其中的 table_locks_waited 和 table_locks_immediate 来对表锁进行分析

table_locks_immediate：产生表锁的次数，表示可以立即获取锁的查询次数，每立即获取锁值+1

table_locks_waited：出现表锁争用而发生等待的次数 ( 不能立即获取锁的次数，等待一次 +1 )，此值高则代表存在较为严重的表锁争用

tips：在 MyISAM 的读写调度中是写优先，所以在做读写分离 (主从复制) 时，MyISAM 不适合作为主表的引擎。因为写锁是排它锁，其余线程不能执行任何操作，大量的更新与查询时更新优先，可能会使得查询永远阻塞

行锁

InnoDB 对行锁的支持比较好，InnoDB 中行锁通过索引加载，即：行锁加在索引响应的行上，如果 SQL 语句没有使用到索引，则全表扫描，行锁失效退化为表锁，此时其它事务也就无法对当前表进行更新或插入操作。对于删改操作，InnoDB 会给涉及到的记录加上排它锁 (这个排它锁不排查询)，对于查操作则不会加锁

行锁的特点有：开销大，加锁慢，会出现死锁；锁细粒度小，发生锁冲突的概率低，并发度高

InnoDB 和 MyISAM 最大的区别在于

1. 前者支持事务，后者不支持事务
2. 前者默认就采用了行锁，后者采用了表锁 ( MyISAM 甚至不支持行锁 )

行锁测试

创建表并插入数据

create table innodbLock(
    a int(11),
    b varchar(16)
);

insert into innodbLock values
(1, "b2"),
(3, "3"),
(4, "4000"),
(5, "5000"),
(6, "6000"),
(7, "7000"),
(8, "8000"),
(9, "9000"),
(1, "b1");

create index idx_innoLock_a on innodbLock(a);
create index idx_innoLock_b on innodbLock(b);

由于行锁是 InnoDB 在事务提交时自动加的，因此为看出使用了行锁，需要先关闭自动提交

set autocommit = 0;

在第一个会话中关闭自动提交，并执行更新语句

update innodblock set b="12345" where a=4;

然后在第二个会话中进行查询

select * from innodblock;

可以看见数据没有被更新，因为 MySQL 默认的隔离级别为可重复读，可以避免脏读和不可重复读

然后在第一个会话中提交

commit;

再到第二个会话中进行查询，可以发现对应记录更新

当两个会话都关闭自动提交后

1. 在第一个会话中执行更新语句但没有提交
2. 到第二个会话中执行更新语句，第二个会话被阻塞
3. 回到第一个会话中 commit 提交后，第二个会话才能继续执行语句
4. 同时第二个会话的更新语句执行后，也要 commit，不然第一个会话再进行更新也会被阻塞

tips：在上述的这个测试中，针对的是两个会话对同一条数据进行更新！

测试两个会话对两条不同的记录进行更新

首先，对两个会话都关闭自动提交

在第一个会话中执行

update innodblock set b="9999" where a=4;

第二个会话中执行

update innodblock set b="4343434" where a = 5;

然后各自对 innodblock 进行查询

会话 1 查询结果如下

会话 2 查询结果如下

从结果可以很容易的看出来会话 1 和会话 2 针对不同的行 (记录) 进行更新操作，并没有发生阻塞，只是对对方而言自己更新但未提交的数据是不可见的

因此，InnoDB 对表中涉及到删改操作的记录自动添加了行锁，避免了对同一行数据的删改操作！

行锁失效

InnoDB 的行锁是根据索引响应的行来加的，因此如果索引失效，则行锁会退化为表锁

测试

首先将两个会话都关闭自动提交

然后在会话 1 中，执行

update innodblock set a=3 where b=23;

让 b 的条件发生自动类型转换，从而导致索引失效

然后在会话 2 中执行

update innodblock set b="12" where a = 9;

发生阻塞

可以看见，上面两条 SQL 并不是针对的一条记录进行修改，但是依然发生了阻塞，这是因为会话 1 中的行锁失效，退化为了表锁，因此不能对表中任何数据进行任何更改

手动锁定一行

语法

select xxxx for update;

当加上 for update 后，查询的出来的对应记录将被锁定

比如

select * from innodblock where a=8 for update;

测试

首先，关闭两个会话的自动提交

然后在会话 1 中执行

select * from innodblock where a=8 for update;

然后在会话 1 中执行

update innodblock set b = "xxx" where a = 8;

可以发现阻塞

但是会话 1 对该记录的操作不会有任何影响

行锁状态量

通过命令

show status like "innodb_row_lock%";

查看状态量

innodb_row_lock_current_waits：当前因为行锁阻塞的会话的数量

innodb_row_lock_time：从系统启动到目前为止行锁锁定的总时长

innodb_row_lock_time_avg：每次等待的平均时长

innodb_row_lock_time_max：目前为止等待的最长时间

innodb_row_lock_waits：等待行锁释放的总次数

间隙锁

当使用范围条件而不是等值条件时检索数据，并请求共享锁或排它锁时，InnoDB 会给符合条件的，已有数据记录的索引项上锁，对于键值在条件范围内但是并不存在的记录，叫做间隙，同时 InnoDB 也会对这个间隙上锁，这就是所谓的间隙锁

比如，在表 InnoDBLock 中，是没有 a = 2 这条记录的

此时，在会话 1 中更新 (1, 6) 的数据

update innodblock set b="3456" where a<=6 and a>=1;

然后在会话 2 中，插入一条 a=2 的记录

insert into innodblock values(2, "333");

结果发现阻塞了

缺点：因为 SQL 执行过程中如果通过范围查找的话，会锁定整个范围内所有的索引键值，即使这个键值并不存在。这会造成无法对索引范围内的所有数据进行增删改等操作

行锁优化建议

1. 尽可能让所有数据检索都通过索引来完成，避免无索引导致行锁升级为表锁
2. 合理设计索引，尽量缩小锁的范围
3. 尽可能减少检索条件 ( 范围条件 )，避免间隙锁
4. 尽可能控制事务大小，减少锁定资源和实践开销
5. 尽可能低级别的事务隔离