What are the ACID properties of a MySQL transaction?

MySQL transactions follow ACID characteristics to ensure the reliability and consistency of database transactions. First, atomicity ensures that transactions are executed as an indivisible whole, either all succeed or all fail to roll back. For example, withdrawals and deposits must be completed or not occur at the same time during transfer operations; second, consistency ensures that transactions transition the database from one valid state to another, and maintains the correct data logic through mechanisms such as constraints and triggers; third, isolation controls the visibility of multiple transactions when concurrent execution, prevents dirty reading, non-repeatable reading and fantasy reading. MySQL supports Read Uncommitted, Read Committed, and Repeatable Read and Serializable are four isolation levels; finally, persistence (Durability) ensures that the changes of the transaction are permanently saved after the transaction is committed, and can be restored by redoing the log even if the system crashes. The InnoDB storage engine controls the log refresh behavior through parameters such as innodb_flush_log_at_trx_commit to achieve persistence.

MySQL transactions follow the ACID properties , which ensure reliable and consistent database transactions. These properties are cruel for maintaining data integrity, especially in systems where multiple users access and modify data concurrently.

Atomicity

This property ensures that a transaction is treated as a single, indivisible unit of work. Either all operations within the transaction success, or none of them do. For example, if you're transferring money from one bank account to another, both the withdrawal and deposit must complete successfully. If either fails, the entire transaction is rolled back, leaving the database unchanged.

• Think of it like an all-or-nothing guaranteee.
• In MySQL, this is managed using logs and rollback mechanisms.
• You don't want half of a financial transaction applied — atomicity prevents that.

Consistency

Consistency ensures that a transaction brings the database from one valid state to another. It enforces defined rules (like constraints, triggers, or cascades) so that even after a transaction runs, the data remains logically correct.

• For instance, if you have a foreign key constraint, a transaction can't insert a reference to a non-existent row.
• This also includes maintaining database-specific rules like unique keys or check constraints.
• MySQL uses the underlying storage engine (like InnoDB) to enforce consistency before and after transactions.

Isolation

Isolation determines how visible the effects of one transaction are to others running at the same time. Without proper isolation, issues like dirty reads, non-repeatable reads, or phantom reads may occur.

MySQL supports several isolation levels:

• Read Uncommitted – allow dirty reads
• Read Committed – prevents dirty reads but allows non-repeatable reads
• Repeatable Read – default in MySQL; prevents dirty and non-repeatable reads
• Serializable – highest level, prevents all three types of inconsistencies

You can set the isolation level depending on your application's needs. Higher isolation means better data accuracy but potentially lower performance due to more locking.

Durability

Durability guarantees that once a transaction has been committed, it will remain so, even in the event of a system failure such as a crash or power outage. MySQL achieves durability through its logging system — specifically, the redo log used by the InnoDB storage engine.

• When you commit a transaction, changes are first written to a log file before being applied to the actual database files.
• This log ensures that changes can be recovered even if the server crashes right after the commit.
• You can control durability behavior to some extent with settings like innodb_flush_log_at_trx_commit .

These four ACID properties together ensure that MySQL transactions behave reliably under all conditions. They're fundamental to any serious database system and particularly important when building applications that require strong data integrity, like banking systems or inventory management platforms.

Basically that's it.

The above is the detailed content of What are the ACID properties of a MySQL transaction?. For more information, please follow other related articles on the PHP Chinese website!