什么是事务和并发

菠萝蜜blm

于 2024-04-25 18:41:03 发布

阅读量1.1k

点赞数 32

分类专栏：一起成为数据库高手文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/weixin_46984703/article/details/138196710

版权

一起成为数据库高手专栏收录该内容

33 篇文章 1 订阅

订阅专栏

1. 事务

事务

事务（trasaction）是完成一个完整事件的一系列SQL语句。这一组SQL语句是一条船上的蚂蚱，要不然都成功，要不然都失败，如果一部分执行成功一部分执行失败那成功的那一部分就会回滚以保持数据的一致性。

银行交易：你给朋友转账红包含从你账户转出和往他账户转入两个步骤，两步必须同时成功，如果转出成功但转入不成功则转出的金额会返还。

例子2

订单记录：向父子表插入分级（层）/耦合数据，一个订单 (order) 记录对应多个订单项目 (order_items) 记录，如果在记录一个新订单时，订单记录录入成功但对应的订单项目记录录入一半系统就崩了，那这个订单的信息就是不完整的，数据库将失去数据一致性

ACID 特性

事务有四大特性，总结为 ACID（刚好是英文单词“酸的”）：

Atomicity 原子性，即整体性，不可拆分行（unbreakable），所有语句必须都执行成功事务才算完成，否则只要有语句执行失败，已执行的语句也会被复原。
Consistency 一致性，指的是通过事务我们的数据库将永远保持一致性状态，比如不会出现没有完整订单项目的订单。
Isolation 隔离性，指事务间是相互隔离互不影响的，尤其是需要访问相同数据时。具体而言，如果多个事务要修改相同数据，该数据会被锁定，每次只能被一个事务有权修改，其它事务必须等这个事务执行结束后才能进行。
Durability 持久性，指的是一旦事务执行完毕，这种修改就是永久的，任何停电或系统死机都不会影响这种数据修改。

1.2 创建事务

创建事务案例：

创建一个事务来储存订单及其订单项目（为了简化，这个订单只有一个项目）

用 START TRANSACTION 来开始创建事务，用 COMMIT 来提交事务

START TRANSACTION; -- 开启事务

INSERT INTO orders (customer_id, order_date, status)
VALUES (1, '2019-01-01', 1);
-- 只需明确声明并插入这三个非自增必须（不可为空）字段

INSERT INTO order_items 
-- 所有字段都是必须的，就不必申明了
VALUES (last_insert_id(), 1, 2, 3);

COMMIT;-- 提交事务

执行，会看到最新的订单和订单项目记录

当 MySQL 看到上面这样的事务语句组，会把所有这些更改写入数据库，如果有任何一个更改失败，会自动撤销之前的修改，这种情况被称为事务被退回（回滚）（is rolled back）

为了模拟退回的情况，可以用调试逐条执行语句，执行一半，即录入了订单但还没录入订单项目时断开连接（模拟客户端或服务器崩溃或断网之类的情况），重连后会发现订单和订单项目都没有录入

手动退回

多数时候是用 START TRANSACTION + COMMIT 来创建事务，当我们想先进行一下事务里语句的测试/错误检查并因此想在执行结束后不修改原数据，可以将最后的 COMMIT; 换成 ROLLBACK;，这会回滚事务并撤销所有的更改。

autocommit（默认自动事务）

我们执行的每一个语句（可以是增删查改 SELECT、INSERT、UPDATE 或 DELETE 语句），就算没有 START TRANSACTION + COMMIT，也都会被 MySQL 包装（wrap）成事务并在没有错误的前提下自动提交，这个过程由一个叫做 autocommit 的系统变量控制，默认开启。

因为有 autocommit 的存在，当事务只有一个语句时，不需要再创建事务，但要将多个语句作为一个事务时就必须要加 START TRANSACTION + COMMIT 来创建事务。

开启和关闭事务：

START TRANSACTION; -- 开启事务

……;
-- 提交 / 回滚
COMMIT / ROLLBACK;
-- 查看数据库系统中 autocommit 模式的当前设置是启用还是禁用
SHOW VARIABLES LIKE 'autocommit';

1.3 并发和锁定

并发

现实中常出现多个用户访问相同数据的情况，这被称为“并发”（concurrency），当一个用户企图修改另一个用户正在检索或修改的数据时，并发会成为一个问题。

案例

假设要通过如下事务语句给1号顾客的积分增加10分

START TRANSACTION;
UPDATE customers
SET points = points + 10
WHERE customer_id = 1;
COMMIT;

现在有两个会话（注意是两个链接（connection），而不是同一个会话下的两个SQL标签，这两个链接相当于是在模拟两个用户）都要执行这段语句，用调试逐句执行，当第一个执行到UPDATE 而还没有 COMMIT 提交时，转到第二个会话，执行到UPDATE语句时会一直等待执行（若等的时间太久会超时而放弃执行），这时跳回第一个对话 COMMIT 提交，第二个会话的 UDDATE 才执行成功，最后将第二段对话的事务也COMMIT提交，此时刷新顾客表会发现1号顾客的积分多了20分

上锁

所以，可以看到，当一个事务修改一行或多行时，会给这些行上锁，这些锁会阻止其他事务修改这些行，直到前一个事务完成（不管是提交还是退回）为止，由于上述MySQL默认状态下的锁定行为，多数时候不需要担心并发问题，但在一些特殊情况下，默认行为不能满足应用里的特定场景，这时可以修改默认行为。

1.3.1 并发问题

1. Lost Updates 丢失更新

两个或多个事务并发更新同一行数据时，后提交的事务覆盖了先提交的事务的更改，导致先前的更改丢失就是丢失更新。

例如：

当事务A要更新小许所在的城市，而事务B要更新小许的电话时，如果两个事务都读取了小许的记录，在A更新了城市但未提交时，B更新了电话，后执行的B更新后会覆盖先执行的A的更新，城市的更新将会丢失。

解决方法：

就是前面说的锁定机制，锁定会防止多个事务同时更新同一个数据，必须完成一个再执行另一个。

2. Dirty Reads 脏读

事务B读取到了事务A还没提交的数据，当作最终结果，因为某些原因事务A回滚了，这条数据没有更新成功，事务B就拿到了一条无效的或不正确的数据，就是脏读。会导致事务基于不一致的数据做出错误的决定。

例如：

事务A将某顾客的积分从10分增加为20分，但在提交前就被事务B读取了，事务B按照这个尚未提交的顾客积分确定了折扣数额，可之后系统崩溃事务A回滚了，所以该顾客的积分其实仍然是10分，因此事务B读取了一个数据库中从未提交的数据并以此做了决定。

解决办法：

设定事务的隔离等级，例如让一个事务无法看见其它事务还没提交的更新数据。可以把事务B设为 READ COMMITED 等级，它将只能读取提交后的数据。

积分提交完之后，B事务依此做决定，如果之后积分再修改，这就不是我们考虑的问题了，我们只需要保证B事务读取的是提交后的数据就行了。

3. Non-repeating Reads 不可重复读取（或 Inconsistent Read 不一致读取）

上面的隔离能保证只读取提交过的数据，但有时会发生一个事务读取同一个数据两次但两次结果不一致的情况。

同一个事务中，同一查询在不同时间点重复执行，但返回的结果不同。是由于在两次查询之间，另一个事务修改了查询涉及的数据，导致第二次查询返回了不同的数据。

例如：

事务A需要读取两次某顾客的积分数据，读取第一次时是10分，此时事务B把该积分更新为0分但还没有提交，然后事务A第二次读取积分为0分，这就发生了不可重复读取或不一致读取。

解决办法：

可以增加事务A的隔离等级解决，让它在执行过程中看不见其它事务的数据更改（即便是提交过的），使用 Repeatable Read 可重复读取，来保证读取的数据是可重复和一致的，无论过程中其它事务对数据做了何种更改，读取到的都是数据的初始状态。

4. Phantom Reads 幻读

在同一个事务内的两次查询之间，由于其他事务更新了一些数据，多了符合前一个查询条件的数据，导致第二次查询返回了不同数量的行，就是幻读。

例如：

事务A要查询所有积分超过10的顾客并向他们发送带折扣码的E-mail，查询后执行结束前，事务B更新了（可能是增删改）数据，然后多了一个满足条件的顾客，事务A执行结束后就会有这么一个满足条件的顾客没有收到折扣码，这就是幻读，在查询中错过了它因为它是在我们查询语句后才更新的。

解决办法：

取决于想解决的问题具体是什么样的，以及把这个顾客包括进事务中有多重要。如果不是必须的就再执行事务A来让这顾客包含进去。

但如果包含最新的所有满足条件的顾客是至关重要的，就要保证查询过程中没有任何其他可能影响查询结果的事务在进行，为此，建立另一个隔离等级叫 Serializable 序列化，它让事务能够知晓是否有其它事务正在进行可能影响查询结果的数据更改，并会等待这些事务执行完毕后再执行，这是最高的隔离等级，提供了最高的操作确定性。但是当用户和并发增加时，等待的时间会变长，系统会变慢，影响性能和可扩展性，出于这个原因，只有在必须要避免幻读的情况下才使用这个隔离等级。

1.4 事务隔离级别

为了解决上述的四个并发问题，有四个标准的事务隔离级别：

Read Uncommitted 读取未提交：无法解决任何一个问题，因为事务间并没有任何隔离，他们甚至可以读取彼此未提交的更改
Read Committed 读取已提交：给予事务一定的隔离，这样只能读取已提交的数据，这防止了Dirty Reads 脏读，但在这个级别下，事务仍可能读取同个内容两次但得到不同的结果，因为另一个事务可能在两次读取之间更新并提交了数据，也就是它不能防止Non-repeating Reads 不可重复读取（或 Inconsistent Read 不一致读取）
Repeatable Read 可重复读取：在这一级别下，可以确保不同的读取会返回相同的结果，即便数据在这期间被更改和提交
Serializable 序列化：可以防止以上所有问题，这一级别还能防止幻读，如果数据在执行过程中改变了，事务会等待获取到最新的数据，但这很明显会给服务器增加负担，因为管理等待的事务需要消耗额外的储存和CPU资源

并发问题 VS 性能和可扩展性：

更低的隔离级别更容易并发，会有更多用户能在相同时间接触到相同数据，但也因此会有更多的并发问题，另一方面因为隔离的锁定更少，性能会更高。

相反，更高的隔离等级限制了并发也减少了并发问题，但代价是性能和可扩展性的降低，因为需要更多的锁定和资源

MySQL的默认等级是 Repeatable Read 可重复读取，它可以防止除幻读外的所有并发问题并且比序列化更快，多数情况下应该保持这个默认等级。

如果对于某个特定的事务，防止幻读至关重要，可以改为 Serializable 序列化来防止幻读。

对于某些对数据一致性要求不高的批量报告或者对于数据很少更新的情况，同时又想获得更好性能时，可考虑前两种等级

总的来说，一般都保持默认隔离等级，只在特别需要时才做改变。

1.4.1 设置隔离级别的方法

读取隔离级别：

SHOW VARIABLES LIKE 'transaction_isolation';

显示默认隔离级别为 'REPEATABLE READ 可重复读取'

修改隔离级别：

SET [SESSION]/[GLOBAL] TRANSACTION ISOLATION LEVEL SERIALIZABLE;

默认设定的是下一次事务的隔离等级，加上 SESSION 是设置本次会话（链接）之后所有事务的隔离级别，加上 GLOBAL 就是设置之后所有对话的所有事务的隔离级别。

如果是应用开发人员，你的应用内有一个功能或函数可以链接数据库来执行某一事务（可能是利用对象关系映射或是直接连接MySQL），你就可以连接数据库，用 SESSION 关键词设置本次链接的事务的隔离等级，然后执行事务，最后断开连接，这样数据库的其它事务就不会受影响

1.4.1.1 读取未提交隔离级别

主要通过模拟脏读来表明 Read Uncommitted（读取未提交）为最低的隔离等级并会遇到所有并发问题。

案例：

建立链接1和链接2，模拟用户1和用户2，分别执行如下语句：

链接1：

查询顾客1的积分，用于之后的商业决策（如确定折扣等级）

注意里面的 SELECT 查询语句虽然没被 START TRANSACTION + COMMIT 包裹，但由于 autucommit，MySQL会把执行的每一条没错误的语句包装在事务中并自动提交，所以这个查询语句也是一个事务，隔离等级为上一句设定的 READ UNCOMMITTED（读取未提交）

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 设置隔离级别为读取未提交
SELECT points
FROM customers
WHERE customer_id = 1

链接2：

建立事务，将顾客1的积分（由原本的2293）改为20

START TRANSACTION;
UPDATE customers
SET points = 20
WHERE customer_id = 1;
ROLLBACK;

模拟过程:

链接1将下一次事务的隔离等级设定为 READ UNCOMMITTED 读取未提交

→ 链接2执行了更新但尚未提交

→ 链接1执行了查询，得到结果为尚未提交的数据，即查询结果为20分而非原本的2293分

→ 链接2的更新事务回滚了（可能是手动退回也可能是因故障中断）

这样我们的对话1就使用了一个数据库中从未存在过的值，这就是脏读问题，总之，READ UNCOMMITTED 读取未提交 是最低的隔离级别，在这一级别我们会遇到所有的并发问题。

1.4.1.2 读取已提交隔离级别

Read Committed 读取已提交等级只会读取别人已提交的数据，所以不会发生脏读，但因为能够读取到执行过程中别人已提交的更改，所以还是会发生不可重复读取（不一致读取）的问题。

由于每个事务只能看到已提交的数据，而不能看到其他事务尚未提交的数据，因此可能会发生更新丢失（Lost Update）的问题。所以也不能避免更新丢失的问题

案例1：不会发生脏读

把上一节链接1的设置隔离级别设置为 READ COMMITTED 读取已提交等级，会发现链接1不会读取到链接2未提交的更改，只有当改为20分的事务提交以后才能被链接1查询到。

案例2：可能会发生不可重复读取（不一致读取）

虽然不会存在脏读，但会出现其他的并发问题，如 Non-repeating Reads 不可重复读取，即在一个事务中你会两次读取相同的内容，但每次都得到不同的值

模拟该问题，将顾客1的分数还原为2293，将上面的连接1里的语句变为两次相同的查询（查询1号顾客的积分），连接2里的UPDATE语句不变，还是将1号顾客的积分（由原本的2293）更改为20

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT points FROM customers WHERE customer_id = 1;
SELECT points FROM customers WHERE customer_id = 1;
COMMIT;

注意虽然案例1里已经执行过一次 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 但这里还是要再执行一次，因为该语句是设定（本对话内下一次）事务的隔离等级，如果这里不执行，事务就会恢复为MySQL默认隔离等级，即 Repeatable Read 可重复读取

因为这里事务里有两个语句，所以必须添加 START TRANSACTION + COMMIT 包装成一个事务，否则autocommit会把它们分别包装形成两个事务

模拟过程：

再次设定隔离等级为 READ COMMITTED，启动事务，执行第一次查询，得到分数为2293

→ 执行链接2的 UPDATE 语句并提交

→ 再执行链接1的第二次查询，得到分数为20，同一个事务里的两次查询得到不同的结果，发生了 Non-repeating Reads 不可重复读取（或 Inconsistent Read 不一致读取）

1.4.1.3 可重复读取隔离界别

在这一默认级别上，只会读取已提交的更改，同一个事务内读取会始终保持一致性，但会忽视正在进行但未提交可能影响查询结果的更改，而漏掉一些结果，发生幻读。这个默认级别还能避免更新丢失问题。

MySQL默认等级是 REPEATABLE READ（重复读取）而且MySQL默认会在执行事务内的增删改语句时锁定相关行，可以判断 REPEATABLE READ（重复读取）正是通过执行修改语句时锁定相关行来避免更新丢失问题的（不过执行查询语句时应该不是通过锁定而只是通过记忆原始值来保证一致读取的，因为查询语句中途并不会阻止别人更改相关行）。

案例1：不会发生不可重复读取（不一致读取）

此案例和上一个案例完全一样，只是把隔离等级的设定语句改为了 REPEATABLE READ 可重复读取，发现两次查询中途别人把积分从2293改为20不会影响两次查询的结果，都是初始状态的20分，没有发生不可重复读取。

案例2：可能发生幻读

这一级别是会发生幻读的问题，一个模拟情形如下：

用户1：查询在 'VA' 州的顾客

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM customers WHERE state = 'VA';
SELECT points FROM customers WHERE customer_id = 1;
COMMIT;

用户2：将1号顾客所在州更改为 'VA'

START TRANSACTION;
UPDATE customers
SET state = 'VA'
WHERE customer_id = 1;
COMMIT;

假设customer表中原本只有2号顾客在维州（'VA'）

→ 用户2现在将1号顾客也改为VA州，已执行UPDATE但还没提交，所以这个更改还在内存里。
→ 此时用户1查询身处VA州的顾客，只会查到2号顾客
→ 用户2提交更改
→ 若1号用户未提交，再执行一次事务中的查询语句会还是只有2号顾客，因为在 REPEATABLE READ 可重复读取隔离级别，读取会保持一致性。
→ 若1号用户提交后再执行一次查询，会得到1号和2号两个顾客的结果，之前的查询遗漏了2号顾客，这被称作为幻读。

这一等级下1号用户的事务只顾读取当前已提交的数据，不能察觉现在进行但还未提交的可能对查询结果造成影响的更改，导致遗漏这些新的“幽灵”结果

1.4.1.4 序列化隔离级别

案例

和上面那个案例一样，把用户1事务的隔离级别设置为 SERIALIZABLE 序列化，

模拟场景如下：

→ 用户2现在将1号顾客也改为VA州，已执行UPDATE语句但还没有提交，所以这个更改还在内存里
→ 此时用户1查询身处VA州的顾客，会察觉到用户2的事务正在进行，因而会等待用户2完成
→ 用户2提交更改
→ 用户1的查询语句执行并返回最新结果：顾客1和顾客2

1.5 死锁

如果两个同时在进行的事务分别锁定了对方下一步要使用的行，会互相等待对方执行完成，导致无法执行后面语句，就会发生死锁，死锁不能完全避免但有一些方法能减少其发生的可能性。

案例

用户1：将1号顾客的州改为'VA'，再将1号订单的状态改为1

USE sql_store;
START TRANSACTION;
UPDATE customers SET state = 'VA' WHERE customer_id = 1;
UPDATE orders SET status = 1 WHERE order_id = 1;
COMMIT;

用户2：和用户1完全相同的两次更改，但顺序颠倒

USE sql_store;
START TRANSACTION;
UPDATE orders SET status = 1 WHERE order_id = 1;
UPDATE customers SET state = 'VA' WHERE customer_id = 1;
COMMIT;

模拟场景：

用户1和2均执行完各自的第一个更改
→ 用户2执行第二个更改，但此时用户1正在修改该字段已经将该字段锁定，只能等待用户1完成
→ 用户1执行第二个更改，该字段又被用户2锁定，两个操作互相等待，就会出现死锁，报错：Error Code: 1213. Deadlock found ……

缓解方法

死锁如果只是偶尔发生一般不是什么问题，重新尝试或提醒用户重新尝试即可，死锁不可能完全避免，但有一些方法可以最小化其发生的概率：

1. 注意语句顺序：如果两个事务总是发生死锁，检查它们的代码，这些事务可能是储存过程的一部分，看一下事务里的语句顺序，如果这些事务以相反的顺序更新记录，就很可能出现死锁，为了减少死锁，在更新多条记录时遵循相同的顺序。
2. 尽量让事务小一些，锁的持续时间短一些，这样就不太容易和其他事务相冲突。
3. 如果事务要操作非常大的表，运行时间可能会很长，冲突的风险就会很高，让这样的事物避开高峰期运行，以避开大量活跃用户。

菠萝蜜blm

关注

32
点赞
踩
17

收藏

觉得还不错? 一键收藏
0
评论
什么是事务和并发

现在有两个会话（注意是两个链接（connection），而不是同一个会话下的两个SQL标签，这两个链接相当于是在模拟两个用户）都要执行这段语句，用调试逐句执行，当第一个执行到UPDATE 而还没有 COMMIT 提交时，转到第二个会话，执行到UPDATE语句时会一直等待执行（若等的时间太久会超时而放弃执行），这时跳回第一个对话 COMMIT 提交，第二个会话的 UDDATE 才执行成功，最后将第二段对话的事务也COMMIT提交，此时刷新顾客表会发现1号顾客的积分多了20分。换成 ROLLBACK;
复制链接

扫一扫