Understanding MySQL Transaction Atomicity Through Practical Examples

Database Transaction Atomicity: Misunderstanding Can Lead to Data Loss!​​

​Thinking:​​

DB: MySQL 5.7.x, 8.0.x

​Generate test data:​

use jack;
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());

‍

Create test script:​​

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;

‍

Question:​​

The third INSERT in the 01.sqlscript will fail due to a primary key conflict. After executing the 01.sqlscript, will some of these SQL statements partially succeed, or will the entire transaction be rolled back?

Because MySQL can execute scripts in different ways, we explain them by scenario:

​Scenario 1: Execution via SOURCE command:​​

This method is similar to manual execution: it skips errors and continues executing subsequent SQL statements.

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)

‍

As you can see, within this transaction, only the third INSERT failed due to a primary key conflict. The other parts executed successfully. It seems like some SQLs in the transaction succeeded and some failed? Does this conflict with transaction atomicity? Actually, it does not. If the third INSERT is intended for backup and the fourth DELETE for cleanup, a scenario could occur where the backup fails but the deletion succeeds.

mysql> select * from jack.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 jack.t1021_bak;
+----+------+----------+
| id | name | time     |
+----+------+----------+
|  3 | xxx  | 17:25:08 |
+----+------+----------+
1 row in set (0.00 sec)

‍

Scenario 2: Execution via mysql < .sqlmethod:​​

Initialize test data again:

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());

‍

Execute the SQL script via mysql < .sql:

[mysql@ ~]$ mysql -uroot -p*** -D jack < /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'

‍

Query the data:

Because the third INSERT failed due to a primary key conflict, execution was interrupted immediately, and subsequent operations were not executed. The entire transaction was rolled back. This seems to comply with transaction atomicity.

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)

‍

Scenario 3: SQL_MODE Parameter​

Transaction behavior is also related to the sql_modeparameter. This hasn't been tested yet but is planned for a separate article on sql_mode.

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

​How to Safely Implement Transaction Atomicity:​​

​Database-side Atomicity Implementation:​​

A safer approach is to manually add checks to determine the outcome of all SQL statements within a transaction. If any fail, roll back the entire transaction; only commit if all are successful.

Original script:

vi 01.sql

START TRANSACTION;
insert into t1021_bak(select * from t1021 where id=3);
delete from t1021 where id=3;
commit;

‍

Modified script (using a stored procedure):

Note: The following stored procedure was generated by DeepSeek for testing purposes only. Do not use directly in production!

Stored Procedure:

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 exception handler
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
            error_message = MESSAGE_TEXT;
        SET error_occurred = 1;
    END;
    
    START TRANSACTION;
    
    -- Get source data row count
    SELECT COUNT(*) INTO source_count FROM t1021 WHERE id = 3;
    
    IF source_count = 0 THEN
        ROLLBACK;
        SELECT 'Failure: Source data does not exist (record with id=3 not found)' AS result;
    ELSE
        -- Reset error status
        SET error_occurred = 0;
        SET error_message = NULL;
        
        -- Execute insert operation
        INSERT INTO t1021_bak SELECT * FROM t1021 WHERE id = 3;
        
        IF error_occurred = 1 THEN
            ROLLBACK;
            SELECT 
                'Failure: Insert operation exception' AS result,
                CONCAT('Error details: ', IFNULL(error_message, 'Unknown error')) AS error_details,
                'Possible causes: Primary key conflict, data type mismatch, table structure inconsistency, etc.' AS possible_causes;
        ELSE
            SET insert_count = ROW_COUNT();
            
            IF insert_count != source_count THEN
                ROLLBACK;
                SELECT 
                    'Failure: Inserted row count does not match source data row count' AS result,
                    CONCAT('Expected to insert: ', source_count, ' row(s), Actually inserted: ', insert_count, ' row(s)') AS details;
            ELSE
                -- Reset error status
                SET error_occurred = 0;
                SET error_message = NULL;
                
                -- Execute delete operation
                DELETE FROM t1021 WHERE id = 3;
                
                IF error_occurred = 1 THEN
                    ROLLBACK;
                    SELECT 
                        'Failure: Delete operation exception' AS result,
                        CONCAT('Error details: ', IFNULL(error_message, 'Unknown error')) AS error_details;
                ELSE
                    SET delete_count = ROW_COUNT();
                    
                    IF delete_count != source_count THEN
                        ROLLBACK;
                        SELECT 
                            'Failure: Deleted row count does not match source data row count' AS result,
                            CONCAT('Expected to delete: ', source_count, ' row(s), Actually deleted: ', delete_count, ' row(s)') AS details;
                    ELSE
                        COMMIT;
                        SELECT 
                            'Success: Data transfer completed' AS result,
                            CONCAT('Successfully transferred ', source_count, ' record(s)') AS details;
                    END IF;
                END IF;
            END IF;
        END IF;
    END IF;
END //

DELIMITER ;

‍

Call the stored procedure:

Execution result:

mysql> CALL safe_data_transfer_detailed();
+-----------------------------+----------------------------------------------------------------+-----------------------------------------------------------------------------+
| result                      | error_details                                                  | possible_causes                                                             |
+-----------------------------+----------------------------------------------------------------+-----------------------------------------------------------------------------+
| Failure: Insert operation exception | Error details: Duplicate entry '3' for key 't1021_bak.PRIMARY' | Possible causes: Primary key conflict, data type mismatch, table structure inconsistency, etc. |
+-----------------------------+----------------------------------------------------------------+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)

‍

Check data: The entire transaction was rolled back.

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)

‍

Now test a successful insertion:

First, resolve the primary key conflict:

‍

update t1021_bak set id=100 where id=3;

‍

Then execute the stored procedure again:

mysql> CALL safe_data_transfer_detailed();
+-----------------------------+--------------------------+
| result                      | details                  |
+-----------------------------+--------------------------+
| Success: Data transfer completed | Successfully transferred 1 record(s) |
+-----------------------------+--------------------------+
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)

‍

Besides stored procedures, you can also use SELECT ROW_COUNT()to check the result of the previous INSERT operation. For example:

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)

‍

​Application-side Atomicity Implementation:​​

Refer to the example explained on the OceanBase official website:

Real-world scenario:

In an e-commerce platform, when a user purchases a product, it typically involves multiple operations: reducing product inventory, deducting the user's account balance, generating an order record, etc. If an error occurs during the purchase process (e.g., insufficient user balance or out-of-stock), atomicity ensures that these operations are either all completed or all rolled back.

Scenario application: Order processing in an e-commerce platform

/**
 * Simulate order processing in an e-commerce platform (Atomicity)
 * If inventory is insufficient or balance is insufficient, the order should be rolled back to ensure data consistency.
 */
void processOrder(User& user, Product& product, int quantity) {
    // Start transaction
    startTransaction();

    try {
        // Check inventory
        if (product.stock < quantity) {
            throw std::runtime_error("Insufficient inventory");
        }

        // Deduct user balance
        user.debit(product.price * quantity);

        // Update product inventory
        product.updateStock(-quantity);

        // Commit transaction
        commitTransaction();
    } catch (const std::exception& e) {
        // If any error occurs, rollback the transaction
        rollbackTransaction();
        throw e;  // Re-throw the error
    }
}

‍

In this example, if inventory is insufficient or the balance is insufficient, the transaction will be rolled back, ensuring no abnormal state where the deduction is successful but the product inventory isn't reduced. The entire transaction is atomic; the user won't face a situation where some operations succeed and others fail.

​Transaction Atomicity Overview:​​

Finally, let's look at how different databases define transaction atomicity. Oracle and OceanBase provide more detailed explanations.

​Oracle:​​

https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/transactions.html

​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.

​Statement-Level Atomicity​

Oracle Database supports statement-level atomicity, meaning each SQL statement is an atomic unit of work that 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. If a SQL statement causes an error during execution, it is unsuccessful, and all effects of the statement are rolled back (statement-level rollback). A failed statement only causes the loss of work it would have performed itself; it does not cause the loss of any work that preceded it in the current transaction. The rollback effect is as if the statement had never been run. Any side effects (e.g., triggers) are considered part of the atomic statement. Errors causing statement-level rollback include duplicate primary key violations or deadlocks. Syntax errors found during parsing do not trigger statement-level rollback as the statement hasn't executed.

​MySQL:​​

https://dev.mysql.com/doc/refman/8.0/en/mysql-acid.html

​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, and Data visibility during operations.

https://dev.mysql.com/doc/refman/8.0/en/glossary.html

​ACID​

An acronym for Atomicity, Consistency, Isolation, Durability. InnoDB's transactional features adhere to ACID principles. Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes, either all succeed on commit, or all are undone on rollback. The database remains consistent. Transactions are isolated and durable.

​atomic​

In SQL, transactions are units of work that either succeed entirely (committed) or have no effect at all (rolled back). The 'A' in ACID.

​atomic DDL​

An atomic DDL statement combines data dictionary updates, storage engine operations, and binary log writes into a single atomic transaction, fully committed or rolled back even if the server halts. Supported from MySQL 8.0.

​PostgreSQL:​​

https://www.postgresql.org/docs/18/glossary.html#GLOSSARY-ATOMICITY

​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 execution, no partial results are visible after recovery. This is one of the ACID properties.

​Dameng Database:​​

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

​Atomicity​

The atomicity of a transaction ensures that a set of update operations contained within the transaction are indivisible; that is, these update operations are a whole. For the database, they are either all done or none are done, and cannot be partially completed. This property is guaranteed even after a system crash. After a crash, database recovery is performed to recover and undo the impact of transactions that were active at the time of the crash on the database, thus ensuring transaction atomicity. Before modifying any actual data on disk, the system records the information of the modification operation itself to the disk. When a crash occurs, the system can determine the state of the transaction based on these operation records to decide whether to undo all modifications made by the transaction or redo the modification operations.

​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​

All operations of a transaction on the database form an indivisible unit of work. These operations are either all executed or nothing is done.

​TiDB:​​

https://docs.pingcap.com/zh/tidb/stable/glossary/#acid

​ACID​

Four properties essential for reliable transaction processing: Atomicity, Consistency, Isolation, Durability.

1. ​Atomicity:​​ All operations in a transaction are completed, or none are. It does not end in an intermediate state. TiDB ensures distributed transaction atomicity through the atomicity of the Region where the Primary Key resides.
2. ​Consistency:​​ Database integrity is not broken before the transaction starts and after it ends. TiDB checks data consistency before writing; data is written to memory and returns success only after passing the check.
3. ​Isolation:​​ The ability of a database to allow multiple concurrent transactions to read and modify its data simultaneously. Isolation prevents data inconsistencies caused by interleaved execution of concurrent transactions. See TiDB Transaction Isolation Levels.
4. ​Durability:​​ Once a transaction is committed, modifications to the data are permanent and will not be lost even in the event of a system failure. In TiDB, once a transaction is successfully committed, all data is persistently stored in TiKV. Even if the TiDB server crashes, data will not be lost.

​OceanBase:​​

https://open.oceanbase.com/blog/16348478992

​Manifestation of ACID Properties in Specific Scenarios​

​Atomicity​

Requires that all operations in a transaction either all succeed or all fail. The transaction is an indivisible unit of operation; any partial operation failure will cause the entire transaction to roll back, ensuring database integrity. (Uses the same e-commerce example as above).

https://open.oceanbase.com/blog/21944341504

​OceanBase Distributed Transaction ACID Implementation​

​Atomicity​

Ensures transaction operations either all succeed or all roll back. OceanBase uses a two-phase commit (2PC) combined with the Paxos protocol:

1. ​Prepare Phase (Prewrite):​​ The transaction coordinator sends operation requests to relevant nodes. Nodes record redo logs and acquire locks. Paxos ensures multi-replica log synchronization.
2. ​Commit Phase (Commit):​​ After confirming all nodes are ready, the coordinator sends a commit command. Nodes complete data commit and release locks. If any node fails, the transaction rolls back.

Practical example: In a financial transfer scenario, configure OceanBase's transaction timeout parameter (ob_trx_timeout) to 100ms to avoid lock conflicts caused by long transactions:

SET GLOBAL ob_trx_timeout = 100000; -- 100ms