MySQL 意向共享锁、意向排他锁、死锁

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL 意向共享锁、意向排他锁、死锁

MySQL 意向共享锁、意向排他锁、死锁

终有救赎

2023-11-06600阅读6分钟

关注

专栏持续更新中:MySQL详解

一、InnoDB表级锁

我们知道,InnoDB是支持行锁,但不是每次都获取行锁,如果不使用索引的,那还是获取的表锁。而且有的时候,我们希望直接去使用表锁

在绝大部分情况下都应该使用行锁,因为事务的并发效率比表锁更高,但个别情况下也使用表级锁:

  • 事务需要更新大部分或全部数据,表又比较大,如果使用默认的行锁,给大部分行都加锁(此时不如直接加表锁),不仅这个事务执行效率低,而且可能造成其他事务长时间等待和锁冲突
  • 事务涉及多个表,比较复杂,如果都用行锁,很可能引起死锁,造成大量事务回滚

当我们希望获取表锁时,可以使用以下命令:

LOCK TABLE user READ  -- 获取这张表的读锁
LOCK TABLE user WRITE -- 获取这张表的写锁
事务执行...
COMMIT/ROLLBACK;      -- 事务提交或者回滚
UNLOCK TABLES;        -- 本身自带提交事务,释放线程占用的所有表锁

在使用表锁的时候,涉及到效率的问题:如果我们要获取一张表的排它锁X,最起码得确定,这张表没有被其他事务获取过S锁或X锁,以及这张表没有任何行被其他事务获取过行S或X锁

假如这张表有1000万个数据,那我怎么知道这1000万行哪些有行锁哪些没有行锁呢?

除了挨个检查,没有更好的办法,这就导致效率低下的问题

我们这里学习的意向共享锁和意向排他锁就是用来解决,由于需要加表锁而去挨个遍历数据,确定是否有某些数据被加了行锁,而导致的效率低下问题。作用就是快速判断表里是否有记录被加锁

二、意向共享锁和意向排他锁(表锁而非行锁)

意向锁的作用:为了可以更快速的获取表锁

意向共享锁(IS锁):事务在给一行记录加共享锁前,必须先取得该表的IS锁

意向排他锁(IX锁):事务在给一行记录加排他锁前,必须先取得该表的IX锁

(上面表格所有的锁都是针对整表)

  1. 在加行锁之前,由InnoDB存储引擎自动加上表的IS或IX锁,我们无法手动获取IS或IX锁
  2. 意向锁之间都兼容,不会产生冲突
  3. 意向锁存在的意义是为了更高效的获取表锁(表格中的X、S、IX、IS指的是表锁,不是行锁)
  4. 意向锁是表级锁,协调表锁和行锁的共存关系,主要目的是显示事务正在锁定某行或者试图锁定某行。

分析事务1获取行X锁和事务2获取表S锁

首先事务1需要给表的第10行数据加X锁,于是InnoDB存储引擎自动给整张表加上了IX锁。当事务2再想获取整张表的S锁时,看到这张表已经有别的事务获取了IX锁了,就说明这张表肯定有某些数据被加上了X锁,这就导致事务2不能给整张表加S锁了。此时事务2只能等待,无法成功获取表S锁

当某个事务要获取表的X锁时,不需要再检查哪些行被加上了X或S锁,只需要检查整表是否被加上IX或IS锁即可

三、死锁

1. 数据库中的死锁

MyISAM 表锁是 deadlock free 的, 这是因为 MyISAM 不支持事务,只支持表锁,而且总是一次获得所需的全部锁,要么全部满足,要么等待,因此不会出现死锁。如果是处理多张表,还是可能出现死锁问题的

image.png

在 InnoDB 中,除单个 SQL 组成的事务外,锁是逐步获得的,即锁的粒度比较小(行锁),这就决定了在 InnoDB 中发生死锁是可能的

死锁问题一般都是我们自己的应用造成的,和多线程编程的死锁情况相似,大部分都是由于我们多个线程在获取多个锁资源的时候,获取的顺序不同而导致的死锁问题。因此我们应用在对数据库的多个表做更新的时候,不同的代码段,应对这些表按相同的顺序进行更新操作,以防止锁冲突导致死锁问题

2. 死锁场景以及解决办法

死锁出现的场景如下:

事务1成功获取行锁1

事务2成功获取行锁2

事务1无法获取行锁2,被阻塞的同时也无法释放行锁1

事务2无法获取行锁1,被阻塞的同时也无法释放行锁2

image.png

此时所有的事务都阻塞住了,相当于进程内的所有线程都阻塞住了,发生了死锁问题

解决死锁办法:多个事务/线程获取多个相同资源锁的时候应该按照同样的顺序获取锁。与此同时,由于mysqld(MySQL Server守护进程)设置了事务阻塞的超时时间,事务不会阻塞很长时间,超时后事务处理失败,自动释放当前占有的锁

3. 操作

设置自动提交 以及 可重复读隔离级别,开启事务

image.png

查询一下表数据,在可重复读隔离级别使用的是MVCC提供的快照读,并没有加锁

image.png

事务1获取id=7的排他锁,事务2获取id=8的排他锁

image.png

事务1再次获取id=8的排他锁,发生阻塞

image.png

事务2再次获取id=7的排他锁

image.png

此时由于MySQL Server检测到发生了死锁,于是解除事务1的阻塞,进行事务1的rollback,释放其占有的行锁,于是事务2成功获取id=7的排他锁

两个事务发生死锁时,MySQL Server会选择一个事务释放锁并进行rollback

四、锁的优化建议

  • 在能正确完成业务的前提下,为确保效率,尽量使用较低的隔离级别(必须避免脏读)
  • 设计合理的索引并尽量使用索引访问数据,使加锁更准确,减少锁冲突的机会,提高并发能力
  • 选择合理的事务大小,小事务发生锁冲突的概率小(事务越大,包含的SQL越多,可能包含更多的表资源和行资源的锁,增大了锁冲突的概率)
  • 不同的程序访问一组表时,应尽量约定以相同的顺序访问各表,对一个表而言,尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会
  • 尽量用相等条件访问数据,这样可以避免间隙锁对并发插入的影响(其实等值查询也会加间隙锁)
  • 不要申请超过实际需要的锁级别
  • 除非必须,查询时不要显示加锁(在已提交读和可重复读隔离级别,MVCC提供了读取机制,不需要手动加锁)
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
SQL 关系型数据库 MySQL
MySQL 锁
MySQL里常见的几种锁
53 3
|
2月前
|
SQL 关系型数据库 MySQL
MySQL死锁及源码分析!
MySQL死锁及源码分析!
MySQL死锁及源码分析!
|
2月前
|
SQL 关系型数据库 MySQL
案例剖析,MySQL共享锁引发的死锁问题!
案例剖析,MySQL共享锁引发的死锁问题!
|
26天前
|
SQL 算法 关系型数据库
面试:什么是死锁,如何避免或解决死锁;MySQL中的死锁现象,MySQL死锁如何解决
面试:什么是死锁,死锁产生的四个必要条件,如何避免或解决死锁;数据库锁,锁分类,控制事务;MySQL中的死锁现象,MySQL死锁如何解决
|
2月前
|
关系型数据库 MySQL 数据库
一个 MySQL 数据库死锁的案例和解决方案
本文介绍了一个 MySQL 数据库死锁的案例和解决方案。
93 3
|
2月前
|
存储 关系型数据库 MySQL
优化 MySQL 的锁机制以提高并发性能
【10月更文挑战第16天】优化 MySQL 锁机制需要综合考虑多个因素,根据具体的应用场景和需求进行针对性的调整。通过不断地优化和改进,可以提高数据库的并发性能,提升系统的整体效率。
89 1
|
2月前
|
存储 关系型数据库 MySQL
MySQL锁,锁的到底是什么?
【10月更文挑战第16天】MySQL 锁锁定的是与数据和资源相关的对象,其目的是为了保证数据的一致性、避免冲突,并在并发环境下合理协调事务或操作的执行。理解锁的对象和意义对于优化数据库性能、处理并发问题至关重要。
70 0
|
2月前
|
关系型数据库 MySQL 数据库
mysql锁详解
通过理解并合理运用MySQL中的锁机制,开发者可以有效管理数据库并发访问,平衡性能与数据一致性需求。更多关于MySQL锁的深入探讨和最佳实践,请参考专业的数据库管理资源[[深入MySQL锁机制详解
42 0
|
2月前
|
监控 关系型数据库 MySQL
一次彻底讲清如何处理mysql 的死锁问题
【10月更文挑战第16天】本文详细介绍了如何处理 MySQL 中的死锁问题,涵盖死锁的概念、原因、检测方法及解决策略,强调通过优化事务设计、调整数据库参数、手动处理和预防措施等手段,有效减少死锁,提升数据库性能与稳定性。
348 0
|
1天前
|
SQL 关系型数据库 MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因,包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题,并提供了详细的解决步骤,如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等,确保导入过程顺利。