五种主流数据库：事务处理

于 2024-05-23 21:00:00 发布
阅读量1.6k 收藏 44
点赞数 39
分类专栏： 数据库 文章标签： 数据库 sql mysql oracle postgresql sqlserver sqlite
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/horses/article/details/139135385
版权

DML 语句执行的数据增删改合操作可以通过事务控制语言（Transaction Control Language，TCL）进行提交（确认数据的修改）或者撤销（取消数据的修改）。

本文将会介绍五种主流数据库中事务的概念、事务控制语句以及并发事务的隔离问题，包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。

功能MySQLOracleSQL ServerPostgreSQLSQLite
ACID属性✔️✔️✔️✔️✔️
事务控制语句✔️✔️✔️✔️✔️
隔离级别读未提交
读已提交
【可重复读】
串行化		【读已提交】
串行化		读未提交
【读已提交】
可重复读
串行化		【读已提交】
可重复读
串行化		【串行化】

数据库事务

在数据库中，事务（Transaction）指的是一组相关的 SQL 语句，它们在业务逻辑上组成一个原子单元。数据库管理系统必须保证一个事务中的所有操作全部提交或者全部撤销。

最常见的数据库事务就是银行账户之间的转账操作。例如，从账户 A 转出 1000 元到账户 B。此时，数据库的操作流程如下图所示。

在这里插入图片描述
上图是一个简化的转账流程，实际上银行转账还需要检查账户的状态、判断是否收取转账费用等。以上流程包括以下几个步骤：

  1. 开始转账流程。查询账户 A 的余额是否足够，如果其余额不足，则终止转账。
  2. 从账户 A 扣除 1000 元。
  3. 往账户 B 存入 1000 元。
  4. 在系统中记录本次转账的交易流水。
  5. 提交并结束本次转账流程。

数据库管理系统必须保证以上所有操作要么全部成功，要么全部失败。如果从账户 A 扣除 1000 元成功，但是往账户 B 存入 1000 元失败，就意味着账户 A 将会损失 1000 元。用数据库的术语来说，这种情况导致了数据的不一致性。

数据库管理系统的最重要功能就是确保数据的一致性和完整性。在用户执行操作的过程中，数据库可能会遇到系统崩溃、介质失效等故障，此时数据库必须能够从失败的状态恢复到一致的状态。为了实现这些核心功能，数据库中的事务需要满足 4 种基本的属性。

事务的 ACID 属性

按照 SQL 标准，数据库中的事务具有原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）以及持久性（Durability），也就是 ACID 属性：

  • 原子性指的是一个事务中的操作要么全部成功，要么全部失败。例如，某个事务需要更新 100 条记录，但是在更新到一半时系统出现故障，数据库必须保证能够撤销已经修改过的数据，就像没有执行过任何更新一样。
  • 一致性意味着在事务开始之前数据库处于一致性的状态，事务完成之后数据库仍然处于一致性的状态。例如，在银行转账事务中如果一个账户扣款成功，但是另一个账户入账失败，就会出现数据不一致（此时需要撤销已经执行的扣款操作）的情况。另外，数据库还必须确保数据的完整性，比如账户扣款之后不能出现余额为负数的情况（可以通过余额字段上的检查约束实现）。
  • 隔离性与并发事务有关，表示一个事务的修改在提交之前对其他事务不可见，多个并发的事务之间相互隔离。例如，在账户 A 向账户 B 转账的过程中，账户 B 查询的余额应该是转账之前的数目。如果多个账户同时向账户 B 转账，最终账户 B 的余额也应该保持一致性，和多个账户依次进行转账的结果一样。SQL 标准定义了 4 种不同的事务隔离级别，我们将会在下文中进行介绍。
  • 持久性表示已经提交的事务必须永久生效，即使发生断电、系统崩溃等故障，数据库也不会丢失数据。数据库管理系统通常使用重做日志（REDO）或者预写式日志（WAL）实现事务的持久性。简单来说，它们都是在提交之前将数据的修改记录到日志文件中，当数据库出现崩溃时就可以利用这些日志重做之前的修改，从而避免数据的丢失。

下面我们介绍如何通过事务控制语句来实现事务的提交和撤销等操作。

事务控制语句

SQL 标准定义了以下用于管理数据库事务的事务控制语句：

  • START TRANSACTION，开始一个新的事务。
  • COMMIT，提交一个事务。
  • ROLLBACK，撤销一个事务。
  • SAVEPOINT name，设置一个事务保存点，用于撤销部分事务。
  • RELEASE SAVEPOINT name，释放事务保存点。
  • ROLLBACK TO name，将事务撤销到保存点，保存点之前的修改仍然保留。

五种主流数据库对于 SQL 事务控制语句的支持如下表所示。

事务控制MySQL（InnoDB）OracleSQL ServerPostgreSQLSQLite
开始事务START TRANSACTION、BEGIN自动开始BEGIN TRANSACTIONSTART TRANSACTION、BEGINBEGIN
提交事务COMMITCOMMITCOMMITCOMMITCOMMIT
撤销事务ROLLBACKROLLBACKROLLBACK TRANSACTIONROLLBACKROLLBACK
设置事务保存点SAVEPOINTSAVEPOINTSAVE TRANSACTIONSAVEPOINTSAVEPOINT
释放事务保存点RELEASE SAVEPOINT系统自动管理系统自动管理RELEASE SAVEPOINTRELEASE SAVEPOINT
撤销到保存点ROLLBACK TOROLLBACK TOROLLBACK TRANSACTIONROLLBACK TOROLLBACK TO
设置自动提交默认自动提交，SET autocommit = {ON | OFF}手动提交，不支持设置默认自动提交，SET IMPLICIT_TRANSACTIONS {ON | OFF}自动提交，不支持设置自动提交，不支持设置

下面演示一下这些事务控制语句的作用，为此首先需要创建一个银行账户表：

CREATE TABLE bank_card(
  card_id   VARCHAR(20) NOT NULL PRIMARY KEY, -- 卡号
  user_name VARCHAR(50) NOT NULL, -- 用户名
  balance   NUMERIC(10,4) NOT NULL, -- 余额
  CHECK (balance >= 0)
);

bank_card 是一个简化的账户表，只包含银行卡号、用户名以及余额信息，同时要求余额大于或等于 0。

开始事务

我们首先为 bank_card 表增加一个账户：

-- Oracle 无须执行 BEGIN 语句
-- Microsoft SQL Server 使用 BEGIN TRANSACTION 语句
BEGIN;
INSERT INTO bank_card VALUES ('62220801', 'A', 1000);

其中，BEGIN 语句表示开始一个新的事务，INSERT 语句表示插入一条记录。

Oracle 数据库自动开始一个新的事务，无须执行 BEGIN 语句。Microsoft SQL Server 使用 BEGIN TRANSACTION 语句开始一个新的事务。

此时，如果我们打开另一个数据库连接，查询 bank_card 表不会返回任何结果。这是因为数据库事务具有隔离性，而我们还没有提交上面的数据修改。

提交事务

为了使得上面的数据修改生效，我们可以使用 COMMIT 语句提交当前事务：

COMMIT;

此时，我们可以在另一个数据库连接中查询到账户 A，即使服务器出现故障而崩溃，数据库也能够保证数据不会丢失。

除 Oracle 外，其他数据库在修改数据之后都无须执行 COMMIT 语句。这是因为它们默认都会自动提交 DML 语句的操作。以 MySQL 为例，它默认启用了自动提交（autocommit）功能：

-- MySQL
show variables like 'autocommit';
Variable_name|Value
-------------|-----
autocommit   |ON

show 命令可以查看 MySQL 中的变量设置，启用 autocommit 相当于在每个语句之后自动执行了一个 COMMIT 语句。我们也可以将该变量设置为 OFF，关闭自动提交功能。

对于 Microsoft SQL Server，我们可以使用 IMPLICIT_TRANSACTIONS 变量控制事务的自动提交。默认情况下该参数为 OFF，表示启用自动提交功能；将其设置为 ON，表示自动开始一个新的事务，但是需要手动进行提交。

PostgreSQL 和 SQLite 默认启用自动提交功能，它们同时也可以通过 BEGIN 加上 COMMIT或者 ROLLBACK 的事务控制方式来提交事务，但其不支持自动提交的设置。

提示：许多数据库客户端和开发工具提供了自动提交和手动提交的设置功能，实际上它们都是通过在后台隐式执行 COMMIT 或者 BEGIN 语句来实现的。

撤销事务

如果我们想要撤销没有提交的事务，可以使用 ROLLBACK 语句，例如：

-- Oracle 无须执行 BEGIN 语句，Microsoft SQL Server 使用 BEGIN TRANSACTION 语句
BEGIN;
INSERT INTO bank_card VALUES ('62220802', 'B', 500);
ROLLBACK; -- Microsoft SQL Server 使用 ROLLBACK TRANSACTION 语句
SELECT card_id, user_name, balance
FROM bank_card;

其中，BEGIN 语句表示开始一个新的事务，然后使用 INSERT 语句插入一条记录，最后的 ROLLBACK 语句撤销了该事务。

Oracle 数据库自动开始一个新的事务，无须执行 BEGIN 语句。Microsoft SQL Server 使用 BEGIN TRANSACTION 语句开始一个新的事务，使用 ROLLBACK TRANSACTION 语句撤销一个事务。

我们执行以上语句之后不会创建账户 B，查询返回的结果如下：

card_id |user_name|balance 
--------|---------|---------
62220801|A        |1000.0000

事务保存点

事务保存点（Savepoint）可以实现数据库事务的部分撤销，例如：

-- Oracle 无须执行 BEGIN 语句，Microsoft SQL Server 使用 BEGIN TRANSACTION 语句
BEGIN;
INSERT INTO bank_card VALUES ('62220802', 'B', 500);
SAVEPOINT sv; -- Microsoft SQL Server 使用 SAVE TRANSACTION 语句
INSERT INTO bank_card VALUES ('62220803', 'C', 2000);
ROLLBACK TO sv; -- Microsoft SQL Server 使用 ROLLBACK TRANSACTION 语句
COMMIT;

其中，BEGIN 语句表示开始一个新的事务，然后使用 INSERT 语句插入一条记录并设置保存点 sv，接着插入另一条记录并使用 ROLLBACK 语句撤销该事务到保存点 sv，此时第一个插入语句的修改没有被撤销，最后使用 COMMIT 语句提交事务。

Oracle 数据库自动开始一个新的事务，无须执行 BEGIN 语句。Microsoft SQL Server 使用 BEGIN TRANSACTION 语句开始一个新的事务，使用 SAVE TRANSACTION 语句设置事务保存点，使用 ROLLBACK TRANSACTION 语句撤销事务。

我们执行以上语句之后不会创建账户 C，但是会创建账户 B。如果我们再次查询 bank_card表，将会返回如下结果：

card_id |user_name|balance 
--------|---------|---------
62220801|A        |1000.0000
62220802|B        | 500.0000

注意：数据库中的某些操作可能会导致隐式的提交操作，相当于执行了一次 COMMIT语句，常见的这类语句包括 DDL、DBA 执行的管理操作以及数据库备份恢复等。

并发事务与隔离级别

在企业应用中，数据库通常需要支持多用户的并发访问，这就意味着我们在操作数据的同时，其他人或者应用程序可能也在操作相同的数据。此时数据库管理系统必须保证多个用户之间不会产生相互影响，数据不会出现不一致性。

提示：SQLite 通常只支持单个进程访问数据库，不存在并发的问题。因此本节内容不适用于 SQLite 数据库。

并发问题

数据库的并发事务意味着多个用户同时访问相同的数据，比如账户 A 和账户 C 同时给账户 B 转账。数据库的并发访问可能会带来以下问题：

  • 脏读（Dirty Read）。当一个事务允许读取另一个事务修改但未提交的数据时，就可能发生脏读。例如，账户 B 的初始余额为 0，账户 A 向账户 B 转账 1000 元但没有提交。如果此时账户 B 能够看到账户 A 转过来的 1000 元，并且取款 1000 元，然后 A 账户取消了转账操作，意味着银行损失了 1000 元。显然，银行不会允许这种事情发生。
  • 不可重复读（Nonrepeatable Read）。一个事务读取某条记录后，该数据被另一个事务修改并提交，该事务再次读取相同的记录时，结果发生了变化。例如，在对账户 B 进行查询时的初始余额为 0，此时账户 A 向账户 B 转账 1000 元并且提交成功，之后对账户 B 再次查询时，发现余额变成了 1000 元。这种情况并不会导致数据的不一致。
  • 幻读（Phantom Read）。一个事务第一次读取数据后，另一个事务增加或者删除了某些记录，导致该事务再次读取时返回结果的数量发生了变化。幻读和不可重复读有点类似，都是由于其他事务修改数据导致的结果变化。
  • 更新丢失（Lost Update）。第一类更新丢失指的是，当两个事务更新相同的数据时，第一个事务被提交，之后第二个事务被撤销，这导致第一个事务的更新也被撤销。所有遵循SQL 标准的数据库都不会产生第一类更新丢失。第二类更新丢失指的是，当两个事务同时读取某个记录后分别进行修改提交，造成先提交事务的修改丢失。

为了解决数据库并发访问可能导致的各种问题，SQL 标准定义了事务的隔离级别。

隔离级别

SQL 标准定义了 4 种不同的事务隔离级别，它们（从低到高排列）可能产生的问题如下表所示：

隔离级别脏读不可重复读幻读更新丢失
读未提交（Read Uncommitted）可能可能可能第二类
读已提交（Read Committed）–可能可能第二类
可重复读（Repeatable Read）––可能–
序列化（Serializable）––––

读未提交隔离级别最低：一个事务可以看到其他事务未提交的修改，相当于不隔离。该级别可能产生各种并发异常。Oracle 不支持读未提交隔离级别。PostgreSQL 读未提交隔离级别的实现等同于读已提交隔离级别的实现。

读已提交隔离级别：使用该隔离级别，只能看到其他事务已经提交的数据，因此不会出现脏读，但是存在不可重复读、幻读和第二类更新丢失问题。“读已提交”是大部分数据库的默认隔离级别，包括 Oracle、Microsoft SQL Server 以及 PostgreSQL。

可重复读隔离级别：使用该隔离级别，可能出现幻读。MySQL（InnoDB）和 PostgreSQL 在可重复读隔离级别消除了幻读，但是存在第二类更新丢失问题。MySQL（InnoDB）默认使用可重复读隔离级别。Oracle 不支持可重复读隔离级别。

序列化提供了最高级别的事务隔离，它要求事务只能一个接着一个地执行，不支持并发访问。SQLite 实际上实现的就是这种隔离级别。

事务的隔离级别越高，越能保证数据的一致性，但同时会对并发带来更大的影响。我们通常使用数据库的默认隔离级别，至少可以避免脏读，同时拥有不错的并发性能。尽管可能存在不可重复读（某些数据库不会）、幻读以及更新丢失的问题，但是它们并不一定会导致数据的不一致性，而且我们在必要时可以通过应用程序进行处理或者设置为更高的隔离级别。

案例分析

接下来我们通过一个案例演示读已提交隔离级别的作用和存在的问题。如果使用 MySQL数据库，我们首先需要在每个连接会话中执行以下语句将隔离级别设置为读已提交：

-- MySQL
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION 是标准 SQL 语句，除 SQLite 外的其他数据库都支持该语句。

我们打开一个连接会话，开始一个事务并查询账户 A 的余额：

-- 会话 1
-- Oracle 无须执行 BEGIN 语句，Microsoft SQL Server 使用 BEGIN TRANSACTION 语句
BEGIN;
SELECT card_id, user_name, balance
FROM bank_card
WHERE user_name = 'A';

card_id |user_name|balance 
--------|---------|---------
62220801|A        |1000.0000

账户 A 的当前余额为 1000 元。然后我们打开另一个连接会话，开始一个事务并修改账户 A 的余额：

-- 会话 2
-- Oracle 无须执行 BEGIN 语句，Microsoft SQL Server 使用 BEGIN TRANSACTION 语句
BEGIN;
UPDATE bank_card
SET balance = balance + 100
WHERE user_name = 'A';
SELECT card_id, user_name, balance
FROM bank_card
WHERE user_name = 'A';

card_id |user_name|balance 
--------|---------|---------
62220801|A        |1100.0000

在会话 2 中显示账户 A 的当前余额已经被修改为 1100 元。

此时我们再次查询会话 1 中的余额：

-- 会话 1
SELECT card_id, user_name, balance
FROM bank_card
WHERE user_name = 'A';
card_id |user_name|balance 
--------|---------|---------
62220801|A        |1000.0000

查询结果仍然是 1000，没有出现数据的脏读问题。然后我们在会话 2 中提交事务：

-- 会话 2
COMMIT;

接着我们再次查询会话 1 中的余额：

-- 会话 1
SELECT card_id, user_name, balance
FROM bank_card
WHERE user_name = 'A';

card_id |user_name|balance 
--------|---------|---------
62220801|A        |1100.0000

查询结果变成了“1100”，意味着会话 1 读取了会话 2 提交后的结果，同时也意味着发生了数据的不可重复读。

接着我们在会话 2 中开始一个新的事务，删除账户 A 并提交：

-- 会话 2
-- Oracle 无须执行 BEGIN 语句，Microsoft SQL Server 使用 BEGIN TRANSACTION 语句
BEGIN;
DELETE
FROM bank_card
WHERE user_name = 'A';
COMMIT;

我们再次查询会话 1 中的余额：

-- 会话 1
SELECT card_id, user_name, balance
FROM bank_card
WHERE user_name = 'A';

card_id |user_name|balance 
--------|---------|---------

查询没有返回任何数据，意味着此时产生了数据的幻读。

最后，我们演示一下并发修改可能导致的更新丢失问题。我们在会话 1 中给账户 B 转账 100 元但不提交事务：

-- 会话 1
UPDATE bank_card
SET balance = 500 + 100
WHERE user_name = 'B';
SELECT card_id, user_name, balance
FROM bank_card
WHERE user_name = 'B';

card_id |user_name|balance 
--------|---------|---------
62220802|B        |600.0000

我们继续在会话 2 中开始一个新的事务，并且给账户 B 转账 200 元：

-- 会话 2
-- Oracle 无须执行 BEGIN 语句，Microsoft SQL Server 使用 BEGIN TRANSACTION 语句
BEGIN;
SELECT card_id, user_name, balance
FROM bank_card
WHERE user_name = 'B';

card_id |user_name|balance 
--------|---------|---------
62220802|B        |500.0000

UPDATE bank_card
SET balance = 500 + 200
WHERE user_name = 'B';

此时会话 2 将会处于等待状态。因为当一个事务已经修改某个记录但未提交时，另一个事务不允许同时修改该记录，数据库的并发写操作一定是按照顺序执行的。

然后我们在会话 1 中提交事务：

-- 会话 1
COMMIT;

提交之后会话 2 中的更新语句可以正常执行，我们在会话 2 中也提交事务：

-- 会话 2
COMMIT;

最后，账户 B 的余额为 700 元。我们总共为账户 B 转账了 300 元，正确的结果应该是“800”，会话 1 中的更新丢失了。问题的原因在于会话 2 无法得知会话 1 的修改。对于我们给出的这个示例比较好解决，可以使用以下语句替换上面的 UPDATE 语句：

UPDATE bank_card
SET balance = balance + 200
WHERE user_name = 'B';

每次更新账户余额时基于字段值进行更新，而不是使用之前查询返回的结果（500）。

提示：解决更新丢失问题的其他方法包括悲观锁、乐观锁以及设置更高的隔离级别等，具体内容超出了本文的讨论范畴。

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

不剪发的Tony老师

为 5 个 C 币而折腰。

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值