How Transactions Work in Relational vs. Document Databases

What is a Database Transaction ??!!!!

A database transaction is a sequence of operations performed as a single logical unit of work. These operations can include creating, reading, updating, or deleting data in a database. The key feature of a transaction is that it ensures the integrity and consistency of the database, even in the face of errors, system failures, or concurrent access by multiple users. It is closely related with database ACID principle. So lets go to the ACID principle.

ACID Principle

The concept of a transaction is closely associated with the ACID properties, which are fundamental principles that ensure reliable processing in a database. ACID stands for

A - ATOMICITY

• Atomicity ensures that a transaction is treated as a single, indivisible unit of work. This means that either all the operations in the transaction are completed successfully, or none of them are applied.

• Imagine you are running an inventory system where you need to update the quantity of a product and record a sale. The transaction involves these steps:

  1. Check if the product has enough quantity (e.g., 2 units available).

  2. If yes, decrement the product quantity by 2.

  3. Insert a record in the Sales collection to note the sale of 2 units.

C - CONSISTENCY

• Consistency ensures that a transaction brings the database from one valid state to another, maintaining the integrity of the database according to all defined rules (e.g., constraints, triggers).

• Before the transaction, the product quantity might be 50, and there are no new sales records. After a successful transaction, the product quantity will be 48, and there will be a new sale record. Consistency ensures that the product quantity is correctly updated and that the sale is recorded according to the business rules (e.g., ensuring that the product quantity never drops below zero).

I - ISOLATION

• Isolation ensures that the operations of one transaction are invisible to other concurrent transactions until the transaction is committed. This prevents interference and ensures that transactions do not affect each other.

• If another transaction is trying to update the same product's quantity or record another sale at the same time, Isolation guarantees that these transactions won’t interfere with each other. For example, while the first transaction is running, another transaction won’t see the decremented quantity or the new sale record until the first transaction is fully committed.

D - DURABILITY

• Definition: Durability guarantees that once a transaction has been committed, its changes are permanent and will survive system failures. This is typically ensured through mechanisms like logging or writing to non-volatile storage.

• After the transaction is committed, the product quantity is reduced by 2, and the sale record is added. Durability ensures that even if the system crashes immediately after the transaction, when the system recovers, the product quantity will still be 48, and the sale record will still be present in the database.

Let's See a Real Life Example

You manage an inventory system for an electronics store. The system needs to handle product sales while ensuring that the inventory is accurately updated and sales are properly recorded. The database has two key collections (or tables):

• Products: Stores details about each product, including its available quantity.

• Sales: Records details of each sale, including the product sold, quantity sold, and the sale date.

Scenario Details

1. Customer Purchase

A customer wants to buy 2 units of a laptop with Product ID: 1. The system needs to:

1. Check if there are at least 2 units available.

2. If there are, reduce the quantity of the product in the inventory by 2.

3. Record the sale in the Sales collection/table.

Transaction Flow

1. Start Transaction:

   • The system begins a transaction to manage the sale.

2. Check Product Quantity:

   • The system verifies that there are 2 units available.

3. Update Inventory:

   • The product quantity is reduced by 2.

4. Record Sale:

   • The sale of 2 units is recorded in the Sales collection.

5. Commit Transaction:

   • The transaction is successfully completed and committed.

6. Handle System Crash (if any):

   • If a system crash occurs after committing the transaction, the changes (reduced quantity and recorded sale) are permanent and will persist.

This scenario shows how the ACID properties ensure that the product sale is handled reliably, even in the face of potential errors or concurrent operations.

Coding Example For Relational Database system

Suppose you are developing your application based on that scenario. You are using

• C# programming language

• .NET Core Framework

• MS SQL Server Relational Database for Data Storage

• Entity Framework Core As ORM

This is how you make a database Transaction

using (var context = new InventoryContext())
{
    // transaction start
    using (var transaction = context.Database.BeginTransaction())
    {
        try
        {
            // Retrieve the product from the database
            var product = context.Products.SingleOrDefault(p => p.Id == 1);

            // Check if there is enough quantity
            // if product is null it will be immidiately roll back
            if (product != null && product.Quantity >= 2)
            {
                // Update the product quantity
                // in this case reduce the quantity by 2
                product.Quantity -= 2;
                context.SaveChanges();

                // Record the sale
                // adding the information to the sales table
                var sale = new Sale
                {
                    ProductId = 1,
                    QuantitySold = 2,
                    Date = DateTime.Now
                };
                context.Sales.Add(sale);
                context.SaveChanges();

                // Commit the transaction
                // everything is done so commit 
                // and make permanent change in the database
                transaction.Commit();
            }
            else
            {
                // Not enough quantity, roll back the transaction
                Console.WriteLine("Not enough quantity to complete the sale.");
                transaction.Rollback();
            }
        }
        catch (Exception ex)
        {
            // If there's an error, roll back the transaction
            transaction.Rollback();
            Console.WriteLine("Transaction failed: " + ex.Message);
        }
    }
}

Remember in the transaction wither everything recorded perfectly or nothing will be recorded. NO IN BETWEEN.

For Non Relational Database system (MongoDB)

Before i give the example of this in a non relational database you need to know one term which is "Read Concern"

What is Read Concern in MongoDB?

Read Concern in MongoDB defines the level of isolation for read operations, meaning it controls the consistency and durability of the data you read from your database. It determines how "fresh" the data is that you're reading.

Read Concern Levels:

MongoDB supports different levels of Read Concern:

1. "local" (Default):

   • The query returns the most recent data available on the node that receives the read operation. This level doesn't guarantee that the data has been replicated to other nodes in a replica set. It's fast but may return stale data if other nodes are not yet synchronized.

2. "available":

   • Similar to "local", but ensures that the query reads data even if a replica set member is recovering.

3. "majority":

   • The query returns data that has been acknowledged by a majority of the replica set members. This provides stronger consistency, ensuring that the data you read is the same across the majority of nodes, but it may be slower due to the need for confirmation from multiple nodes.

4. "linearizable":

   • The query returns data that reflects all successful writes issued with a "majority" write concern before the read operation. It provides the strongest consistency, ensuring that you always read the most recent data, but it can be much slower and impact performance.

5. "snapshot":

   • Available for transactions, "snapshot" read concern ensures that you read data that is consistent with the start of a transaction.

Scenario:

Imagine you're managing a small inventory system with a MongoDB database. You have two collections:

1. products - storing product details.

2. sales - recording sales transactions.

You want to ensure that when you read data from these collections during a transaction, the data is consistent and reflects a specific point in time, even if other operations are happening concurrently.

// products collection
{
    "_id": 1,
    "name": "Laptop",
    "quantity": 50,
    "price": 1000
}

// sales collection
{
    "_id": 1,
    "product_id": 1,
    "quantity_sold": 2,
    "date": "2024-08-21"
}

Coding Example Using MongoDB

Here is the code for making transaction using "Snapshot" Read Concern.

const session = db.getMongo().startSession();
session.startTransaction({
    readConcern: { level: "snapshot" }
});

try {
    const product = session.getDatabase("inventory").products.findOne({ _id: 1 });

    if (product.quantity >= 2) {
        // Update the product quantity
        session.getDatabase("inventory").products.updateOne(
            { _id: 1 },
            { $inc: { quantity: -2 } }
        );

        // Record the sale
        session.getDatabase("inventory").sales.insertOne({
            product_id: 1,
            quantity_sold: 2,
            date: new Date()
        });

        // Commit the transaction
        session.commitTransaction();
    } else {
        // Abort the transaction if there's not enough quantity
        session.abortTransaction();
        console.log("Not enough quantity to complete the sale.");
    }
} catch (error) {
    // If an error occurs, abort the transaction
    session.abortTransaction();
    console.error("Transaction failed: ", error);
} finally {
    session.endSession();
}

Let's Explain it One By One

• This line bellow starts a new session in MongoDB. A session is necessary for running a transaction because it provides a context in which the transaction occurs. The session allows MongoDB to track the operations within the transaction and ensure they are applied atomically (all or nothing).

const session = db.getMongo().startSession();

• We start the transaction within the session. The startTransaction() method begins the transaction, and we specify the readConcern level as "snapshot". This ensures that all reads during the transaction will see a consistent view of the data as it was at the start of the transaction, regardless of any other operations occurring concurrently.

session.startTransaction({
    readConcern: { level: "snapshot" }
});

• We use a try block to handle any errors that might occur during the transaction. In this line, we're reading a document from the products collection where _id equals 1. The findOne() method retrieves a single document, and since this read operation is part of the transaction, it uses the "snapshot" read concern to ensure that the data is consistent with the start of the transaction.

try {
    const product = session.getDatabase("inventory").products.findOne({ _id: 1 });

• This line checks if the quantity of the product is greater than or equal to 2. This is important because we only want to proceed with the sale if there's enough stock available. If the condition is true, the transaction continues; if not, it will be aborted.

if (product.quantity >= 2) {

• If the product has enough quantity, we proceed to update the document. The updateOne() method is used to decrement the quantity field by 2. The $inc operator decreases the quantity by the specified amount (-2 in this case). This operation is part of the transaction, so it won’t be visible outside the transaction until it's committed

session.getDatabase("inventory").products.updateOne(
    { _id: 1 },
    { $inc: { quantity: -2 } }
);

• After updating the product quantity, we record the sale in the sales collection. The insertOne() method adds a new document to the sales collection with details about the sale, such as product_id, quantity_sold, and the current date. This insert operation is also part of the transaction.

session.getDatabase("inventory").sales.insertOne({
    product_id: 1,
    quantity_sold: 2,
    date: new Date()
});

• After updating the product quantity, we record the sale in the sales collection. The insertOne() method adds a new document to the sales collection with details about the sale, such as product_id, quantity_sold, and the current date. This insert operation is also part of the transaction.

session.commitTransaction();

• If the initial check (product.quantity >= 2) fails, the transaction is aborted using abortTransaction(). Aborting the transaction means that none of the operations within it will be applied to the database. We also log a message to indicate that the sale couldn't be completed due to insufficient stock.

} else {
    session.abortTransaction();
    console.log("Not enough quantity to complete the sale.");
}

• If an error is thrown at any point within the try block, the transaction is aborted to ensure that no partial changes are applied to the database. The error message is also logged for debugging purposes

} catch (error) {
    session.abortTransaction();
    console.error("Transaction failed: ", error);
}

• The finally block runs regardless of whether the transaction was successful or not. The endSession() method is called to clean up the session, releasing any resources associated with it. It's a good practice to end the session after the transaction is complete to avoid resource leaks.

finally {
    session.endSession();
}

Key Points

• Session Management: The code starts and manages a session, which is required for transactions in MongoDB.

• Transaction: The transaction ensures that multiple operations (reading the product, updating the quantity, and inserting a sale record) are treated as a single atomic unit.

• Read Concern "snapshot": This guarantees that the reads within the transaction see a consistent view of the data, even if other operations are happening concurrently.

• Error Handling: The try-catch-finally structure ensures that errors are handled gracefully, with the transaction being aborted if something goes wrong.

Side By Side Comparison