首页 > 其他分享 >delete、update对表的锁定

delete、update对表的锁定

时间:2024-11-07 14:31:26浏览次数:4  
标签:语句 对表 事务 update UPDATE InnoDB 锁定 WHERE delete

delete语句对表的锁定

在 MySQL 中,DELETE 语句在执行时确实会对表进行锁定,以确保数据的一致性和完整性。具体来说,DELETE 语句会使用行级锁或表级锁,这取决于存储引擎和事务隔离级别。以下是一些关键点:

InnoDB:使用行级锁,不会阻止其他事务插入新行,只要这些新行不满足 WHERE 条件。

锁机制

  1. InnoDB 存储引擎

    • 行级锁:InnoDB 默认使用行级锁。当执行 DELETE 语句时,InnoDB 会锁定符合 WHERE 条件的行。这意味着其他事务可以继续插入新行,只要这些新行不满足 WHERE 条件。
    • 间隙锁:为了防止幻读,InnoDB 可能会使用间隙锁(Gap Locks)或临键锁(Next-Key Locks)。这些锁会锁定索引范围,防止其他事务在这些范围内插入新行。
  2. MyISAM 存储引擎

    • 表级锁:MyISAM 使用表级锁。当执行 DELETE 语句时,整个表会被锁定,阻止其他事务对表进行任何写操作(包括插入、更新和删除)。

EXPLAIN 输出解析

1	DELETE	trackinformation		range	idx_timestamp	idx_timestamp	9	const	229675	100.00	Using where
  • type: range — 表示使用了索引范围扫描。
  • key: idx_timestamp — 表示实际使用的索引。
  • rows: 229675 — 表示估计需要扫描的行数。
  • Extra: Using where — 表示使用了 WHERE 子句进行过滤。

锁的影响

  • 行级锁:如果使用 InnoDB 存储引擎,DELETE 语句只会锁定符合 WHERE 条件的行。其他事务可以继续插入新行,只要这些新行不满足 WHERE 条件。
  • 表级锁:如果使用 MyISAM 存储引擎,DELETE 语句会锁定整个表,阻止其他事务插入新行。

分批删除

为了减少锁的持有时间和对其他事务的影响,可以使用分批删除的方法。这不仅可以减少锁的持有时间,还可以避免一次性删除大量数据导致的性能问题。

SET @batch_size = 10000;

REPEAT
    DELETE FROM trackinformation
    WHERE timestamp < 1730690061000
    LIMIT @batch_size;
UNTIL ROW_COUNT() < @batch_size END REPEAT;

事务管理

在高并发环境中,可以考虑使用事务来管理删除操作,以确保数据的一致性和减少锁的持有时间。

START TRANSACTION;

SET @batch_size = 10000;

REPEAT
    DELETE FROM trackinformation
    WHERE timestamp < 1730690061000
    LIMIT @batch_size;
UNTIL ROW_COUNT() < @batch_size END REPEAT;

COMMIT;

总结

  • 锁机制DELETE 语句会根据存储引擎的不同使用行级锁或表级锁。
  • InnoDB:使用行级锁,不会阻止其他事务插入新行,只要这些新行不满足 WHERE 条件。
  • MyISAM:使用表级锁,会阻止其他事务插入新行。
  • 分批删除:推荐使用分批删除的方法,以减少锁的持有时间和对其他事务的影响。

希望这些信息对您有帮助!如果有更多问题,请随时提问。

UPDATE 语句的锁定行为

在 MySQL 中,UPDATE 语句的锁定行为取决于所使用的存储引擎和事务隔离级别。以下是一些关键点,帮助您理解 UPDATE 语句在不同存储引擎下的锁定行为:

1. InnoDB 存储引擎

InnoDB 是 MySQL 的默认存储引擎,支持行级锁和事务。UPDATE 语句在 InnoDB 中的行为如下:

  • 行级锁:InnoDB 会在 UPDATE 语句中锁定被修改的行。这意味着其他事务可以继续插入新行,只要这些新行不满足 UPDATE 语句的 WHERE 条件。
  • 间隙锁:为了防止幻读,InnoDB 可能会使用间隙锁(Gap Locks)或临键锁(Next-Key Locks)。这些锁会锁定索引范围,防止其他事务在这些范围内插入新行。

2. MyISAM 存储引擎

MyISAM 存储引擎使用表级锁。UPDATE 语句在 MyISAM 中的行为如下:

  • 表级锁UPDATE 语句会锁定整个表,阻止其他事务对表进行任何写操作(包括插入、更新和删除)。

事务隔离级别

事务隔离级别也会影响锁定行为:

  • 读未提交(Read Uncommitted):最低的隔离级别,几乎不使用行锁,允许脏读。
  • 读已提交(Read Committed):每个事务可以看到其他事务已经提交的更改,但不会看到未提交的更改。
  • 可重复读(Repeatable Read):InnoDB 的默认隔离级别,保证在一个事务中多次读取同一数据时结果一致,使用行级锁和间隙锁。
  • 可串行化(Serializable):最高的隔离级别,使用表级锁,确保事务完全隔离。

示例

假设您使用 InnoDB 存储引擎,并且事务隔离级别为 Repeatable Read,以下是一个 UPDATE 语句的示例:

UPDATE trackinformation
SET some_column = some_value
WHERE timestamp < 1730690061000;

在这个例子中,InnoDB 会锁定所有满足 WHERE 条件的行,但不会锁定整个表。其他事务可以继续插入新行,只要这些新行不满足 WHERE 条件。

分批更新

为了减少锁的持有时间和对其他事务的影响,可以使用分批更新的方法:

SET @batch_size = 10000;

REPEAT
    UPDATE trackinformation
    SET some_column = some_value
    WHERE timestamp < 1730690061000
    LIMIT @batch_size;
UNTIL ROW_COUNT() < @batch_size END REPEAT;

事务管理

在高并发环境中,可以考虑使用事务来管理更新操作,以确保数据的一致性和减少锁的持有时间:

START TRANSACTION;

SET @batch_size = 10000;

REPEAT
    UPDATE trackinformation
    SET some_column = some_value
    WHERE timestamp < 1730690061000
    LIMIT @batch_size;
UNTIL ROW_COUNT() < @batch_size END REPEAT;

COMMIT;

总结

  • InnoDBUPDATE 语句使用行级锁,不会锁定整个表,允许其他事务插入新行。
  • MyISAMUPDATE 语句使用表级锁,会锁定整个表,阻止其他事务插入新行。
  • 分批更新:推荐使用分批更新的方法,以减少锁的持有时间和对其他事务的影响。

希望这些信息对您有帮助!如果有更多问题,请随时提问。

标签:语句,对表,事务,update,UPDATE,InnoDB,锁定,WHERE,delete
From: https://www.cnblogs.com/firsthelloworld/p/18532187

相关文章

  • SQL Server 2022 RTM Cumulative Update 15 发布下载 (累积更新包)
    SQLServer2022RTMCumulativeUpdate15发布下载(累积更新包)最新的累积更新(CU)下载,包含自SQLServer2022RTM发布以来的所有更新。请访问原文链接:https://sysin.org/blog/sql-server-2022/查看最新版。原创作品,转载请保留出处。作者主页:sysin.orgSQLServer202......
  • blazor after visual studio update throws some strange JS warnings
    题意:Blazor在VisualStudio更新后出现一些奇怪的JS警告。问题背景:Afterupdatevisualstudioto17.11.3,inalmosteveryfilethatiopenedwithvisualstudiothrewsomewarning:在将VisualStudio更新到17.11.3后,几乎每个我用VisualStudio打开的文......
  • 三周精通FastAPI:19 Body - Updates 请求体 - 更新数据
    官网文档:https://fastapi.tiangolo.com/zh/tutorial/body-updates/请求体-更新数据¶用 PUT 更新数据¶更新数据请用 HTTP PUT 操作。把输入数据转换为以JSON格式存储的数据(比如,使用NoSQL数据库时),可以使用 jsonable_encoder。例如,把 datetime 转换为 str。......
  • json-server详细模拟GET、POST、DELETE等接口数据教程
    引言在前端开发过程中,我们经常需要在后端API尚未就绪的情况下模拟接口数据。json-server是一个强大而灵活的工具,可以帮助我们快速创建一个模拟的RESTfulAPI。本文将详细介绍如何使用json-server来模拟GET、POST、PUT、PATCH、DELETE等常用的HTTP方法,以及如何处理复杂的数......
  • ENT-ENT Updates
    @目录一、征稿简介二、重要信息三、服务简述四、投稿须知一、征稿简介二、重要信息期刊官网:https://ais.cn/u/3eEJNv三、服务简述涵盖了与耳鼻喉科相关的广泛主题,包括诊断方法、治疗技术和预防策略。ENTUpdates旨在发表具有最高科学和临床价值的研究,并鼓励提交高质量......
  • DBeaver如何生成select,update,delete,insert语句
    前言我们在使用DBeaver时,经常是需要查看数据库表的。有时候,我们需要用DBeaver生成select,update,delete,insert等语句,这样可以使我们方便的开发代码,那么应该怎么生成呢?如何生成语句首先,我们点击下我们要生成语句的表,右键点击。然后,我们点击下生成SQL选项,在展开的选项中,可以选择s......
  • Windows Server 2022 OVF, updated Oct 2024 (sysin) - VMware 虚拟机模板
    WindowsServer2022OVF,updatedOct2024(sysin)-VMware虚拟机模板2024年10月版本更新,现在自动运行sysprep,支持ESXiHostClient部署请访问原文链接:https://sysin.org/blog/windows-server-2022-ovf/查看最新版。原创作品,转载请保留出处。作者主页:sysin.org现......
  • Windows Server 2019 OVF, updated Oct 2024 (sysin) - VMware 虚拟机模板
    WindowsServer2019OVF,updatedOct2024(sysin)-VMware虚拟机模板2024年10月版本更新,现在自动运行sysprep,支持ESXiHostClient部署请访问原文链接:https://sysin.org/blog/windows-server-2019-ovf/查看最新版。原创作品,转载请保留出处。作者主页:sysin.orgWin......
  • Windows Server 2019 中文版、英文版下载 (updated Oct 2024)
    WindowsServer2019中文版、英文版下载(updatedOct2024)WindowsServer2019Version1809请访问原文链接:https://sysin.org/blog/windows-server-2019/查看最新版。原创作品,转载请保留出处。作者主页:sysin.org本站将不定期发布官方原版风格月度更新ISO。WindowsSe......
  • Windows Server 2016 OVF, updated Oct 2024 (sysin) - VMware 虚拟机模板
    WindowsServer2016OVF,updatedOct2024(sysin)-VMware虚拟机模板2024年10月版本更新,现在自动运行sysprep,支持ESXiHostClient部署请访问原文链接:https://sysin.org/blog/windows-server-2016-ovf/查看最新版。原创作品,转载请保留出处。作者主页:sysin.org现......