mysql-210714-01

最新推荐文章于 2024-12-11 13:57:15 发布

原创最新推荐文章于 2024-12-11 13:57:15 发布 · 168 阅读

0 ·

CC 4.0 BY-SA版权

白光一

文章标签：

#mysql

mysql 专栏收录该内容

16 篇文章

订阅专栏

本文详细解析了事务在数据库中的概念，介绍了ACID特性，展示了不同隔离级别的应用，以及如何通过实例演示MySQL事务的开启、关闭与操作。了解事务如何确保数据一致性与安全性。

mysql-210714-02

事务（Transaction）

事务（Transaction）

什么是事务

TCL  事务控制语言

commit		提交事务
rollback 	回滚事务
savepoint	保存点

start transaction 	关闭自动提交机制

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


比如：银行账户转账，从A账户向B账户转账1000，需要 执行两条update语句
	update t_act set balance=balance-1000 where actno='act-101';
	update t_act set balance=balance+1000 where actno='act-102';

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

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

理解：
	事务就是 “捆绑” 一系列DML语句，
	要么同时执行成功，要么就失败。

事务原理

假设有一件事，
需要先执行insert语句，再执行update语句，最后执行delete语句，
然后结束。

开启事务机制（开始）

执行insert语句--->insert...
（这个执行完成后，把这个执行记录到数据库的操作历史当中，并不会向文件中保存一条数据，不会真正的修改硬盘上的数据。）
执行update语句--->update...
（这个执行完成后，也是记录历史操作，不会真正的修改硬盘上的数据。）
执行delete语句--->delete...
（这个执行完成后，也是记录历史操作[记录到缓存]，不会真正的修改硬盘上的数据。）

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

提交事务（commit）
会把 “开始---结束” 的一系列DML操作 中的数据持久化到硬盘上，
把 “开始---结束” 间的历史操作（历史缓存）清空。

回滚事务（rollback）
把 “开始---结束” 间的历史操作（历史缓存）清空。

事务特征

事务包括四大特征（ACID）：
	A：原子性：事务最小的工作单元，不可再分。
	C：一致性：事务必须保证多条DML语句同时成功或同时失败。
	I：隔离性：事务A与事务B之间具有隔离。
	D：持久性：最终数据必须持久化到银盘文件中去，事务才算结束。
	
Atomicity，Consistency，Isolation，Durability

关于事务的隔离级别

事务隔离性存在隔离级别，理论上隔离级别包括4个
	
	第一级别：读未提交  （read uncommitted）
		对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。
		读未提交存在 脏读现象(Dirty Read) ，表示读到了脏的数据。
		
	第二级别：读已提交  （read committed）
		对方事务提交之后的数据我方可以读取到，
		这个隔离级别解决了：脏读现象，
		读已提交隔离级别存在的问题：不可重复读。
		
	第三级别：可重复读  （repeatable read）
		这个隔离级别解决了：不可重复读，
		可重复读隔离级别存在的问题：读取到的数据时是 “幻想”。
		
	第四级别：序列化读 / 串行化读
		解决了所有问题，
		效率低，需要事务排队。
		
		
Oracle数据库默认的隔离级别是：读已提交
MySQL数据库默认的隔离级别是：可重复读

// 设置业务隔离级别
mysql> set global transaction isolation level read uncommitted;
// 查看当前隔离级别
// MySQL 8 版本 使用这条语句，查看当前隔离级别。
mysql> select @@global.transaction_isolation;

关于级别的理解

第一级别：
	两个同学操作同一个数据库，同一个表，
	同学A操作DML语句，还未提交事务，
	但是同学B能获取同学A操作（DML）的数据。
	
第二级别：
	两个同学操作同一个数据库，同一个表，
	同学A，同学B都开启MySQL命令窗口，
	同学B一直在进行DML操作，
	同学A在此期间进行DQL操作，查询结果就一直不一样。
	
第三级别：
	两个同学操作同一个数据库，同一个表，
	同学A，同学B都开启MySQL命令窗口，
	同学B一直在进行DML操作，
	同学A在此期间进行DQL操作，查询的结构始终是一开始的结果，
	不会随着同学B的操作，而影响到同学A的DQL操作的结果。
	
第四级别：
	两个同学操作同一个数据库，同一个表，
	同学A，同学B都开启MySQL命令窗口，
	如果同学A在进行DML操作，
	这时候同学B也来进行DML操作，此时不允许同学B操作，
	需要排队等，同学A操作完在轮到同学B。

事务演示

MySQL事务默认情况下是自动提交的。
只要执行任意一条DML语句则提交一次。

关闭自动提交机制
	start transaction;

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

事务自动提交

// 插入信息
mysql> insert into t_user(id,username) values(1001,'bgy1001');

// 查询信息
mysql> select * from t_user;
+------+----------+
| id   | username |
+------+----------+
| 1001 | bgy1001  |
+------+----------+

// 事务回滚
mysql> rollback;

// 回滚完还是一样，说明MySQL默认自动提交事务。
mysql> select * from t_user;
+------+----------+
| id   | username |
+------+----------+
| 1001 | bgy1001  |
+------+----------+

关闭事务自动提交（未提交事务）

// 使用 start transaction 关闭自动提交机制 
mysql> start transaction;

// 查询信息
mysql> select * from t_user;
+------+----------+
| id   | username |
+------+----------+
| 1001 | bgy1001  |
+------+----------+

// 插入记录
mysql> insert into t_user(id,username) values (1002,'bgy1002');
// 修改记录
mysql> update t_user set username='bgy1001test' where id=1001;

// 再次查询信息，但是数据没有提交，数据已被修改
mysql> select * from t_user;
+------+-------------+
| id   | username    |
+------+-------------+
| 1001 | bgy1001test |
| 1002 | bgy1002     |
+------+-------------+

// 事务回滚
mysql> rollback;

// 再次查询信息，数据恢复成原来数据
mysql> select * from t_user;
+------+----------+
| id   | username |
+------+----------+
| 1001 | bgy1001  |
+------+----------+

手动事务提交（提交事务）

// 查询信息
mysql> select * from t_user;
+------+----------+
| id   | username |
+------+----------+
| 1001 | bgy1001  |
+------+----------+

// 使用 start transaction 关闭自动提交机制 
mysql> start transaction;

// 添加数据信息
mysql> insert into t_user(id,username) values(1002,'xiaowu');
mysql> insert into t_user(id,username) values(1003,'laoli');

// 查询信息，此时事务未提交
mysql> select * from t_user;
+------+----------+
| id   | username |
+------+----------+
| 1001 | bgy1001  |
| 1002 | xiaowu   |
| 1003 | laoli    |
+------+----------+

// 提交事务
mysql> commit;

// 查询信息
mysql> select * from t_user;
+------+----------+
| id   | username |
+------+----------+
| 1001 | bgy1001  |
| 1002 | xiaowu   |
| 1003 | laoli    |
+------+----------+

// 事务回滚
mysql> rollback;

// 查询信息，发现数据回滚不去了
// rollback，只能回滚到上一级的提交点
mysql> select * from t_user;
+------+----------+
| id   | username |
+------+----------+
| 1001 | bgy1001  |
| 1002 | xiaowu   |
| 1003 | laoli    |
+------+----------+

演示读未提交（read uncommitted）

读未提交（read uncommitted）

打开两个dos窗口，连上统一个数据库，同一个表

dos01窗口


// 使用数据库mysql_test_210711_01
mysql> use mysql_test_210711_01;

// 设置业务隔离级别
mysql> set global transaction isolation level read uncommitted;

// 查看当前隔离级别
// 报错原因，我的MySQL是8版本，
// select @@global.tx_isolation这个语句适合5版本
mysql> select @@global.tx_isolation;
ERROR 1193 (HY000): Unknown system variable 'tx_isolation'

// 查看当前隔离级别
// MySQL 8 版本 使用这条语句，查看当前隔离级别。
mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-UNCOMMITTED               |
+--------------------------------+

// 退出重新进
exit;

// 进入数据库，并查看当前的隔离级别
mysql> use mysql_test_210711_01;
mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-UNCOMMITTED               |
+--------------------------------+

// 关闭自动提交事务机制
mysql> start transaction;

// 查看 dos02窗口 还没进行DML操作语句之前的表数据
mysql> select * from t_user;
+------+----------+
| id   | username |
+------+----------+
| 1001 | bgy1001  |
| 1002 | xiaowu   |
| 1003 | laoli    |
| 1004 | mingming |
+------+----------+

// 查看 dos02窗口 进行DML操作语句之后的表数据
// 发现 dos02窗口 还没提交事务， 这里就能查询到表数据。
mysql> select * from t_user;
+------+----------+
| id   | username |
+------+----------+
| 1001 | bgy1001  |
| 1002 | xiaowu   |
| 1003 | laoli    |
| 1004 | mingming |
| 1005 | fugui    |
+------+----------+

dos02窗口

// 进入数据库
mysql> use mysql_test_210711_01;

// 关闭自动提交事务机制
mysql> start transaction;

// 没有提交事务
mysql> insert into t_user(username) values('fugui');