MySQL锁

概述

介绍

锁是计算机协调多个进程或线程并发访问某一资源的机制，在数据库中，除传统的计算资源（CPU、IO）的争用除外，数据也是一种供许多用户共享的资源。保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也会影响数据库并发访问性能的重要因素。从这个角度来说，锁对数据库而言，尤为重要，也更加复杂。

分类

MySQL中的锁分类，按照锁粒度分，分以下三类：

• 全局锁：锁定数据库中的所有表
• 表级锁：每次操作锁整张表
• 行级锁：每次操作锁住对应的行数据

全局锁

全局锁就是对整个数据库实例加锁，锁的是所有的库和表，加锁之后就是处于只读状态，只能读不能写，DML语句都会处于阻塞状态。

使用场景

典型的使用场景就是做全库备份，对所有的表进行锁定，保证一致性视图，保证数据完整性。
为什么做数据库备份就需要加全局锁，不加行不行？

不加锁

会出现数据不一致问题

加锁

从开始备份到备份结束，其他客户端只允许读数据，不允许写数据，从而保证备份数据的一致性。

操作

加锁

flush tables with read lock;
1

备份（下面这个不是SQL语句，可以直接在windows的终端执行）

mysqldump -uroot -p1234 xxx>xx.sql
1

释放全局锁

unlock tables;
1

特点

数据库加全局锁，是一个比较重的操作：

• 如果在主库备份，备份期间都不能执行更新操作，业务基本就得停摆。
• 如果在从库备份，备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟

在InnoDB引擎中，我们可以在备份时加上参数--single-transaction参数来完成不加锁的一致性数据备份，底层其实是通过快照读来实现的。

mysqldump --single-transaction -uroot -p1234 xxx>xx.sql
1

表级锁

介绍

表级锁，每次操作锁住整张表，锁粒度大，发生冲突概率高，并发度低。
应用在MyISAM、InnoDB、BDB存储引擎中。

分类

表锁

锁的就是整张表

表共享读锁（read lock）：简称读锁

举例：
1、客户端1加表读锁；
2、客户端1执行DQL，正常执行；
3、客户端2执行DQL正常执行；
4、客户端1执行DDL/DML操作，直接报错；
5、客户端2执行DDL/DML操作，阻塞；等到表读锁被释放会结束阻塞；
6、客户端1释放读锁，客户端2结束阻塞，执行语句。

客户端1和客户端2可以同时持有表读锁，写操作会被阻塞，直到两个事务释放了读锁。当客户端1释放读锁后，其他写操作才有可能执行，但如果客户端2仍然持有读锁，其他写操作仍然会被阻塞，直到客户端2也释放了读锁。
表级读锁可以多个客户端同时持有，只要有一把读锁，就会阻塞写操作，直到所有的读锁被释放。

表独占写锁（write lock）：简称写锁

举例：
1、客户端1加表写锁；
2、客户端1能执行读、写，正常执行；
3、客户端2既不能执行读也不能执行写；
4、客户端1释放读锁，客户端2结束阻塞，执行语句。

客户端1添加了表级写锁，其他客户端既不能读也不能写，但是客户端1既能读又能写。
读锁不会阻塞其他客户端的读，但是会阻塞写。
写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。
表级别的写锁（排他锁）是互斥的，即一次只能有一个事务持有写锁。如果客户端1已经持有了表的写锁，那么客户端2在这个时候尝试加表写锁会被阻塞，直到客户端1释放了写锁。
写锁是排他的，它会阻止其他事务同时获取写锁。这确保了在任何给定时刻只有一个事务可以对表执行写操作，以保证数据的一致性。
因此，在有一个事务持有表写锁的情况下，其他事务想要加表写锁会被阻塞，直到当前事务释放写锁。

语法

1、加锁：lock table 表名 read/write
2、释放锁：unlock tables 或者客户端断开连接

元数据锁（meta data lock，MDL）

MDL加锁过程是系统自动控制的，不需要我们通过lock这样的关键字去显示加锁，当我们去访问一张表的时候，MDL元数据锁会自动加。
MDL锁住要作用是维护表元数据的数据一致性，当表中还存在未提交的事务的时候，此时不能修改表结构。为了避免DML和DDL的冲突，保证读写的正确性。
在MySQL5.5之后引入了MDL，当对一张表进行增删改查的时候，加MDL读锁（共享）；当对表结构进行变更操作的时候，加MDL写锁（排他）；
读锁与读锁之间是兼容的，写锁和写锁之间是互斥的。写锁和读锁之间也是互斥的。
应用场景

1、SQL加表锁或写锁的时候，会加对应的元数据锁；
2、执行select、select… lock in share mode时会自动加元数据锁的读锁，
3、执行insert、update、delete、select… for update时，会自动加共享写锁，其实SHARED_WRITE也是MDL读锁
4、alter table…修改表结构 ，加的是排他锁。EXCLUSIVE与其他所有的MDL都是互斥的。

在事务中执行select和insert、update、delete操作时都会加元数据锁。
这两类的元数据锁都是共享锁，共享读锁，共享写锁。共享锁之间是兼容的，无论怎么操作都行。
举例说明：
举例1:
1、客户端1开启一个事务；
2、客户端1执行查询操作（共享读锁），但是并未提交事务；
3、客户端2开启一个事务；
4、客户端2执行查询（共享读锁）或修改操作（共享写锁），可以执行；
5、两个事务提交，这两个事务内加的都是共享锁，互不影响。

SHARED_WRITE也是MDL读锁：
共享读锁（SHARED_READ)与SHARED_READ和SHARED_WRITE都是兼容的；
共享写锁（SHARED_WRITE)与SHARED_READ和SHARED_WRITE都是兼容的；

举例2:
1、客户端1开启一个事务；
2、客户端1执行查询操作（共享读锁），但是并未提交事务；
3、客户端2开启一个事务；
4、客户端2要修改表结构，执行alter table语句（EXCLUSIVE），此时会出现阻塞，直到客户端1提交了事务；
5、客户端1提交事务，客户端2结束阻塞，执行SQL语句。
问：怎么知道是元数据锁？

select object_type,object_schema,object_name,lock_type,lock_duration 
from performance_schema.metadata_locks;
1
2

测试：
查询元数据锁；
客户端1开启了事务
客户端1执行查询操作：

begin;
SELECT * FROM payment;
1
2

客户端2开启事务并执行修改操作：

begin;
update payment set serial = 'xxx' WHERE id = 1;
1
2

元数据锁就是为了避免DML和DDL冲突，保证读写的正确性。

意向锁

为了避免DML只执行时，加的行锁和表锁的冲突，在InnoDB中引入意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表的检查。

没加意向锁之前

线程A：开启了一个事务，执行update，默认的MySQL事务隔离级别，会添加行锁。
线程B：要lock table，加表锁，此时行锁和表锁就会冲突，线程B要加锁的话，会检查表中每行数据是否有行锁，性能很差。

加意向锁之后

线程A：加对应数据的行锁，以及加表的意向锁；
线程B：加表锁的时候，检查表中意向锁的情况，要加的表锁和意向锁是兼容的，那么就加锁成功，否则阻塞。阻塞到A线程，提交事务，释放行锁和意向锁之后。

意向锁分类

意向共享锁（IS）

• 由语句select…lock in share mode添加。
• 意向共享锁和表锁共享锁（read）是兼容的，与表锁排他锁（write）互斥。

意向排他锁（IX）

• 由语句insert、update、delete、select…for update添加。
• 意向共享锁和表锁共享锁（read）表锁和排他锁（write）都互斥，意向锁之间是不会互斥的。

通过以下SQL查看意向锁和行锁的情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data 
from performance_schema.data_locks;
1
2

举例：

begin;
-- 意向锁维度：这条SQL语句加的是这条数据的行锁以及这张表的意向共享锁。
select * from payment where id = 1 lock in share mode;
1
2
3

begin;
-- 意向锁维度：这条语句并不会加任何锁；
select * from payment where id = 1;
1
2
3

查看意向锁和行锁的情况：

此时，客户端2对这张表加表锁：

-- 加表读锁，成功：因为意向共享锁和表读锁是兼容的
lock tables payment read;

-- 加表写锁，阻塞：因为意向共享锁和表写锁是互斥的
lock tables payment write;
1
2
3
4
5

举例2:
客户端1:

begin;
-- 意向锁维度：这条SQL语句加的是这条数据的行锁以及这张表的意向排他锁。
update payment set serial = 'xxx' where id = 1;
1
2
3

这个行锁也是行锁排他锁；表锁意向排他锁。

此时，客户端2对这张表加表锁：

-- 加表读锁，阻塞：因为意向排他锁和表读锁是互斥的
lock tables payment read;

-- 加表写锁，阻塞：因为意向排他锁和表写锁是互斥的
lock tables payment write;
1
2
3
4
5

行级锁

行级锁，每次操作锁住对应的行数据。锁粒度最小，发生锁冲突的概率最低，并发度最高，应用在InnoDB引擎中。InnoDB支持行级锁，MyISM不支持行级锁；
MySQL中的MyISM与InnoDB存储引擎当中，有三大区别： 事务、外键、行级锁。
InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加锁。也就是说，行锁锁的是索引而不是数据。
对于行级锁，主要分为以下三类：

• 行锁：锁单个行记录的锁，防止其他事务对这行数据进行delete或者update。在RC、RR都支持；

• 间隙锁：间隙锁，只锁间隙，不包含该记录。确保索引记录记录间隙不变，防止其他事务在这个间隙进行insert防止幻堵问题。在RR隔离级别下都支持。

• 临键锁：行锁和间隙锁的组合，同时锁住数据，并锁住数据前面的间隙。在RR隔离级别下支持。

分类

InnoDB引擎下实现了以下两种类型行锁：

• 共享锁（S）：共享锁和共享锁之间是兼容的，但是共享锁和排他锁之间是互斥的；
• 排他锁（X）：获取到这条数据排他锁的事务可以执行更新，其他事务就不能再获取到这行数据的共享锁和排他锁。

常见的增删改查的SQL加的都是什么锁？

影认情况下，InnoDB在RR事务隔离级别运行，Innodb使用next-key锁进行搜索和察引扫描，以防止幻读。
1、针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
2、Innodb的行锁是针对于索引加的貌，不通过索引条件检索数据，那么innodb将对表中的所有记录加锁，此时就会开级为表锁。
可以通过以下SQL，查看意向锁及行锁的情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data 
from performance_schema.data_locks;
1
2

演示：
验证共享锁与共享锁：
客户端1:

-- 开启事务
begin;

-- 查询
SELECT * FROM payment WHERE id = 1;
1
2
3
4
5

此时的没有任何的行锁，查看行锁情况：无任何锁（只针对行级锁）

客户端2:

-- 开启事务
begin;

-- 查询
SELECT * FROM payment WHERE id = 1;
1
2
3
4
5

此时客户端2仍然可以查到这条数据。
如果想对这条SQL语句加一把锁

SELECT * FROM payment WHERE id = 1 lock in share mode;
1

再次查询意向锁及行锁情况：

此时会有一把行锁，S：共享锁；X：排他锁。
S,REC_NOT_GAP：表示的是行锁共享锁；没有间隙
其他客户端也执行查询：

此时会有两把共享锁，共享锁与共享锁之间是相互兼容的，所以，这条语句依旧可以执行成功。
提交事务1后，共享锁还剩一把。

验证共享锁与排他锁：
客户端2:

update payment set serial = 'xxx' where id = 3;
1

此时，持有的是id为3的这行数据的行锁排他锁，所以可以正常执行，因为另一个事务持有的是id为1的是数据的行锁共享锁，并不是同一把行锁。

update payment set serial = 'xxx' where id = 1;
1

此时，这条语句会被阻塞，因为id为1的这行数据的行锁共享锁被另一个事务持有，需要等另一个事务提交事务之后才可以。
说明：共享锁与排他锁之间是互斥的；
验证：排他锁与排他锁之间
客户端1:

update payment set serial = 'xxx' where id = 1;
1

客户端2:

update payment set serial = 'xxx' where id = 1;
1

此时客户端2的语句会被阻塞，因为事务1持有id为1的行数据的行锁排他锁。
等待事务1释放排他锁后，事务2就可以获取到这把行数据的排他锁。
说明：行锁的排他锁与排他锁之间是互斥的；
验证：
Innodb的行锁是针对于索引加的貌，不通过索引条件检索数据，那么innodb将对表中的所有记录加锁，此时就会开级为表锁。
客户端1:

begin;
update payment set serial = 'xxx' where serial = 'yyy';
1
2

客户端2:

begin;
update payment set serial = 'xxx' where id = 2;
1
2

此时，客户端2会处于阻塞状态。
因为，事务1并不是根据索引去做数据更新，此时锁的是表，事务2不能获取到行锁，处于阻塞状态。
等待事务1提交后，事务2就可以获取到行锁。
我们可以针对更新的serial字段建立一个索引。

create index idx_payment_serial on payment(serial);
1

此时，再去更新就不会锁整张表，而是加行锁。

间隙锁/临键锁

默认情况下，InnoDB在RR事务隔离级别运行，InnoDB使用next-key 锁进行搜索和察引扫描，以防止幻读。

• 索引上的等值查询（唯一素引）,给不存在的记录加锁时，优化为间隙锁。

验证：
当前表中的数据为：1、2、3、4、8。
此时，我们更新数据为6的数据；

update payment set serial = 'xxx' where id = 6;
1

但是，此时6这行数据并不存在，此时会对4-8之间的间隙加锁，锁的是4-8之间，不包含4和8。

X,GAP：X表示排他锁，GAP表示间隙锁。8表示锁的是8之前的那一段间隙；
此时，如果，我们在这个间隙中插入一行数据，比如插入7，发现无法插入：

insert into payment values (7,'7777');
1

因为中间的间隙被锁了，所以现在间隙中间不能写入数据，为了防止幻读。
当另一个事务提交后，释放掉锁，就会结束阻塞。

• 索引上的等值查询（普通索引）,向右遍历时最后一个值不满足查询需求时，临键锁退化为间隙锁。

• 索引上的范围查询（唯一索引）–会访问到不满足条件的第一个值为止。

唯一索引进行范围查询时，加的是临键锁。

解释：
dbcloud payment PRIMARY RECORD S,REC_NOT_GAP 4
是对4这条记录加一个行锁。
dbcloud payment PRIMARY RECORD S 8
锁的是8之前的那段间隙。
dbcloud payment PRIMARY RECORD S supremum pseudo-record
锁的是8之后到正无穷大的临键锁。

注意：间隙锁唯一目的是防止其他事务插入间隙，间隙锁可以共存，一个事务采用的同隙锁不会阻止另一个事务在同一间隙上采用间隙锁；
间隙锁：锁间隙不包含数据记录。
临键锁：锁数据之前的这部分间隙和当前的数据记录。

小结