Transaction ACID Properties

Overview

In this tutorial, we will learn about MySQL transaction ACID properties. A database transaction is a unit of interaction with the database that is treated in a coherent and reliable way independent of other transactions. In general, a database transaction must be atomic, meaning that it must be either entirely completed or aborted.

ACID Properties

A database system will guarantee the ACID properties for each transaction. These are:

Atomic/Atomicity
Consistent/Consistency
Isolated/Isolation
Durable/Durability

MySQL transactions are ACID compliant. ACID acronym stands for the following properties:

Property	Description
Atomic	All the statements execute successfully or are canceled as a unit.
Consistent	A database is in a consistent state when a transaction begins and is left in a consistent state by the transaction.
Isolated	One transaction does not affect another transaction.
Durable	All the changes that are committed are guaranteed to be stored persistently in the database and will not be lost. And all changes that are rolled back are guaranteed not to exist anymore.

MySQL supports transactional storage engine. Transactions provide stronger guarantees about the outcome of the database operations. MySQL server to support transactions also requires overhead in physical resources like CPU, memory and disk space. Some Applications need transactional support and the guarantees of data integrity outweigh the resource overhead. For example, Banking applications typically need transactions.

MySQL also supports non-transactional storage engines. Applications that doesn’t require transactional processing can opt for non-transactional storage engine.

—