What Is Database ACID?

ACID is an acronym for the four major properties of a database: atomicity, consistency, isolation, and durability. It’s often associated with relational databases, but a NoSQL (non-relational) database can also follow ACID rules. ACID transactions are common in many of the popular database engines available on the market, and they ensure that read and write actions do not interfere with the integrity of your data.

What Is Database ACID?

A database that follows ACID best practices has the following characteristics:

• Atomicity: Prevent data loss, orphaned records, and partial transactions with atomicity. A database with atomicity provides “all or nothing” transactions so that you don’t lose data if a part of a transaction fails midstream.
• Consistency: Table constraints in ACID databases require all transactions to store data in a consistent format.
• Isolation: An ACID database ensures that the transactions do not return dirty reads or writes by isolating data and performing transactions one by one.
• Durability: System failure (e.g., a power outage) can interfere with transactions, so an ACID database ensures that failover prevents data loss from a critical event.

Atomicity

Modern database transactions have more than one step to finalize a record. For example, a customer might create an order, so a stored procedure uses an INSERT statement to add a record to the orders and products_ordered tables. If adding a record to the order table fails, then you don’t want to add a record to the products_ordered table. This would create an orphaned record.

With atomicity, the database rolls back all transactions when one fails so that data stays consistent and avoids corruption. Only when all steps in a transaction are successfully completed is the data stored. Atomicity avoids data corruption, data loss, and orphaned records.

Consistency

Relational databases have constraints allowing only specific types of data to be stored. NoSQL databases store data with their own set format. For example, if you specify that only a decimal number can be stored in an order total column, then attempting to store a string will result in a failure. Keeping data consistent provides developers with a known value when they work with a data set.

Consistency in a database means that data can be predicted, meaning that you know if you pull data for any particular record you will get an expected result. Using the order table example, you know that you will receive a decimal value for any query retrieving a value from the order total column.

Isolation

Dirty reads and dirty writes happen when a user runs a query at the specific moment when the database is also making changes to a value. For example, if you want to know the total for all orders for the month, you might get an incorrect result if you read data at the time an order total is updated. The same can happen with write actions if data is read before a previous update statement can execute. 

Dirty reads and writes can corrupt your data and destroy data integrity. Isolated transactions execute statements one by one, so data stays consistent and uncorrupted even with millions of daily transactions. Isolated transactions can lock records, make changes, and then release records for the next transaction.

Durability

Both structured and unstructured databases must store data consistently and permanently after a transaction completes even if there is a system failure. Transaction logs and infrastructure failure must also be implemented, but the database itself must be able to store data consistently and without causing any data corruption. Database engines have their own strategies and failover techniques, but administrators must be able to rely on them to recover data.

For example, a power failure might interrupt a transaction midway through. Whether the administrator needs to roll back transactions or recover data, the database must be able to handle the failure. For most administrators, failover is also handled with backups and redundancy. ACID can help with an organisation’s larger disaster recovery strategy. 

Conclusion

Mission-critical databases need ACID properties to support enterprise applications. ACID properties are essential for data integrity and ensuring that your data can be retrieved and used by as many applications as you want. Administrators still need to take backups and ensure that the database server runs smoothly, but having ACID properties is critical for business continuity.