MySQL事务与锁机制详解：确保数据一致性的关键【MySQL系列】

本文将系统讲解 MySQL 中事务的四大特性、隔离级别与实现原理，深入拆解锁机制的种类与应用场景，并结合典型死锁案例进行分析，为你构建起应对复杂一致性问题的坚实基础。

一、什么是事务？

事务（Transaction）是数据库的一个操作序列，这些操作要么全部执行成功，要么全部不执行，具有原子性。

1.1 事务的四大特性（ACID）

特性含义
Atomicity（原子性）事务中的操作要么全部完成，要么全部不做
Consistency（一致性）事务执行前后数据要保持一致
Isolation（隔离性）多个事务之间互不干扰，彼此隔离
Durability（持久性）一旦事务提交，变更永久保存

1.2 示例：电商下单事务

START TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE id = 1001;
INSERT INTO orders(user_id, product_id) VALUES (2001, 1001);
COMMIT;

若其中一步失败，则整个操作应回滚，确保库存与订单保持一致。

二、MySQL事务的使用方式

2.1 显式事务

START TRANSACTION;
-- 或者 BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

2.2 隐式事务

某些语句自动开启事务，如 INSERT、UPDATE、DELETE，但只有在 autocommit=0 时生效。

2.3 回滚事务

ROLLBACK;

用于中止事务，将所有变更撤销。

三、事务隔离级别（Isolation Level）

事务隔离是控制事务间读写操作的机制，关系到并发一致性问题。

3.1 四种隔离级别

隔离级别说明会产生的问题
READ UNCOMMITTED可以读到未提交数据脏读（Dirty Read）
READ COMMITTED只能读到已提交数据不可重复读（Non-repeatable Read）
REPEATABLE READ同一事务内多次读取结果一致幻读（Phantom Read）
SERIALIZABLE串行执行事务，最高隔离级别性能最差

3.2 设置隔离级别

-- 查询当前隔离级别
SELECT @@tx_isolation;-- 设置为可重复读（默认）
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

四、常见并发问题举例

4.1 脏读（Dirty Read）

读到了其他事务未提交的数据。

4.2 不可重复读（Non-repeatable Read）

两次查询同一数据，结果不同。

4.3 幻读（Phantom Read）

事务期间查询记录总数发生变化（如新增或删除记录）。

五、MySQL中的锁机制

InnoDB 引擎支持多种锁类型，确保并发事务安全。

5.1 锁的分类

按操作类型
  • 共享锁（S锁）：允许多个事务读取，但不允许修改。
  • 排它锁（X锁）：阻止其他事务读写，独占资源。
按数据粒度
  • 表锁：锁定整张表，开销小，冲突多。
  • 行锁：只锁定操作的行，精度高，适合高并发。
按加锁方式
  • 自动加锁：MySQL 根据语句自动加锁。
  • 显式加锁：开发者手动控制加锁粒度。

5.2 显式锁语法

-- 加共享锁
SELECT * FROM products WHERE id = 1 LOCK IN SHARE MODE;-- 加排它锁
SELECT * FROM products WHERE id = 1 FOR UPDATE;

六、行级锁详解

6.1 InnoDB行锁基于索引

仅对访问到的索引记录加锁，非索引条件会退化为表锁。

-- 索引字段，行锁生效
SELECT * FROM products WHERE id = 1001 FOR UPDATE;-- 非索引字段，锁定整表
SELECT * FROM products WHERE name = 'iPhone' FOR UPDATE;

6.2 Next-Key Lock

防止幻读，锁定记录与相邻间隙。

七、死锁与解决策略

7.1 死锁产生条件

  • 两个或多个事务持有资源并互相等待对方释放

7.2 死锁案例

-- 事务A
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 1;
-- 等待事务B释放id=2
UPDATE products SET stock = stock - 1 WHERE id = 2;-- 事务B
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 2;
-- 等待事务A释放id=1
UPDATE products SET stock = stock - 1 WHERE id = 1;

7.3 InnoDB死锁检测机制

MySQL 自动检测死锁并回滚其中一个事务。

7.4 解决策略

  • 避免交叉锁定顺序，统一资源访问顺序
  • 降低事务粒度，缩短事务执行时间
  • 捕获死锁错误代码（1213），重试事务

八、锁监控与诊断

8.1 查看当前锁

SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

8.2 查看事务状态

SHOW ENGINE INNODB STATUS\G

从中可分析死锁信息、锁等待、事务运行时间等。

九、电商业务场景中的事务应用

9.1 场景：下单减库存

BEGIN;
-- 检查库存
SELECT stock FROM products WHERE id = 1001 FOR UPDATE;-- 插入订单
INSERT INTO orders(user_id, product_id) VALUES (2001, 1001);-- 扣减库存
UPDATE products SET stock = stock - 1 WHERE id = 1001;
COMMIT;

使用 FOR UPDATE 锁定库存，避免超卖。

9.2 场景：支付更新订单状态

BEGIN;
UPDATE orders SET status = 'paid' WHERE id = 3001 AND status = 'unpaid';
INSERT INTO payments(order_id, amount) VALUES (3001, 99.99);
COMMIT;

状态判断避免重复支付。

本项目适用于后台管理系统、电商用户中心、SaaS 用户模块等场景，特别适合开发者进行实战演练与面试准备。

一、项目背景与需求概述

我们将构建一个基础版的用户管理系统，具备以下业务功能：

  • 用户注册与登录
  • 用户角色与权限分配
  • 日志记录与用户状态追踪
  • 多条件用户查询与分页

涉及的核心业务对象包括：用户、角色、权限、日志等。

二、数据库建模与表结构设计

2.1 实体关系图（ER图）简要说明

  • 一位用户可以拥有多个角色（多对多）
  • 一个角色可以拥有多个权限（多对多）
  • 用户与登录日志是一对多关系

2.2 用户表（users）

CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(50) NOT NULL UNIQUE,password VARCHAR(100) NOT NULL,email VARCHAR(100),status TINYINT DEFAULT 1 COMMENT '0:禁用, 1:启用',created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

2.3 角色表（roles）

CREATE TABLE roles (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL UNIQUE,description VARCHAR(255)
);

2.4 权限表（permissions）

CREATE TABLE permissions (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL UNIQUE,code VARCHAR(50) NOT NULL UNIQUE COMMENT '用于权限标识，如 user:view'
);

2.5 用户-角色关联表（user_role）

CREATE TABLE user_role (user_id INT,role_id INT,PRIMARY KEY (user_id, role_id),FOREIGN KEY (user_id) REFERENCES users(id),FOREIGN KEY (role_id) REFERENCES roles(id)
);

2.6 角色-权限关联表（role_permission）

CREATE TABLE role_permission (role_id INT,permission_id INT,PRIMARY KEY (role_id, permission_id),FOREIGN KEY (role_id) REFERENCES roles(id),FOREIGN KEY (permission_id) REFERENCES permissions(id)
);

2.7 登录日志表（login_logs）

CREATE TABLE login_logs (id INT PRIMARY KEY AUTO_INCREMENT,user_id INT,ip_address VARCHAR(45),login_time DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY (user_id) REFERENCES users(id)
);

三、数据初始化脚本

3.1 插入初始角色与权限

INSERT INTO roles(name, description) VALUES ('admin', '系统管理员'), ('user', '普通用户');INSERT INTO permissions(name, code) VALUES
('查看用户', 'user:view'),
('新增用户', 'user:create'),
('删除用户', 'user:delete');-- 分配权限给角色
INSERT INTO role_permission(role_id, permission_id) VALUES
(1, 1), (1, 2), (1, 3), -- admin 拥有全部权限
(2, 1);                 -- user 仅能查看用户

3.2 插入测试用户

INSERT INTO users(username, password, email) VALUES
('alice', 'hashed_pwd1', 'alice@example.com'),
('bob', 'hashed_pwd2', 'bob@example.com');-- 分配角色
INSERT INTO user_role(user_id, role_id) VALUES
(1, 1), -- alice 为管理员
(2, 2); -- bob 为普通用户

四、典型查询场景实现

4.1 查询所有启用用户及其角色

SELECT u.id, u.username, r.name AS role
FROM users u
JOIN user_role ur ON u.id = ur.user_id
JOIN roles r ON ur.role_id = r.id
WHERE u.status = 1;

4.2 查询某用户拥有的所有权限

SELECT p.name, p.code
FROM users u
JOIN user_role ur ON u.id = ur.user_id
JOIN role_permission rp ON ur.role_id = rp.role_id
JOIN permissions p ON rp.permission_id = p.id
WHERE u.username = 'alice';

4.3 查询最近7天登录日志

SELECT u.username, l.ip_address, l.login_time
FROM login_logs l
JOIN users u ON l.user_id = u.id
WHERE l.login_time >= NOW() - INTERVAL 7 DAY
ORDER BY l.login_time DESC;

4.4 用户分页查询（带关键字搜索）

SELECT *
FROM users
WHERE username LIKE '%bob%'
ORDER BY created_at DESC
LIMIT 0, 10;

五、事务控制与一致性保障

在角色授权或用户注册等业务流程中，可以使用事务来确保数据完整性。

5.1 注册用户 + 分配默认角色（事务）

START TRANSACTION;INSERT INTO users(username, password, email) VALUES('charlie', 'hashed_pwd3', 'charlie@example.com');
SET @uid = LAST_INSERT_ID();
INSERT INTO user_role(user_id, role_id) VALUES(@uid, 2); -- 默认赋普通角色COMMIT;

5.2 授权失败时回滚

START TRANSACTION;-- 假设某权限不存在导致失败
INSERT INTO role_permission(role_id, permission_id) VALUES(1, 999);-- 失败时回滚
ROLLBACK;

六、索引优化与执行分析

6.1 建议加索引字段

  • users.username：用于登录验证、搜索
  • login_logs.user_id：日志查询
  • user_role.user_id / role_permission.role_id：JOIN 优化
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_user_log ON login_logs(user_id);

6.2 执行计划分析

EXPLAIN SELECT u.username, r.name FROM users u JOIN user_role ur ON u.id = ur.user_id JOIN roles r ON ur.role_id = r.id;

可查看索引是否使用、JOIN 类型、Rows 扫描数量等。

更多推荐【MySQL完整系列】：MySQL数据库从0到拿捏系列

  1. MySQL数据库零基础入门教程：从安装配置到数据查询全掌握
    关键词：安装、登录、客户端、库表基础、简单查询

  2. MySQL数据表操作全指南：建表、修改、删除一步到位
    关键词：DDL语句、字段类型、主键/外键、约束、规范设计
    聚焦表结构的创建和维护，配合真实业务建表案例（如用户表、订单表）。

  3. MySQL增删改查基础教程：熟练掌握DML语句操作
    关键词：INSERT、UPDATE、DELETE、SELECT、WHERE、ORDER BY
    实战演练日常的数据库操作命令，重点讲解查询语句的条件与排序。

  4. MySQL高级查询技巧：分组、聚合、子查询与分页
    关键词：GROUP BY、HAVING、聚合函数、LIMIT、子查询
    向中级进阶，涵盖常见报表需求与分页列表的查询实现。

  5. MySQL多表查询详解：内连接、外连接、自连接通通搞懂
    关键词：JOIN、INNER JOIN、LEFT JOIN、UNION、自连接
    深度讲解表与表之间如何通过字段建立关联并进行数据整合。

  6. MySQL索引与性能优化入门：让查询提速的秘密武器
    关键词：索引原理、EXPLAIN、慢查询、查询优化
    开启性能优化之路，适合准备应对数据量增长或面试的人。

  7. MySQL事务与锁机制详解：确保数据一致性的关键
    关键词：事务四大特性、锁类型、死锁案例、隔离级别
    涉及电商、支付系统等对数据一致性要求高的业务场景。

  8. MySQL项目实战演练：搭建用户管理系统的完整数据库结构
    关键词：业务建模、表关系设计、数据初始化、查询场景
    以实战带动知识回顾，模拟真实业务项目，整合前面所学内容。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.pswp.cn/web/82044.shtml

如若内容造成侵权/违法违规/事实不符，请联系多彩编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

UE5 Mat HLSL - Load

UE5 Mat HLSL - Load

特性Load()Sample()输入类型整数索引（int2/int3）浮点 UV 采样器状态（SamplerState）数据获取精确读取指定位置的原始数据基于 UV 插值和过滤后的数据典型用途精确计算、非过滤访问（如物理模拟）纹理贴图渲染…
阅读更多...
基于vue框架的独居老人上门护理小程序的设计r322q（程序+源码+数据库+调试部署+开发环境）带论文文档1万字以上，文末可获取，系统界面在最后面。

基于vue框架的独居老人上门护理小程序的设计r322q（程序+源码+数据库+调试部署+开发环境）带论文文档1万字以上，文末可获取，系统界面在最后面。

系统程序文件列表 项目功能：用户,护理人员,服务预约,服务评价,服务类别,护理项目,请假记录 开题报告内容 基于Vue框架的独居老人上门护理小程序的设计开题报告 一、研究背景与意义 （一）研究背景 随着社会老龄化的加剧，独居老…
阅读更多...
鸿蒙如何引入crypto-js

鸿蒙如何引入crypto-js

import CryptoJS from ohos/crypto-js 报错。 需要先安装ohom：打开DevEco，点击底部标签组（有Run, Build, Log等）中的Terminal，在Terminal下执行： ohpm install 提示 install completed in 0s 119ms&…
阅读更多...
【C++】入门基础知识（1.5w字详解）

【C++】入门基础知识（1.5w字详解）

本篇博客给大家带来的是一些C基础知识！包含函数栈帧的详解！ 🐟🐟文章专栏：C 🚀🚀若有问题评论区下讨论，我会及时回答 ❤❤欢迎大家点赞、收藏、分享！ 今日思想&#xff1…
阅读更多...
二.MySQL库的操作

二.MySQL库的操作

一.创建数据库create database 名称; 字符集和校验规则 一、字符集（Character Set） 表示数据库中可以使用哪些字符。 例如：utf8 可以存储包括中文在内的多种语言字符，gbk 更适合中文字符环境。 功能举例控制支持哪些语言字符utf…
阅读更多...
【Linux 学习计划】-- 命令行参数 | 环境变量

【Linux 学习计划】-- 命令行参数 | 环境变量

目录 命令行参数 环境变量 环境变量的本质是什么？ 相关配置文件 修改环境变量的相关操作 代码获取env —— environ 内建命令 结语 命令行参数 试想一下，我们的main函数，也是一个函数，那么我们的main函数有没有参数呢&am…
阅读更多...
具有离散序列建模的统一多模态大语言模型【AnyGPT】

具有离散序列建模的统一多模态大语言模型【AnyGPT】

第1章 Instruction 在人工智能领域、多模态只语言模型的发展正迎来新的篇章。传统的大型语言模型(LLM)在理解和生成人类语言方面展现出了卓越的能力，但这些能力通常局限于 文本处理。然而，现实世界是一个本质上多模态的环境，生物体通过视觉、…
阅读更多...
git查看commit属于那个tag

git查看commit属于那个tag

1. 快速确认commit原始分支及合入tag # git describe 213b4b3bbef2771f7a1b8166f6e6989442ca67c8 查看commit合入tag # git describe 213b4b3bbef2771f7a1b8166f6e6989442ca67c8 --all 查看commit原始分支 2.查看分支与master关系 # git show --all 0.5.67_0006 --stat 以缩…
阅读更多...
day10机器学习的全流程

day10机器学习的全流程

浙大疏锦行 1.读取数据 import pandas as pd import pandas as pd #用于数据处理和分析，可处理表格数据。 import numpy as np #用于数值计算，提供了高效的数组操作。 import matplotlib.pyplot as plt #用于绘制各种类型的图表# 设置中文字体…
阅读更多...
基于对比学习的推荐系统开发方案，使用Python在PyCharm中实现

基于对比学习的推荐系统开发方案，使用Python在PyCharm中实现

以下是一个基于对比学习的推荐系统开发方案，使用Python在PyCharm中实现。本文将详细阐述技术原理、系统设计和完整代码实现。 基于对比学习的推荐系统开发方案 一、技术背景与原理 1.1 对比学习核心思想 对比学习（Contrastive Learning）通过最大化正样本相似度、最小化负…
阅读更多...
2025山东CCPC题解

2025山东CCPC题解

文章目录 L - StellaD - Distributed SystemI - Square PuzzleE - Greatest Common DivisorG - Assembly Line L - Stella 题目来源：L - Stella 解题思路 签到题，因为给出的字母不是按顺序，可以存起来赋其值，然后在比较。 代码…
阅读更多...
某航参数逆向及设备指纹分析

某航参数逆向及设备指纹分析

文章目录 1. 写在前面2. 接口分析3. 加密分析4. 算法还原5. 设备指纹风控分析与绕过 【🏠作者主页】：吴秋霖 【💼作者介绍】：擅长爬虫与JS加密逆向分析！Python领域优质创作者、CSDN博客专家、阿里云博客专家、华为云享…
阅读更多...
Python训练营---Day41

Python训练营---Day41

DAY 41 简单CNN 知识回顾 数据增强卷积神经网络定义的写法batch归一化：调整一个批次的分布，常用与图像数据特征图：只有卷积操作输出的才叫特征图调度器：直接修改基础学习率 卷积操作常见流程如下： 1. 输入 → 卷积层 …
阅读更多...
【Netty系列】Reactor 模式 2

【Netty系列】Reactor 模式 2

目录 流程图说明 关键流程 以下是 Reactor 模式流程图，结合 Netty 的主从多线程模型，帮助你直观理解事件驱动和线程分工： 流程图说明 Clients（客户端） 多个客户端（Client 1~N）向服务端发起连…
阅读更多...
前端开发中 ＜＞ 符号解析问题全解：React、Vue 与 UniApp 场景分析与解决方案

前端开发中 ＜＞ 符号解析问题全解：React、Vue 与 UniApp 场景分析与解决方案

前端开发中 <> 符号解析问题全解&#xff1a;React、Vue 与 UniApp 场景分析与解决方案 在前端开发中&#xff0c;<> 符号在 JSX/TSX 环境中常被错误解析为标签而非比较运算符或泛型&#xff0c;导致语法错误和逻辑异常。本文全面解析该问题在不同框架中的表现及解…
阅读更多...
【Web应用】 Java + Vue 前后端开发中的Cookie、Token 和 Swagger介绍

【Web应用】 Java + Vue 前后端开发中的Cookie、Token 和 Swagger介绍

文章目录 前言一、Cookie二、Token三、Swagger总结 前言 在现代的 web 开发中&#xff0c;前后端分离的架构越来越受到欢迎&#xff0c;Java 和 Vue 是这一架构中常用的技术栈。在这个过程中&#xff0c;Cookie、Token 和 Swagger 是三个非常重要的概念。本文将对这三个词进行…
阅读更多...
投稿Cover Letter怎么写

投稿Cover Letter怎么写

Cover Letter控制在一页比较好&#xff0c;简短有力地推荐你的文章。 Dear Editors: Small objects detection in remote sensing field remains several challenges, including complex backgrounds, limited pixel representation, and dense object distribution, which c…
阅读更多...
创建型设计模式之Prototype（原型）

创建型设计模式之Prototype（原型）

创建型设计模式之Prototype&#xff08;原型&#xff09; 摘要&#xff1a; Prototype&#xff08;原型&#xff09;设计模式通过复制现有对象来创建新对象&#xff0c;避免重复初始化操作。该模式包含Prototype接口声明克隆方法、ConcretePrototype实现具体克隆逻辑&#xff…
阅读更多...
spark在执行中如何选择shuffle策略

spark在执行中如何选择shuffle策略

目录 1. SortShuffleManager与HashShuffleManager的选择2. Shuffle策略的自动选择机制3. 关键配置参数4. 版本差异（3.0+新特性）5. 异常处理与调优6. 高级Shuffle服务（CSS）1. SortShuffleManager与HashShuffleManager的选择 SortShuffleManager：默认使用，适用于大规模数据…
阅读更多...
AUTOSAR图解==＞AUTOSAR_EXP_AIADASAndVMC

AUTOSAR图解==＞AUTOSAR_EXP_AIADASAndVMC

AUTOSAR高级驾驶辅助系统与车辆运动控制接口详解 基于AUTOSAR R22-11标准的ADAS与VMC接口规范解析 目录 1. 引言2. 术语和概念说明 2.1 坐标系统2.2 定义 2.2.1 乘用车重心2.2.2 极坐标系统2.2.3 车辆加速度/推进力方向2.2.4 倾斜方向2.2.5 方向盘角度2.2.6 道路变量2.2.7 曲率…
阅读更多...
最新文章

Copyright @ 2022~2023