Transaction Testing

Transaction Concept

Transaction (Transaction) is a set of SQL operations executed as a single logical unit of work. These operations either all execute successfully or all fail.

Typical Transaction Application Scenarios:

  1. Bank transfer operations (need to update balances of two accounts simultaneously)
  2. Order processing systems (need to create orders and deduct inventory simultaneously)
  3. Data batch processing tasks (need to ensure all batch operations succeed)

Transaction Control Commands

  • BEGIN or START TRANSACTION: Start a new transaction
  • COMMIT: Commit transaction, permanently save all changes
  • ROLLBACK: Rollback transaction, undo all uncommitted changes
  • SAVEPOINT: Create savepoint within transaction, allowing partial rollback

Four Characteristics of Database Transactions

Atomicity

Atomicity ensures all operations in a transaction either all execute successfully or all fail. This is like a bank transfer: if account A’s debit succeeds but account B’s credit fails, the system automatically reverses account A’s debit.

Consistency

Consistency ensures the database is in a consistent state before and after transaction execution. The database ensures consistency through predefined various constraint conditions (such as primary key constraints, foreign key constraints, unique constraints, etc.).

Isolation

Isolation defines visibility and influence degree between multiple concurrent transactions. Common isolation levels include:

  1. Read Uncommitted
  2. Read Committed
  3. Repeatable Read
  4. Serializable

Durability

Durability ensures once a transaction is committed, its result will be permanently saved in the database. Even if a system failure occurs, data will not be lost.

Minimal Testing

Create New Table

CREATE TABLE `user_info` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `money` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Transaction Testing

The test simulates operations under transaction isolation environment across two database session windows.

Test Steps:

  1. Window A starts transaction: START TRANSACTION;
  2. Window B executes query: At this point Window B cannot see modifications not yet committed by Window A
  3. Window A executes update: UPDATE user_info SET money = money + 1 WHERE name = 'wzk';
  4. After Window A COMMIT, Window B can see the updated data

This test verifies: Increases, deletes, and modifications within a transaction do not take effect for other sessions immediately. They only take effect after COMMIT.