mysql InnoDB行锁

2024-03-12 19:48
文章标签 mysql innodb database 行锁

本文主要是介绍mysql InnoDB行锁,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

mysql的InnoDB存储引擎既支持行级锁,也支持表级锁,默认行级锁。

行级锁开销大,加锁慢,会出现死锁,锁定粒度最小,发生锁冲突的概率最低,并发度最高。

 

事务及其ACID属性

事务是由一组sql语句组成的逻辑处理单元,具有4个属性;

原子性(Atomicity):事务是一个原子操作单元,其对数据的修改,要么全部执行,要么全部不执行。

一致性(Consistent):在事务开始和完成时,数据都必须保持一致状态,这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性;事务结束时,所有的内部数据结构(如所有或链表)也都必须是正确的。

隔离性(Isolation):数据库系统提供一定的隔离机制,保证事务在不受外部并发操作形象的独立环境执行,这意味着事务处理过程中的中间状态对外部是可见的,反之亦然。

持久性(Durable):事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

 

并发事务带来的问题

数据丢失(Lost Update):当2个或多个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其他事务的存在,就会发生丢失更新问题,最后的更新会覆盖其他事务所做的更行。

脏读(Dirty Reads):一个事务正在对一条记录做修改,在这个事务完成并提交前,这条记录的数据就处于不一致状态,这时,另一个事务也来读取同一条记录,如果不加控制,第二个事务的取了这些脏数据,并据此做处理,就会产生未提交的数据依赖关系,即脏读。

不可重复读(Non-Repeatable Reads):一个事务在读取某些数据后的某个时间,再次读取以前读取过的数据,却发现其读取出的数据已经发生了改变或某些记录已经被删除了,这种现象就叫不可重复读。

幻读(Phantom Reads): 一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其他查询条件的新数据,这种现象叫做幻读。

 

解决方式

事务隔离有2种实现方式

一种是在读取数据前,对其加锁,阻止其他事务对数据进行修改。

二种是不加任何锁,通过一定机制生成一个数据请求时间点额一致性数据快照,并用这个快照来提供一定级别的一致性读取,从用户角度来看,好像数据库可以提供一定级别的多个版本,这种技术叫做数据多版本并发控制,也称为多版本数据库。

隔离级别

隔离级别读数据一致性脏读不可重复读幻读
未提交读

最低级别,只能保证不读取

物理上损坏的数据。

已提交读语句级
可重复读事务级
可序列化最高级别,事务级

 

 

行锁争夺

通过

show status like 'innodb_row_lock%';

查看争夺情况。

innodb_row_lock_current_waits: 当前正在等待锁定的数量。

innodb_row_lock_time:从系统启动到现在锁定的总时间长度。

innodb_row_lock_time_avg:每次等待锁花费的平均时间。

innodb_row_lock_time_max: 从系统启动到现在等待最长的一次所花费的时间。

innodb_row_lock_waits:系统启动后到现在总共等待的次数。

 

 

行锁类型

共享锁(S):允许一个事务取读一行,阻止其他事务获得相同数据集的排他锁。

SELECT * FROM table WHERE .... LOCK IN SHARE MODE;

排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享锁和排他锁。

SELECT * FROM table WHERE ....FOR UPDATE;

为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB还有2种内部使用的意向锁,都是表锁。

意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。

意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。

意向锁是自动加的,对于UPDATE、DELETE、INSERT,InnoDB会自动给涉及数据集加排他锁,对SELECT,InnoDB不会加锁。

共享锁

session1session2
select id, name from author where id = 1;select id, name from author where id = 1;

当session对id=1的记录加share mode的共享锁;

select id, name from author where id = 1 lock in share mode;

 
 

其他session仍然可以查询记录,并也可以对该就加share mode共享锁;

select id, name from author where id = 1 lock in share mode;

当sesison对锁定的记录更新时,等待锁;

update author set name ="test" where id  = 1;

等待

 
 

其他session也对该记录进行更新操作时,则会导致死锁退出。

update author set name ="test" where id  = 1;

error: Dead lock found when trying to get lock; try restarting transaction;

获得锁后,可以成功更新;

update author set name ="test" where id  = 1;

 

 

排他锁

session1session2
select id, name from author where id = 1;select id, name from author where id = 1;

当前session对id=1的记录加for update的排他锁;

select id, name from author where id = 1 for update;

 
 

其他sesison也可以查询该记录,但是不能对该记录加排他锁,会等待获得锁;

select id, name from author where id = 1;

可以执行

select id, name from author where id = 1 for update;

等待

当前session可以对锁定的记录进行更新操作,更新后释放锁;

update author set name = "test" where id = 1;

commit;

 
 

其他session获得锁,得到其他session提交的记录。

select id, name from author where id = 1 for update;

 

注意: 在不通过索引条件查询时,InnoDB会锁定表种的所有记录。

 

间隙锁

当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁,对于键值在条件范围内但并不存在的记录,叫间隙,InnoDB也会对这个间隙加锁,这种锁机制就是间隙锁,即Next-Key锁。

InnoDB除了通过范围条件加锁时使用Next-key锁外,如果使用相等条件请求给一个不存在的记录加锁,也会使用Next-key锁。

 

这篇关于mysql InnoDB行锁的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/802387

相关文章

MySQL 8 中的一个强大功能 JSON_TABLE示例详解

《MySQL8中的一个强大功能JSON_TABLE示例详解》JSON_TABLE是MySQL8中引入的一个强大功能,它允许用户将JSON数据转换为关系表格式,从而可以更方便地在SQL查询中处理J... 目录基本语法示例示例查询解释应用场景不适用场景1. ‌jsON 数据结构过于复杂或动态变化‌2. ‌性能要

MySQL字符串常用函数详解

《MySQL字符串常用函数详解》本文给大家介绍MySQL字符串常用函数,本文结合实例代码给大家介绍的非常详细,对大家学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql字符串常用函数一、获取二、大小写转换三、拼接四、截取五、比较、反转、替换六、去空白、填充MySQL字符串常用函数一、

MySQL中比较运算符的具体使用

《MySQL中比较运算符的具体使用》本文介绍了SQL中常用的符号类型和非符号类型运算符,符号类型运算符包括等于(=)、安全等于(=)、不等于(/!=)、大小比较(,=,,=)等,感兴趣的可以了解一下... 目录符号类型运算符1. 等于运算符=2. 安全等于运算符<=>3. 不等于运算符<>或!=4. 小于运

虚拟机Centos7安装MySQL数据库实践

《虚拟机Centos7安装MySQL数据库实践》用户分享在虚拟机安装MySQL的全过程及常见问题解决方案,包括处理GPG密钥、修改密码策略、配置远程访问权限及防火墙设置,最终通过关闭防火墙和停止Net... 目录安装mysql数据库下载wget命令下载MySQL安装包安装MySQL安装MySQL服务安装完成

MySQL进行数据库审计的详细步骤和示例代码

《MySQL进行数据库审计的详细步骤和示例代码》数据库审计通过触发器、内置功能及第三方工具记录和监控数据库活动,确保安全、完整与合规,Java代码实现自动化日志记录,整合分析系统提升监控效率,本文给大... 目录一、数据库审计的基本概念二、使用触发器进行数据库审计1. 创建审计表2. 创建触发器三、Java

MySQL逻辑删除与唯一索引冲突解决方案

《MySQL逻辑删除与唯一索引冲突解决方案》本文探讨MySQL逻辑删除与唯一索引冲突问题,提出四种解决方案:复合索引+时间戳、修改唯一字段、历史表、业务层校验,推荐方案1和方案3,适用于不同场景,感兴... 目录问题背景问题复现解决方案解决方案1.复合唯一索引 + 时间戳删除字段解决方案2:删除后修改唯一字

Zabbix在MySQL性能监控方面的运用及最佳实践记录

《Zabbix在MySQL性能监控方面的运用及最佳实践记录》Zabbix通过自定义脚本和内置模板监控MySQL核心指标(连接、查询、资源、复制),支持自动发现多实例及告警通知,结合可视化仪表盘,可有效... 目录一、核心监控指标及配置1. 关键监控指标示例2. 配置方法二、自动发现与多实例管理1. 实践步骤

MySQL 主从复制部署及验证(示例详解)

《MySQL主从复制部署及验证(示例详解)》本文介绍MySQL主从复制部署步骤及学校管理数据库创建脚本,包含表结构设计、示例数据插入和查询语句,用于验证主从同步功能,感兴趣的朋友一起看看吧... 目录mysql 主从复制部署指南部署步骤1.环境准备2. 主服务器配置3. 创建复制用户4. 获取主服务器状态5

SpringBoot中六种批量更新Mysql的方式效率对比分析

《SpringBoot中六种批量更新Mysql的方式效率对比分析》文章比较了MySQL大数据量批量更新的多种方法,指出REPLACEINTO和ONDUPLICATEKEY效率最高但存在数据风险,MyB... 目录效率比较测试结构数据库初始化测试数据批量修改方案第一种 for第二种 case when第三种

MySql基本查询之表的增删查改+聚合函数案例详解

《MySql基本查询之表的增删查改+聚合函数案例详解》本文详解SQL的CURD操作INSERT用于数据插入(单行/多行及冲突处理),SELECT实现数据检索(列选择、条件过滤、排序分页),UPDATE... 目录一、Create1.1 单行数据 + 全列插入1.2 多行数据 + 指定列插入1.3 插入否则更