Journey Through Database Transaction Principles: ACID and BASE

Throughout my career, I’ve had the opportunity to work with a variety of applications with diverse data requirements. From my experience, I can attest that the choice of whether to follow ACID (Atomicity, Consistency, Isolation, Durability) or BASE (Basically Available, Soft state, Eventual consistency) principles depends heavily on the business context and specific needs of your application. In this post, I’d like to share some insights I’ve gained about these principles, their use cases, and the importance of choosing the right approach for your project.

I’ll be using examples from Node.js, AWS Lambda, PostgreSQL, and DynamoDB to illustrate.

Understanding ACID Compliance

ACID principles are the cornerstone of transaction processing in traditional relational databases like PostgreSQL. These principles, when adhered to, provide a high level of trust in the system due to the data integrity they enforce.

The four properties of ACID are:

1. Atomicity: Every transaction is treated as a single “unit”, which either succeeds completely or fails completely.
2. Consistency: Every transaction brings the database from one valid state to another.
3. Isolation: Concurrent execution of transactions results in a system state that would be obtained if transactions were executed serially.
4. Durability: Once a transaction has been committed, it will remain so, even in the case of a system failure.

Working with PostgreSQL and implementing transactions using Node.js, I found that ACID compliance was both a necessity and a robust feature. The databases’ ability to handle transactions reliably and ensuring ACID compliance made them the ideal choice for applications that required high consistency and reliability.

ACID Use Cases and Best Practices

An excellent example of an ACID-compliant application is a banking system. If you’re transferring money from one account to another, it’s crucial that if funds are debited from one account, they should indeed be credited to another account.

Here are a few best practices to ensure ACID compliance when building such applications:

1. Manage Transactions Effectively: Encapsulate operations within a transaction to ensure that either all operations succeed, or if any fail, all changes are rolled back.
2. Isolation Levels: Choose the correct isolation level per transaction to manage concurrent access to data.
3. Effective Connection Management: When dealing with AWS Lambda that can freeze execution context when idle, effectively manage database connections to avoid any unexpected behavior.

To illustrate this, let’s look at a code snippet for a money transfer operation using AWS Lambda with Node.js and PostgreSQL:

const { Pool } = require('pg');
const connectionString = 'postgresql://username:password@database-url:5432/mydatabase';
const pool = new Pool({ connectionString });


exports.handler = async (event, context) => {
  const sender = event.sender;
  const receiver = event.receiver;
  const amount = event.amount;
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const deductQuery = 'UPDATE accounts SET balance = balance - $1 WHERE id = $2';
    await client.query(deductQuery, [amount, sender]);
    const addQuery = 'UPDATE accounts SET balance = balance + $1 WHERE id = $2';
    await client.query(addQuery, [amount, receiver]);
    await client.query('COMMIT');
    return { 'status': 'success' };
  } catch (err) {
    await client.query('ROLLBACK');
    console.error(err);
    return { 'status': 'error', 'message': err };
  } finally {
    client.release();
  }
};

This example employs the BEGIN, COMMIT, and ROLLBACK SQL statements to ensure atomicity and consistency of the transaction. If any operation fails, all changes are rolled back, and the system remains in a consistent state.

Embracing BASE Compliance

In contrast to ACID, the BASE model is commonly used in the realm of distributed systems, such as NoSQL databases like DynamoDB. BASE is built around the idea of allowing temporary inconsistencies between nodes in favor of availability and fault tolerance.

The three properties of BASE are:

1. Basically Available: The system guarantees availability of the data as per the CAP theorem.
2. Soft state: The state of the system could change over time, even without input due to the eventual consistency model.
3. Eventual Consistency: The system will become consistent over time, given that the system doesn’t receive input during that time.

Working with DynamoDB and implementing systems using AWS Lambda, I learned that BASE is all about handling the realities of distributed systems. It sacrifices some consistency for availability and partition tolerance.

There were instances in these projects where I had to prioritize system availability over absolute consistency. Let’s say you’re building a social media application where it’s more important to have fast, reliable access to data across the globe. It might be acceptable if a user’s like on a post doesn’t immediately appear to all other users, as long as it eventually does (usually within seconds).

BASE Use Cases and Best Practices

A classic example of where BASE principles shine is in a globally distributed application like a social media platform. It’s more crucial that user data is available quickly than it is to have absolute consistency across all nodes at all times.

Here are a few best practices for working with BASE principles:

1. Handle Eventual Consistency: Allow the system to serve read requests from any node, improving availability and performance. Develop strategies to handle inconsistent data states until the system eventually reaches consistency.
2. Implement Retries and Idempotency: As BASE allows temporary inconsistencies, it’s essential to retry operations and ensure operations are idempotent (repeated requests have the same effect as a single one).
3. Monitor and Alert: Implement robust monitoring and alerting mechanisms to ensure you’re aware of any major inconsistencies and can rectify them promptly.

To illustrate, let’s examine a Node.js function deployed on AWS Lambda that handles “liking” a post in a social media application using DynamoDB:

const AWS = require('aws-sdk');
const docClient = new AWS.DynamoDB.DocumentClient();

exports.handler = async (event, context) => {
  const postId = event.postId;
  const userId = event.userId;
  const params = {
    TableName: 'Posts',
    Key: { 'id': postId },
    UpdateExpression: 'ADD likes :inc',
    ExpressionAttributeValues: { ':inc': 1 },
    ReturnValues: 'UPDATED_NEW'
  };
  try {
    const data = await docClient.update(params).promise();
    return { 'newLikesCount': data.Attributes.likes };
  } catch (err) {
    console.error(err);
    return err;
  }
};

This function adds a “like” to a post. Due to the eventual consistency model, the update may not be immediately visible to all users, but the like count will eventually become consistent across all nodes.

Conclusion

Choosing between ACID and BASE compliance, like many design decisions in software development, requires an understanding of the trade-offs. Each set of principles shines in different contexts, and picking the right one depends on the specifics of your application. Remember that the best solution isn’t always the one that ensures the highest level of consistency or the one that provides the highest availability, but the one that suits your application’s needs and user expectations the best.

—

How about the other way around?

Given that DynamoDB and PostgreSQL have different data models (NoSQL vs. SQL), they have different strengths and weaknesses when it comes to implementing ACID and BASE principles. However, you can still use practices to achieve ACID-like behavior in DynamoDB and BASE-like behavior in PostgreSQL. Here are examples of both:

Implementing ACID principles with DynamoDB

DynamoDB is a NoSQL database that naturally fits into the BASE model. However, it does offer features like transactions and conditional writes that can help you achieve ACID-like properties.

For instance, let’s use the transactional write feature of DynamoDB to implement a banking application that transfers money between accounts:

const AWS = require('aws-sdk');
const dynamodb = new AWS.DynamoDB();

exports.handler = async (event, context) => {
  const sender = event.sender;
  const receiver = event.receiver;
  const amount = event.amount;
  const params = {
    TransactItems: [
      {
        Update: {
          TableName: 'Accounts',
          Key: { 'account_id': { S: sender } },
          UpdateExpression: 'SET balance = balance - :val',
          ExpressionAttributeValues: { ':val': { N: amount.toString() } },
          ConditionExpression: 'balance >= :val'
        }
      },
      {
        Update: {
          TableName: 'Accounts',
          Key: { 'account_id': { S: receiver } },
          UpdateExpression: 'SET balance = balance + :val',
          ExpressionAttributeValues: { ':val': { N: amount.toString() } }
        }
      }
    ]
  };
  try {
    const data = await dynamodb.transactWriteItems(params).promise();
    return { 'status': 'success' };
  } catch (err) {
    console.error(err);
    return { 'status': 'error', 'message': err };
  }
};

Implementing BASE principles with PostgreSQL

PostgreSQL, a relational database, naturally aligns with ACID principles. However, with the use of replication features, a BASE-like behavior can be achieved.

For instance, you can create a read replica of your PostgreSQL database. This replica can be used to serve read requests, while all write operations are directed to the main database. This way, you provide high availability and partition tolerance, and the system will eventually become consistent as the changes are propagated from the primary database to the read replica.

Here’s a conceptual Node.js function that handles a “like” operation on a post, and uses a PostgreSQL read replica for read operations:

const { Pool } = require('pg');
const connectionStringMain = 'postgresql://username:password@database-url:5432/mydatabase';
const connectionStringReplica = 'postgresql://username:password@database-replica-url:5432/mydatabase';

const poolMain = new Pool({ connectionString: connectionStringMain });
const poolReplica = new Pool({ connectionString: connectionStringReplica });
exports.handler = async (event, context) => {
  const postId = event.postId;
  const userId = event.userId;
  const clientMain = await poolMain.connect();
  const clientReplica = await poolReplica.connect();
  try {
    const updateQuery = 'UPDATE posts SET likes = likes + 1 WHERE id = $1';
    await clientMain.query(updateQuery, [postId]);
    const getLikesQuery = 'SELECT likes FROM posts WHERE id = $1';
    const { rows } = await clientReplica.query(getLikesQuery, [postId]);
    
    return { 'newLikesCount': rows[0].likes };
    
  } catch (err) {
    console.error(err);
    return err;
    
  } finally {
    clientMain.release();
    clientReplica.release();
  }
};

In these examples, DynamoDB and PostgreSQL are bent slightly against their natural inclinations to demonstrate how you might implement ACID principles in DynamoDB and BASE principles in PostgreSQL. It’s crucial to note that while these examples give you a flavor of ACID and BASE principles, they do not strictly adhere to the definitions of ACID and BASE models. It’s always best to choose the right tool for the right job, considering the requirements of your application and the strengths of the database you’re using.

—

Conclusion

In conclusion, designing data systems and writing code that adhere to either ACID or BASE principles is not just a theoretical exercise. It has tangible impacts on your application’s consistency, availability, and resilience to failure.

ACID and BASE models provide different sets of guarantees and trade-offs and are better suited for different kinds of applications. ACID’s strong consistency model is crucial for applications that need strict data accuracy and integrity, such as financial transactions. On the other hand, BASE’s eventual consistency model provides high availability and partition tolerance, which are essential for large, distributed systems where high performance and scalability are more important than strict consistency.

Understanding the underlying principles of ACID and BASE is crucial, but it’s equally important to understand how to implement these principles in real-world applications. The tools we use to build these applications — like DynamoDB and PostgreSQL — offer different features that can help us implement ACID and BASE principles.

The examples shown illustrate how to push DynamoDB and PostgreSQL slightly outside their comfort zones to demonstrate ACID and BASE principles. However, it’s vital to remember that each tool has its strengths and should be used where it fits best. There is no one-size-fits-all solution; your specific application requirements should guide your decision in selecting the right consistency model and the right database system.

Remember, technology is a tool that serves us, not the other way around. By understanding these principles and the trade-offs involved, we can make more informed decisions about our application architecture, leading to more robust, reliable, and effective systems.

Next time, we will take a look on implementing these principles in developing real-time and event-driven applications.

References:

1. ACID (Atomicity, Consistency, Isolation, Durability): This article from TechTarget provides a good overview of ACID properties in databases. Link
2. ACID and Database Transactions: Microsoft’s SQL Server documentation provides more insight into ACID compliance and its relevance in database transactions. Link
3. ACID vs. BASE: This article is an excellent resource for understanding the trade-offs between ACID and BASE principles. Link
4. DynamoDB Transactions: This is the AWS documentation for DynamoDB transactions, which shows how ACID properties are maintained in a non-relational database. Link
5. PostgreSQL Concurrency: This article gives an in-depth view of PostgreSQL’s concurrency control, which is critical to maintaining the isolation property of ACID. Link