An Overview of Transactions and ACID Properties for Relational Databases with AlgoJS

AlgoJS is streamlining the learning process for interview preparation for beginner Software Engineers one step at a time, making engineering roles more accessible to beginners from all backgrounds. You can find more resources from AlgoJS on YouTube.

Transactions and ACID properties are fundamental concepts for understanding relational databases and are critical in ensuring data integrity, reliability, and consistency. In fact, one of the main reasons developers choose an SQL database over other options is to guarantee data integrity through ACID properties. SQL databases are designed to be ACID-compliant, making them suitable for scenarios where data consistency, transaction reliability, and failure recovery are critical.

Here’s what you need to know.

What is ACID compliance?

ACID properties are a set of principles designed to ensure that database transactions (operations that read from, write to, or modify the database) are executed reliably so that the database stays accurate and consistent before and after transactions. They maintain this accuracy and consistency even in the face of errors, failures, or concurrent transactions. Each letter in ACID stands for a different principle: Atomicity, Consistency, Isolation, and Durability. 

Here’s what each of them means.

Atomicity: Ensures that a transaction is treated as a single unit that either succeeds completely or fails completely. It guarantees that if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged.

Consistency: Ensures that a transaction brings the database from one valid state to another, maintaining all predefined rules, such as unique constraints and foreign keys.

Isolation: Determines how transaction integrity is impacted by the interaction of concurrent transactions. It ensures that concurrently executing transactions don’t impact each other's execution and outcome.

Durability: Guarantees that once a transaction has been committed, it stays that way, even in the event of a system failure. This means the changes made by the transaction are permanently recorded in the database.

The ACID properties together ensure that database transactions are processed reliably and help maintain the integrity and consistency of the database.

What is a transaction?

In SQL databases, ACID compliance is achieved through transactions. 

Transactions allow multiple database operations to be packaged or grouped together into a single, indivisible unit of work. This means that all the operations within a transaction either complete successfully together or fail together, keeping the database consistent. Transactions are designed to handle operations in a way that protects the integrity of your data, even in the case of system failures, errors, or concurrent access by multiple users. 

Here are some examples of common situations where you’d use transactions:

• Financial operations: In banking systems, transactions ensure that financial operations, such as transfers from one account to another, are completed reliably. For example, when transferring money, the transaction ensures that the amount is deducted from one account and added to another account atomically, maintaining the total balance.
• E-commerce systems: Transactions manage the steps involved in placing an order, such as updating inventory, creating an order record, and adjusting the user's balance or rewards points. This ensures that the order process is completed entirely or not at all, preventing issues like selling more items than in stock or charging a customer without placing their order.
• Booking systems: In systems for booking flights, hotels, or rental cars, transactions ensure that no double bookings occur and that all aspects of a booking (payment processing, reservation confirmation) are completed together. This prevents scenarios where a customer is charged without securing a booking.

Considerations and tradeoffs with ACID adherence

While strict adherence to ACID properties ensures data integrity, it can impact performance and scalability. Here's a brief overview of the trade-offs and how databases balance these considerations. 

Things to consider

• Overhead and latency: ACID compliance adds processing overhead, increasing operation latency due to extra steps like logging and locking.
• Scalability challenges: High isolation levels can reduce concurrency, complicating horizontal scaling and leading to lock contention, which lowers throughput.
• The complexity: Maintaining ACID in distributed databases increases complexity, especially for atomicity and durability, due to the need for coordination protocols.

Balancing strategies

• Tunable isolation levels: Many SQL databases offer adjustable isolation levels to mitigate concurrency issues, but there’s a trade-off between consistency and performance. Lower isolation levels can increase throughput but at the cost of things like dirty or phantom reads.
• Concurrency control: Databases choose between optimistic concurrency control, which assumes transactions do not conflict and checks for conflicts at commit time, and pessimistic concurrency control, which locks resources during a transaction. Optimistic concurrency can improve performance in low-conflict environments, while pessimistic concurrency is beneficial when conflicts happen often.
• NoSQL and NewSQL solutions: NoSQL databases often relax ACID properties to improve scalability and performance, especially in distributed systems. However, some NoSQL systems provide configurable consistency models to support ACID-like transactions in specific contexts. NewSQL combines NoSQL scalability with strict ACID compliance, using innovative techniques to reduce performance trade-offs.
• Advanced transaction management techniques: Techniques like multi-version concurrency control (MVCC) and in-memory processing help mitigate the performance impacts of ACID compliance, improving throughput and reducing latency. Using in-memory processing and sophisticated indexing strategies can also help mitigate the performance impact of ACID compliance.

Want to learn more?

You can find deeper dives into all things data structures, algorithms, and system design on the AlgoJS YouTube channel.  

You can also join the Formation Fellowship to access unconditional support from a team of engineering mentors, technical recruiters, career coaches, and more. Take the assessment to find out where you could use support.