MySQL05-锁

最新推荐文章于 2025-12-15 21:10:21 发布
原创 最新推荐文章于 2025-12-15 21:10:21 发布 · 120 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#数据库

锁

InnoDB锁

MyISAM和InnoDB存储引擎使⽤的锁：

  • MyISAM采⽤表级锁(table-level locking)。
  • InnoDB⽀持⾏级锁(row-level locking)和表级锁，默认为⾏级锁
  • 表级锁： MySQL中锁定 粒度最⼤ 的⼀种锁，对当前操作的整张表加锁，实现简单，资源消耗也⽐j较少，加锁快，不会出现死锁。其锁定粒度最⼤，触发锁冲突的概率最⾼，并发度最低，MyISAM和 InnoDB引擎都⽀持表级锁。
  • ⾏级锁： MySQL中锁定 粒度最⼩ 的⼀种锁，只针对当前操作的⾏进⾏加锁。 ⾏级锁能⼤⼤减少数据库操作的冲突。其加锁粒度最⼩，并发度⾼，但加锁的开销也最⼤，加锁慢，会出现死锁

InnoDB锁类型

实现了两种标准的行级锁

  • 共享锁（S Lock）：允许事务读一行数据
  • 排他锁（X Lock）：允许事务删除或更新一行数据

S 锁仅与 S 锁兼容

一致性非锁定读

InnoDB 存储引擎通过多版本控制的方式来读取当前执行时间数据库中的行数据。如果读取的行正在执行 DLETE 或 UPDARA 操作，这时读操作不会因此去等待行上锁的释放，相反InnoDB存储引擎会去读取行的一个快照数据。

一个行记录可能不止一个快照数据（通过undo 段来实现），一般称这种技术为行多版本技术，由此带来的并发技术称为多版本并发控制（MVCC）。

事务隔离级别中
READ-COMMITTED(读取已提交)，REPEATABLE-READ(可重复读)下，使用一致性非锁定读。然而他们对于快照数据的定义却不同
READ-COMMITTED(读取已提交)：总是读取锁定行的最新一份的快照数据。
REPEATABLE-READ(可重复读)：对于快照数据，总是读取事务开始时的行数据版本
在这里插入图片描述

一致性锁定读

用户想要显式地对数据库的 SELECT 操作进行加锁以保证数据逻辑的一致性。
在这里插入图片描述

行锁的三种算法

  • Record Lock：单个行记录上的锁
  • Gap Lock：间隙锁，锁定一个范围，但不包含记录本身
  • Next-Key Lock：Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身

锁问题/并发事务带来的问题

通过锁机制可以实现事务的隔离要求，使得事务可以并发的工作。锁提高了并发，但是却会带来潜在的问题。不过好在事务隔离性的要求，锁只会带来三种问题

  • 脏读（Dirty read）: 当⼀个事务正在访问数据并且对数据进⾏了修改，⽽这种修改还没有提交到数据库中，这时另外⼀个事务也访问了这个数据，然后使⽤了这个数据。因为这个数据是还没有提交的数据，那么另外⼀个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

READ-UNCOMMITTED(读取未提交)

  • 丢失修改（Lost to modify）: 简单来说就是一个事务的更新操作会被另一个事务的更新操作所覆盖，从而导致数据不一致。例如
    1）事务1将行记录r 更新为v1。但是事务1并未提交
    2）与此同时，事务2将行记录r 更新为 v2，事务2未提交
    3）事务1提交
    4）事务2提交
    但是在当前数据库的任何隔离级别下，都不会导致数据库理论意义上的丢失更新问题，这是因为，对于行的DML操作，需要对行或更粗粒度的对象加锁，因此上述中，事务2不能对行记录r 进行更新操作，其会被阻塞，直到事务1提交、

我们遇到的一般是逻辑意义上的丢失更新问题
1）指在⼀个事务T1查询⼀个数据，放入本地内存，并显示给一个终端用户user1
2）另外⼀个事务T2也查询了该数据，放入本地内存，并显示给一个终端用户user2
3）user1修改了这个数据后，更新数据库并提交
4）user2修改了这个数据后，更新数据库并提交
这样user1的修改结果就被丢失。
例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。

解决方法：就是让事务在这种情况下的操作变成串行化，而不是并行操作。即在步骤1）中，对用户读取的记录加上一个排他 X 锁。同样步骤2）中，用户同样需要加一个排他 X锁。这样user2就必须等

  • 不可重复读（Unrepeatableread）: 指在⼀个事务内多次读同⼀数据集合。在这个事务还没有结束时，另⼀个事务也访问该同⼀数据集合，并做了一些DML操作。因此，在第⼀个事务中的两次读数据之间，由于第⼆个事务的修改导致第⼀个事务两次读取的数据可能不太⼀样。这就发⽣了在⼀个事务内两次读到的数据是不⼀样的情况，因此称为不可重复读。

不可重复读，是因为在读取已提交的隔离级别下，读取的是该记录的最新的快照。

一般来说不可重复读是可以接受的，因为其读到的是已经提交的数据，本身并不会带来很大的问题，因此很多厂商将其数据库的默认隔离级别设置为 READ-COMMITTED(读取已提交)

InnoDB 存储引擎的默认⽀持的隔离级别是 REPEATABLE-READ（可重读）
解决方案：通过Next-Key Lock 算法来避免不可重复读的问题

脏读和不可重复读的区别是：脏读是读到未提交的数据，而不可重复读读到的却是已经提交的数据，但是其实违反了数据库事务一致性的要求

在这里插入图片描述

MySQL--主从优化案例
m0_62754777的博客
10-15 1412
背景介绍 本文根据MySQL主备延迟问题解决、分析的过程，描述数据库主备延迟可能的原因和实际的处理方法。业务差异造就了数据库的场景不同、配置不同、参数不同，但是技术上都有共通的原理，希望各位同学能从本文中得到借鉴。附件中总结了主备同步的基础知识，以供参考。 1 问题描述 DB日常巡检过程中，发现阿里云RDS For MySQL实例主备两节点出现较长时间的延迟。通过排查不同的时间段，发现主备延迟一直存在，每天成规律的上下波动（图1），从昨天开始主备延迟呈持续上升状态（图2）。 图1 图2
Mysql 会话锁查看
JIANGWANGZHEN_1的博客
02-22 1169
MySQL会话锁查看
MySQL实战45讲学习笔记：行锁功过-怎么减少行锁对性能的影响？（第7讲）
weixin_30235225的博客
09-30 458
一、上节回顾今日计划 在上一篇文章中，我跟你介绍了 MySQL 的全局锁和表级锁，今天我们就来讲讲 MySQL的行锁。 MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB是支持行锁的，这...
Innodb行锁
songtaiwu的博客
06-14 1881
行锁
【MySQL】InnoDB中的行级锁
morris
01-15 1万+
行锁，也称为记录锁，顾名思义就是在记录上加的锁。但是要注意，这个记录指的是通过给索引上的索引项加锁。InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。 不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。 只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL 通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会
Mysql InnoDB引擎的行锁和表锁
热门推荐
luzhensmart的专栏
08-14 2万+
InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。行级锁与表级锁本来就有许多不同之处，另外，事务的引入也带来了一些新问题。下面我们先介绍一点背景知识，然后详细讨论InnoDB的锁问题。 背景知识 事务（Transaction）及其ACID属性 事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。 ...
（Mysql 七）InnoDB引擎的行锁和表锁
ZJE
02-22 2万+
mysql常用引擎有MYISAM和InnoDB，而InnoDB是mysql默认的引擎。MYISAM不支持行锁，而InnoDB支持行锁和表锁。 1.行锁和表锁 2.行锁的类型 3.行锁的实现 1.行锁和表锁 在mysql 的 InnoDB引擎支持行锁，与Oracle不同，mysql的行锁是通过索引加载的，即是行锁是加在索引响应的行上的，要是对应的SQL语句没有走索引
MySQL详解－－锁
Able
02-19 581
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。本章我们着重讨论MySQL锁机制的特点，常见的锁问题，以及解决MySQL
MySQL05 - 事务之锁机制
qq_43350524的博客
01-31 1050
MySQL05 - 事务之锁机制
05mysql的锁分析
wang0907的博客
07-18 993
本文如无特殊说明，使用的存储引擎都是InnoDB.只要是共享的资源就会存在并发访问数据的一致性的问题，数据库也是如此，数据库需要合理的设置数据的访问规则，而，这种用来设置数据访问规则的数据结构我们就叫做是锁。本文就一起来看下MySQL都提供了哪些锁机制。............
MySQL笔记-InnoDB锁问题
yequn1997的博客
05-27 2534
1.MySQL锁概述 MyISAM存储引擎采用的是表级锁 InnoDB存储引擎既支持行级锁，也支持表级锁，默认情况下是采用行级锁 MySQL锁的特性： 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低； 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高； 页面锁：开销和加锁时间介于表锁和行锁之间；会出现死锁，锁定粒度介于表锁和行锁之间，并发度一般。 2.InnoDB锁问题 InnoDB与MyISAM的最大不同有两点：支持事务（
MySQL锁机制全解析
weixin_50701238的博客
06-29 1218
MySQL锁机制解析：从全局锁到行级锁的全面指南 本文详细介绍了MySQL中各种锁机制及其应用场景。主要涵盖三类锁：1)全局锁（FLUSH TABLES WITH READ LOCK）用于全库备份；2)表级锁（MyISAM默认）包括共享读锁、独占写锁和元数据锁；3)InnoDB行级锁（记录锁、间隙锁、临键锁和插入意向锁）。重点分析了共享锁（S锁）和排他锁（X锁）的特性与冲突场景，并通过多个实际案例演示了不同锁的加锁效果、阻塞表现和死锁形成过程。文章还探讨了索引对锁定的影响以及隔离级别与锁机制的关系，为开发者
MySQL -- 09 -- MySQL调优之explain工具（8.0）
Gene Xu
08-20 474
官方文档：EXPLAIN Output Format 使用 explain 可以模拟优化器执行 SQL 语句，从而知道 MySQL 是如何处理 SQL 语句、如何分析查询语句、如何分析表结构的性能瓶颈 表的读取顺序 数据读取操作的操作类型 哪些索引可以使用 哪些索引被实际使用 表之间的引用 每张表有多少行数据被优化器查询 此外，除了 select 语句，其他如：insert、update、delete、replace 等语句也可以使用 explain 来查询执行计划 一、.
MySQL5.6-5.7-8.0在线DDL和DML如何避免阻塞说明
冰刀(skate)
05-07 903
尽管支持Algorithm=INPLACE ，但因为数据实质上需要重新组织，因此操作的开销高昂。尽管支持Algorithm=INPLACE ，但因为数据实质上需要重新组织，因此操作的开销高昂。尽管支持Algorithm=INPLACE ，但因为数据实质上需要重新组织，因此操作的开销高昂。尽管支持Algorithm=INPLACE ，但因为数据实质上需要重新组织，因此操作的开销高昂。尽管支持Algorithm=INPLACE ，但因为数据实质上需要重新组织，因此操作的开销高昂。
面试君与面试官的分歧：innodb支持行锁就不锁表么？
bigwood99的博客
03-03 1125
面试官观点：innodb引擎只会使用行锁。 面试君观点：innodb引擎支持行锁，但在不使用索引或无索引的时候会使用表锁。 虽然当时未做过多争论，不过这个问题还是挺有意思，MySQL的innodb引擎到底会锁表么？ 这个在网上搜索，大部分的回答是innodb是在索引上实现行锁的，如果没有索引或无法利用索引，那么可能会升级为锁表。 从《MySQL技术内幕 InnoDB存储引擎 第2版》第六...
从一次增删改操作开始：彻底理解 MySQL Buffer Pool 的地位与作用
soft2001525的博客
12-11 2121
它是 InnoDB 的核心内存引擎，是所有数据读写的唯一入口，是数据库性能与安全性的关键支撑。理解 Buffer Pool 的逻辑，你就理解了 MySQL 的一半。redo logundo log（事务回滚）MVCC脏页、刷盘策略自适应哈希索引B+树索引加载机制查询优化都与 Buffer Pool 有直接关系。如果你想系统搞懂 MySQL，从 Buffer Pool 开始是最佳路径。如果本篇文章对你有帮助，欢迎：✏️ 评论交流。
兰顿蚂蚁——CAD二次开发
最新发布
12-15 293
【代码】兰顿蚂蚁——CAD二次开发。
基于Java+SpringBoot+Vue的美甲店管理系统【附源码+文档+部署视频+讲解)
小熊的博客
12-11 1240
基于SpringBoot的美甲店管理系统开发案例，涵盖用户、美甲师和管理员三大角色功能模块，采用Vue+SpringBoot+MySQL技术栈实现。系统包含服务预约、订单管理、耗材采购等全流程数字化功能，采用MVC设计模式和B/S架构。包含用户、美甲师、管理员三类角色的功能模块，该系统聚焦美甲店的日常运营场景，通过用户端浏览服务与套餐、美甲师端处理预约与评价、管理员端统筹人员、业务与资源的模式，实现美甲店从服务展示、预约调度到耗材管理的全流程数字化，提升门店运营效率与服务体验。
KingbaseES 面向应用程序的SQL开发：从原理到实战的深度探索
优快云博客专家，领域包括但不限于：AI、大数据、Python、架构师，有合作、课程、问题、疑惑请私信博主
12-14 613
KingbaseES数据库SQL开发实践解析 摘要：本文深入探讨国产数据库KingbaseES在SQL开发中的核心实践，重点分析四大关键技术：1) SQL处理机制，包括游标管理和绑定变量优化；2) 正则表达式的高级应用与性能优化；3) 多类型索引的选择与优化策略；4) 事务控制与锁机制。通过具体代码示例，展示了KingbaseES在金融、政务等关键领域的高性能SQL开发能力，包括事务隔离级别设置、死锁检测等高级特性。文章为开发者提供了从基础语法到高级优化的完整SQL开发指南，助力企业实现高效可靠的数据库应用
mysql bin-log恢复
08-05
<think>我们正在讨论MySQL的二进制日志（Binary Log）用于数据恢复。根据引用内容，我们可以总结出以下步骤： 1. 确保MySQL已经开启了二进制日志（如果没有开启，需要修改配置文件并重启MySQL）。 2. 使用mysqlbinlog工具查看二进制日志内容，定位需要恢复的操作位置（通过时间或位置点）。 3. 使用mysqlbinlog导出特定位置或时间段的日志内容（可以输出到文件，也可以直接通过管道导入mysql执行）。 4. 执行恢复（如果导出到文件，则使用source命令或mysql命令导入；如果通过管道，则直接执行）。 具体步骤： 步骤1：检查二进制日志是否开启 - 登录MySQL，执行：`show variables like 'log_bin';`，如果Value为ON则表示开启。 步骤2：查看二进制日志文件列表 - 执行：`show binary logs;` 步骤3：查看具体的二进制日志内容，确定要恢复的位置 - 使用mysqlbinlog工具（在命令行中执行，不是在MySQL中）： ```bash mysqlbinlog /var/lib/mysql/binlog.000001 ``` 如果日志文件很大，可以结合less或grep进行查找。 步骤4：根据位置点或时间点导出日志 - 按位置点导出： ```bash mysqlbinlog --start-position=107 --stop-position=2345 /var/lib/mysql/binlog.000001 > recovery.sql ``` - 按时间点导出： ```bash mysqlbinlog --start-datetime="2024-01-01 00:00:00" --stop-datetime="2024-01-02 00:00:00" /var/lib/mysql/binlog.000001 > recovery.sql ``` 步骤5：执行恢复 - 方法1：使用source命令（在MySQL命令行中）： ```sql source recovery.sql; ``` - 方法2：使用mysql命令行工具： ```bash mysql -u root -p < recovery.sql ``` 注意事项： - 恢复前最好备份当前数据，以防恢复操作带来新的问题。 - 如果恢复的数据涉及多个二进制日志文件，需要按顺序导出并执行。 引用[3]和[4]提供了具体的操作示例。 另外，引用[5]提到了重做日志（Redo Log），但重做日志是InnoDB存储引擎层面的日志，用于保证事务的持久性和崩溃恢复，而二进制日志是MySQL服务器层面的，主要用于复制和数据恢复。两者不同，我们这里讨论的是二进制日志。 生成相关问题： 1. 如何开启MySQL的二进制日志？ 2. 二进制日志和重做日志有什么区别？ 3. 如何清理旧的二进制日志文件？ 请根据以上步骤和注意事项编写回答。</think>### MySQL 二进制日志（Binary Log）数据恢复详解 Binary Log 是 MySQL 的核心日志机制，记录所有修改数据库的操作（增删改）。通过解析日志可实现精确数据恢复，以下是完整操作流程： --- #### 一、前提条件 1. **开启 Binary Log** 在 MySQL 配置文件 `my.cnf` 中添加： ```ini [mysqld] log-bin = /var/log/mysql/mysql-bin.log # 日志路径 server-id = 1 # 主从复制需唯一ID binlog_format = ROW # 推荐ROW格式[^1] ``` ```bash sudo systemctl restart mysql # 重启生效 ``` 2. **验证日志状态** ```sql SHOW VARIABLES LIKE 'log_bin'; -- 需返回 ON SHOW MASTER STATUS; -- 查看当前日志文件及位置[^1] ``` --- #### 二、恢复数据核心步骤 ##### ▶ 场景1：按时间点恢复（误删数据） ```bash # 导出特定时间段的日志 mysqlbinlog \ --start-datetime="2024-06-01 09:00:00" \ --stop-datetime="2024-06-01 10:00:00" \ mysql-bin.000001 > recovery.sql # 执行恢复 mysql -u root -p < recovery.sql ``` ##### ▶ 场景2：按位置点恢复（精确操作） ```bash # 解析日志找到操作位置（如误删前的位置） mysqlbinlog mysql-bin.000001 | less # 导出指定位置区间（如 start-position=107, stop-position=2345） mysqlbinlog \ --start-position=107 \ --stop-position=2345 \ mysql-bin.000001 | mysql -u root -p[^4] ``` ##### ▶ 场景3：恢复单库/单表 ```bash # 通过库名过滤日志 mysqlbinlog \ --database=your_db_name \ mysql-bin.000001 | mysql -u root -p ``` --- #### 三、关键技巧与注意事项 1. **日志解析工具** ```bash mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000001 # 解码ROW格式日志 ``` - 输出内容包含实际执行的 SQL 语句[^3] 2. **安全恢复建议** - 恢复前备份当前数据：`mysqldump -u root -p --all-databases > full_backup.sql` - 测试环境验证恢复脚本 - 大事务操作分段恢复，避免锁表 3. **常见错误处理** - **日志文件不存在**：检查 `show binary logs;` 确认有效文件 - **权限问题**：确保 `mysqlbinlog` 和 MySQL 用户有日志读取权限 - **GTID 冲突**：添加 `--skip-gtids` 参数忽略 GTID 限制 --- #### 四、Binary Log 维护 1. **清理旧日志** ```sql PURGE BINARY LOGS BEFORE '2024-05-01'; -- 删除指定日期前的日志[^2] SET GLOBAL expire_logs_days = 7; -- 自动保留7天日志 ``` 2. **磁盘空间监控** ```sql SHOW BINARY LOGS; -- 查看日志文件大小 ``` --- #### 五、与 Redo Log 的区别[^5] | **特性** | **Binary Log** | **Redo Log** | |----------------|------------------------------------|----------------------------------| | 作用层级 | MySQL 服务层 | InnoDB 存储引擎层 | | 记录内容 | 逻辑操作（SQL 语句） | 物理页修改（数据页变化） | | 主要用途 | 数据恢复、主从复制 | 崩溃恢复（Crash Recovery） | | 文件位置 | 自定义路径（如 /var/log/mysql/） | 固定路径（ib_logfile0,1） | > 提示：数据恢复优先使用 Binary Log；系统崩溃恢复由 Redo Log 自动完成。 --- ### 相关问题 1. 如何基于 GTID 实现更安全的二进制日志恢复？ 2. 主从复制中断时，如何利用 Binary Log 修复数据一致性？ 3. Binary Log 的 ROW/STATEMENT/MIXED 格式对恢复有何影响？ 4. 如何监控 Binary Log 的生成速率和磁盘占用？ [^1]: Binary log 是 MySQL 数据库的重要日志类型，它能够记录对数据库进行的所有修改操作[^1]。 [^2]: 删除旧的 Binary Log 文件可释放磁盘空间或维护数据库[^2]。 [^3]: 通过 `mysqlbinlog` 解析日志并定位操作位置[^3]。 [^4]: 基于位置点恢复不限定于某个特定的库或表[^4]。 [^5]: Redo Log 用于在 MySQL 崩溃恢复期间纠正由不完整事务写入的数据[^5]。
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值