12 事务

最新推荐文章于 2024-09-01 13:09:09 发布

且听吟风°

最新推荐文章于 2024-09-01 13:09:09 发布

阅读量856

点赞数 20

分类专栏： 05 MySQL 文章标签：数据库 mysql oracle sql 开发语言

本文链接：https://blog.csdn.net/qq_43422358/article/details/141721339

版权

05 MySQL 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

CURD不加控制，会有什么问题？

在这里插入图片描述

当客户端A检查还有一张票时，将票卖掉，还没有执行更新数据库时，客户端B检查了票数，发现大于0，于是又卖了一次票。然后A将票数更新回数据库，一张票被卖了两次

CURD满足什么属性，能解决上述问题

1.买票的过程是原子的
2.买票互相不影响
3.买完票永久有效
4.买前，买后的都要是确定的状态

1. 什么是事务

事务就是一组DML语句组成，这些语句在逻辑上存在相关性，这一组DML语句要么全部成功，要么全部失败，是一个整体。mysql提供一种机制，保证我们达到这样的效果，事务还规定了不同的客户端看到的数据是不同的

事务就是要做或所做的事情，主要用于处理操作量大，复杂度高的数据。假设一种场景，毕业了，学校教务系统不需要你的数据，要删除基本信息，也要删除其他信息，成绩表现等。这样，就需要多条mysql语句，这些操作组合起来，就构成了一个事务

正如上面所说，一个mysql数据库，不止一个事务运行，同一时刻，甚至有大量的请求被包装成事务，在向服务器发起事务处理请求，而每条事务至少一条sql，最多很多sql，这样如果大家都访问同样的表数据，在不加保护的情况，就绝对会出现问题。甚至，因为事务由多条sql构成，那么会存在执行到一半出错或者不想再执行的情况，那么已经执行的怎么办呢？

所以，一个完整的事务，绝对不是简单的sql集合，还需要满足如下四个属性：

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成。不会结束在中间某个环节，事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像事务从来没有执行过一样
一致性：在事务开始之前和结束之后，数据库的完整性没有被破坏，表示写入的资料必须完全符合所有的预设规则，包含资料的精确度、串联型以及后续数据库可以自发性的完成预定的工作
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务的隔离分为不同级别，包括读未提交（read uncommitted）、读提交（read commintted）、可重复读（repeatable read）和串行化（serializable）
持久性：事务处理结束后，对数据的修改是永久的，即便系统故障也不会丢失

上面四个属性，可以简称为ACID
原子性（Atomicity，或称不可分割性）
一致性（Consistency）
隔离性（Isolation，或称独立性）
持久性（Durability）

2. 为什么会出现事务？

事务被mysql编写者设计出来，本质是为了当应用程序访问数据库时，能够简化编程模型，不需要去考虑各种各样的潜在错误和并发问题，可以想一下当我们使用事务时，要么提交，要么回滚，不会去考虑网络异常了，服务器宕机了，同时更改一个数据怎么办对吧》因此事务本质上是为了应用层服务的，而不是伴随着数据库系统天生就有的

备注：后面把mysql中一行信息，称为一行记录

3. 版本支持

mysql中，只有使用了innodb数据库引擎或表才支持事务，myisam不支持

查看数据库引擎

mysql> show engines; --表格显示
mysql> show engines \G --行显示
*************************** 1. row ***************************
Engine: InnoDB – 引擎名称
Support: DEFAULT – 默认引擎
Comment: Supports transactions, row-level locking, and foreign keys – 描述
Transactions: YES – 支持事务
XA: YES
Savepoints: YES – 支持事务保存点
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MEMORY --内存引擎
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO – MyISAM不支持事务
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
9 rows in set (0.00 sec)

4. 提交方式

两种：
自动提交
手动提交

查看提交方式

mysql> show variables like ‘autocommit’;

set改变自动提交模式

mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
#SET AUTOCOMMIT=0 禁止自动提交

mysql> show variables like ‘autocommit’;

mysql> SET AUTOCOMMIT=1;
#SET AUTOCOMMIT=1 开启自动提交
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like ‘autocommit’;

5. 常见操作方式

简单银行用户表

提前准备

## Centos 7 云服务器，默认开启3306 mysqld服务
[@VM-0-3-centos ~]$ sudo netstat -nltp
[sudo] password for whb:
Active Internet connections (only servers)

## 使用win cmd远程访问Centos 7云服务器，mysqld服务(需要win上也安装了MySQL，这里看到结
果即可)
## 注意，使用本地mysql客户端，可能看不到链接效果，本地可能使用域间套接字，查不到链接
C:\Users>mysql -uroot -p -h
Enter password: ***********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3484
Server version: 5.7.33 MySQL Community Server (GPL)
Copyright © 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input
statement.

## 使用netstat查看链接情况，可知：mysql本质是一个客户端进程
[@VM-0-3-centos ~]$ sudo netstat -ntp

## 为了便于演示，我们将mysql的默认隔离级别设置成读未提交。
## 具体操作后面会说明，现在已使用为主。
mysql> set global transaction isolation level READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye

##需要重启终端，进行查看
mysql> select @@tx_isolation;

创建测试表

create table if not exists account(
id int primary key,
name varchar(50) not null default ‘’,
blance decimal(10,2) not null default 0.0
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;

正常演示-证明事务的开始与回滚

mysql> show variables like ‘autocommit’; – 查看事务是否自动提交。我们故意设置成自动提交，看看该选项是否影响begin

mysql> start transaction; – 开始一个事务begin也可以，推荐begin
Query OK, 0 rows affected (0.00 sec)

mysql> savepoint save1; --创建一个保存点save1
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values (1, ‘张三’, 100); – 插入一条记录
Query OK, 1 row affected (0.05 sec)

mysql> savepoint save2; – 创建一个保存点save2
Query OK, 0 rows affected (0.01 sec)

mysql> insert into account values (2, ‘李四’, 10000); – 在插入一条记录
Query OK, 1 row affected (0.00 sec)

mysql> select * from account; --两条记录都在了

mysql> rollback to save2; --回滚到保存点save2
Query OK, 0 rows affected (0.03 sec)

mysql> select * from account; --一条记录没有了

mysql> rollback; --直接rollback，回滚在最开始
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account; – 所有刚刚的记录没有了
Empty set (0.00 sec)

结论：
只要输入begin或者start transaction，事务便必须要通过commit提交，才会持久化，与是否设置set autocommit无关
事务可以手动回滚，同时，当操作异常，mysql会自动回滚
对于innodb每一条sql都默认封装成事务，自动提交（select有特殊情况，因为有MVCC）

6. 事务隔离级别

如何理解隔离性

mysql服务可能会同时被多个客户端进程（线程）访问，访问的方式以事务方式进行
一个事务可能由多条sql构成，也就意味着，任何一个事务，都有执行前，执行中，执行后的阶段。而所谓的原子性，其实就是让用户层，要么看到执行前，要么看到执行后。执行中出现问题，可以随时回滚。所以耽搁事务，对用户表现出来的特性，就是原子性
但，毕竟所有事务都要有个执行过程，那么在多个事务各自执行多个sql的时候，还是有可能会互相影响。比如：多个事务同时访问同一张表，甚至同一行数据
数据库中，为了保证事务执行过程中不受干扰，就有了一个重要特征：隔离性
数据库中，允许事务受不同程度的干扰，就有了一个重要特征：隔离级别

隔离级别

读未提交【Read Uncommitted】：在该隔离级别，所有的事务都可以看到其他事物没有提交的执行结果。（实际生产中不可能使用这种隔离级别），相当于没有任何隔离性，也会有很多并发问题，如脏读，幻读，不可重复读。上面的实验用的就是这个隔离性

读提交【Read Committed】：该隔离级别是大多数数据库默认的隔离级别（不是mysql默认的）。它满足了隔离的简单提议：一个事务只能看到其他的已经提交的事务所做的改变。这种隔离级别会引起不可重复读，即一个事务执行时，如果多次select，可能得到不同的结果

可重复读【Repeatable Read】：这是mysql默认的隔离级别，它确保一个事务，在执行中，多次读取操作数据时，会看到同样的数据行，但是会有幻读问题

串行化【Serializable】：这时事务的最高隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决了幻读的问题。它在每个读的数据行上面加上共享锁。但是可能会导致超时和锁竞争（这种隔离级别太极端，实际生产基本不使用）

隔离级别如何实现：隔离，基本都是通过锁实现的，不同的隔离级别，锁的使用是不同的。常见有，表锁，行锁，读锁，写锁，间隙锁（GAP），Next-Key锁（GAP+行锁）等。目前先关注上层使用

查看与设置隔离性

– 查看
mysql> SELECT @@global.tx_isolation; --查看全局隔级别

mysql> SELECT @@session.tx_isolation; --查看会话(当前)全局隔级别

mysql> SELECT @@tx_isolation; --默认同上

–设置-- 设置当前会话 or 全局隔离级别语法
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

–设置当前会话隔离性，另起一个会话，看不多，只影响当前会话
mysql> set session transaction isolation level serializable; – 串行化
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.tx_isolation; --全局隔离性还是RR

mysql> SELECT @@session.tx_isolation; --会话隔离性成为串行化

mysql> SELECT @@tx_isolation; --同上

–设置全局隔离性，另起一个会话，会被影响
mysql> set global transaction isolation level READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.tx_isolation;

mysql> SELECT @@session.tx_isolation;

mysql> SELECT @@tx_isolation;

– 注意，如果没有现象，关闭mysql客户端，重新连接。

读取的问题

一个事务在执行中，读到另一个执行中事务的更新(或其他操作)但是未commit的数据，这种现象叫做脏读(dirty read)

同一个事务内，同样的取，在不同的时间段(依旧还在事务操作中！)，读取到了不同的值，这种现象叫做不可重复读(non reapeatable read)

一般的数据库在可重复读情况的时候，无法屏蔽其他事务insert的数据(为什么？因为隔离性实现是对数据加锁完成的，而insert待插入的数据因为并不存在，那么一般加锁无法屏蔽这类问题),会造成虽然大部分内容是可重复读的，但是insert的数据在可重复读情况被读取出来，导致多次查找时，会多查找出来新的记录，就如同产生了幻觉。这种现象，叫做幻读(phantom read)。很明显，MySQL在RR级别的时候，是解决了幻读问题的(解决的方式是用Next-Key锁(GAP+行锁)解决的。

在这里插入图片描述
总结：
其中隔离级别越严格，安全性越高，但数据库的并发性能也就越低，往往需要在两者之间找一个平衡点
不可重复读的重点是修改和删除，同样的条件，读取过的数据再读取出来发现值不一样了，幻读的重点在于新增：同样的条件，第1次和第2次读出来的记录数不一样
说明：mysql默认的隔离级别是可重复读，一般情况下不要修改
从上面的例子可以看出，事务也有长短事务这样的概念。事务间互相影响，指的是事务在并行执行的时候，即没有commit的时候，影响会比较大

隔离级别	脏读	不可重复读	幻读	加锁读
读未提交（read uncommited）	√	√	√	不加锁
读已提交（read commited）	×	√	√	不加锁
可重复读（repeatable read）	×	×	×	不加锁
可串行化（serializable）	×	×	×	加锁

在这里插入图片描述

一致性（Consistency）

事务执行的结果，必须使数据库从一个一致性状态，变到另一个一致性状态。当数据库只包含事务成功提交的结果时，数据库处于一致状态，如果系统运行发生中断，某个事物尚未完成而被迫中断，而改变未完成的事务对数据库所做的修改已被写入数据库，此时数据库就处于一种不正确（不一致）的状态，因此一致性是通过原子性保证的
其实一致性和用户的业务逻辑强相关，一般mysql提供技术支持，一致性还要用户业务逻辑支撑，由用户决定
技术上，通过AID保证C