MySQL事务管理(上)

MySQL注定会被多个用户或者客户端，因为MySQL存的是数据；MySQL内部使用的是多线程的方式来实现数据的存储工作；

前言

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

如今有个数据库里面是火车票售票系统所对应的数据库表；MySQL是一个网络服务可以被多人服务那么在抢票的时候任何人都可以抢票，那么多个客户端都在同时读取数据；那么日常生活中转账也是一样了，A现在想给B转账(同行银行卡)，那么做法就是把A账上的钱减少200元给B账上加200因为是同行所以用的是同一张数据库的表，在减A这张表数据的时候网络出现问题或者数据库出问题或者其他原因导致在B上没有把数据加上来，那么就有了中间过程，就相当于A说我转了但是B说我没收到。我们允许有异常产生但是要把数据补回来，返回正常；

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

1. 买票的过程得是原子的吧

2. 买票互相应该不能影响吧

3. 买完票应该要永久有效吧

4. 买前和买后都要是确定的状态吧

 事务

什么是事务？

事务就是一组DML语句(允许用户或应用程序从数据库中检索、添加、修改和删除数据，以及执行数据的各种管理任务)组成，这些语句在逻辑上(站在上层使用者的角度)存在相关性，这一组DML语句要么全部成功，要么全部失败，是一个整体。MySQL提供一种机制，保证我们达到这样的效果。事务还规定不同的客户端看到的数据是不相同的(因为到来的时间不同，所以看到不同的数据)。(简单来说事务就是一条或多条sql共同构成的一个sql集合体，这个集合体要共同一起完成某个任务这就叫事务)

事务就是要做的或所做的事情主要用于处理操作量大，复杂度高的数据。假设一种场景：你毕业了， 学校的教务系统后台 MySQL 中，不再需要你的数据，要删除你的所有信息(一般不会) ), 那么要删除你的基本信息(姓名，电话，籍贯等)的同时，也删除和你有关的其他信息，比如：你的各科成绩，你在校表现，甚至你在论坛发过的文章等。这样，就需要多条 MySQL 语句构成，那么所有这些操作合起来，就构成了一个事务。(站在程序员角度来讲就是一串delete，这些delete合起封装起来叫事务，严格上来说事务并不是程序员术语，而是站在使用者的上层角度)

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

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

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（ Read uncommitted ）、读提交（ read committed ）、可重复读（ repeatable read ）和串行化 （ Serializable ）

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。(数据持久化)

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

为什么会出现事务 

MySQL要同时帮我们不同的客户端处理不同的事务请求，决定了MySQL在运行期间有大量的事务，同时也要管理这些事务；

事务不能仅仅站在程序员的角度考虑问题，一定要站在数据库的使用者的角度考虑问题；

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

MySQL可以有数据库，数据库里有表，表里面可以有一行信息,我们把这一行信息叫做一行记录；

 事务的版本支持

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务， MyISAM 不支持。

先查看当前MySQL所支持的存储引擎：mysql> show engines\G     有很多个
support为default代表我MySQL当前使用innodb为默认引擎；savepoints表示支持事务保存点

可以看到myisam不支持事务

 事务提交方式

事务的提交方式常见的有两种： 自动提交       手动提交

 查看事务提交方式

mysql> show variables like 'autocommit'; //ON表示自动提交

改变 MySQL 的自动提交模式:

mysql> set autocommit=0;//关掉自动提交

mysql> set autocommit=1;//打开自动提交

 事务常见操作方式

前序

首先我们清楚MySQL是命令行式的客户端，mysqld是服务端

记住MySQL的服务一般不要暴漏在公网上，暴漏在公网上别人也可以连接，我也可以连接但是不一定能登录，因为MySQL不仅需要账号密码还要求在哪里登录；

使用netstat查看链接情况，可知：mysql本质是一个客户端进程

 为了便于演示，我们将mysql的默认隔离级别设置成读未提交。

mysql>  set global transaction isolation level read uncommitted;

现在查发现是可重复读

则需要重启客户端重新登陆即可

 现在让两个用户同时连接MySQL，两个客户端访问mysqld服务

创建测试表 mysql> 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 processlist;//查看多少人访问服务

用于实时监视和了解数据库当前的活动连接和执行情况，每一行代表一个连接（也就是一个进程），包括连接的ID、用户、主机、数据库、执行的SQL语句等信息。

 正常演示

-证明事务的开始与回滚

mysql> show variables like 'autocommit';查看事务是否自动提交。我们故意设置成自 动提交，看看该选项是否影响begin
启动事务：start  transaction或者begin都可以，两个客户端不同的启动方式，启动2个事务；

创建一个保存点save1：mysql> savepoint save1;//在左边客户端创建
右边客户端查看数据：为什么右边可以看到因为我设置的是读未提交
左边客户端插入数据：mysql> insert into account values (1, '张三', 100);
右边查看数据：发现查到了

再创建一个保存点save2：mysql> savepoint save2;//在左边客户端创建，设置保存点方便未来定向回滚
左边客户端插入数据：insert into account values (2, '李四', 10000);
右边查看数据：发现查到了

下边的所有都属于同一个事物，也就是说这些sql都会被打包成一个事务让我们的mysql执行
 
当然如果想放弃插入的话可以使用回滚:mysql> rollback to save2;//因为我设置了保存点所以可以定向回滚
再查数据：发现第二条插入没了；回滚到哪个插入点则插入点后面的sql都没有用了
回滚到最开始即不想要这个事务了：mysql> rollback;//直接rollback，回滚在最开始

再次进行查找：发现没有数据了
如果不想再进行操作了可以进行commit提交
commit 是用来提交事务的命令，当执行 commit 后，数据库系统会将事务中的所有操作永久性地应用到数据库中。这意味着所有的数据修改、插入、更新或删除操作都会被保存到数据库中，同时释放相关的锁资源，使得其他事务可以访问这些数据。

 重新开始事务：
不设置保存点进行插入：
查找数据：
因为没有设置保存点所以不能定向回滚所以只能回到最开始：mysql> rollback;
再次查找：发现为空，数据全没了
再进行提交:commit

 再次开始新的事务：
左边再次插入：
右边再次查找：
进行保存：mysql> commit;//进行commit之后再进行rollback就没意义了

回滚只有在事务运行期间才有意义
这时候再进行查找：发现数据持久化保存了

 非正常演示

1 - 证明未commit，客户端崩溃，MySQL自动会回滚（隔离级别设置为读未提交）

 开始事务：begin

查找数据：
插入数据：mysql> insert into account values (3, '王五', 100007);

因为是读未提交所以另一个事务能看到数据：右边的事务查找
不commit，左边ctrl + \ (异常终止MySQL)，发现MySQL崩溃退出了，自动回滚 这时右边的MySQL还没退出，进行查找数据：发现id=3的数据没有因为没有提交事务

 2 - 证明commit了，客户端崩溃，MySQL数据不会在受影响，已经持久化

 开始事务：
插入数据：mysql> insert into account values (3, '王五', 100007);

查找数据：
进行commit提交再进行查找：发现数据还在
那么我此时再进行异常退出（ctrl+\），我再查找：发现数据还在
甚至我把右边的事务终止掉再进行查找：发现还在
换而言之只要commit了数据就持久化了无论正常退出还是异常退出都没有对数据产生影响

那么我明明设置的是自动提交，但是我刚才为什么异常退出的时候出现回滚了则代表没有提交事务？那是因为我是手动提交，我用了begin或者start  transaction表示我手动提交，跟我设置的没有一点关系；

 3 - 对比试验。证明begin操作会自动更改提交方式，不会受MySQL是否自动提交影响

 查看事务提交方式：mysql> show variables like 'autocommit';
关闭自动提交：mysql> set autocommit=0;//因为这个操作是局部的(退出mysql再进还是ON)，所以要在两个客户端同时执行才能都修改

查看关闭后的：
开始事务：begin

左边插入数据：mysql> insert into account values (4, '赵六', 12369);

右边查找数据：
左边进行异常终止ctrl+\然后在右边进行再进行查找：发现插入的不在了
说明左边终端崩溃后，自动回滚说明不是自动提交

说明手动开始事务就要手动commit跟是否自动没有关系

 4 - 证明单条 SQL 与事务的关系

我们以前从来都是单sql外加其他操作，我们没有写过begin和commit；

关闭自动提交：
我不begin了而是插入数据：mysql> insert into account values (4, '赵六', 12369);
查找：
ctrl + \ or ctrl + d,终止左边终端，在右边进行查找：发现赵六没有了

 开启自动提交：

进行插入：mysql> insert into account values (4, '赵六', 12369);
右边终端查找：
ctrl + \ or ctrl + d,终止左边终端，再在右边进行查找：发现数据保存了不跟上次一样
左边终端崩溃后，并不影响，已经持久化。autocommit 起作用；单sql对于autocommit来说就是事务

 结论

1. 只要输入begin或者start transaction，事务便必须要通过commit提交，才会持久化，与是 否设置set autocommit无关。

2. 事务可以手动回滚，同时，当操作异常，MySQL会自动回滚

3. 对于 InnoDB 每一条 SQL 语言都默认封装成事务，自动提交。（select有特殊情况，因为 MySQL 有 MVCC ）

4. 从上面的例子，我们能看到事务本身的原子性(回滚)，持久性(commit)

 事务操作注意事项

1. 如果没有设置保存点，也可以回滚，只能回滚到事务的开始。直接使用 rollback(前提是事务还没有提交)

2. 如果一个事务被提交了（commit），则不可以回退（rollback）

3. 可以选择回退到哪个保存点

4. InnoDB 支持事务， MyISAM 不支持事务

5. 开始事务可以使 start transaction 或者 begin

 事务隔离级别

如何理解隔离性

MySQL服务可能会同时被多个客户端进程(线程)访问，访问的方式以事务方式进行；

一个事务可能由多条SQL构成，也就意味着，任何一个事务，都有执行前，执行中，执行后的阶 段。而所谓的原子性，其实就是让用户层，要么看到执行前，要么看到执行后。执行中出现问题， 可以随时回滚。所以单个事务，对用户表现出来的特性，就是原子性。

但毕竟所有事务都要有个执行过程，那么在多个事务各自执行多个SQL的时候，就还是有可能会 出现互相影响的情况。比如：多个事务同时访问同一张表，甚至同一行数据。

就如同你妈妈给你说：你要么别学，要学就学到最好。至于你怎么学，中间有什么困难，你妈妈不 关心。那么你的学习，对你妈妈来讲，就是原子的。那么你学习过程中，很容易受别人干扰，此 时，就需要将你的学习隔离开，保证你的学习环境是健康的。

数据库中，为了保证事务执行过程中尽量不受干扰，就有了一个重要特征：隔离性；

数据库中，允许事务受不同程度的干扰，就有了一种重要特征：隔离级别；

在事务场景中隔离是必须的 ，这个隔离是运行中的事务进行隔离(运行前和运行后不管)；

隔离级别 

读未提交【Read Uncommitted】：

这是最低的隔离级别，在该隔离级别，所有的事务都可以看到其他事务没有提交的执行结果。（实际生产中不可能使用这种隔离级别的），但是相当于没有任何隔离性，也会有很多并发问题，如脏读，幻读，不可重复读等，我们上面为了做实验方便，用的就是这个隔离性。

读提交【Read Committed】 ：

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

 可重复读【Repeatable Read】：

这是 MySQL 默认的隔离级别，这个级别确保一个事务在执行期间多次读取相同数据时，看到的数据保持一致，会看到同样的数据行，即使其他事务修改了这些数据也不会影响当前事务。

可以避免脏读和不可重复读的问题，但仍可能出现幻读问题（即在同一事务中多次查询时，结果集中的行数不一致）

 串行化【Serializable】:

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

隔离级别如何实现：隔离，基本都是通过锁实现的，不同的隔离级别，锁的使用是不同的。常见有，表 锁，行锁，读锁，写锁，间隙锁(GAP),Next-Key锁(GAP+行锁)等。不过，我们目前现有这个认识就行， 先关注上层使用。 

隔离级别的影响

• 隔离级别越低，例如读未提交，数据库的并发控制越少，因为事务可以相互干扰并看到彼此未提交的工作。
• 而隔离级别越高，如可重复读或串行化，数据库会更加严格地控制事务的并发执行，以确保数据的一致性和隔离性

查看与设置隔离性 

查看

在MySQL当中有三种查看隔离级别方法：

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

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

 mysql> SELECT @@tx_isolation;//是@@session.tx_isolation的简写，效果是一样的

global与 session的区别就是global是session的默认配置，当我们在登陆MySQL的时候，默认MySQL会读取全局配置好的隔离级别，用来初始化本次登陆的会话隔离级别；这就有点像我global设置好了相当于全局的配置，我们的session默认选择把global的拷贝一份给自己当然也可以更改这个隔离级别(更改session的话只会影响当前会话的隔离级别，更改global的话影响后续所有的客户端登录)；
会话可以理解为客户端与服务端进行持续交互过程，一个数据库会话从客户端应用程序连接到数据库开始，到断开连接结束，期间可以执行多个SQL查询和事务操作；在数据库管理系统中，一个会话（session）从客户端应用程序连接到数据库开始，直到断开连接结束，这段时间内的所有交互和操作都属于同一个会话。在这个会话期间，客户端可以执行多个SQL查询和事务操作，这些操作可以涉及数据的读取、写入、更新和删除等。

设置

语法：set [session | global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable}//建议不要省略[]里的内容

设置当前会话隔离性：mysql> set session transaction isolation level read committed;

查看当前会话隔离级别：mysql> SELECT @@session.tx_isolation;查看全局隔离级别：mysql> SELECT @@global.tx_isolation;//没受影响

另起一个客户端查看全局隔离级别：mysql> SELECT @@global.tx_isolation;//不受影响 查看当前会话的隔离级别：mysql> SELECT @@session.tx_isolation;//是global的拷贝

那么现在右边终端的隔离级别三位一体都是读未提交，左边的隔离级别有读未提交和读提交那么左边用哪个，MySQL会采用就近原则使用session的隔离级别；

更改全局隔离级别：mysql> set global transaction isolation level serializable;

查看左边全局隔离级别：mysql> SELECT @@global.tx_isolation;
查看左边当前会话隔离级别：mysql> SELECT @@session.tx_isolation;

查看右边全局隔离级别：mysql> SELECT @@global.tx_isolation;

查看右边当前会话隔离级别：mysql> SELECT @@session.tx_isolation;
发现更改全局后只有全局变了而左右端口的当前会话没有变，只有重新登陆才会发生变化；

 读未提交【Read Uncommitted】

几乎没有加锁，虽然效率高，但是问题太多，严重不建议采用

设置隔离级别为读未提交：mysql> set global transaction isolation level read uncommitted;

重启客户端查看当前会话隔离级别：mysql> SELECT @@session.tx_isolation;

让两个客户端同时begin那么就是并发在跑 ；左边客户端对数据修改操作未提交，右边的客户端可以立即看到修改后的；可以读到别人未提交的数据因为我没加锁，但是事务roolback的话数据就没了；

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

读提交【Read Committed】

设置全局变量：set global transaction isolation level read committed;

重启客户端查看级别：两个客户端都是一样的都是读提交

启动事务：begin
右边查找数据：
左边插入数据未提交：mysql> insert into account values (6, '赵七', 123666);
右边再进行查找：发现没有新插入的(到那时数据已经被写入到数据库中了)
但是左边的表自己查数据发现能查到新插入的数据：自己事物内部随便
左边的commit之后右边再查发现能查到新的数据：右边的事务没有结束，能查到结束的事务，查不到运行中的事务；

但是我右边的事务还在运行前后查到的数据不同，此时还在当前事务中，并未commit，那么就造成了，同一个事务内，同样的读取，在不同的时间段 (依旧还在事务操作中！)，读取到了不同的值，这种现象叫做不可重复读(non reapeatable read)！！

我提交了是应该让其他事务看到但不应该让运行中的事务(并发运行中的)看到， 可以让后续的新起的事务看到；

不可重复读会导致：

1. 数据不一致性：由于两次读取同一行数据的结果不同，事务内部的处理逻辑可能会基于错误的数据状态进行操作，导致最终的数据结果与预期不符。

2. 难以复现问题：由于不可重复读问题通常是由并发事务导致的，而且不易稳定复现，这使得排查和解决问题变得更加复杂和耗时。

3. 逻辑错误和处理异常：在某些场景下，事务处理可能依赖于读取到的数据的稳定性和一致性。如果出现了不可重复读，可能会导致程序逻辑错误或者异常情况的发生，影响应用程序的正常运行。

4. 事务处理安全性降低：不可重复读问题会降低事务的隔离性，从而增加了处理并发访问时出现异常情况的风险。这可能需要通过选择更高的事务隔离级别或者调整业务逻辑来弥补。

 

原文地址：https://blog.csdn.net/yiqizhuashuimub/article/details/140545217

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！