思考:
DB:MySQL 5.7.x,8.0.x
生成测试数据:
use cjc;
drop table if exists t1021;
drop table if exists t1021_bak;
create table t1021(id int,name varchar(10),time time,primary key(id));
create table t1021_bak(id int,name varchar(10),time time,primary key(id));
insert into t1021 values(1,'x',now());
insert into t1021 values(2,'y',now());
insert into t1021 values(3,'z',now());
insert into t1021_bak values(3,'xxx',now());
创建测试脚本:
vi 01.sql
START TRANSACTION;
insert into t1021 values(4,'a',now());
insert into t1021 values(5,'a',now());
insert into t1021_bak(select * from t1021 where id=3);
delete from t1021 where id=3;
commit;
问题:
01.sql脚本中第3个insert因主键冲突会失败，执行01.sql脚本后，这些sql会部分成功，还是全部回滚？
因为mysql执行脚本有多种方式，分场景进行说明:
场景1.source方式执行:
这种方式和手动一条一条执行类似，都是遇到错误跳过，并继续执行后面的sql。
mysql> source /home/mysql/scripts/01.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
ERROR 1062 (23000): Duplicate entry '3' for key 't1021_bak.PRIMARY'
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
可以看到，这个事务里，只有第三条insert因主键冲突失败了，其他部分都执行成功了，看上去好像一个事务里的部分sql成功？部分sql失败？和事务原子性冲突了？实际上并没有。如果使用第三条insert来进行备份数据，第四条delete来清理数据库的话，可能会出现备份失败，但删除成功的场景。
mysql> select * from cjc.t1021;
+----+------+----------+
| id | name | time |
+----+------+----------+
| 1 | x | 17:25:08 |
| 2 | y | 17:25:08 |
| 4 | a | 17:32:11 |
| 5 | a | 17:32:11 |
+----+------+----------+
4 rows in set (0.00 sec)
mysql> select * from cjc.t1021_bak;
+----+------+----------+
| id | name | time |
+----+------+----------+
| 3 | xxx | 17:25:08 |
+----+------+----------+
1 row in set (0.00 sec)
场景2.mysql < .sql方式执行:
初始化测试数据:
drop table if exists t1021;
drop table if exists t1021_bak;
create table t1021(id int,name varchar(10),time time,primary key(id));
create table t1021_bak(id int,name varchar(10),time time,primary key(id));
insert into t1021 values(1,'x',now());
insert into t1021 values(2,'y',now());
insert into t1021 values(3,'z',now());
insert into t1021_bak values(3,'xxx',now());
通过 mysql < .sql方式执行sql脚本:
[mysql@cjc-db-01 ~]$ mysql -uroot -p*** -D cjc < /home/mysql/scripts/01.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1062 (23000) at line 4: Duplicate entry '3' for key 't1021_bak.PRIMARY'
查询数据:
因为第三条insert因主键冲突失败，直接中断了，不会继续执行后面的操作，整个事务都回滚了，看上去似乎符合事务的原子性。
mysql> select * from t1021;
+----+------+----------+
| id | name | time |
+----+------+----------+
| 1 | x | 17:36:48 |
| 2 | y | 17:36:48 |
| 3 | z | 17:36:48 |
+----+------+----------+
3 rows in set (0.00 sec)
mysql> select * from t1021_bak;
+----+------+----------+
| id | name | time |
+----+------+----------+
| 3 | xxx | 17:36:48 |
+----+------+----------+
1 row in set (0.00 sec)
场景3.SQL_MODE参数
事务行为还和sql_mode参数有关，这里还没来得及测试，计划后面单独写一篇sql_mode的文章。
https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html
如何安全实现事务原子性:
数据库端实现原子性:
实际上，更保险的方式是人为加入判断，判断一个事务内所有SQL的执行情况，如有失败，全部回滚，如全成功，才能提交，例如:
原脚本:
vi 01.sql
START TRANSACTION;
insert into t1021_bak(select * from t1021 where id=3);
delete from t1021 where id=3;
commit;
修改后的脚本:
改成了存储过程，详细判断了每条语句的执行结果，如果部分失败，回滚整个事务，
注意，以下存储过程由deepseek生成，仅用于测试，请勿直接用于生产！！！
存储过程如下：
DELIMITER //
CREATE PROCEDURE safe_data_transfer_detailed()
BEGIN
DECLARE source_count INT DEFAULT 0;
DECLARE insert_count INT DEFAULT 0;
DECLARE delete_count INT DEFAULT 0;
DECLARE error_occurred INT DEFAULT 0;
DECLARE error_message TEXT;
-- 声明异常处理器
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
error_message = MESSAGE_TEXT;
SET error_occurred = 1;
END;
START TRANSACTION;
-- 获取源数据行数
SELECT COUNT(*) INTO source_count FROM t1021 WHERE id = 3;
IF source_count = 0 THEN
ROLLBACK;
SELECT '失败：源数据不存在（id=3的记录未找到）' AS result;
ELSE
-- 重置错误状态
SET error_occurred = 0;
SET error_message = NULL;
-- 执行插入操作
INSERT INTO t1021_bak SELECT * FROM t1021 WHERE id = 3;
IF error_occurred = 1 THEN
ROLLBACK;
SELECT
'失败：插入操作异常' AS result,
CONCAT('错误详情：', IFNULL(error_message, '未知错误')) AS error_details,
'可能原因：主键冲突、数据类型不匹配、表结构不一致等' AS possible_causes;
ELSE
SET insert_count = ROW_COUNT();
IF insert_count != source_count THEN
ROLLBACK;
SELECT
'失败：插入行数与源数据行数不匹配' AS result,
CONCAT('期望插入: ', source_count, ' 行, 实际插入: ', insert_count, ' 行') AS details;
ELSE
-- 重置错误状态
SET error_occurred = 0;
SET error_message = NULL;
-- 执行删除操作
DELETE FROM t1021 WHERE id = 3;
IF error_occurred = 1 THEN
ROLLBACK;
SELECT
'失败：删除操作异常' AS result,
CONCAT('错误详情：', IFNULL(error_message, '未知错误')) AS error_details;
ELSE
SET delete_count = ROW_COUNT();
IF delete_count != source_count THEN
ROLLBACK;
SELECT
'失败：删除行数与源数据行数不匹配' AS result,
CONCAT('期望删除: ', source_count, ' 行, 实际删除: ', delete_count, ' 行') AS details;
ELSE
COMMIT;
SELECT
'成功：数据转移完成' AS result,
CONCAT('成功转移 ', source_count, ' 条记录') AS details;
END IF;
END IF;
END IF;
END IF;
END IF;
END //
DELIMITER ;
调用存储过程：
执行结果如下:
mysql> CALL safe_data_transfer_detailed();
+-----------------------------+----------------------------------------------------------------+-----------------------------------------------------------------------------+
| result | error_details | possible_causes |
+-----------------------------+----------------------------------------------------------------+-----------------------------------------------------------------------------+
| 失败：插入操作异常 | 错误详情：Duplicate entry '3' for key 't1021_bak.PRIMARY' | 可能原因：主键冲突、数据类型不匹配、表结构不一致等 |
+-----------------------------+----------------------------------------------------------------+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)
查看数据:回滚了整个事务
mysql> select * from t1021;
+----+------+----------+
| id | name | time |
+----+------+----------+
| 1 | x | 18:06:28 |
| 2 | y | 18:06:28 |
| 3 | z | 18:06:28 |
+----+------+----------+
3 rows in set (0.00 sec)
mysql> select * from t1021_bak;
+----+------+----------+
| id | name | time |
+----+------+----------+
| 3 | xxx | 18:06:29 |
+----+------+----------+
1 row in set (0.00 sec)
在看一下插入成功的效果:
先解决主键冲突:
update t1021_bak set id=100 where id=3;
在执行存储过程:
mysql> CALL safe_data_transfer_detailed();
+-----------------------------+--------------------------+
| result | details |
+-----------------------------+--------------------------+
| 成功：数据转移完成 | 成功转移 1 条记录 |
+-----------------------------+--------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1021;
+----+------+----------+
| id | name | time |
+----+------+----------+
| 1 | x | 18:06:28 |
| 2 | y | 18:06:28 |
+----+------+----------+
2 rows in set (0.00 sec)
mysql> select * from t1021_bak;
+-----+------+----------+
| id | name | time |
+-----+------+----------+
| 3 | z | 18:06:28 |
| 100 | xxx | 18:06:29 |
+-----+------+----------+
2 rows in set (0.00 sec)
除了存储过程以为，也可以使用:
SELECT ROW_COUNT()
来判断上一条插入的结果，例如:
mysql> insert into t1021 values(2,'z',now());
ERROR 1062 (23000): Duplicate entry '2' for key 't1021.PRIMARY'
mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| -1 |
+-------------+
1 row in set (0.00 sec)
mysql> insert into t1021 values(3,'z',now()),(4,'a',now()),(5,'h',now());
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)
代码端实现原子性
参考OceanBase官网解释的例子：
现实场景：
在一个电商平台中，当用户购买商品时，通常涉及多个操作：减少商品库存、扣除用户账户余额、生成订单记录等。如果在购买过程中发生了错误（比如用户账户余额不足，或库存已售空），原子性确保这些操作要么全部完成，要么全部撤销。
场景应用：电商平台的订单处理
/**
* 模拟电商平台中的订单处理（原子性）
* 如果库存不足或余额不足，订单应当回滚，确保数据一致。
*/
void processOrder(User& user, Product& product, int quantity) {
// 开始事务
startTransaction();
try {
// 检查库存
if (product.stock < quantity) {
throw std::runtime_error("库存不足");
}
// 扣除用户余额
user.debit(product.price * quantity);
// 更新商品库存
product.updateStock(-quantity);
// 提交事务
commitTransaction();
} catch (const std::exception& e) {
// 如果发生任何错误，回滚事务
rollbackTransaction();
throw e; // 将错误抛出
}
}
在这个例子中，如果库存不足或余额不足，事务将回滚，确保不会出现扣款成功但商品库存没有减少的异常状态。整个事务是原子性的，用户不会面临部分操作成功、部分操作失败的情形。
事务原子性概述:
最后看一下不同数据库，对事务原子性的定义，其中Oracle和OceanBase更详细一些。
Oracle:
https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/transactions.html
原子性
事务的所有任务要么全部执行，要么全部不执行。不存在部分完成的事务。例如，若某个事务开始更新100行数据，但在完成20行更新后系统发生故障，则数据库将回滚已更新的这20行数据。
Atomicity
All tasks of a transaction are performed or none of them are. There are no partial transactions. For example, if a transaction starts updating 100 rows, but the system fails after 20 updates, then the database rolls back the changes to these 20 rows.
语句级原子性
Oracle数据库支持语句级原子性，这意味着每条SQL语句都是一个原子工作单元，其结果要么完全成功要么完全失败。
成功的语句与已提交的事务不同。当数据库以原子单元形式无错误地解析并执行SQL语句时（例如在多行更新中所有行都被修改的情况），该单一SQL语句即执行成功。
若SQL语句在执行过程中引发错误，则该语句执行不成功，其所有操作效果都将被回滚。这种操作称为语句级回滚，具有以下特性：
(1)未成功执行的SQL语句仅导致其本应执行的工作丢失。
失败的语句不会导致当前事务中先前执行的工作丢失。例如，在"示例事务：账户借贷"中，若第二条UPDATE语句执行出错并被回滚，则第一条UPDATE语句已执行的工作不会被回滚。用户仍可显式提交或回滚第一条UPDATE语句。
(2)回滚的效果等同于该语句从未运行过。
原子语句的任何副作用（例如执行语句时触发的触发器）均被视为该原子语句的组成部分。作为原子语句部分产生的所有工作，要么全部成功，要么全部无效。
导致语句级回滚的错误示例如尝试插入重复主键值。涉及死锁（即对相同数据的竞争）的单一SQL语句也可能引发语句级回滚。但在SQL语句解析阶段发现的错误（如语法错误）因尚未执行，故不会触发语句级回滚。
Statement-Level Atomicity
Oracle Database supports statement-level atomicity, which means that a SQL statement is an atomic unit of work and either completely succeeds or completely fails.
A successful statement is different from a committed transaction. A single SQL statement executes successfully if the database parses and runs it without error as an atomic unit, as when all rows are changed in a multirow update.
If a SQL statement causes an error during execution, then it is not successful and so all effects of the statement are rolled back. This operation is a statement-level rollback. This operation has the following characteristics:
(1)A SQL statement that does not succeed causes the loss only of work it would have performed itself.
The unsuccessful statement does not cause the loss of any work that preceded it in the current transaction. For example, if the execution of the second UPDATE statement in "Sample Transaction: Account Debit and Credit" causes an error and is rolled back, then the work performed by the first UPDATE statement is not rolled back. The first UPDATE statement can be committed or rolled back explicitly by the user.
(2)The effect of the rollback is as if the statement had never been run.
Any side effects of an atomic statement, for example, triggers invoked upon execution of the statement, are considered part of the atomic statement. Either all work generated as part of the atomic statement succeeds or none does.
An example of an error causing a statement-level rollback is an attempt to insert a duplicate primary key. Single SQL statements involved in a deadlock, which is competition for the same data, can also cause a statement-level rollback. However, errors discovered during SQL statement parsing, such as a syntax error, have not yet been run and so do not cause a statement-level rollback.
MySQL:
https://dev.mysql.com/doc/refman/8.0/en/mysql-acid.html
原子性
ACID模型的原子性方面主要涉及InnoDB事务处理。相关的MySQL功能包括：
(1)自动提交设置
(2)COMMIT语句
(3)ROLLBACK语句
(4)操作期间的数据可见性
Atomicity
The atomicity aspect of the ACID model mainly involves InnoDB transactions. Related MySQL features include:
The autocommit setting.
The COMMIT statement.
The ROLLBACK statement.
https://dev.mysql.com/doc/refman/8.0/en/glossary.html
ACID
这是一个代表原子性、一致性、隔离性和持久性的缩写术语。这些特性都是数据库系统应具备的理想属性，并且都与事务概念紧密相关。InnoDB的事务特性严格遵循ACID原则。
事务是可作为原子工作单元进行提交或回滚的操作序列。当事务对数据库执行多项更改时，这些更改要么在事务提交时全部成功生效，要么在事务回滚时全部撤销。
数据库始终保持一致性状态——无论是在每次提交或回滚之后，还是在事务执行过程中。如果跨多个表更新关联数据，查询结果将呈现所有旧值或所有新值，而不会出现新旧值混杂的情况。
事务在执行过程中相互受保护（隔离）：它们既不会相互干扰，也无法查看彼此未提交的数据。这种隔离是通过锁定机制实现的。有经验的用户可以在确认事务确实不会相互干扰时，通过调整隔离级别来权衡保护强度以获取更高性能和并发性。
事务执行结果具有持久性：一旦提交操作成功，该事务所做的更改将免受电源故障、系统崩溃、竞争条件或其他许多非数据库应用易受潜在危险的影响。持久性通常涉及写入磁盘存储，并配备一定冗余机制以防止写入过程中发生断电或软件崩溃。（在InnoDB中，双写缓冲机制为持久性提供了支持。）
ACID
An acronym standing for atomicity, consistency, isolation, and durability. These properties are all desirable in a database system, and are all closely tied to the notion of a transaction. The transactional features of InnoDB adhere to the ACID principles.
Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back.
The database remains in a consistent state at all times — after each commit or rollback, and while transactions are in progress. If related data is being updated across multiple tables, queries see either all old values or all new values, not a mix of old and new values.
Transactions are protected (isolated) from each other while they are in progress; they cannot interfere with each other or see each other's uncommitted data. This isolation is achieved through the locking mechanism. Experienced users can adjust the isolation level, trading off less protection in favor of increased performance and concurrency, when they can be sure that the transactions really do not interfere with each other.
The results of transactions are durable: once a commit operation succeeds, the changes made by that transaction are safe from power failures, system crashes, race conditions, or other potential dangers that many non-database applications are vulnerable to. Durability typically involves writing to disk storage, with a certain amount of redundancy to protect against power failures or software crashes during write operations. (In InnoDB, the doublewrite buffer assists with durability.)
原子性
在SQL语境下，事务是指要么完全成功（提交时）、要么完全不产生任何效果（回滚时）的工作单元。这种不可分割的（“原子”）特性正是ACID缩写中的"A"。
atomic
In the SQL context, transactions are units of work that either succeed entirely (when committed) or have no effect at all (when rolled back). The indivisible ("atomic") property of transactions is the “A” in the acronym ACID.
原子DDL
原子DDL语句将数据字典更新、存储引擎操作以及与DDL操作相关的二进制日志写入合并为单个原子事务。即使在服务器运行中断期间，该事务也能确保操作完全提交或回滚。MySQL 8.0版本新增了原子DDL支持功能。
atomic DDL
An atomic DDL statement is one that combines the data dictionary updates, storage engine operations, and binary log writes associated with a DDL operation into a single, atomic transaction. The transaction is either fully committed or rolled back, even if the server halts during the operation. Atomic DDL support was added in MySQL 8.0. For more information, see Section 15.1.1, “Atomic Data Definition Statement Support”.
PostgreSQL:
https://www.postgresql.org/docs/18/glossary.html#GLOSSARY-ATOMICITY
原子性
事务的特性，要求其所有操作作为一个单一单元要么全部完成，要么全部不执行。此外，若在事务执行过程中发生系统故障，恢复后不会出现任何部分执行的结果。这是ACID属性中的其中之一。
Atomicity
The property of a transaction that either all its operations complete as a single unit or none do. In addition, if a system failure occurs during the execution of a transaction, no partial results are visible after recovery. This is one of the ACID properties.
达梦数据库:
https://eco.dameng.com/document/dm/zh-cn/pm/management-affairs.html#19.2.1%20%E5%8E%9F%E5%AD%90%E6%80%A7
原子性
事务的原子性保证事务包含的一组更新操作是原子不可分的，也就是说这些更新操作是一个整体，对数据库而言全做或者全不做，不能部分地完成。这一性质即使在系统崩溃之后仍能得到保证，在系统崩溃之后将进行数据库恢复，用来恢复和撤销系统崩溃时处于活动状态的事务对数据库的影响，从而保证事务的原子性。系统对磁盘上的任何实际数据的修改之前都会将修改操作本身的信息记录到磁盘上。当发生崩溃时，系统能根据这些操作记录当时该事务处于何种状态，以此确定是撤销该事务所做出的所有修改操作，还是将修改的操作重新执行。
电科金仓:KingbaseES
https://docs.kingbase.com.cn/cn/KES-V9R1C10/reference/system_principles/%E4%BA%8B%E5%8A%A1/%E4%BA%8B%E5%8A%A1%E7%9A%84%E7%89%B9%E5%BE%81
原子性（Atomicity）
一个事务对数据库的所有操作，是一个不可分割的工作单元。这些操作要么全部执行，要么什么也不做。
TiDB:
https://docs.pingcap.com/zh/tidb/stable/glossary/#acid
ACID 是指数据库管理系统在写入或更新资料的过程中，为保证事务是正确可靠的，所必须具备的四个特性：原子性 (atomicity)、一致性 (consistency)、隔离性 (isolation) 以及持久性 (durability)。
(1)原子性 (atomicity)
指一个事务中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。TiDB 通过 Primary Key 所在 Region 的原子性来保证分布式事务的原子性。
(2)一致性 (consistency)
指在事务开始之前和结束以后，数据库的完整性没有被破坏。TiDB 在写入数据之前，会校验数据的一致性，校验通过才会写入内存并返回成功。
(3)隔离性 (isolation)
指数据库允许多个并发事务同时对其数据进行读写和修改的能力。隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致，主要用于处理并发场景。关于 TiDB 支持的隔离级别，请参考 TiDB 事务隔离级别。
(4)持久性 (durability)
指事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。在 TiDB 中，事务一旦提交成功，数据全部持久化存储到 TiKV，此时即使 TiDB 服务器宕机也不会出现数据丢失。
OceanBase:
https://open.oceanbase.com/blog/16348478992
数据库事务的ACID属性在具体场景的体现
原子性（Atomicity）
原子性的定义
原子性（Atomicity）要求事务中的所有操作要么全部成功，要么全部失败。也就是说，事务是一个不可分割的操作单元，任何部分操作失败都会导致整个事务的回滚，保证数据库的完整性。
现实场景：
在一个电商平台中，当用户购买商品时，通常涉及多个操作：减少商品库存、扣除用户账户余额、生成订单记录等。如果在购买过程中发生了错误（比如用户账户余额不足，或库存已售空），原子性确保这些操作要么全部完成，要么全部撤销。
场景应用：
电商平台的订单处理
/**
* 模拟电商平台中的订单处理（原子性）
* 如果库存不足或余额不足，订单应当回滚，确保数据一致。
*/
void processOrder(User& user, Product& product, int quantity) {
// 开始事务
startTransaction();
try {
// 检查库存
if (product.stock < quantity) {
throw std::runtime_error("库存不足");
}
// 扣除用户余额
user.debit(product.price * quantity);
// 更新商品库存
product.updateStock(-quantity);
// 提交事务
commitTransaction();
} catch (const std::exception& e) {
// 如果发生任何错误，回滚事务
rollbackTransaction();
throw e; // 将错误抛出
}
}
在这个例子中，如果库存不足或余额不足，事务将回滚，确保不会出现扣款成功但商品库存没有减少的异常状态。整个事务是原子性的，用户不会面临部分操作成功、部分操作失败的情形。
分布式数据库的高效事务管理：OceanBase 的 ACID 实现与实战解析
https://open.oceanbase.com/blog/21944341504
OceanBase 分布式事务的 ACID 实现
原子性（Atomicity）
原子性确保事务操作要么全部成功，要么全部回滚。OceanBase 采用两阶段提交（2PC）结合 Paxos 协议实现原子性：
(1)准备阶段（Prewrite）：事务协调者向相关节点发送操作请求，节点记录 redo 日志并加锁，Paxos 确保日志多副本同步。
(2)提交阶段（Commit）：协调者确认所有节点准备就绪后，发送提交指令，节点完成数据提交并释放锁。若任一节点失败，事务回滚。
实战示例：在金融转账场景中，配置 OceanBase 的事务超时参数（ob_trx_timeout）为 100ms，以避免长事务导致锁冲突：
SET GLOBAL ob_trx_timeout = 100000; -- 100ms
欢迎关注我的公众号《IT小Chen》
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
