A database transaction is a unit of work performed against a database that is independent of other transactions. The purpose of a transaction is two-fold:
- To provide a reliable record and means to recover in case of failure when performing work.
- To provide isolation between applications or processes that are accessing data concurrently.
In any good relational database management system, transactions adhere to a set of strict rules or properties abbreviated as ACID:
- Atomicity
- Consistency
- Isolation
- Durability
Atomicity
Guarantees that either all operations in the transaction succeed or they all fail. This prevents partial updates that could leave a database in a questionable or incomplete state. For instance, imagine your database transaction was a financial transaction that transferred money from one account to another. That would consist of two operations: a deduction from account A and a deposit into account B. This is an obvious example where we want both operations to succeed or both to fail. If only one operation was successful we would either be destroying money or inventing new money from nothing (something apparently only the federal reserve is allowed to do).
Consistency
Is similar to atomicity but has more to do with referential integrity. This is where constraints come into play, such as a primary key. If you have an ID column used to track rows based on a unique identifier, you wouldn’t want to allow multiple rows to have the same value for ID; if there were the database would be in an inconsistent state. A transaction that preserves consistency ensures that the database is in a consistent state when the transaction begins and when the transaction ends.
Isolation
This protects transactions from the effects of other transactions via mechanisms like locks and row versioning. Database management systems typically employ different levels of isolation. For instance, in Microsoft SQL Server, the READ UNCOMMITTED isolation level allows transactions to read data that other transactions are modifying, while the stricter SERIALIZABLE isolation level will lock ranges of rows or entire tables to prevent the reading of data until the transaction modifying data completes and commits.
Durability
A transaction is considered durable when it commits and stays committed. This is accomplished by flushing the data to a stable/nonvolatile media (think disk vs in-memory) although as storage mediums continue to advance and innovate, the implementation may change. The important thing is that the work is saved and is not prone to being lost or rolled back without intervention.
Microsoft SQL Server, Oracle, MySQL, and PostgreSQL are some of the most popular database systems which follow ACID properties by default.
