MySQL - 深入理解锁机制和实战场景

本文涉及的产品
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: MySQL - 深入理解锁机制和实战场景

MySQL 为了解决事务并发、数据安全的问题,提供并使用了锁机制,下面先看一张图,了解一下 MySQL 有哪些锁:

接下来,一起深入学习 MySQL 的锁机制。

1. 锁的分类

1.1 行级锁

行级锁应用在 InnoDB 存储引擎中,每次锁住一行数据。

  • 优点:锁定粒度小,发生锁冲突的概率最低,并发度最高。
  • 缺点:开销大,加锁慢;会出现死锁。

1.2 页级锁

页级锁应用在 DBD 存储引擎中,每次锁住一页数据 - 16KB左右。

  • 特点:开销和加锁时间介于表级和行级之间;会出现死锁,锁定力度介于表锁和行锁之间,并发度一般。

1.3 表级锁

表级锁应用在 MyISAM、InnoDB、BDB 等存储引擎中,每次操作锁住整张表。

  • 优点:开销小,加锁快;不会出现死锁。
  • 缺点:锁定粒度大,发生锁冲突的概率最高,并发度最低。

2. 行级锁的分类

接下来进行行级锁的详解,行级锁主要分为以下7类:共享/排他锁、意向锁、记录锁、间隙锁、临建锁、插入意向锁、自增锁。

2.1 共享锁、排他锁

共享锁(S锁):共享锁(Share Locks,简记为S锁)又被称为读锁,其他事务可以并发读取数据,但任何事务都不能获取数据上的排他锁(只能加共享锁,不能加排他锁),直到已释放所有共享锁。

若事务 T 对数据对象 A 加上 S锁,则事务 T 只能读 A;其他事务只能再对 A 加 S锁,而不能加 X锁,直到T释放A上的S锁。这就保证了其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改。

select ... lock in share mode;

排他锁(X锁):排它锁((Exclusive lock,简记为X锁))又称为写锁,不能允许读,也不能允许写,排他锁不能与其他锁一起使用。

若事务T对数据对象 A 加上 X锁,则只允许 T 读取和修改 A,其它任何事务都不能再对 A 加任何类型的锁,直到 T 释放 A 上的锁。它防止任何其它事务获取资源上的锁,直到在事务的末尾将资源上的原始锁释放为止。在更新操作(INSERT、UPDATE 或 DELETE)过程中始终应用排它锁。

select ... for update;

MySQL 中,updatedeleteinsertalter 这些写的操作默认都会加上排他锁。select 默认不会加任何锁类型。一旦写数据的任务没有完成,数据是不能被其他任务读取的,这对并发操作有较大的影响。

2.2 意向锁

InnoDB 为了支持多粒度的锁,即允许行级锁和表级锁共存,而引入意向锁。意向锁是指未来的某个时刻,事务可能要加共享/排他锁,先提前声明一个意向。这样如果有人尝试对全表进行修改,就不需要判断表中的数据是否被加锁了,只需要通过等待意向互斥锁被释放就行了。

意向共享锁(IS):事务想要在获得表中某些记录的共享锁,需要在表上先加意向共享锁。

意向互斥锁(IX):事务想要在获得表中某些记录的互斥锁,需要在表上先加意向互斥锁。

意向锁其实不会阻塞全表扫描之外的任何请求,它们的主要目的是为了表示是否有人请求锁定表中的某一行数据。

2.3 记录锁(RS)

单个行记录上的锁。记录锁总是会锁住索引记录,如果 InnoDB 存储引擎表

在建立的时候没有设置任何一个索引,那么InnoDB存储引擎会使用隐式的主键来进行锁定。

2.4 间隙锁(GR)

间隙锁锁住记录中的间隔,即范围查询的记录。

select * From user where id between 1 and 10 for update;

这个脚本会锁住 1 到 10 的数据,以防止其他事务修改该区间的记录;

间隙锁的主要目的,就是为了防止其他事务在间隔中插入数据,以导致“不可重复读”。如果把事务的隔离级别降级为读提交(Read Committed, RC),间隙锁则会自动失效。

2.5 临建锁(next-key Locks)

临建锁是记录锁和间隙锁的组合,锁的范围既包含记录又包含索引区间。默认情况下,InnoDB使用临建锁来锁定记录。但当查询的索引含有唯一属性的时候,临建锁会进行优化,将其降级为记录锁,即仅锁住索引本身,不是范围。

临键锁的主要目的,也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为 RC,临键锁则也会失效。

2.6 插入意向锁(insert intention locks)

对已有数据行的修改和删除,必须加互斥锁,对于数据的插入,加插入意向锁。是专门针对于 insert 操作的。

2.7 自增锁(auto-inc locks)

是一种特殊的表级别的锁,专门针对事务插入 auto-increment 类型的列。最简单的情况,如果一个事务正在往表中插入记录,所有其他事务的插入必须等待,以便第一个事务插入的行,是连续的主键值。

3. 悲观锁和乐观锁

3.1 悲观锁

悲观的假定大概率会发生并发更新冲突,访问、处理数据前就加排他锁,在整个数据处理过程中锁定数据,事务提交或回滚后才释放锁。

  • 优点:

悲观并发控制实际上是“先取锁再访问”的保守策略,为数据处理的安全提供了保证。

  • 缺点:

(1)在效率方面,处理加锁的机制会让数据库产生额外的开销,还有增加产生死锁的机会;

(2)在只读型事务处理中由于不会产生冲突,也没必要使用锁,这样做只能增加系统负载;还有会降低了并行性,一个事务如果锁定了某行数据,其他事务就必须等待该事务处理完才可以处理那行数

案例:

  1. 假定 MySQL 有客户端A 与客户端B 同时开启事务
  2. 在 A 里面进行查询数据:select * from tab where id = 1 for update;
  3. 由于 B端 还没有提交事务,A端 则会在执行这条 SQL 语句时锁住(卡住不动),B端 结束了事务,A端 才会继续执行 SQL。

3.2 乐观锁

乐观的假定大概率不会发生并发更新冲突,访问、处理数据的过程中不加锁,只在更新数据时根据版本号时间戳判断是否有冲突,有则处理,无责提交事务。

如果系统并发量非常大,悲观锁会带来非常大的性能问题,选择使用乐观锁,现在大部分应用属于乐观锁

下单案例:

  1. 查询商品信息
select (quantity,version) from products where id=1;
  1. 根据商品信息生成订单
insert into orders ... 
insert into items ...
  1. 修改商品库存
update products set quantity = quantity - 1,version = version +  1 where id = 1 and version = #{version};

除了自己手动实现乐观锁之外,许多数据库访问框架也封装了乐观锁的实现,比如 hibernate 框架。MyBatis 框架大家可以使用 OptimisticLocker 插件来扩展。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
26天前
|
SQL 关系型数据库 MySQL
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(8.0版本升级篇)
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(8.0版本升级篇)
95 0
|
2天前
|
关系型数据库 MySQL 中间件
【MySQL实战笔记】07 | 行锁功过:怎么减少行锁对性能的影响?-02 死锁和死锁检测
【4月更文挑战第19天】在高并发环境下,死锁发生在多个线程间循环等待资源时,导致无限期等待。MySQL中,死锁可通过`innodb_lock_wait_timeout`参数设置超时或`innodb_deadlock_detect`开启死锁检测来解决。默认的50s超时可能不适用于在线服务,而频繁检测会消耗大量CPU。应对热点行更新引发的性能问题,可以暂时关闭死锁检测(风险是产生大量超时),控制并发度,或通过分散记录减少锁冲突,例如将数据分拆到多行以降低死锁概率。
17 1
|
5天前
|
SQL 关系型数据库 MySQL
Python与MySQL数据库交互:面试实战
【4月更文挑战第16天】本文介绍了Python与MySQL交互的面试重点,包括使用`mysql-connector-python`或`pymysql`连接数据库、执行SQL查询、异常处理、防止SQL注入、事务管理和ORM框架。易错点包括忘记关闭连接、忽视异常处理、硬编码SQL、忽略事务及过度依赖低效查询。通过理解这些问题和提供策略,可提升面试表现。
25 6
|
5天前
|
关系型数据库 MySQL 索引
MySQL 锁机制
MySQL 锁机制
7 0
|
12天前
|
存储 关系型数据库 MySQL
【MySQL实战笔记】 04 | 深入浅出索引(上)-02
【4月更文挑战第9天】InnoDB数据库使用B+树作为索引模型,其中主键索引的叶子节点存储完整行数据,非主键索引则存储主键值。主键查询只需搜索一棵树,而非主键查询需两次搜索,因此推荐使用主键查询以提高效率。在插入新值时,B+树需要维护有序性,可能导致数据页分裂影响性能。自增主键在插入时可避免数据挪动和页分裂,且占用存储空间小,通常更为理想。然而,如果场景仅需唯一索引,可直接设为主键以减少查询步骤。
13 1
【MySQL实战笔记】 04 | 深入浅出索引(上)-02
|
13天前
|
关系型数据库 MySQL 数据库
MySQL锁三部曲:临键、间隙与记录的奇妙旅程
MySQL锁三部曲:临键、间隙与记录的奇妙旅程
18 0
|
14天前
|
存储 SQL 关系型数据库
【MySQL实战笔记】03.事务隔离:为什么你改了我还看不见?-02
【4月更文挑战第7天】数据库通过视图实现事务隔离,不同隔离级别如读未提交、读已提交、可重复读和串行化采用不同策略。以可重复读为例,MySQL使用多版本并发控制(MVCC),每个事务有其独立的视图。回滚日志在无更早视图时被删除。长事务可能导致大量存储占用,应避免。事务启动可显式用`begin`或设置`autocommit=0`,但后者可能意外开启长事务。建议使用`autocommit=1`并显式管理事务,若需减少交互,可使用`commit work and chain`。
29 5
|
16天前
|
SQL 存储 关系型数据库
【MySQL实战笔记】02.一条SQL更新语句是如何执行的-2
【4月更文挑战第5天】两阶段提交是为确保`redo log`和`binlog`逻辑一致,避免数据不一致。若先写`redo log`, crash后数据可能丢失,导致恢复后状态错误;若先写`binlog`,crash则可能导致重复事务,影响数据库一致性。一天一备相较于一周一备,能缩短“最长恢复时间”,但需权衡额外的存储成本。
16 1
|
26天前
|
SQL 关系型数据库 MySQL
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(数据恢复补充篇)(一)
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(数据恢复补充篇)
30 0
|
1月前
|
存储 Kubernetes 关系型数据库
KubeSphere 核心实战之一【在kubesphere平台上部署mysql】(实操篇 1/4)
KubeSphere 核心实战之一【在kubesphere平台上部署mysql】(实操篇 1/4)
44 0