ACID Compliance in Relational Databases.

In the previous edition, I discussed one of the system’s components: databases. I then discussed databases, the types of Databases, and relational and non-relational databases, elucidating the benefits and advantages of each type of database.

In this episode, I will continue with Database ACID compliance and explore why it’s important in databases, especially relational databases. We will elucidate the following topics:

1. Atomicity
2. Consistency
3. Isolation
4. Durability

What is ACID Compliance?

Database ACID Compliance is a set of database attributes that ensures that database transactions are completed efficiently. It consists of Atomicity, Consistency, Isolation, and Durability.

ACID Compliance in Relational Databases

A transaction is a group of operations executed as a single unit of work.

An example of a transaction is when money is transferred between bank accounts. Money must be debited from one account and credited to another.

If a database fulfills the following aspects of ACID compliance, it is known to be ACID-compliant.

Atomicity compliance ensures that all transactions are completed successfully. If not, the transaction is aborted, and no changes are made to the database.

Database transactions, also known as atoms, can be divided into smaller components to ensure their integrity.

During a transaction, either all operations occur, or none do. If a debit is successfully deducted from one account, Atomicity guarantees that the corresponding credit is applied to the other account.

Atomicity in Postgres Database

Implementing atomicity in the PostgreSQL database involves the use of transactions. Here’s a basic example of how you can ensure atomicity in PostgreSQL:

BEGIN; -- Start a transaction

-- Your SQL statements go here
-- For example:
UPDATE account SET balance = balance - 100 WHERE account_id = 123; -- Debit from one account
UPDATE account SET balance = balance + 100 WHERE account_id = 456; -- Credit to another account

COMMIT; -- If all statements succeed, commit the transaction
-- If any statement fails or encounters an error, the transaction will be rolled back automatically

• BEGIN; starts a new transaction.
• SQL statements within the transaction are executed one after another.
• If all SQL statements execute successfully, COMMIT; is used to save the changes permanently.
• If any statement within the transaction fails or encounters an error, the transaction is rolled back automatically, and all changes made are discarded. This ensures atomicity — all operations are completed successfully, or none are.

Consistency

Consistency compliance guarantees that a transaction brings the database from one valid state to another, maintaining database invariants.

Consistency guarantees that transactions uphold data integrity, preserving the data consistently and accurately. It mandates adherence to data constraints. For instance, a constraint may dictate that the amount column cannot hold negative values. Should a transaction result in data that violates these constraints, the transaction is terminated, and an error is flagged.

Consistency in Postgres Database

In PostgreSQL, consistency can be implemented through various means, including:

1. Constraints : Utilize constraints such as CHECK constraints to ensure that data adheres to specified rules. For example, you can enforce that a column cannot contain negative values using a CHECK constraint.

CREATE TABLE example_table (
    amount NUMERIC CHECK (amount >= 0)
);

1. Transactions : Wrap multiple database operations within a transaction block to ensure that either all operations are completed successfully or none are. This helps maintain consistency by avoiding intermediate states that could compromise data integrity.

BEGIN;
-- SQL statements here
COMMIT;

1. Foreign Keys : Use foreign key constraints to enforce referential integrity between tables. This ensures that data relationships remain consistent.

CREATE TABLE table1 (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

1. Indexes : Properly index your database tables to optimize data access and maintain consistency in query results.

CREATE INDEX idx_table_column ON table(column);

By utilizing these features and best practices, you can effectively implement consistency in PostgreSQL databases, ensuring data integrity and reliability.

Isolation

Isolation compliance ensures that transactions executed concurrently leave the database in the same state as if they were executed sequentially.

Isolation means that the transaction’s intermediate state is invisible to other transactions until a commit is made (concurrent control).

For example, Account A has $1,000, and two transactions are made simultaneously. Transaction A wants to transfer $1000 to another account, and transaction B wants to transfer $200. They would leave the account invalid if these two transactions were allowed (-$200).

To prevent this, a database should only allow one transaction on an account at a time. The transactions should be done sequentially and put in some queue.

Isolation in Postgres Database

Isolation in PostgreSQL, as in other relational databases, is primarily achieved through transaction isolation levels. PostgreSQL supports several isolation levels to control how transactions interact with each other. Here’s how you can implement isolation in PostgreSQL:

1. Read Uncommitted : This is the lowest isolation level, where transactions can see uncommitted changes made by other transactions.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

1. Read Committed : Transactions can only see changes committed by other transactions. This is the default isolation level in PostgreSQL.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

1. Repeatable Read : Transactions can only see data that was committed before the transaction started. This prevents non-repeatable reads.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

1. Serializable : This is the highest isolation level, where transactions behave as if executed serially, preventing concurrency issues.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

You can set the isolation level for a particular transaction or change the default isolation level for the entire session.

Ensure you choose an appropriate isolation level based on your application’s requirements to balance consistency and performance.

Proper indexing and query optimization can also help manage isolation levels effectively.

Durability

Durability compliance ensures that changes persist in non-volatile memory when a transaction is completed successfully, even in system failure. Once a transaction is committed, it remains so, even in a system crash.

As in most databases, Durability in PostgreSQL primarily involves ensuring that committed transactions persist even in the face of system failures. PostgreSQL achieves durability through a combination of mechanisms:

1. Write Ahead Logging (WAL): PostgreSQL uses WAL to ensure durability. Before any changes are made to the database, they are written to a log file (WAL) on disk. Once the changes are safely recorded in the WAL, they are applied to the data files. This ensures that even if a crash occurs before changes are written to disk, they can be replayed from the WAL during recovery.
2. Synchronous Commit : PostgreSQL allows configuring synchronous commit behavior, where the server waits for confirmation that data has been written to disk before acknowledging a transaction commit. This provides stronger durability guarantees at the expense of potentially increased latency.
3. fsync and Write-Through : PostgreSQL relies on the operating system’s facilities to ensure that data is written to disk and persists across system crashes. This typically involves using mechanisms like fsync to force data to be written to disk and ensure write-through caching policies.

To implement durability in PostgreSQL, you don’t usually need to take explicit actions beyond ensuring that your database is properly configured and the underlying storage system is reliable.

However, you can configure parameters related to WAL and synchronous commit behavior in the PostgreSQL configuration file ( postgresql.conf) to tailor durability settings to your specific requirements.

For example, you can adjust the wal_level, fsync, and synchronous_commit parameters in postgresql.conf to control how PostgreSQL ensures durability. Ensuring proper backup and recovery mechanisms is crucial for maintaining durability in PostgreSQL databases.

ACID Compliance Databases

Below is the list of databases that are ACID compliance:

1. MongoDB from version 4.0
2. MySQL
3. PostgreSQL
4. Oracle
5. MariaSQL

Today, I discussed ACID Compliance in Databases, where I discussed Atomicity, Consistency, Isolation, and Durability.

Next week, I will start exploring Database Replication.

Don’t miss it. Share with a friend.

This article is from my newsletter “Backend Weekly,” where I explain complex concepts in Backend Engineering every weekend, share exclusive backend engineering resources, and help you become a great Backend Engineer.

Originally published at https://newsletter.masteringbackend.com.