ACID Compliance in Relational Databases

Hello “👋”

Welcome to another week, another opportunity to become a Great Backend Engineer.

Today’s issue is brought to you by Masteringbackend → A great resource for backend engineers. We offer next-level backend engineering training and exclusive resources.

Before we get down to the business of today. Part 9 of Understanding System Design.

I have a gift for you: You will love this one.

Masteringbackend is launching the beta version of its platform, which allows you to learn backend engineering in one place. The platform will help you grow your backend engineering career and turn you into a great backend engineer.

Here are some of the features:

1. Roadmaps => MB Roadmap enables a structured-based learning approach for Backend engineers.

2. Project Land => MB Projects enables backend engineers to use a learn-by-building model. Build real-world backend projects without coding the frontend.

3. Backend Portfolio => Create and manage your backend portfolio with many real-world backend projects.

4. BackLand => Learn backend engineering by solving challenges in a gamifying way.

Sound interesting?

The beta version is out for testing, reviews, and feedback.

JOIN THE PLATFORM

Now, back to the business of today.

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.

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

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

In this example:

• 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)
);

2. 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;

3. 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)
);

CREATE TABLE table2 (
    id SERIAL PRIMARY KEY,
    table1_id INT REFERENCES table1(id)
);

4. 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;

2. 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;

3. 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;

4. 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 that you choose an appropriate isolation level based on your application's requirements to balance consistency and performance.

Additionally, 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

That will be all for this week. I like to keep this newsletter short.

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

Did you learn any new things from this newsletter this week? Please reply to this email and let me know. Feedback like this encourages me to keep going.

See you on Next Week.

Remember to get the  Masteringbackend → A great resource for backend engineers. We offer next-level backend engineering training and exclusive resources.

Top 5 Remote Backend Jobs this week

Here are the top 5 Backend Jobs you can apply to now.

👨‍💻 A3get
✍️ Backend Engineer
📍Remote
💰 Click on Apply for salary details
Click here to Apply for this role.

👨‍💻 iFindTech
✍️ Backend Developer(Go)
📍Remote, Stockholm
💰 Click on Apply for salary details
Click here to Apply for this role.

👨‍💻 InMobi Glance
✍️ SDE II - Backend
📍Remote, Singapore
💰 Click on Apply for salary details
Click here to Apply for this role.

👨‍💻 Wondercraft
✍️ Engineer Lead
📍Remote, Europe
💰 Click on Apply for salary details
Click here to Apply for this role.

Want more Remote Backend Jobs? Visit GetBackendJobs.com

Backend Engineering Resources

1. Backend Engineering Hub

2. All Backend Books

3. Visit our Backend Store

4. Join our Community

LAST WORD 👋 

How am I doing?

I love hearing from readers, and I'm always looking for feedback. How am I doing with The Backend Weekly? Is there anything you'd like to see more or less of? Which aspects of the newsletter do you enjoy the most?

Hit reply and say hello - I'd love to hear from you!

Stay awesome,
Solomon

I moved my newsletter from Substack to Beehiiv, and it's been an amazing journey. Start yours here.