【MySQL】二十七、TCL——事务

最新推荐文章于 2023-06-04 19:06:54 发布

pangpd

最新推荐文章于 2023-06-04 19:06:54 发布

阅读量149

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/weixin_43519707/article/details/113916079

版权

MySQL 专栏收录该内容

36 篇文章 7 订阅

订阅专栏

文章目录

1. 什么是事务？
3. 事务的原理
2. 事务的特性
3. 事务之间的隔离性（四个级别）
4. 演示事务

1. 什么是事务？

事务是一个完整的业务逻辑单元，不可再分。

可以理解为，多条SQL语句捆绑在一起，和实际的业务逻辑有关系。这些SQL语句要么同时成功，要么同时失败。

比如：银行账户转账，从A账户想B账户转账10000，需要执行两条update语句。

update t_act set balance = balance - 10000 where acton = 'act-001';
update t_act set balance = balance + 10000 where acton = 'act-002';

在这里插入图片描述

以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。

要想保证以上的两条DML语句同时成功或者同时失败，那么就需要使用数据库的“事务机制”。

注意：

（1）只有DML（insert ，delete，update）因为它们这三个语句都是和数据库表当中的“数据”相关的。事务的存在是为了保证数据的完整性，安全性。

（2）所有的业务都能使用1条DML语句搞定，还需要事务机制吗？

不需要事务。但实际情况不是这样的，通常一个“事儿（事务【业务】）”需要多条DML语句共同联合完成。**

3. 事务的原理

假如一个事，需要先执行insert，再执行一条update，最后执行一条delete，这个事才算完成。

开启事务机制（开始）：

执行insert语句–> insert … （这个执行成功之后，把这个执行记录到数据库的操作历史【记录到缓存中】，并不会向文件中保存一条数据，不会真正的修改硬盘上的数据）
执行update语句–> update… （这个执行也是记录一下操作历史，不会真正的修改硬盘上的数据）
执行delete语句–> delete… （这个执行也是记录一下操作历史，不会真正的修改硬盘上的数据）

提交事务或者回滚事务（结束）

提交和回滚都会使事务结束，如果是提交，将会把操作历史执行在硬盘上，让硬盘上的数据彻底改变，然后所有的历史操作就会清空。如果是回滚事务，也会把历史记录清空，只是不会把让记录改变，不和硬盘打交道。

提交事务：commit；

回滚事务：rollback；

设置保存点：savepoint；

2. 事务的特性

事务包括四大特性：ACID

A：原子性：事务是最小的工作单元，不可再分。

C：一致性：事务必须保证多条DML语句同时成功或者同时失败。

I：隔离性：事务A与事务B之间具有隔离。

D：持久性：持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束。

3. 事务之间的隔离性（四个级别）

事务隔离性存在隔离级别，理论上隔离级别包括4个：

第一级别：读未提交（read uncommitted）

对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。
读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。
脏读：指一个事务读取了另外一个事务未提交的数据。
第二级别：读已提交（read committed）

对方事务提交之后的数据我方可以读取到。
这种隔离级别解决了：脏读现象没有了。
读已提交存在的问题是：不可重复读（不能读到相同的数据）。

不可重复读：指在一个事务内读取表中的某一行数据，多次读取结果不同。

不可重复读和脏读的区别是，脏读是读取前一事务未提交的脏数据，不可重复读是重新读取了前一事务已提交的数据。

第三级别：可重复读（repeatable read）

这种隔离级别解决了：不可重复读问题。
这种隔离级别存在的问题是：读取到的数据是幻象。
第四级别：序列化读/串行化读（serializable）

解决了所有问题。
效率低。需要事务排队。

oracle数据库默认的隔离级别是：读已提交。

mysql数据库默认的隔离级别是：可重复读。

4. 演示事务

MySQL事务默认情况下是自动提交的。

（什么是自动提交：只要执行任意一条DML语句则提交一次）

怎么关闭自动提交？start transaction;

（1）准备表

drop table if exists t_user;
create table t_user(
id int primary key auto_increment,
username varchar(255)
);

4.1 MySQL中的事务是支持自动提交的

MySQL中的事务是支持自动提交的，只要执行一条DML，则提交一次。

insert into t_user (username) values('zs');

select * from t_user;

+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
+----+----------+

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

即使使用了rollback，这里也是没有办法回滚了，因为MySQL默认提交了事务。已经将上面的插入语句持久化到了硬盘中：

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
+----+----------+

4.2 演示：使用start transaction：关闭自动提交机制。

当前t_user表中仅有一条数据。

start transaction;

insert into t_user (username) values ('lisi');
insert into t_user(username) values('wangwu');

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  2 | lisi     |
|  3 | wangwu   |
+----+----------+

回滚：

rollback;

再次查询：

select * from t_user;

+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
+----+----------+

再如：

start transaction;

insert into t_user(username) values('wangwu');
insert into t_user(username) values('rose');
insert into t_user(username) values('jack');

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  4 | wangwu   |
|  5 | rose     |
|  6 | jack     |
+----+----------+

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  4 | wangwu   |
|  5 | rose     |
|  6 | jack     |
+----+----------+
4 rows in set (0.00 sec)

回滚：

rollback;

在rollback之前，commit已经将数据持久化硬盘当中了，这时的rollbac只能回滚到上次的提交点。此时的rollback就没有用了。

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  4 | wangwu   |
|  5 | rose     |
|  6 | jack     |
+----+----------+
4 rows in set (0.00 sec)

4.3 演示：读未提交（READ UNCOMMITTED）

设置全局的隔离级别是：读未提交（read uncommitted）

set global transaction isolation level read uncommitted;

查看事务的全局隔离级别：

select @@global.tx_isolation; // MySQL5.7
select @@transaction_isolation; // MySQL8

+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-UNCOMMITTED      |
+-----------------------+

（1）登录数据库，使用bjpowernode表；

use bjpowernode;

（2）设置全局的隔离级别是：读未提交；

set global transaction isolation level read uncommitted;

（3）退出数据库；

quit;

（4）打开两个命令窗口A和B，使用同一个账户登录MySQL，使用bjpowernode数据库，都设置关闭自动提交机制。

use bjpowernode;
start transaction;

（5）在A窗口中，准备一张表：

create table t_user(
id int auto_increment,
name varchar(255),
primary key(id));

（6）在A窗口中，插入8条数据：

insert into t_user(name) values('king')

+----+------+
| id | name |
+----+------+
|  1 | king |
|  2 | king |
|  3 | king |
|  4 | king |
|  5 | king |
|  6 | king |
|  7 | king |
|  8 | king |
+----+------+

（7）在B 窗口中，插入一条数据，name为’SMITH’，这里我们不要使用commit命令提交；

insert into t_user (name) values('SMITH');

（8）在A窗口中，我们查询t_user表记录：

+----+-------+
| id | name  |
+----+-------+
|  1 | king  |
|  2 | king  |
|  3 | king  |
|  4 | king  |
|  5 | king  |
|  6 | king  |
|  7 | king  |
|  8 | king  |
|  9 | SMITH |
+----+-------+
9 rows in set (0.00 sec)

我们发现，在B窗口中，即使没有使用commit提交事务，仍然可以查询到SMITH，这就是读未提交。

4.4 演示：读已提交（READ COMMITTED）

（1）登录MySQL，使用bjpowernode表，设置全局隔离级别：读已提交；

use bjpowernode;
set global transaction isolation level read committed;

（2）退出当前数据库；

quit;

（3）打开两个命令窗口A和B，使用同一个账户登录MySQL，使用bjpowernode数据库，设置关闭自动提交机制。

use bjpowernode;
set transaction;

(4) 当前t_user表中的数据；

+----+------+
| id | name |
+----+------+
|  1 | king |
|  2 | king |
|  3 | king |
|  4 | king |
|  5 | king |
|  6 | king |
|  7 | king |
|  8 | king |
+----+------+

（5）在B窗口中，插入一条name为’test’的记录：

insert into t_user (name) values('test');

（6）在A窗口中，查询t_user表中的数据：

select * from t_user;

+----+------+
| id | name |
+----+------+
|  1 | king |
|  2 | king |
|  3 | king |
|  4 | king |
|  5 | king |
|  6 | king |
|  7 | king |
|  8 | king |
+----+------+

我们发现还是原来的8行记录；

（7）如果这个时候，我们将B窗口中插入记录进行提交；

commit;

（8）这个时候，再次在A窗口中，查询t_user表中的数据：

select * from t_user;

+----+------+
| id | name |
+----+------+
|  1 | king |
|  2 | king |
|  3 | king |
|  4 | king |
|  5 | king |
|  6 | king |
|  7 | king |
|  8 | king |
| 10 | test |
+----+------+

我们就可以查询到了name为test的记录。只有当B窗口的数据进行commit后，在A窗口中才能查询到插入的记录，这就是读已提交。

4.5 演示：可重复读（REPEATABLE READ）

（1）登录MySQL，使用bjpowernode表，设置全局隔离级别：可重复读；

use bjpowernode;
set global transaction isolation level repeatable read;

（2）退出当前数据库；

quit;

（3）打开两个命令窗口A和B，使用同一个账户登录MySQL，使用bjpowernode数据库，设置关闭自动提交机制。

use bjpowernode;
set transaction;

(4) 当前t_user表中的数据；

+----+------+
| id | name |
+----+------+
|  1 | king |
|  2 | king |
|  3 | king |
|  4 | king |
|  5 | king |
|  6 | king |
|  7 | king |
|  8 | king |
| 10 | test |
+----+------+

（5）在B窗口中，删除表t_user中的数据，并提交;

delete from t_user;
commit;

在B窗口中查询，发现已经没数据了：

mysql> select * from t_user;
Empty set (0.00 sec)

（6）在A窗口中，查询t_user表中的数据：

select * from t_user;

+----+------+
| id | name |
+----+------+
|  1 | king |
|  2 | king |
|  3 | king |
|  4 | king |
|  5 | king |
|  6 | king |
|  7 | king |
|  8 | king |
| 10 | test |
+----+------+

我们在A窗口中，发现还有原来的数据。实际上这里读到的数据是备份数据，这是一种幻读；

4.6 演示：串行化读（SERIALIZABLE）

（1）登录MySQL，使用bjpowernode表，设置全局隔离级别：串行化读；

use bjpowernode;
set global transaction isolation level serializable;

（2）退出当前数据库；

quit;

（3）打开两个命令窗口A和B，使用同一个账户登录MySQL，使用bjpowernode数据库，设置关闭自动提交机制。

use bjpowernode;
set transaction;

(4) 当前t_user表中的数据；

+----+------+
| id | name |
+----+------+
|  1 | king |
|  2 | king |
|  3 | king |
|  4 | king |
|  5 | king |
|  6 | king |
|  7 | king |
|  8 | king |
| 10 | test |
+----+------+

（5）在A窗口中，插入一条数据：

insert into t_user (name) values('hehe');

（6）在B窗口中，查询表t_user 中的数据：

在这里插入图片描述

然而，这个时候，查询卡住了！！这是因为，A窗口中的事务没有提交！

（7）在A窗口中，提交事务；

commit

此时，在B窗口中立即出现了查询结果，并且出现了hehe记录。

mysql> select * from t_user;
+----+------+
| id | name |
+----+------+
|  1 | king |
|  2 | king |
|  3 | king |
|  4 | king |
|  5 | king |
|  6 | king |
|  7 | king |
|  8 | king |
| 10 | test |
| 11 | hehe |
+----+------+
10 rows in set (2.16 sec)

这便是串行化读，这种方式安全，但是效率低。

pangpd

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
【MySQL】二十七、TCL——事务

1. 什么是事务？事务是一个完整的业务逻辑单元，不可再分。比如：银行账户转账，从A账户想B账户转账10000，需要执行两条update语句。update t_act set balance = balance - 10000 where acton = 'act-001';update t_act set balance = balance + 10000 where acton = 'act-002';以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。要想保证以上
复制链接

扫一扫