MySQL锁

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

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

概述

介绍

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

分类

MySQL中的锁分类,按照锁粒度分,分以下三类:

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

全局锁

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

使用场景

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

不加锁

会出现数据不一致问题
image.png

加锁

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

操作

加锁

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):简称读锁

image.png

举例:
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):简称写锁image.png

举例:
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写锁(排他)
读锁与读锁之间是兼容的,写锁和写锁之间是互斥的。写锁和读锁之间也是互斥的。
应用场景
image.png

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;

测试:
查询元数据锁;image.png
客户端1开启了事务
客户端1执行查询操作:

begin;
SELECT * FROM payment;

image.png
客户端2开启事务并执行修改操作:

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

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

意向锁

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

没加意向锁之前

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

加意向锁之后

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

意向锁分类

意向共享锁(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;

查看意向锁和行锁的情况:
image.png
此时,客户端2对这张表加表锁:

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

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

举例2:
客户端1:

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

这个行锁也是行锁排他锁;表锁意向排他锁。
image.png
此时,客户端2对这张表加表锁:

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

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

行级锁

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

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

image.png

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

image.png

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

image.png

分类

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

  • 共享锁(S):共享锁和共享锁之间是兼容的,但是共享锁和排他锁之间是互斥的;
  • 排他锁(X):获取到这条数据排他锁的事务可以执行更新,其他事务就不能再获取到这行数据的共享锁和排他锁。

image.png
常见的增删改查的SQL加的都是什么锁?
image.png
影认情况下,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;

此时的没有任何的行锁,查看行锁情况:无任何锁(只针对行级锁)
image.png
客户端2:

-- 开启事务
begin;

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

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

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

再次查询意向锁及行锁情况:
image.png
此时会有一把行锁,S:共享锁;X:排他锁
S,REC_NOT_GAP:表示的是行锁共享锁;没有间隙
其他客户端也执行查询:
image.png
此时会有两把共享锁,共享锁与共享锁之间是相互兼容的,所以,这条语句依旧可以执行成功。
提交事务1后,共享锁还剩一把。
image.png
验证共享锁与排他锁:
客户端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;

image.png
但是,此时6这行数据并不存在,此时会对4-8之间的间隙加锁,锁的是4-8之间,不包含4和8。
image.png
X,GAP:X表示排他锁,GAP表示间隙锁。8表示锁的是8之前的那一段间隙;
此时,如果,我们在这个间隙中插入一行数据,比如插入7,发现无法插入:

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

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

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

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

唯一索引进行范围查询时,加的是临键锁。
image.png
解释:
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之后到正无穷大的临键锁。

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

小结

image.png

### MySQL 机制详解 MySQL 中的机制是为了保障数据库在高并发环境下的数据一致性和稳定性而设计的重要功能之一。以下是关于 MySQL 机制的具体解析: #### 1. 的分类 MySQL 主要分为两种类型的:表级和行级。 - **表级 (Table-Level Lock)** 表级是最简单的定策略,适用于 MyISAM 存储引擎。它会在整个表上施加,无论是读操作还是写操作都会影响到整张表。对于 `SELECT` 操作,MyISAM 会自动给涉及的所有表加上读;而对于 `UPDATE`, `INSERT`, 和 `DELETE` 则会自动加上写[^3]。 - **行级 (Row-Level Lock)** 行级由 InnoDB 存储引擎实现,提供更高的并发能力。只有涉及到具体记录的操作才会触发行级。例如,在执行 `SELECT ... FOR UPDATE` 或者 `SELECT ... LOCK IN SHARE MODE` 时,InnoDB 只会对符合条件的特定行加而不是封整个表[^2]。 #### 2. 不同存储引擎的特性 不同的存储引擎有不同的行为: - **MyISAM**: 默认使用的是表级别的,这意味着即使是一个小范围内的更新也会阻塞其他线程对该表任何部分的访问[^3]。 - **InnoDB**: 支持事务以及更细粒度的行级,这使得它可以更好地处理复杂的多用户场景下的并发请求[^4]。 #### 3. 常见类型及其作用 除了基本的表级与行级区分外,还有几种具体的形式用于满足不同需求: - **共享 (Shared Lock, S-Lock)** 当一个客户端通过命令如 `LOCK TABLES table_name READ` 获取了一个表上的共享之后,其它客户也可以获得该表上的共享,但不能再获取排他直到当前持有共享的所有连接释放它们为止[^2]。 - **排他 (Exclusive Lock, X-Lock)** 排他允许独占式的修改权限。如果某个事务已经获得了某条记录或者某些列上的排他,则在此期间不允许别的事务再对此同一组资源申请任何形式的新——既包括另外的排他也包括新的共享[^2]。 - **GAP ** 这种特殊的用来阻止新纪录插入到现有两条连续记录之间的空隙(gap)里去。比如当我们运行下面这条SQL语句的时候就会用到gap lock:`SELECT * FROM table_name WHERE id > 10 FOR UPDATE;` - **Next-Key Lock** 是一种组合了索引记录本身(record)和其前面那个区间(gap)两者一起保护起来的一种复合型模式。主要用于解决可重复读(repeatable read)隔离级别下可能出现的幻影问题(phantom problem)[^4]。 #### 4. 死现象及预防措施 死是指两个或更多事务相互等待对方持有的资源从而进入僵局的状态。为避免这种情况发生可以采取以下方法: - 尽量按照固定的顺序访问资源; - 减少单次事务持续时间; - 设置合理的超时参数让系统能够及时发现并终止潜在的死循环状况等等[^4]。 ```sql -- 示例:如何手动解表 UNLOCK TABLES; ``` --- ###
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值