Database transactions explained

A database transaction groups related changes to database records into one unit, committing (saving) them together if they succeed and rolling back (canceling) any changes not yet committed if they fail. For example, a bank transfer should debit one account and credit another in the same transaction.

Transactions protect data inside the database, but only within limits. The database enforces the rules you define. You must handle concurrent transactions carefully, and transactions do not automatically include actions by outside services, such as a payment provider.

How database transactions work

A transaction begins, runs related database operations, then ends with a commit or a rollback. A commit keeps the changes. A rollback cancels changes that have not been committed.

A simplified transfer looks like this:

BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;

COMMIT;

If the second update fails, the application can roll back the transaction, canceling the first update too. A real transfer also needs rules to check that both accounts exist and that the source account has enough funds. The database can enforce these rules with constraints, and code can enforce them with application logic. The transaction groups the related database work so it succeeds or fails as a unit.

Many databases use autocommit by default. With autocommit, each statement runs in its own transaction unless the application starts a transaction that includes multiple statements. If two related updates run separately, the first can commit before the second fails. Grouping them in one transaction prevents this partial result.

A savepoint marks a point inside an open transaction. The application can roll back to that point and keep changes made earlier in the transaction. This helps when you need to abandon one part of a larger operation without canceling all the work.

ACID properties in database transactions

ACID describes four properties commonly associated with database transactions:

For example, a foreign-key constraint can require an account reference to point to an existing account. A uniqueness constraint can prevent duplicate identifiers. These constraints help preserve consistency, but the database cannot infer every rule, such as whether a particular transfer is allowed.

How isolation handles concurrent transactions

Transactions can overlap. Imagine two purchases that each check the same remaining inventory before reducing it. If both transactions act on outdated information, they could each proceed as though the item were available. Isolation rules govern how the database handles this kind of concurrency.

A dirty read happens when a transaction reads a change that another transaction has not committed; a nonrepeatable read returns a different value when a transaction reads the same row twice. A repeated query that finds a different set of matching rows produces a phantom read. A serialization anomaly occurs when concurrent transactions produce an outcome that could not happen if they ran one at a time.

Isolation levels provide different protections against these effects. Serializable is the strongest standard level. PostgreSQL guarantees that successfully committed concurrent transactions have the same effect as if they had run in some one-at-a-time order. If the database detects a conflict, it can reject a transaction. The application needs to be ready to retry the whole transaction.

PostgreSQL 18 defaults to Read Committed, while MySQL 8.4’s InnoDB engine defaults to Repeatable Read. Because isolation level names do not guarantee the same behavior in every database, check your database and version’s documentation before choosing or changing a level. Depending on the database and workload, more protection can mean more blocking or retries.

Keep transactions focused

Long-running transactions can hold locks or other resources and slow down concurrent work. Keep a transaction to the database operations that need to succeed or fail together. Do not leave it open while waiting for unrelated work.

For example, do not leave a database transaction open while waiting for a user to respond or another service to finish a request. A normal database transaction cannot undo an email that has already been sent or a charge handled by an outside payment provider. To coordinate work across services, systems use other designs, such as distributed transactions or the saga pattern. A saga coordinates separate local transactions and actions that compensate for earlier work.

Rollbacks and retries

A rollback cancels changes in an open transaction. A retry runs the transaction’s logic again after the database rejects it because of a conflict. Rolling back ends the current attempt; retrying repeats the work.

When a database reports a serialization failure, retry the whole transaction, including the reads behind its decisions. Repeating only the final update can use outdated information and cause the conflict again. Take care when retrying operations that trigger external side effects, because a database rollback cannot undo those actions.