MYSQL-初级-事务篇

已于 2024-07-30 09:09:27 修改
阅读量509 收藏 8
点赞数 13
分类专栏： MYSQL 文章标签： mysql 数据库
于 2024-07-30 09:08:55 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_51329147/article/details/140787496
版权

目录

概述

事务是一组操作集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起想系统提交或撤销请求，这么操作要么全部执行成功，要么全部失效。

为什么有事务？

假设有一个银行数据库，张三向李四转账 1000 元。
image.png
这个转账操作涉及到两个步骤：

  1. 从张三的账户中扣除1000元。
  2. 在李四的账户中增加1000元。

如果没有事务，这两个步骤可能会分开执行，可能会出现以下问题：

  1. 第一步执行成功，张三 的账户减少了1000 元，但是第二步执行失败，李四 的账户没有增加1000 元。这样会导致数据不一致，张三 的钱消失了。
  2. 如果第一步执行失败，张三 的账户没有减少1000元，但是第二步执行成功，李四 的账户增加了1000元。这样也会导致数据不一致，李四 无缘无故多了1000 元。

解决办法：
使用事务可以解决这个问题，将上述两个步骤放在一个事务中。

  1. 如果第一步执行成功而第二步执行失败，那么整个事务会回滚，即第一步的操作也会被撤销，张三 的账户不会减少1000元。
  2. 同样，如果第一步执行失败，整个事务也会回滚，不会执行第二步。这样就可以确保数据的一致性。

事务操作

  1. 查看/设置事务提交方式
SHOW VARIABLES LIKE 'autocommit';

Variable_name|Value|
-------------+-----+
autocommit   |ON   |

-- 设置自动提交事务
SET autocommit = ON;
-- 设置手动提交事务
SET autocommit = OFF;
  1. 开启事务
-- 开始事务
BEGIN;

-- 执行一系列操作
UPDATE account SET balance = balance - 1000 WHERE account_id = 1;
UPDATE account SET balance = balance + 1000 WHERE account_id = 2;

-- 提交事务
COMMIT;

-- 如果发生错误，可以回滚事务
-- ROLLBACK;

事务的四大特性（AICD）

事务的四大特性，通常被称为ACID特性，是数据库管理系统保证数据一致性和可靠性的基础。这些特性分别是：

原子性（Atomicity）

事务中的所有操作要么全部完成，要么全部不完成，不会处于中间状态。如果事务在执行过程中发生错误，系统会回滚到事务开始前的状态，就像事务从未执行过一样。

  1. 从张三的账户中扣除1000元。
  2. 在李四的账户中增加1000元。

两个步骤要么全部完成，要么全部失效。

一致性（Consistency）

事务必须使数据库从一个一致性状态转移到另一个一致性状态。这意味着事务执行结束后，数据库的完整性约束没有被破坏，数据保持一致。

转账后，张三和李四的账户总额 == 转账前，张三和李四的账户总额。

隔离性（Isolation）

事务的隔离性意味着一个事务的执行不应该受到其他事务的干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久性（Durability）

一旦事务被提交，它对数据库的更改就是永久性的。即使系统发生崩溃，事务的结果也不会丢失，因为它们已经被持久化到数据库中。
这些特性共同保证了数据库事务的可靠性，即使在并发访问和系统故障的情况下，也能保持数据的一致性和完整性。

完成转账后，张三和李四的账户数据被持久存储到磁盘。

并发事务问题

脏读（Dirty Read）

一个事务读取了另一个未提交事务修改过的数据。如果这个未提交的事务最终回滚，那么第一个事务读取的数据就是无效的。
image.png

  1. bilibli 开启事务 A，查看了张三的账户余额还有 2000 元，自动扣款年度大会员 200 元。
  2. 张三开启事务 B，查看账户余额还有 1800 元。
  3. 此时，bilibili 发生异常，自动口矿失败，事务回滚。
  4. 张三之前查看的账户余额数据就是无效的。

假设我们已经解决了脏读的问题。

不可重复读

事务 A 两次读取同一行数据，但在两次读取之间，事务 B 修改了这行数据。因此，事务 A 在两次读取中得到了不同的结果。
主要针对 update 和 delete 操作。
image.png

  1. 张三开启事务 A，第一次查看账户余额 2000 元。
  2. bilibili 开启事务 B 自动扣款年费大会员 200 元，并执行了 commit成功提交了事务。
  3. 张三再次读取余额，发现账户余额只有 1800 元，两次查看发现余额不同。

幻读

事务 A 在数据库中执行查询两次，但在两次查询之间，事务 B 插入了一些满足查询条件的新行。因此，事务 A 在两次查询中得到了不同的结果集。
主要针对 insert 操作。
image.png

  1. bilibli 管理员开启事务 A，查询用户账号信息，发现没有 Boss 的账户。
  2. 此时，Boss 开启事务 B，并成功注册了账户。
  3. 此时 bilibli 管理员想新增 Boss 账户时，发现新增失败。
  4. bilibili 管理员再次查询用户的账号信息，任然没有 Boss 的账户。

bilibli 管理员两次查询的用户数据不同，即幻读。

事务隔离级别

隔离级别脏读不可重复读幻读
Read uncommitted（未提交读）✔✔✔
Read committed（已提交读）❌✔✔
Repeatable Read**（默认）**❌❌✔
Serializable❌❌❌

查看事务隔离级别

SELECT @@tx_isolation;
-- 或者（在MySQL 5.7.20及更高版本中）
SELECT @@transaction_isolation;

|@@transaction_isolation|
|-----------------------+
|REPEATABLE-READ        |

查看当前 Session 的事务隔离级别

  1. 将事务隔离级别设置为READ UNCOMMITTED。
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  1. 将事务隔离级别设置为READ COMMITTED。
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  1. 将事务隔离级别设置为REPEATABLE READ。
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  1. 将事务隔离级别设置为SERIALIZABLE。
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

设置全局事务隔离级别

  1. 讲事务隔离级别设置为READ UNCOMMITED。
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  1. 将事务隔离级别设置为READ COMMITTED。
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
  1. 将事务隔离级别设置为REPEATABLE READ。
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  1. 将事务隔离级别设置为SERIALIZABLE。
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

证明

现在有账户余额表：

id|name|salary|
--+----+------+
 1|张三  |  2000|

解决脏读问题

模拟场景
-- bilibli开启事务                           
START TRANSACTION;                       

-- 1. 查看张三用户余额                         
SELECT * FROM test_table tt;
|id|name|salary|
|--+----+------+
| 1|张三  |  2000|

-- 2. bilibli自动扣款
UPDATE test_table tt
SET tt.salary = tt.salary - 200
WHERE name = '张三'; 
                                                -- 设置Session隔离级别为READ UNCOMMITTED
                                                SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
                                                -- 张三开启事务 
                                                START TRANSACTION;        
                                                -- 1. 张三查看账户余额
                                                SELECT * FROM test_table tt;
                                                |id|name|salary|
                                                |--+----+------+
                                                | 1|张三|1800  |    

-- 3. 自动扣款发生异常，回滚事务
ROLLBACK;    

|id|name|salary|
|--+----+------+
| 1|张三  |  2000|
  1. 张三查询的账户余额并不是真实余额，发生了脏读。
解决
  1. 将 MYSQL 默认的隔离级别设置为 READ COMMITED。
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  1. 执行同样的操作
START TRANSACTION;
SELECT * FROM test_table tt;
COMMIT;

// output
|id|name|salary|
|--+----+------+
| 1|张三  |  2000|

解决不可重复读

模拟场景
-- 设置Session隔离级别为 READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 1. 张三开启事务
START TRANSACTION;   
-- 2. 张三查看余额   
SELECT * FROM test_table tt;
|id|name|salary|
|--+----+------+
| 1|张三  |  2000|

                                                            -- 3. bilibli开启事务                           
                                                            START TRANSACTION;                       
                                                            -- 4. bilibli自动扣款
                                                            UPDATE test_table tt
                                                            SET tt.salary = tt.salary - 200
                                                            WHERE name = '张三'; 
                                                            -- 5. 提交事务
                                                            COMMIT;
-- 6. 张三再次查看余额                                        
SELECT * FROM test_table tt;
|id|name|salary|
|--+----+------+
| 1|张三  |  1800|

-- 7. 提交事务
COMMIT;
  1. 张三两次查询的账户余额不同，发生了不可重复读。
解决
  1. 将 MYSQL 默认的隔离级别设置为 REPEATABLE READ。
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  1. 执行同样的操作
-- 1. 张三开启事务
START TRANSACTION;   
-- 2. 张三查看余额   
SELECT * FROM test_table tt;
|id|name|salary|
|--+----+------+
| 1|张三  |  2000|

                                                            -- 3. bilibli开启事务                           
                                                            START TRANSACTION;                       
                                                            -- 4. bilibli自动扣款
                                                            UPDATE test_table tt
                                                            SET tt.salary = tt.salary - 200
                                                            WHERE name = '张三'; 
                                                            -- 5. 提交事务
                                                            COMMIT;
-- 6. 张三再次查看余额                                        
SELECT * FROM test_table tt;
|id|name|salary|
|--+----+------+
| 1|张三  |  2000|

-- 7. 提交事务
COMMIT;

幻读

模拟场景
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 1.  bilibli 管理员开启事务
START TRANSACTION;   
-- 2. bilibli 管理员查看账号信息
SELECT * FROM test_table tt;
|id|name|salary|
|--+----+------+
| 1|张三  |  2000|
                                                            SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
                                                            -- 3.Boss 开启事务                           
                                                            START TRANSACTION;                       
                                                            -- 4. Boss 注册bilibli账号
                                                            INSERT INTO test_table (id,name,salary)
                                                            VALUES(2,'Boss',100000);
                                                            -- 5. 提交事务
                                                            COMMIT;
-- 6. bilibli 管理员新增 Boss 账号                                    
INSERT INTO test_table (id,name,salary)
VALUES(2,'Boss',100000);

// 发生报错，主键冲突，新增失败
// SQL 错误 [1062] [23000]: Duplicate entry '2' for key 'test_table.PRIMARY'

-- 7. bilibli 管理员再次查看账号信息，还是没有
SELECT * FROM test_table tt;
|id|name|salary|
|--+----+------+
| 1|张三  |  2000|
  1. bilibli 管理员新增失败后，再次查看，还是没有 Boss 的账号。
解决
  1. 将 MYSQL 默认的隔离级别设置为 SERIALIZABLE。
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  1. 再次执行
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 1.  bilibli 管理员开启事务
START TRANSACTION;   
-- 2. bilibli 管理员查看账号信息
SELECT * FROM test_table tt;
|id|name|salary|
|--+----+------+
| 1|张三  |  2000|
                                                            SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
                                                            -- 3.Boss 开启事务                           
                                                            START TRANSACTION;                       
                                                            -- 4. Boss 注册bilibli账号
                                                            INSERT INTO test_table (id,name,salary)
                                                            VALUES(2,'Boss',100000);

                                                            // 发生阻塞，无法新增数据。。。。。。。。。。
                                                      
-- 5. bilibli 管理员新增 Boss 账号                                    
INSERT INTO test_table (id,name,salary)
VALUES(2,'Boss',100000);

-- 6. bilibli 管理员再次查看账号
SELECT * FROM test_table tt;
|id|name|salary|
|--+----+------+
| 1|张三  |  1800|
| 2|Boss|100000|

                                                            // 结束阻塞，新增报错

Reference

  1. 黑马程序员 MySQL数据库入门到精通，从mysql安装到mysql高级、mysql优化全囊括
RainbowJie1
关注
  • 13
    点赞
  • 踩
  • 8
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL-自用
Xshenj的博客
05-03 570
MySQL初级——自用
MySQL学习笔记-初级操作
12-14
这些只是MySQL初级操作的基础，随着学习的深入，你还将接触到视图、存储过程、触发器、用户权限管理等更高级的主题。通过实践和不断学习，你将能够熟练掌握MySQL，并在实际项目中发挥重要作用。
MySQL-初级
InterestingCat的博客
08-29 1283
MySQL初级 1、数据库安装 2、SQL的DDL、DML、DQL、DCL操作 3、SQL语句常用函数介绍即用例 4、约束 5、多表查询 6、事务
MySQL初级
fire57的博客
07-20 528
文章目录1 数据库概述2 MySQL基本指令2.1 连接数据库2.2 数据库操作2.3 数据表操作2.4 数据操作，增删改查2.5 退出数据库3 MySQL中的数据类型3.1 数值类型3.2 字符串类型3.3 时间和日期类型4 MySQL中的类型约束和运算4.1 表的字段约束4.2 MySQL的运算符4.3 主键5 MySQL中的表引擎和字符集5.1 表引擎5.2 MyISAM和InnoDB表引擎的区别5.3 字符集5.4 MySQL中的uft8和utf8mb46 MySQL中的数据表操作6.1 创建表6.
Mysql初级学习-SQL语句大全
zhangxuzzu的博客
06-04 75
通俗地被称为存储数据的仓库，只是这个仓库是存储在计算机存储设备上的，并且其所存储的数据是按一定的格式进行存储。若从严格意义上讲，所谓数据库是指长期储存在计算机中的有组织的、可共享的数据集合，且数据库中的数据按一定的数据模型组织、描述和存储，具有较小的冗余度、较高的数据独立性，系统易于扩展，并可以被多个用户共享。永久存储有组织和可共享。常见的关系型数据库有mysql 、SQL Server、Oracle、Sybase、DB2等##显示当前MySQL中的数据库列表##显示指定名称的数据库。
MySQL数据库-MySQL基础-上篇-通用语法
rc4gyyc的博客
03-09 976
黑马程序员MySQL数据库。 DataBase (DB)存储数据的仓库，数据是有组织的进行存储数据库管理系统 DataBase Management System(DBMS)操纵和管理数据库的大型软件操作关系型数据库的编程语言，定义了一套操作系统型数据库统一标准可以通过sql操作数据库管理系统从而操作数据库主流的关系型数据库管理系统MySQL数据库MySQL安装1.MySQL下载及安装MySQL社区版2.MySQL启动3.MySQL客户端连接MySQL自带的客户端命令行4.MySQL数据模型。
【MySQL】深入理解MySQL事务（上篇）
平淡中造就不平凡
01-13 753
在实际业务场景中，如何保证操作的完整性是一个重要的议题，依次执行一系列逻辑强关联的操作，如果在中途发生了错误，就很有可能导致数据的错乱。设想一下在 ATM 取钱的场景，当我们取出一千元的时候，ATM 会在清点完成后一次性吐出一千元，而不是分十次每次吐出一百元，这就是为了保证操作的完整性，要么完整的取走一千元，扣除余额，要么一分钱都没有取走，余额不变，而不会出现中途机器故障导致数据不一致的情况。这样的一次完整操作叫做事务 transaction一个事务中的所有操作要么全部成功执行，要么完全不执行。
1.mysql初级篇
JavanLu的博客
09-02 131
Mysql初级知识整体大纲第一章：基本操作指令操作指令查看mysql服务端版本SQL语言分类第二章：DQL语言操作 整体大纲 第一章：基本操作指令 操作指令 进入mysql： mysql -u root -p 输入密码： 查看mysql中有哪些个数据库: show databases; 新建一个数据库: create database 数据库名 如： create database book; 选择一个数据库: use 数据库名称 如：use test; 查询数据表： show tables;
MySql-面试题
lalajh的博客
07-13 834
范式是数据库设计时遵循的一种规范，不同的规范要求遵循不同的范式。第一范式(1NF)：属性不可分割，即每个属性都是不可分割的原子项。(实体的属性即表中的列)第二范式(2NF)：满足第一范式；且不存在部分依赖，即非主属性必须完全依赖于主属性。(主属性即主键；完全依赖是针对于联合主键的情况，非主键列不能只依赖于主键的一部分)第三范式(3NF)：满足第二范式；且不存在传递依赖，即非主属性不能与非主属性之间有依赖关系，非主属性必须直接依赖于主属性，不能间接依赖主属性。（A -> B, B ->C, A -> C）参考
PHP MySQL初级教程.doc
11-28
【PHP MySQL初级教程】 PHP（PHP: Hypertext Preprocessor）是一种广泛使用的开源服务器端脚本语言，特别适合于Web开发，能够嵌入到HTML中，用于创建动态交互性的网站。PHP的设计目标是让开发人员快速地编写出动态...
MySQL初级教程
12-25
这篇初级教程将引导你了解MySQL的基础知识，包括如何下载和安装MySQL，以及在Java环境中连接数据库。 首先，MySQL的下载和安装是入门的第一步。你可以从MySQL官方网站获取最新版本的MySQL社区服务器。确保选择适合...
MySQL初级考试试题及答案.docx
11-16
MySQL初级考试试题及答案 本资源摘要信息中涵盖了 MySQL 初级考试的试题及答案，涵盖了 MySQL 的基础知识点，包括聚合函数、SQL 语句、视图、游标、索引、事务处理、数据操纵语句等。 1. 聚合函数：MAX 函数用于求...
MySQL-Front.rar
08-13
MySQL-Front是一款直观、易用的图形用户界面（GUI）工具，专为MySQL数据库设计，便于用户进行数据管理、查询、备份和导入导出等工作，尤其适合初级学员学习和使用。 1. **MySQL的基本概念**： - 数据库（Database...
深入剖析：GaussDB与MySQL在COUNT查询中的并行化技术
2401_85789772的博客
07-25 697
COUNT查询是数据库中用于统计记录数的基本操作，它在数据分析、报告生成等场景中非常常见。MySQL 5.7及以上版本开始支持查询的并行执行，可以根据系统配置和查询特性自动或手动启用并行执行。
mysql定时备份
最新发布
yandong634的专栏
07-25 336
注意：导出需要在数据库的my.ini文件中配置数据库用户和密码。右键【任务计划程序库】-》【创建基本任务】或【创建任务】在C盘新建文件夹【mysql-backup】和【bk】在【bk】下新建文件【backup.bat】在windows里面搜索【任务计划程序】
详解Mysql InnoDB引擎 04
程序员进阶之路
07-24 752
InnoDB 是MySQL数据库管理系统的一个存储引擎，它为MySQL提供了事务处理、外键约束和并发控制等关系型数据库管理系统（RDBMS）中重要的功能。以下是关于InnoDB存储引擎的一些关键特点和特性：
Mysql的索引
2401_84592402的博客
07-24 553
索引是一个排序的列表，在列表当中存储索引的值以及索引对应数据所在的物理行索引值和数据是一个映射关系。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值