MySQL锁

原创于 2023-11-19 14:53:50 发布 · 607 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #锁

本文详细介绍了数据库中的锁机制，包括全局锁、表级锁（读写锁）、行级锁以及InnoDB的意向锁，阐述了它们在并发控制中的作用，以及如何影响数据库性能和一致性。特别强调了元数据锁和幻读在事务隔离级别中的重要性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

概述

介绍

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

全局锁

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

使用场景

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

不加锁

会出现数据不一致问题

加锁

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

操作

加锁

flush tables with read lock;

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

mysqldump -uroot -p1234 xxx>xx.sql

释放全局锁

unlock tables;

特点

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

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

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

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

表级锁

介绍

表级锁，每次操作锁住整张表，锁粒度大，发生冲突概率高，并发度低。
应用在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开启了事务
客户端1执行查询操作：

begin;
SELECT * FROM payment;

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

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

元数据锁就是为了避免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;

举例：

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

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

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

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

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

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

举例2:
客户端1:

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

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

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

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

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

行级锁

行级锁，每次操作锁住对应的行数据。锁粒度最小，发生锁冲突的概率最低，并发度最高，应用在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:

-- 开启事务
begin;

-- 查询
SELECT * FROM payment WHERE id = 1;

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

客户端2:

-- 开启事务
begin;

-- 查询
SELECT * FROM payment WHERE id = 1;

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

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

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

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

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

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

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

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

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

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

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

客户端2:

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

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

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

客户端2:

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

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

create index idx_payment_serial on payment(serial);

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

间隙锁/临键锁

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

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

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

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

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

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

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

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

索引上的等值查询（普通索引）,向右遍历时最后一个值不满足查询需求时，临键锁退化为间隙锁。
索引上的范围查询（唯一索引）–会访问到不满足条件的第一个值为止。

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

解释：
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之后到正无穷大的临键锁。