MySQL事务

一、事务的概念和特性

1.1.事务

事务：事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态；如果单元中的所有SQL语句均执行成功，则事物被顺利执行。

1.2.MySQL中的存储引擎

概念：在MySQL中的数据用各种不同的技术存储在文件（或内存）中。

通过show engines；来查看mysql支持的存储引擎。

在mysql中用的最多的存储引擎有：innodb， myisam ,memory 等。其中innodb支持事务，而 myisam、memory等不支持事务

1.3.事务的特点

事务的ACID(acid)属性

原子性（Atomicity）原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
隔离性（Isolation）事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（Durability）持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响

1.4.事务的使用

以第一个DML 语句的执行作为开始

以下面的其中之一作为结束:

COMMIT 或ROLLBACK 语句
DDL 或DCL 语句（自动提交）
用户会话正常结束
系统异常终止

1.5.事务的创建

隐式事务：事务没有明显的开启和结束的标记

比如insert、update、delete语句
delete from 表 where id =1;

显式事务：事务具有明显的开启和结束的标记

前提：必须先设置自动提交功能为禁用
set autocommit=0;
# 查看是否开启事务
SHOW VARIABLES LIKE 'autocommit';

1.6.事务的创建步骤

步骤1：开启事务

set autocommit=0;
start transaction;可选的

步骤2：编写事务中的sql语句(select insert update delete)

语句1;
语句2;
...

步骤3：结束事务

commit;提交事务
rollback;回滚事务

savepoint 节点名;设置保存点

二、事务的隔离级别

4.1.数据库的隔离级别是什么？

对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:

脏读: 对于两个事务T1, T2, T1 读取了已经被T2 更新但还没有被提交的字段. 之后, 若T2 回滚, T1读取的内容就是临时且无效的.
不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了.
幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后T2 在该表中插入了一些新的行. 之后, 如果T1 再次读取同一个表, 就会多出几行.

数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题.

一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱.

4.2.事务隔离级别

数据库提供的4 种事务隔离级别:

说明：

Oracle 支持的2 种事务隔离级别：READ COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED
Mysql 支持4 种事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE READ

4.3.在MySql中设置隔离级别

每启动一个mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个全局变量@@tx_isolation, 表示当前的事务隔离级别.
查看当前的隔离级别:

# 老版本使用
SELECT @@tx_isolation;
# 5.7.20 版本之后，用的是 transaction_isolation。 
SELECT @@transaction_isolation;

设置当前mySQL 连接的隔离级别:

-- 设置当前mysql连接的隔离级别
set session transaction isolation level 隔离级别的英文名称;
案例：
set transaction isolation level read committed;

设置数据库系统的全局的隔离级别:

-- 设置数据库系统全局的隔离级别
set global transaction isolation level 隔离级别的英文名称;
案例：
set global transaction isolation level read committed;

4.4.READ UNCOMMITTED（读未提交数据）演示

⑴.修改隔离级别

# 修改隔离级别为READ UNCOMMITTED
set session transaction isolation level READ UNCOMMITTED;

# 查看修改后的隔离级别 5.7.20 版本之后，用的是 transaction_isolation。 
SELECT @@transaction_isolation;

⑵.查看表中数据

⑶.事务1：在一个会话中修改数据，但是并不提交数据

#开始事务
SET autocommit = 0;

#张三的账户少 500 元，李四的账户多 500 元
UPDATE bank SET currentMoney = currentMoney-500 WHERE customerName = '张三';
UPDATE bank SET currentMoney = currentMoney+500 WHERE customerName = '李四';

⑷.事务2：在另一个会话中创建事务然后查询数据

# 修改隔离级别为READ UNCOMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

# 查看修改后的隔离级别 5.7.20 版本之后，用的是 transaction_isolation。 
SELECT @@transaction_isolation;

#开始事务
SET autocommit = 0;

SELECT * FROM bank;

查询结果如下：会发现第一个会话事务中并没有提交事务，但是事务2中却读取出来了事务1修改后的内容，出现了脏数据，也就是脏读的效果

⑸.这时候事务1，回滚数据，而在事务2中查看数据，会发现两次数据读取出现了不一致，就会出现不可重复读的现象

事务1：回滚数据

ROLLBACK;

事务2：查看数据

之前步骤4查看的结果

事务2本次查看，会发现事务2同一个事务中前后两次的数据不一致，这个就是不可重复读

4.5.READ COMMITED（读已提交数据）

⑴.修改隔离级别

# 修改隔离级别为READ COMMITTED
set session transaction isolation level READ COMMITTED;

# 查看修改后的隔离级别 5.7.20 版本之后，用的是 transaction_isolation。 
SELECT @@transaction_isolation;

⑵.查看表中数据

⑶.事务1：在一个会话中修改数据，但是并不提交数据

#开始事务
SET autocommit = 0;

#张三的账户少 500 元，李四的账户多 500 元
UPDATE bank SET currentMoney = currentMoney-500 WHERE customerName = '张三';
UPDATE bank SET currentMoney = currentMoney+500 WHERE customerName = '李四';

⑷.事务2：在另一个会话中创建事务然后查询数据

# 修改隔离级别为READ READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

# 查看修改后的隔离级别 5.7.20 版本之后，用的是 transaction_isolation。 
SELECT @@transaction_isolation;

#开始事务
SET autocommit = 0;

SELECT * FROM bank;

查询结果如下：会发现第一个会话事务没有提交事务，但是事务2中并没有读取出来了事务1修改后的内容，也就是没有出现脏数据，READ COMMITED（读已提交数据）

也就是脏读的效果

⑸.这时候事务1，提交事务，而在事务2中查看数据，会发现两次数据读取出现了不一致，就会出现不可重复读的现象

事务1：提交事务

# 提交事务
COMMIT;

事务2：查看数据

之前步骤4查看的结果

事务2本次查看，会发现事务2同一个事务中前后两次的数据不一致，这个就是不可重复读，也就是幻读

4.6.REPEATABL READ（可重复读）

4.6.1.脏读

⑴.修改隔离级别

# 修改隔离级别为REPEATABLE READ
set session transaction isolation level REPEATABLE READ;

# 查看修改后的隔离级别 5.7.20 版本之后，用的是 transaction_isolation。 
SELECT @@transaction_isolation;

⑵.查看表中数据

⑶.事务1：在一个会话中修改数据，但是并不提交数据

#开始事务
SET autocommit = 0;

#张三的账户少 500 元，李四的账户多 500 元
UPDATE bank SET currentMoney = currentMoney-500 WHERE customerName = '张三';
UPDATE bank SET currentMoney = currentMoney+500 WHERE customerName = '李四';

⑷.事务2：在另一个会话中创建事务然后查询数据

# 修改隔离级别为READ READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

# 查看修改后的隔离级别 5.7.20 版本之后，用的是 transaction_isolation。 
SELECT @@transaction_isolation;

#开始事务
SET autocommit = 0;

SELECT * FROM bank;

查询结果如下：会发现第一个会话事务没有提交事务，但是事务2中并没有读取出来了事务1修改后的内容，也就是没有出现脏读

4.6.2.不可重复读取

⑴.这时候事务1，提交事务，而在事务2中查看数据，会发现两次数据读取出现了不一致，就会出现不可重复读的现象

事务1：提交事务

# 提交事务
COMMIT;

事务2：查看数据

之前步骤4查看的结果

事务2本次查看，会发现事务2同一个事务中前后两次的数据一致，没有出现不可重复

⑵.那在什么情况，事务2才可以看到正确的数据呢？答案就是事务2提交之后查看，数据就正确了

4.6.3.幻读

⑴.先查看数据，发现只有2条数据，重新开一个事务3，在事务3中，只要开始事务即可，不要修改的SQL

# 修改隔离级别为REPEATABLE READ
set session transaction isolation level REPEATABLE READ;

# 查看修改后的隔离级别 5.7.20 版本之后，用的是 transaction_isolation。 
SELECT @@transaction_isolation;

#开始事务
SET autocommit = 0;

⑵.在事务4中插入一条数据，然后提交事务

# 修改隔离级别为REPEATABLE READ
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

# 查看修改后的隔离级别 5.7.20 版本之后，用的是 transaction_isolation。 
SELECT @@transaction_isolation;

#开始事务
SET autocommit = 0;

# 插入数据
INSERT INTO bank VALUES ('王五',450);

# 提交事务
COMMIT;

⑶.在事务3中执行事务然后提交事务，如下：

#将所有的数据都设置为1200
UPDATE bank SET currentMoney = 1200;

# 提交事务
COMMIT;

发现修改了3条数据，事务4插入的数据也被修改了，就是幻读

4.7.SERIALIZABLE（串行化）

SERIALIZABLE（串行化）是最高级别，可以解决上面的所有问题（脏读、幻读、不可重复读），但是也导致了性能的低下，所以这里就不在演示了，

以幻读为例进行演示

⑴.先查看数据，发现只有2条数据，重新开一个事务3，在事务3中，只要开始事务即可，不要修改的SQL

# 修改隔离级别为SERIALIZABLE
set session transaction isolation level SERIALIZABLE;

# 查看修改后的隔离级别 5.7.20 版本之后，用的是 transaction_isolation。 
SELECT @@transaction_isolation;

#开始事务
SET autocommit = 0;

⑵.在事务4中插入一条数据，然后提交事务

# 修改隔离级别为REPEATABLE READ
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

# 查看修改后的隔离级别 5.7.20 版本之后，用的是 transaction_isolation。 
SELECT @@transaction_isolation;

#开始事务
SET autocommit = 0;

# 插入数据
INSERT INTO bank VALUES ('王五',450);

# 提交事务
COMMIT;

会发现直接到了执行查询语句就出现了阻塞，事务3提交事务了，事务4才能执行插入语句，这样就避免了幻读

三、事务的案例演示

在银行业务中，有一条记账原则，即有借有贷，借贷相等。为了保证这种原则，每发生一笔银行业务，就必须确保会计账目上借方科目和贷方科目至少各记一笔，并且这两笔账要么同时成功，要么同时失败。如果出现只记录了借方科目，或者只记录了贷方科目的情况，就违反了记账原则。会出现记错账的情况。在银行的日常业务中，只要是同一银行（如都是中国农业银行，简称农行），一般都支持账户间的直接转账。因此，银行转账操作往往会涉及两个或两个以上的账户。在转出账户的存款减少一定金额的同时，转入账户的存款就要增加相应的金额。

下面，在 MySQL 数据库中模拟一下上述提及的转账问题。
⑴.假如要从张三的账户直接转账 500 元到李四的账户。首先需要创建账户表，存放用户张三和李四的账户信息。创建账户表和插入数据的 SQL 语句和运行结果如下所示：

# 创建库
CREATE DATABASE myback;

# 选择库
use myback;

CREATE TABLE bank(
    customerName VARCHAR(20),   #用户名
    currentMoney DECIMAL(10,2)    #当前余额
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 插入数据
INSERT INTO bank (customerName,currentMoney) VALUES('张三',1000);
INSERT INTO bank (customerName,currentMoney) VALUES('李四',1);

查询 bank 数据表的 SQL 语句和运行结果如下：

mysql> SELECT * FROM bank;
+--------------+--------------+
| customerName | currentMoney |
+--------------+--------------+
| 张三         |      1000.00 |
| 李四         |         1.00 |
+--------------+--------------+
2 rows in set (0.02 sec)

结果显示，张三和李四两个账户的余额总和为 1000+1=1001 元。
⑵.下面开始模拟实现转账功能。从张三的账户直接转账 500 元到李四的账户，可以使用 UPDATE 语句分别修改张三的账户和李四的账户。张三的账户减少 500 元，李四的账户增加 500 元， SQL 语句如下所示：

/*转账测试：张三转账给李四 500 元*/
#张三的账户少 500 元，李四的账户多 500 元
UPDATE bank SET currentMoney = currentMoney-500 WHERE customerName = '张三';
UPDATE bank SET currentMoney = currentMoney+500 WHERE customerName = '李四';

正常情况下，执行以上的转账操作后，余额总和应保持不变，仍为 1001 元。但是，如果在这个过程的其中一个环节出现差错，如在张三的账户减少 500 元之后，这时发生了服务器故障，李四的账户没有立即增加 500 元，此时，第三方读取到两个账户的余额总和变为 500+1=501 元，即账户总额间少了 500 元。

MySQL 为了解决此类问题，提供了事务。事务可以将一系列的数据操作捆绑成一个整体进行统一管理，如果某一事务执行成功，则在该事务中进行的所有数据更改均会提交，成为数据库中的永久组成部分。如果事务执行时遇到错误，则就必须取消或回滚。取消或回滚后，数据将全部恢复到操作前的状态，所有数据的更改均被清除。
MySQL 通过事务保证了数据的一致性。上述提到的转账过程就是一个事务，它需要两条 UPDATE 语句来完成。这两条语句是一个整体，如果其中任何一个环节出现问题，则整个转账业务也应取消，两个账户中的余额应恢复为原来的数据，从而确保转账前和转账后的余额总和不变，即都是 1001 元。

3.1.演示事务的使用步骤

#开始事务
SET autocommit = 0;

#张三的账户少 500 元，李四的账户多 500 元
UPDATE bank SET currentMoney = currentMoney-500 WHERE customerName = '张三';
UPDATE bank SET currentMoney = currentMoney+500 WHERE customerName = '李四';

# 提交事务
COMMIT;
# 回滚事务
# ROLLBACK

3.2.演示事务对于delete和truncate的处理的区别

#开启事务
SET autocommit=0; #禁用自动提交，需要显式调用 commit 才能提交事务
# 启动一个新事务。默认情况下，MySQL 以自动提交（autocommit）模式运行，每条语句执行完毕后会立即将其修改提交给数据库并永久化。
START TRANSACTION; 

# 编写事务中的sql语句
# DELETE FROM bank;

# 使用 TRUNCATE TABLE 删除所有数据
TRUNCATE TABLE bank;

# 回滚
#ROLLBACK;
# 提交事务
COMMIT

SELECT * FROM bank

3.3.演示savepoint 的使用

SET autocommit=0;
START TRANSACTION;

DELETE FROM bank WHERE customerName='张三';
# 设置保存点
SAVEPOINT a;
DELETE FROM bank WHERE customerName='李四';

# 回滚到保存点
ROLLBACK TO a;

SELECT * FROM bank;

posted @ 2024-05-15 17:10 酒剑仙* 阅读(48) 评论(0) 收藏举报

刷新页面返回顶部

Augus

MySQL事务

一、事务的概念和特性

1.1.事务

1.2.MySQL中的存储引擎

1.3.事务的特点

1.4.事务的使用

1.5.事务的创建

1.6.事务的创建步骤

二、事务的隔离级别

4.1.数据库的隔离级别是什么？

4.2.事务隔离级别

4.3.在MySql中设置隔离级别

4.4.READ UNCOMMITTED（读未提交数据）演示

4.5.READ COMMITED（读已提交数据）

4.6.REPEATABL READ（可重复读）

4.6.1.脏读

4.6.2.不可重复读取

4.6.3.幻读

4.7.SERIALIZABLE（串行化）

以幻读为例进行演示

三、事务的案例演示

3.1.演示事务的使用步骤

3.2.演示事务对于delete和truncate的处理的区别

3.3.演示savepoint 的使用

公告