mysql中的锁

记录下mysql中的各种锁

这些锁的效果的验证要在linux上安装mysql进行测试,如果在本地windows上装mysql有可能不会出现锁的互斥效果。

本文使用的mysql版本是5.6

一、全局锁

全局锁锁的是整个数据库实例，加上全局锁后整个数据库实例下的所有数据库中的所有表都只能进行查询，

包括当前这个加锁的会话也只能查询不能修改。

当前会话中进行加锁操作

--加锁
FLUSH TABLES WITH READ LOCK;

其他会话+当前会话都不能进行修改数据库的操作，注意不止当前数据库，是整个数据库实例下所有的数据库都被锁住了。

-- 解锁
UNLOCK TABLES;

这个语句经常被用来在备份数据库期间对数据库进行锁定不让修改数据。

mysql提供了一个mysqldump 命令可以用来备份整个数据库。

二、表级锁

每次操作锁住整张表。

2.1 表锁

2.1.1 表共享读锁

简称读锁

--给表加读锁
LOCK TABLES t_user read;
-- 释放锁
unlock tables;

客户端1给表加上读锁后，客户端1和其他客户端都可以进行读操作但是都不能进行写操作,进行写操作会报错。

当客户端1释放锁后所有的客户端才能进行写操作。

2.1.2 表独占写锁

简称写锁

客户端1给一个表加写锁以后，其余客户端不能读也不能写,当前客户端可以进行读或者写操作.

--给表加写锁
LOCK TABLES t_user write;
-- 释放锁
unlock tables;

2.2 元数据锁(MDL)

元数据指的就是一张表的表结构，MDL加锁过程是mysql自动控制的，在一张表上有未提交的事务时，不允许对表结构进行修改。

当对一张表进行增删改查时会给元数据加MDL读锁(共享),当对表结构进行修改时加MDL写锁(排他)

先开启事务,对一张表进行增删改查时会给元数据加MDL读锁(共享),

这时其他客户端再对表结构进行修改时会被阻塞住直到事务提交,因为修改表结构语句要给这张表加写锁就要等待读锁释放。

2.3 意向锁

为了避免DML在执行时，加的行锁与表锁的冲突，在innodb中引入了意向锁，使得表锁不用检查表的每一行数据是否加了行锁，只需要检查表上的意向锁是否和要加的行锁兼容。

加锁流程:

客户端1开启事务更新某行数据，给这行数据加了行锁，同时给整张表表意向锁;

客户端2要给整张表加表锁,它会去判断表上有没有意向锁，意向锁的类型和表锁是否兼容，如果不兼容就会进入等待状态。

意向锁的类型:

意向共享锁：由语句select... lock in share model添加

意向排他锁: 由insert、update、delete、select... for update添加

意向锁和表锁的兼容性:

意向共享锁与表锁 read兼容，与表锁排他互斥

意向排他锁与表锁read,write都互斥，

意向锁之间不会互斥。

三、行级锁

每次操作锁住对应的数据行，应用在innodb引擎中。

innodb的数据是基于索引存贮的，行锁是通过对索引上的索引项加锁实现，而不是对记录加锁。

3.1 行锁

锁定单个行记录的锁，防止其他事务对此行数据进行修改操作，在RC,RR隔离级别下都支持

共享锁和共享锁之间是兼容的，共享锁和排他锁是互斥的,排他锁和排他锁是互斥的

3.1.1 共享锁

允许一个事务去读一行数据，阻止其他事务给这一行加排他锁，但不阻止其他事务加共享锁

3.1.2 排他锁

允许持有排他锁的事务更新当前行，阻止其他事务给当前行加共享锁和排他锁

3.1.3 各sql语句的加锁情况

(1) 针对唯一索引进行检索时，对已存在的记录进行等值匹配会优化为加行锁

(2)innodb的行锁是针对索引加的锁，不通过索引条件检索数据，将对表中所有记录都加锁此时就会升级为表锁

3.1.4 行锁的测试

(一定要手动开启事务，因为自动事务的情况下事务提交后行锁就被释放了看不到效果)

创建一张带索引的表(直接使用主键测试)

CREATE TABLE t_user(
id INT PRIMARY key,
NAME VARCHAR(50)
);

客户端1

-- 开启事务
begin;
-- 加共享锁
SELECT * FROM t_user WHERE id=1 LOCK IN SHARE MODE;

然后在客户端2中开启事务，获取共享锁

-- 开启事务
begin;
-- 加共享锁
SELECT * FROM t_user WHERE id=1 LOCK IN SHARE MODE; --可以正常执行

可以正常执行，说明共享锁不互斥。

接着在客户端2中获取这行数据的排他锁

select * from t_user where id=1 for update;

客户端2就会进入阻塞状态。但是如果在客户端2中获取的是其他行如id=2的排他锁则不会阻塞。

当然在客户端2中执行update等语句也会被阻塞直到客户端1的事务提交。

但需要注意的是在执行获取锁的sql前一定要手动开启事务，否则因为事务被自动提交了所以相当于把锁又给释放了。

在默认的事务隔离级别下，普通的查询语句(不获取锁的查询)并不会因为别的事务中有行锁而被阻塞，所以

这种获取锁的查询一般都是和更新配套使用的，当别的事务在进行更新操作时让当前这种获取锁的查询阻塞住以保证获取到的是最新的数据。

如果不是根据索引去查询或者更新,mysql会升级成表锁，

客户端1

-- 开启事务
begin;
-- 加锁查询
select * from t_user where name='bb' for update;

这样实际上给表加了锁，客户端2中用下边的语句去查询也会进入阻塞状态。

--开启事务
begin;
-- 注意id=1的这条记录name不是bb
select * from t_user where id='1' for update;

3.2 间隙锁

锁定索引记录间隙，也就是B+树叶子节点的id之间的间隙，例如id6和12之间还间隔了6个间隙锁保证了其他事务不能在这个间隙内插入数据，可以防止幻读。在RR隔离级别下支持

间隙锁的目录是阻止其他事务在间隙插入数据，间隙锁之间不互斥。

假设数据库中有以下数据3条数据

有以下几种情况:

(1) 索引上的等值查询，给不存在的记录加锁时，优化为间隙锁

 update t_user  set name='h' where id=4;update t_user  set name='h' where id=4;

这条sql执行是因为表里没有id=4的记录，所以会2和5之间的间隙用间隙锁锁住。其他客户端执行如下插入sql会进入阻塞状态

INSERT INTO t_user(id,NAME) VALUES(4,'kk');

3.3 临键锁

行锁和间隙锁的组合，同时锁住数据和数据前的叶子节点id间隙,在RR隔离级别下支持

间隙锁和临界锁是用来处理幻读问题的。