ACID Transactions

Robust database management systems must protect the data they store and process from corruption. Atomicity Consistency Isolation Durability (ACID) provides a set of rules for safeguarding database and transaction integrity.

What is ACID?

The acronym ACID was coined in the 1960s and has been implemented by relational database systems ever since. The four properties can be expanded as follows.

Atomicity

Atomicity enforces the rule that all the changes in a transaction are committed together. If any of the changes in the transaction fail, the whole transaction fails, and any pending changes are rolled back. For example, if you have a banking transaction, the first step is deducting an amount from a savings account, followed by a second step of crediting a related checking account balance with the same amount. If either of the steps fails for any reason, the whole transaction is rolled back. In this way, both accounts will continue with their original balances. Every relational database system does an implicit rollback of any uncommitted transactions by default whenever the database instance is started up. The Relational Database  Management System (RDBMS) has to do this because it optimistically records uncommitted changes to log files, which are only considered confirmed when the corresponding commit record is written in the log.

Consistency

A database system protects itself by validating data that is inserted or updated. When a table is created or altered, each field or attribute of a record has a fixed datatype set. Any attempt to insert spurious data of the wrong datatype is rejected. For example, an alphabetic character would not be allowed in a field of the integer datatype.

Referential integrity constraints provide a second level of control for data fields. These rules are used to constrain the values of a field, for example, those that exist in a related table. This would be considered to be a foreign key constraint. For example, a sales table may reference products that must exist in the products table for the sales record to be valid. Such referential integrity constraints protect the database structure even if an application is poorly coded.

Isolation

Transaction isolation ensures each transaction is kept separate from other transactions. If two transactions want to change the same record, locking mechanisms in an RDBMS will serialize access to make transactions queue for update access to a record. Many databases will escalate row-level locks to the page or table level at times of high contention for transaction locks.

The Structured Query Language (SQL) standards for isolation levels are defined as READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. Uncommitted reads help databases with poor locking models but are considered to be dirty reads in the industry, so they are frowned upon. Repeatable reads, however, are considered very valuable as they maintain a result set integrity, so a set of records retrieved by a query will always add up consistently.

Durability

Data changes happen in memory first, where the CPU can see them. For durability, they are then written to backup storage, which is considered non-volatile storage. Database log files record all changes to protect the RDBMS from server failures. Durability ensures committed transaction changes are not lost when the database instance starts up.

Why is ACID Important to Transactions?

ACID defines a set of rules that protect data and transaction integrity to keep data free from logical corruption. Data files only contain committed data; otherwise, the database will quickly become inconsistent, unrecoverable, and unusable.

Actian Relational Databases Support ACID Transactions

The Actian Data Platform integrates with multiple relational databases, including Actian Vector for high-speed analytic workloads using a columnar storage scheme and Actian Ingres using a row store designed for Transactional workloads. Actian X combines both analytical and transactional database engines. DataConnect provides an intelligent, low-code integration platform to address complex use cases with automated, intuitive, and reusable integrations.

The Actian Data Platform runs on-premises and on multiple cloud platforms, including AWS, Azure, and Google Cloud, so you can run your analytics wherever your data resides.

To get started, visit the website and sign up for a free trial here.