MySQL的行锁、表锁、间隙锁详解(中)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL的行锁、表锁、间隙锁详解(中)

4 InnoDB锁

InnoDB与MyISAM的最大不同有两点

  • 支持事务
  • 采用行锁

行级锁和表级锁本来就有许多不同之处,另外,事务的引入也带来了一些问题。

查看Innodb行锁争用情况

image.png

如果发现争用比较严重,如Innodb_row_lock_waitsInnodb_row_lock_time_avg的值比较高

查询information_schema相关表来查看锁情况

image.png

设置Innodb monitors

进一步观察发生锁冲突的表,数据行等,并分析锁争用的原因

image.png

停止监视器

image.png

默认情况每15秒会向日志中记录监控的内容;

如果长时间打开会导致.err文件变得非常巨大;

所以确认原因后,要删除监控表关闭监视器,或者通过使用–console选项来启动服务器以关闭写日志功能

4.4 InnoDB的行锁

InnoDB支持以下两种类型的行锁

  • 共享锁(读锁S)
  • 若事务 T 对数据对象 A 加了 S 锁;

则事务 T 可以读 A 但不能修改 A;

其它事务只能再对它加 S 锁,而不能加 X 锁,直到 T 释放 A 上的 S 锁;

这保证了,其他事务可以读 A,但在事务 T 释放 S 锁之前,不能对 A 做任何修改操作.

排他锁(写锁X)

若事务 T 对数据对象A加 X 锁;

事务 T 可以读 A 也可以修改 A;

其他事务不能对 A 加任何锁,直到 T 释放 A 上的锁;

这保证了,其他事务在 T 释放 A 上的锁之前不能再读取和修改 A .

MySQL InnoDB默认行级锁

行级锁都是基于索引的,若一条SQL语句用不到索引是不会使用行级锁的,会使用表级锁把整张表锁住

为了允许行/表锁共存,实现多粒度锁,InnoDB还有两种内部使用的:

意向锁(Intention Locks)

这两种意向锁都是表级锁:

  • 意向共享锁(IS)
  • 事务打算给数据行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁
  • 意向排他锁(IX)
    事务打算给数据行加排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁
当前锁/是否兼容/请求锁 X IX S IS
X 冲突 冲突 冲突 冲突
IX 冲突 兼容 冲突 兼容
S 冲突 冲突 兼容 兼容
IS 冲突 兼容 兼容 兼容

如果一个事务请求的锁模式与当前锁兼容,InnoDB就请求的锁授予该事务,反之如果两者不兼容,该事务就要等待锁释放


意向锁是InnoDB自动加的,不需用户干预.

对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及及数据集加排他锁(X);

对于普通SELECT语句,InnoDB不会加任何锁.


对于SELECT语句,可以通过以下语句显式地给记录加读/写锁


共享锁(S)

image.png

排他锁(X)

image.png

共享锁语句主要用在需要数据依存关系时确认某行记录是否存在;

并确保没有人对这个记录UPDATE或DELETE.

但如果当前事务也需要对该记录进行更新,则很有可能造成死锁;

对于锁定行记录后需要进行更新操作的应用,应该使用排他锁语句.

此外还有自增锁(auto-in)和 lock tables/DDL等表级锁

查看锁:

SHOW ENGINE INNODB STATUS;

4.5 实例

4.5.1 Innodb共享锁

session_1 session_2
set autocommit=0,select * from actor where id =1 set autocommit=0,select * from actor where id =1
当前seesion对id为1的记录加入共享锁 select * from actor where id =1 lock in share mode



其他seesion仍然可以查询,并对该记录加入 select * from actor where id =1 lock in share mode
当前session对锁定的记录进行更新,等待锁 update。。。where id=1



当前session对锁定记录进行更新,则会导致死锁退出 update。。。where id=1
获得锁,更新成功


4.5.2 Innodb排他锁

session_1 session_2
set autocommit=0,select * from actor where id =1 set autocommit=0,select * from actor where id =1
当前seesion对id为1的记录加入for update 共享锁 select * from actor where id =1 for update



可查询该记录select *from actor where id =1,但是不能再记录共享锁,会等待获得锁select *from actor where id =1 for update
更新后释放锁 update。。。 commit
其他session,获得锁,得到其他seesion提交的记录

4.6 行锁的实现

行锁是通过给索引上的索引项加锁来实现。若没有索引,InnoDB将通过隐藏的聚簇索引来对记录加锁:

Record Locks

对索引项加锁

Gap lock

对索引项之的“间隙“,第一条记录前的”间隙“,或最后一条记录后的”间隙“,加锁

Next-key lock

前两种的组合,对记录及其前面的间隙加锁

行锁实现特点意味着:

若不通过索引条件检索数据,则Innodb将对表的所有记录加锁,和表锁一样。

记录锁(Record Locks)

record lock是对索引加的锁。例如,

SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;

阻止任何其他事务插入、更新或删除 t.c1=10 的行。


Record Locks总是锁定索引记录,即使表没有定义索引。对于这种情况,InnoDB 会创建一个隐藏的聚集索引并使用该索引进行记录锁定。


record lock的事务数据在 SHOW ENGINE INNODB STATUS 和 InnoDB 监视器输出中显示类似于以下内容:

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

间隙锁(Next-Key锁)

当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据的索引项加锁;

对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁).


举例来说,假如emp表中只有101条记录,其empid的值分别是1,2,…,100,101,下面的SQL:

InnoDB 不仅会对符合条件的 empid 值为 101 的记录加锁;

也会对 empid大于101(这些记录并不存在)的“间隙”加锁

image.png

作用

  • 防止幻读,以满足相关隔离级别的要求
    对于上例,若不使用间隙锁,如果其他事务插入 empid 大于 100 的任何记录,;
    那么本事务如果再次执行上述语句,就会发生幻读
  • 满足其恢复和复制的需要
    在使用范围条件检索并锁定记录时;
    InnoDB 这种加锁机制会阻塞符合条件范围内键值的并发插入,这往往会造成严重的锁等待;
    因此,在实际开发中,尤其是并发插入较多的应用;
    我们要尽量优化业务逻辑,尽量使用相等条件来访问更新数据,避免使用范围条件.
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
5天前
|
存储 SQL 关系型数据库
MYSQL--锁机制*
MYSQL--锁机制*
|
4天前
|
存储 SQL 关系型数据库
【MySQL技术内幕】6.5-锁问题、阻塞、死锁、锁升级
【MySQL技术内幕】6.5-锁问题、阻塞、死锁、锁升级
22 2
|
4天前
|
SQL 存储 算法
【MySQL技术内幕】6.4-锁的算法
【MySQL技术内幕】6.4-锁的算法
20 1
|
4天前
|
存储 SQL 关系型数据库
【MySQL技术内幕】6.3-InnoDB中的锁
【MySQL技术内幕】6.3-InnoDB中的锁
141 57
|
4天前
|
存储 SQL 关系型数据库
【MySQL技术内幕】6.1-锁、lock和latch
【MySQL技术内幕】6.1-锁、lock和latch
13 0
|
5天前
|
存储 关系型数据库 MySQL
深入研究MySQL意向锁
MySQL意向锁是一种特殊的表级锁,由InnoDB存储引擎在操作数据之前自动添加,无需用户干预。它分为意向共享锁(IS)和意向排他锁(IX)两种。意向锁的主要作用是协调行锁和表锁的关系,优化加锁策略,避免全表扫描判断是否存在行锁。意向锁之间不会冲突,但会与表级别的排他锁冲突,从而确保数据库并发访问的一致性和完整性。简而言之,意向锁提高了数据库并发操作的性能和效率。
|
5天前
|
SQL 关系型数据库 MySQL
【MySQL】事务?隔离级别?锁?详解MySQL并发控制机制
【MySQL】事务?隔离级别?锁?详解MySQL并发控制机制
15 0
|
11天前
|
SQL 关系型数据库 MySQL
MySQL数据库——锁-行级锁(行锁、间隙锁和临键锁)
MySQL数据库——锁-行级锁(行锁、间隙锁和临键锁)
16 0
|
4天前
|
弹性计算 关系型数据库 数据库
手把手带你从自建 MySQL 迁移到云数据库,一步就能脱胎换骨
阿里云瑶池数据库来开课啦!自建数据库迁移至云数据库 RDS原来只要一步操作就能搞定!
|
22小时前
|
关系型数据库 MySQL API
实时计算 Flink版操作报错合集之同步MySQL数据到另一个MySQL数据库,第一次同步后源表数据发生变化时目标表没有相应更新,且Web UI中看不到运行的任务,该怎么解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。