服务器之家：专注于VPS、云服务器配置技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - MySQL学习之事务详解

MySQL学习之事务详解

2022-12-12 15:23荣· Mysql

在数据库中 事务(transaction) 可以把多个SQL给打包到一起, 即将多个SQL语句变成一个整体, 也就是说一个事务中的所有操作要么全部成功执行, 要么完全不执行.本文主要来和大家聊聊事务的使用，需要的可以参考一下

一. 事务的业务场景

在数据库中 事务(transaction) 可以把多个SQL给打包到一起, 即将多个SQL语句变成一个整体, 也就是说一个事务中的所有操作要么全部成功执行, 要么完全不执行.

通过实际场景来理解事务:

实际生活中我们经常涉及转帐操作, 张三给李四转账2000元, 涉及到两个操作

  • 给张三的账户余额减去2000元
  • 给李四的账户余额增加2000元

这里就要考虑到这两个操作的完整性, 也就是不能出现张三的账户余额减少了2000元, 但李四的账户余额未发生变化, 这就要求上面的两个操作要么全部执行完成功转账, 要么一个都不执行双方都没有损失, 不会出现中途发生一些问题导致数据不一致的情况.

这样的一次完整操作叫做 事务(transaction), 一个事务中的所有操作要么全部成功执行, 要么完全不执行.

二. 事务的使用

事务是如何保证操作的完整性的呢?

其实事务执行中间出错了, 只需要让事务中的这些操作恢复成之前的样子即可, 这里涉及到的一个操作, 回滚(rollback).

事务处理是一种对必须整批执行的 MySQL 操作的管理机制, 在事务过程中, 除非整批操作全部正确执行, 否则中间的任何一个操作出错, 都会回滚 (rollback)到最初的安全状态以确保不会对系统数据造成错误的改动.

相关语法:

?
1
2
3
4
5
6
7
-- 开启事务
start transaction;
 
-- 若干条执行sql
 
-- 提交/回滚事务
commit/rollback;

注意:

在开启事务之后, 执行sql不会立即去执行, 只有等到commit操作后才会统一执行(保证原子性).

示例:

首先创建一个账户表并初始化数据

?
1
2
3
4
5
6
7
8
-- 创建一个账户表
create table account(
     id int primary key auto_increment,
     name varchar(20),
     money double(10,2)
);
-- 初始化账户信息
insert into account(name, money) values ('张三', 10000), ('李四', 10000);

首先看正常情况下的转账操作

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 张三账户 -2000
mysql> update account set money = money - 2000 where name = '张三';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
-- 李四账户 +2000
mysql> update account set money = money + 2000 where name = '李四';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
-- 转账成功
mysql> select * from account;
+----+--------+----------+
| id | name   | money    |
+----+--------+----------+
|  1 | 张三   |  8000.00 |
|  2 | 李四   | 12000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

如果操作中出现异常情况, 比如sql语句中所写的注释格式错误导致sql执行中断.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 先将张三和李四的账户余额恢复为10000元
update account set money = 10000 where name = '张三';
update account set money = 10000 where name = '李四';
-- 张三 -2000
mysql> update account set money = money - 2000 where name = '张三';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
-- 李四 +2000
mysql> 没加--的注释
    -> update account set money = money + 2000 where name = '李四';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '没加--的注释
update account set money = money + 2000 where name = '李四'' at line 1
-- 出现异常
mysql> select * from account;
+----+--------+----------+
| id | name   | money    |
+----+--------+----------+
|  1 | 张三   |  8000.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

观察结果发现了张三的账户少了2000元, 但李四的账户余额并没有增加, 在实际操作中这种涉及钱的操作发生这种失误可能会造成很大的损失.

为了防止这种失误的出现我们就可以使用事务来打包这些操作.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 先将张的账户余额恢复为10000元
update account set money = 10000 where name = '张三';
-- 开启事务
start transaction;
-- 张三 -2000
mysql> update account set money = money - 2000 where name = '张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
-- 李四 -2000
mysql> 没加--的注释
    -> update account set money = money + 2000 where name = '李四';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '没加--的注释
update account set money = money + 2000 where name = '李四'' at line 1
-- 预期结果
mysql> select * from account;
+----+--------+----------+
| id | name   | money    |
+----+--------+----------+
|  1 | 张三   |  8000.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

观察这里的结果发现在当前的数据库用户查询到的account表中的账户余额发生了变化，但开启了事务之后在commit之前只是临时的预操作并不会真的去修改表中的数据;

可以退出数据库再打开重新查询表中数据或者切换用户去查询去验证表中数据是否发生改变, 这里就不作演示了.

发现操作结果异常之后, 当前用户需要恢复到事务之前的状态, 即进行回滚操作.

?
1
2
3
4
5
6
7
8
9
10
11
12
-- 回滚事务
mysql> rollback;
Query OK, 0 rows affected (0.03 sec)
-- 验证回滚后的状态
mysql> select * from account;
+----+--------+----------+
| id | name   | money    |
+----+--------+----------+
|  1 | 张三   | 10000.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

如果开启事务之后发现预操作的结果是预期的效果, 此时我们就可以提交事务, 当我们提交完事务之后, 数据就是真的修改了, 也就是硬盘中存储的数据真的改变了.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
-- 张三 -2000
mysql> update account set money = money - 2000 where name = '张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
-- 李四 +2000
mysql> update account set money = money + 2000 where name = '李四';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
-- 提交事务
mysql> commit;
Query OK, 0 rows affected (0.03 sec)
 
mysql> select * from account;
+----+--------+----------+
| id | name   | money    |
+----+--------+----------+
|  1 | 张三   |  8000.00 |
|  2 | 李四   | 12000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

要注意事务也不是万能的, 不能保证你删表删库之后可以完全恢复, 只是在适量的数据和操作下使用事务可以避免一些问题.

回滚(rollback)操作, 实际上是我们把事务中的操作再进行逆操作, 前面是插入, 回滚就是删除…

这些操作是有很大开销的, 可以保存, 但不能够无限保存, 最多是将正再执行的事务保存下来, 额外的内容就不好再保存了; 数据库要是有几十亿条数据, 占据了几百G硬盘空间, 不可能去花费几个T甚至更多的空间用来记录这些数据是如何来的.

三. 事务的特性(ACID)

1. 原子性(Atomicity)

一个事务是一个不可分割的最小单位, 事务中的所有操作要么全部成功, 要么全部失败, 没有中间状态.

原子性主要是通过事务日志中的回滚日志(undo log)来实现的, 当事务对数据库进行修改时, InnoDB 会根据操作生成相反操作的 undo log, 比如说对 insert 操作, 会生成 delete 记录, 如果事务执行失败或者调用了 rollback，就会根据 undo log 的内容恢复到执行之前的状态.

事务的原子性, 也是事务的核心特性, 是事务的初心.

2. 一致性(Consistency)

事务执行之前和执行之后数据都是合法的一致性状态, 即使发生了异常, 也不会因为异常引而破坏数据库的完整性约束, 比如唯一性约束等.

事务执行前/执行后, 都得是数据合法的状态; 比如像上面的转账, 不能说转的过程出错了, 导致出现钱转丢了的情况.

3. 持久性(Durability)

事务提交之后对数据的修改是持久性的, 即使数据库宕机也不会丢失, 通过事务日志中的重做日志(redo log)来保证; 事务修改之前, 会先把变更信息预写到 redo log 中, 如果数据库宕机, 恢复后会读取 redo log 中的记录来恢复数据(回滚).

事务产生的修改, 都是会写入硬盘的, 程序重启/主机重启/掉电, 事务都可以正常工作, 保证修改是生效的.

4. 隔离性(Isolation)

这里的隔离性是指一个数据库服务器, 同时执行多个事务的时候, 事务之间的相互影响程度.

一个服务器, 可以同时给多个客户端提供服务, 这多个客户端是并发执行的关系, 多个客户端就会有多个事务, 多个事务同时去操作一个表的时候, 特别容易出现互相影响的问题.

如果隔离性越高, 就意味着事务之间的并发程度越低, 执行效率越慢, 但是数据准确性越高.

如果隔离性越低, 就意味着事务之间的并发程度越高, 执行效率越快, 但是数据准确性越低.

隔离性通过事务的隔离级别来定义, 并用锁机制来保证写操作的隔离性, 用 MVCC 来保证读操作的隔离性.

四. 事务并发异常

在实际生产环境下, 可能会出现大规模并发请求的情况, 如果没有妥善的设置事务的隔离级别, 就可能导致一些异常情况的出现，最常见的几种异常为脏读(Dirty Read), 幻读(Phantom Read)和不可重复读(Unrepeatable Read).

1. 脏读

一个事务读取到了另外一个事务没有提交的数据(读写的是同一份数据).

说详细点就是当一个事务正在访问数据并且对数据进行了修改, 而这种修改还没有提交到数据库中，与此同时时另外一个事务也访问这个数据, 然后使用了这个数据; 因为这个数据是还没有提交的数据, 那么另外一个事务读到的这个数据就是脏数据, 依据脏数据所做的操作可能是不正确的.

用一个场景例子来理解, 张三正在写代码, 李四趴在屏幕前看张三写代码, 等张三走掉之后, 李四就把他刚刚写的这段代码删掉了, 此时李四看到的这段代码就可能是一个错误的代码.

在这个场景下, 张三和李四就可以理解为两个事务, 这两个事务是完全并发没有任何限制的, 此时就会出现脏读问题.

解决脏读问题的办法, 就是降低并发性, 提高隔离性, 具体来说就是给这里的 “写操作” 加锁, 张三在写代码的时候, 李四不能看, 张三和李四约定张三代码写完后会提交到githup上, 李四去githup上去看.

当进行了写加锁的时候, 张三写的时候, 李四就不能同时去读了; 相当于降低了并发程度, 提高了隔离性. 降低了一定的效率, 但是提高了准确性.

2. 不可重复读

在同一事务中, 连续两次读取同一数据, 得到的结果不一致.

还是基于上面的场景进行理解, 上面已经约定了写加锁(张三写代码过程中, 李四不要读, 等到张三提交之后, 李四再去读).

此时张三在写代码, 张三和李四有约定, 所以此时李四在等张三把代码提交到githup上再去看代码.

过了一会儿, 张三写完了, 并将代码提交到了githup上, 李四开始读代码.

当李四正在读这个代码的时候, 张三觉得自己的代码还有不足, 于是张三动手修改, 重新提交了个版本; 导致李四读代码读了一半, 突然代码自动就变了.

这种情况就是不可重复读问题了, 解决办法是给读操作也加锁, 张三在读代码的时候, 李四不能修改.

此时这两个事务之间的并发程度进一步降低了, 隔离性又进一步提高了, 运行速度又进一步变慢了, 数据的准确性又进—步提高了.

3. 幻读

同一事务中, 用同样的操作读取两次, 得到的记录数不相同.

幻读是指当事务不是独立执行时发生的一种现象, 例如第一个事务对一个表中的数据进行了修改, 这种修改涉及到表中的全部数据行; 同时, 第二个事务也修改这个表中的数据, 这种修改是向表中插入一行新数据; 那么, 以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行, 就好象发生了幻觉一样.

再基于2中的场景进行理解, 当前已经约定了写加锁和读加锁, 即张三写代码的时候, 李四不能读; 李四读代码的时候, 张三也不能写.

此时李四在读代码, 张三虽然不能去修改李四现在正在读的这个文件, 但是李四又去新增/删除一个其他的文件, 此时, 虽然李四读的代码内容没变, 但他发现, 文件的数量变了; 这就是幻读问题了.

解决幻读问题的办法是 串行化, 也就是彻底的舍弃并发, 此时只要李四在读代码, 张三就不能进行任何操作.

五. MySQL的四个隔离级别

MySQL中有 4 种事务隔离级别, 由低到高依次为 读未提交 Read Uncommitted, 读已提交 Read Committed , 可重复读 Repeatable Read , 串行化 Serializable.

串行化的事务处理方式是最安全的, 但不能说用这个就一定好, 应该是根据实际需求去选择合适的隔离级别, 比如银行等涉及钱的场景, 就需要确保准确性, 速度慢一点也没什么; 而比如抖音,B站,快手等上面的点赞数, 收藏数就没必要那么精确了, 这个场景下速度提高一点体验会更好一些.

  脏读 不可重复读 幻读
读未提交 read uncommited √ √ √
读已提交 read commited × √ √
可重复读 repeatable read × × √
串行化 serializable × × ×

read uncommited

不做任何限制, 事务之间都是随意并发执行的; 并发程度最高，隔离性最差.

会产生脏读 + 不可重复读 + 幻读问题.

read commited

对写操作加锁, 并发程度降低, 隔离性提高.

解决了脏读问题， 仍然存在不可重复读 + 幻读问题.

repeatable read

写加锁, 读加锁, 隔离性再次提高, 并发程度再次降低.

解决了脏读 + 不可重复读问题, 仍然存在幻读问题.

这个隔离级别也是MySQL的默认隔离级别, 如果需要改的话, 可以通过MySQL的配置文件来进行调整.

serializable

严格执行串行化, 并发程度最低, 隔离性最高, 执行速度最慢.

解决了 脏读 + 不可重复读 + 幻读问题.=

到此这篇关于MySQL学习之事务详解的文章就介绍到这了,更多相关MySQL事务内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://blog.csdn.net/Trong_/article/details/128224148

  • mysql
  • 事务

    • 延伸 · 阅读

    精彩推荐
    最近更新
    编辑推荐
    阅读排行
    热门标签
    150 　 1044 　 1062 　 1053 　 1449 　 1045 　 1290 　 1819 　 2013 　 1406 　 1005 　 1864 　 1862 　 1146 　 1054 　 2006 　 1615 　 1215 　 1175 　 10048 　 2003 　 1366 　 28000 　 1577 　 1135 　 1093 　 1786 　 1067 　 1698 　 2002 　
    © 2019-2022 服务器之家(www.tuohang.net) 版权所有关于我们联系我们版权申明网站地图