What is a database transaction? ACID properties explained

A database transaction is a sequence of operations treated as a single, indivisible unit of work: either all of them complete successfully, or none of them take effect. The best-known example is a money transfer between two bank accounts — withdrawing from one account and depositing into another must happen together. Transactions guarantee that databases stay valid and consistent even under crashes, power failures, and concurrent users.

Why transactions exist

Databases store data that many operations read and write at the same time. Without safeguards, a failure halfway through a multi-step operation could leave the data in a partial, inconsistent state, and concurrent operations could interfere with each other.

Transactions solve both problems. The essential idea — as PostgreSQL's documentation puts it — is that a transaction bundles multiple steps into a single all-or-nothing operation. The intermediate states between the steps are not visible to other concurrent transactions, and if a failure prevents the transaction from completing, none of the steps affect the database at all.

In most SQL databases you start a transaction with BEGIN, make your changes, then either make them permanent with COMMIT or undo them with ROLLBACK. Many systems also treat a single statement as an implicit transaction: if you do not issue BEGIN, each statement gets an implicit BEGIN and, if successful, a COMMIT wrapped around it.

The ACID properties

The guarantees that transactions provide are summarized by the acronym ACID: atomicity, consistency, isolation, and durability.

Atomicity

Atomicity means a transaction is all-or-nothing: if one part fails, the whole transaction fails and the database is left unchanged. To the outside world, a committed transaction appears indivisible, and an aborted transaction does not happen at all. As an example, a transfer that withdraws money from account A and deposits into account B should never leave the amount removed from A without it appearing in B. An atomic system must guarantee this even in power failures, errors, and crashes.

Consistency

Consistency ensures that a transaction can only bring the database from one valid state to another, preserving all defined rules — constraints, triggers, cascades, and any combination of them. This prevents database corruption by an illegal transaction. An example is referential integrity, which guarantees that a primary key–foreign key relationship stays valid. If a validation check fails partway through a transaction, the whole transaction is cancelled and the affected rows are rolled back to their pre-transaction state.

Isolation

Isolation ensures that concurrent execution of transactions leaves the database in the same state that would be obtained if the transactions ran one at a time. The main goal is concurrency control: while a transaction is open, its incomplete changes are not visible to other transactions, and all its changes become visible simultaneously when it completes. Isolation levels trade some of this strictness for performance, as explained below.

Durability

Durability guarantees that once a transaction has been committed, it stays committed even if the system crashes, loses power, or errors immediately afterward. This usually means completed transactions and their effects are recorded in non-volatile (permanent) storage. The PostgreSQL docs give the example of recording a cash withdrawal: the debit must not disappear in a crash right after the customer walks out the door.

Isolation levels

The SQL standard defines four isolation levels that describe how much one transaction can see of another's work. From weakest to strongest:

• Read uncommitted — the weakest level; a transaction could see another's uncommitted changes (a "dirty read"). In practice, many databases, including PostgreSQL, internally treat this exactly like Read committed because they never expose uncommitted data.
• Read committed — a transaction never sees uncommitted data from other transactions. This is the default in PostgreSQL and many other databases and is adequate for most applications.
• Repeatable read — a transaction only sees data committed before it began; it never sees uncommitted data or changes committed by other transactions during its execution. This prevents dirty reads and non-repeatable reads.
• Serializable — the strictest level; any concurrent execution of a set of serializable transactions produces the same effect as running them one at a time. If concurrent transactions would produce a result impossible in any serial order, one is rolled back with a serialization failure.

The four levels differ in which anomalies they allow: dirty reads, non-repeatable reads, phantom reads, and serialization anomalies. Stronger isolation gives more correctness but more locking and contention, so it is slower under heavy concurrency. Most applications default to Read committed and raise the level only where they have a documented need.

ACID vs BASE

ACID and BASE describe opposite design philosophies for data systems. ACID databases prioritize consistency over availability: the whole transaction fails if an error occurs in any step. By contrast, BASE (Basically Available, Soft state, Eventually consistent) databases prioritize availability over consistency — instead of failing, users may temporarily access inconsistent data, and consistency is achieved eventually rather than immediately. No single system is fully ACID and fully BASE at the same time; they sit at opposite ends of the same spectrum. Relational databases such as PostgreSQL and MySQL are ACID; many large-scale NoSQL systems are closer to BASE.

Practical guidance

• Use transactions for any group of operations that must succeed or fail together — financial transfers, order + payment + inventory updates, multi-row writes with invariants.
• Keep transactions short. A long open transaction holds locks and increases contention and the chance of conflicts under concurrency.
• Prefer the default isolation level and raise it only when your queries need a stronger guarantee.
• Handle rollback deliberately in application code: detect failures and issue ROLLBACK rather than leaving a transaction half-open.
• Remember that transactions guarantee database consistency, not application correctness — misuse of a transaction (for example, missing a consistency constraint in the application) is not fixed by ACID.

FAQ

What is a database transaction?

A database transaction is a sequence of operations treated as a single, all-or-nothing unit of work. Either all operations complete successfully and the changes are made permanent, or none take effect, leaving the database unchanged. The classic example is a money transfer that debits one account and credits another as one operation.

What do the letters in ACID stand for?

ACID stands for Atomicity, Consistency, Isolation, and Durability. Atomicity means each transaction is all-or-nothing; consistency means a transaction only moves the database between valid states; isolation means concurrent transactions do not see each other's incomplete work; durability means a committed transaction stays committed even after a crash or power loss.

What is the difference between ACID and BASE?

ACID and BASE are opposite data-system design philosophies. ACID prioritizes consistency over availability, failing the whole transaction if any step errors. BASE (Basically Available, Soft state, Eventually consistent) prioritizes availability, allowing temporary inconsistent data and achieving consistency eventually. Relational databases are typically ACID, while many large-scale NoSQL systems are closer to BASE.

What are the SQL transaction isolation levels?

The SQL standard defines four: Read uncommitted, Read committed, Repeatable read, and Serializable, ordered from weakest to strongest. They control how much one transaction can see of another's changes — weaker levels allow anomalies like dirty reads and are faster, while Serializable guarantees every concurrent execution behaves as if the transactions ran one at a time.

Summary

A database transaction is an all-or-nothing unit of work that keeps data valid under crashes and concurrency. The ACID properties — atomicity, consistency, isolation, and durability — define the guarantees transactions provide. Isolation levels let you tune how strictly concurrent transactions are separated, trading correctness for speed. Relational databases use these mechanisms to keep data consistent, which is why they remain the default choice for systems where integrity matters. For related concepts, see our guides on database indexes and SQL vs NoSQL.