Lecture 8: ACID transactions in MongoDB

Lecture 8: ACID transactions in MongoDB#

Learning Objectives#

By the end of this lecture, students will be able to:

  • Understand the ACID properties and their importance in database transactions.

  • Implement multi-document ACID transactions in MongoDB using the pymongo library.

  • Handle transaction errors and ensure data consistency and reliability.

Introduction to ACID Transactions#

ACID transactions ensure that database operations are processed reliably. ACID stands for:

  • Atomicity: Ensures that all operations within a transaction are completed successfully. If any operation fails, the entire transaction is rolled back.

  • Consistency: Ensures that a transaction brings the database from one valid state to another, maintaining database invariants.

  • Isolation: Ensures that concurrently executing transactions do not affect each other.

  • Durability: Ensures that once a transaction is committed, it remains so, even in the event of a system failure.

Atomicity#

Example: Transferring funds between two bank accounts.

  • Scenario: A customer wants to transfer $100 from their savings account to their checking account.

  • Atomicity: The transaction involves two operations: deducting \(100 from the savings account and adding \)100 to the checking account. If either operation fails (e.g., due to insufficient funds in the savings account), the entire transaction is rolled back, and no money is transferred. This ensures that partial updates do not occur.

Consistency#

Example: Ensuring data integrity during a product purchase.

  • Scenario: A customer purchases a product from an online store.

  • Consistency: The transaction involves updating the inventory to reduce the product quantity and recording the sale in the sales ledger. The database must ensure that the inventory count and sales record are updated together. If the inventory update fails (e.g., items sold out), the sale should not be recorded, maintaining the consistency of the database.

Isolation#

Example: Concurrent ticket booking for a concert.

  • Scenario: Two customers attempt to book the last available ticket for a concert at the same time.

  • Isolation: Each transaction should be isolated from the other, ensuring that only one customer can successfully book the last ticket. The database handles these concurrent transactions in such a way that one transaction completes before the other begins, preventing race conditions and ensuring data integrity.

Durability#

Example: Recording a bank deposit.

  • Scenario: A customer deposits $500 into their bank account.

  • Durability: Once the transaction is committed, the deposit is guaranteed to be recorded in the database, even if there is a system failure immediately after the transaction. This ensures that the customer’s balance reflects the deposit accurately and reliably.

MongoDB Transactions#

MongoDB supports multi-document ACID transactions, which allow you to perform multiple operations across multiple documents and collections in a single transaction. Transactions in MongoDB are available for replica sets and sharded clusters.

Key Concepts#

  • Session: A session is required to start a transaction.

  • Transaction: A sequence of read and write operations that are executed as a single unit.

Example#

Below is an example of how to use transactions in MongoDB using Python and the pymongo library.

1. Setup MongoDB Connection and Collections#

from pymongo import MongoClient # import mongo client to connect
from pymongo.errors import ConnectionFailure, OperationFailure
import json # import json to load credentials
import urllib.parse

# load credentials from json file
with open('credentials_mongodb.json') as f:
    login = json.load(f)

# assign credentials to variables
username = login['username']
password = urllib.parse.quote(login['password'])
host = login['host']
url = "mongodb+srv://{}:{}@{}/?retryWrites=true&w=majority".format(username, password, host)

# connect to the database
client = MongoClient(url)

# drop database banking if they exist
client.drop_database('banking')

# create database and collection
db = client["banking"]

# Collections
accounts_collection = db.accounts
transactions_collection = db.transactions

2. Insert Sample Data#

# Insert sample data into accounts collection
accounts_collection.insert_many([
    {"account_id": "account_1", "balance": 500},
    {"account_id": "account_2", "balance": 300}
])

# Insert sample data into transactions collection
transactions_collection.insert_many([
    {"from_account": "account_1", "to_account": "account_2", "amount": 50, "status": "completed"},
    {"from_account": "account_2", "to_account": "account_1", "amount": 30, "status": "completed"}
])

InsertManyResult([ObjectId('66f22cb2a09f7a2fb0d6ede5'), ObjectId('66f22cb2a09f7a2fb0d6ede6')], acknowledged=True)

# print out documents in accounts collection
for account in accounts_collection.find():
    print(account)

{'_id': ObjectId('66f22cb2a09f7a2fb0d6ede3'), 'account_id': 'account_1', 'balance': 500}
{'_id': ObjectId('66f22cb2a09f7a2fb0d6ede4'), 'account_id': 'account_2', 'balance': 300}

Let’s say we want to update the account balance. For example, a customer deposit $50 into their account_1

# print out account_1 document in accounts collection before update
print(accounts_collection.find_one({"account_id": "account_1"}))

{'_id': ObjectId('66f22cb2a09f7a2fb0d6ede3'), 'account_id': 'account_1', 'balance': 400}

# Increment the balance of account_1 by 50
accounts_collection.update_one(
    {"account_id": "account_1"},
    {"$inc": {"balance": 50}}
)

UpdateResult({'n': 1, 'electionId': ObjectId('7fffffff00000000000000cb'), 'opTime': {'ts': Timestamp(1727147916, 1), 't': 203}, 'nModified': 1, 'ok': 1.0, '$clusterTime': {'clusterTime': Timestamp(1727147916, 1), 'signature': {'hash': b'\xcbc~\xf2\xdfP\x1e\xa7\xcd\xea\xf2\xac\x9f1\xf5\x84\x91\xa0\x04\xee', 'keyId': 7381891571605569538}}, 'operationTime': Timestamp(1727147916, 1), 'updatedExisting': True}, acknowledged=True)

# print out account_1 document in accounts collection after update
print(accounts_collection.find_one({"account_id": "account_1"}))

{'_id': ObjectId('66f22cb2a09f7a2fb0d6ede3'), 'account_id': 'account_1', 'balance': 450}

Explanation of $inc in MongoDB#

What is $inc?#

$inc is an update operator in MongoDB that is used to increment (or decrement) the value of a field by a specified amount. It is commonly used in update operations to modify the value of numeric fields.

Syntax#
{
    "$inc": { <field1>: <amount1>, <field2>: <amount2>, ... }
}

  • <field1>, <field2>: The fields to be incremented or decremented.

  • <amount1>, <amount2>: The amounts by which to increment or decrement the fields. Positive values increment the field, while negative values decrement the field.

Example Usage#

  1. Incrementing a Field

Suppose you have a collection named accounts with documents that contain account balances. You can use $inc to increment the balance of a specific account.

# print out account_1 document in accounts collection before update
print(accounts_collection.find_one({"account_id": "account_1"}))

{'_id': ObjectId('66f22cb2a09f7a2fb0d6ede3'), 'account_id': 'account_1', 'balance': 450}

# Increment the balance of account_1 by 100
accounts_collection.update_one(
    {"account_id": "account_1"},
    {"$inc": {"balance": 100}}
)

UpdateResult({'n': 1, 'electionId': ObjectId('7fffffff00000000000000cb'), 'opTime': {'ts': Timestamp(1727148066, 1), 't': 203}, 'nModified': 1, 'ok': 1.0, '$clusterTime': {'clusterTime': Timestamp(1727148066, 1), 'signature': {'hash': b'\xff\x04\xce\xc4j\xa95\xd0jD\xe9ss\\\x01\xa5\x04\x8ey\xed', 'keyId': 7381891571605569538}}, 'operationTime': Timestamp(1727148066, 1), 'updatedExisting': True}, acknowledged=True)

# print out account_1 document in accounts collection after update
print(accounts_collection.find_one({"account_id": "account_1"}))

{'_id': ObjectId('66f22cb2a09f7a2fb0d6ede3'), 'account_id': 'account_1', 'balance': 550}

  1. Decrementing a Field

You can also use $inc to decrement the value of a field by specifying a negative amount.

# print out account_1 document in accounts collection before update
print(accounts_collection.find_one({"account_id": "account_1"}))

{'_id': ObjectId('66f22cb2a09f7a2fb0d6ede3'), 'account_id': 'account_1', 'balance': 550}

# Decrement the balance of account_1 by 50
accounts_collection.update_one(
    {"account_id": "account_1"},
    {"$inc": {"balance": -50}}
)

UpdateResult({'n': 1, 'electionId': ObjectId('7fffffff00000000000000cb'), 'opTime': {'ts': Timestamp(1727148099, 3), 't': 203}, 'nModified': 1, 'ok': 1.0, '$clusterTime': {'clusterTime': Timestamp(1727148099, 3), 'signature': {'hash': b'\xf9N3\xbbr\xba\xbf8\x9f6h{\x11N\x0c\xdc\xbfP\x13 ', 'keyId': 7381891571605569538}}, 'operationTime': Timestamp(1727148099, 3), 'updatedExisting': True}, acknowledged=True)

# print out account_1 document in accounts collection after update
print(accounts_collection.find_one({"account_id": "account_1"}))

{'_id': ObjectId('66f22cb2a09f7a2fb0d6ede3'), 'account_id': 'account_1', 'balance': 500}

3. Define the Transaction Function#

def transfer_funds(session, from_account, to_account, amount):
    # Check if the from_account has sufficient balance
    from_account_doc = accounts_collection.find_one({"account_id": from_account}, session=session)
    if from_account_doc["balance"] < amount:
        raise ValueError("Insufficient funds")

    # Transfer funds
    accounts_collection.update_one(
        {"account_id": from_account},
        {"$inc": {"balance": -amount}},
        session=session
    )
    accounts_collection.update_one(
        {"account_id": to_account},
        {"$inc": {"balance": amount}},
        session=session
    )
    transactions_collection.insert_one(
        {
            "from_account": from_account,
            "to_account": to_account,
            "amount": amount,
            "status": "completed"
        },
        session=session
    )

4. Execute the Transaction#

# Start a session
with client.start_session() as session:
    # Start a transaction
    with session.start_transaction():
        try:
            transfer_funds(session, "account_1", "account_2", 100)
        except (ConnectionFailure, OperationFailure) as e:
            print(f"Transaction aborted due to: {e}")
            session.abort_transaction()
        else:
            session.commit_transaction()
            print("Transaction committed successfully")

Transaction committed successfully

# print out documents in accounts collection
for account in accounts_collection.find():
    print(account)

{'_id': ObjectId('66f22c7fa09f7a2fb0d6edd6'), 'account_id': 'account_1', 'balance': 50}
{'_id': ObjectId('66f22c7fa09f7a2fb0d6edd7'), 'account_id': 'account_2', 'balance': 300}

# print out documents in transactions collection, drop id field
for transaction in transactions_collection.find({}, {"_id": 0}):
    print(transaction)

{'from_account': 'account_1', 'to_account': 'account_2', 'amount': 50, 'status': 'completed'}
{'from_account': 'account_2', 'to_account': 'account_1', 'amount': 30, 'status': 'completed'}
{'from_account': 'account_1', 'to_account': 'account_2', 'amount': 100, 'status': 'completed'}

5. Testing the constraint#

Let’s add an account_3 with a balance of \(50 and try to perform a transactions to send \)100 from account_3 to account_1. This should give an error because of insufficient funds

# add account_3 to accounts collection with balance 50
accounts_collection.insert_one({"account_id": "account_3", "balance": 50})

InsertOneResult(ObjectId('66f22d79a09f7a2fb0d6ede8'), acknowledged=True)

# Start a session
with client.start_session() as session:
    # Start a transaction
    with session.start_transaction():
        try:
            transfer_funds(session, "account_3", "account_1", 100)
        except (ConnectionFailure, OperationFailure) as e:
            print(f"Transaction aborted due to: {e}")
            session.abort_transaction()
        else:
            session.commit_transaction()
            print("Transaction committed successfully")

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[46], line 6
      4 with session.start_transaction():
      5     try:
----> 6         transfer_funds(session, "account_3", "account_1", 100)
      7     except (ConnectionFailure, OperationFailure) as e:
      8         print(f"Transaction aborted due to: {e}")

Cell In[45], line 6, in transfer_funds(session, from_account, to_account, amount)
      4 from_account_doc = accounts_collection.find_one({"account_id": from_account}, session=session)
      5 if from_account_doc["balance"] < amount:
----> 6     raise ValueError("Insufficient funds")
      8 # Transfer funds
      9 accounts_collection.update_one(
     10     {"account_id": from_account},
     11     {"$inc": {"balance": -amount}},
     12     session=session
     13 )

ValueError: Insufficient funds

# print out documents in accounts collection
for account in accounts_collection.find():
    print(account)

{'_id': ObjectId('66f22cb2a09f7a2fb0d6ede3'), 'account_id': 'account_1', 'balance': 400}
{'_id': ObjectId('66f22cb2a09f7a2fb0d6ede4'), 'account_id': 'account_2', 'balance': 400}
{'_id': ObjectId('66f22d79a09f7a2fb0d6ede8'), 'account_id': 'account_3', 'balance': 50}

# print out documents in transactions collection, drop id field
for transaction in transactions_collection.find({}, {"_id": 0}):
    print(transaction)

{'from_account': 'account_1', 'to_account': 'account_2', 'amount': 50, 'status': 'completed'}
{'from_account': 'account_2', 'to_account': 'account_1', 'amount': 30, 'status': 'completed'}
{'from_account': 'account_1', 'to_account': 'account_2', 'amount': 100, 'status': 'completed'}

Explanation

  1. Setup MongoDB Connection and Collections: Connect to MongoDB and define the collections to be used in the transaction.

  2. Insert Sample Data: Insert sample data into the accounts and transactions collections to set up the initial state.

  3. Define the Transaction Function with Balance Constraint: Define a function transfer_funds that performs the operations within the transaction. This function checks if the from_account has sufficient balance before proceeding with the transfer. If the balance is insufficient, it raises a ValueError. If the balance is sufficent, this function updates the balance of two accounts and logs the transaction.

  4. Execute the Transaction: Start a session and a transaction. Call the transfer_funds function within the transaction. If any operation fails, the transaction is aborted. If all operations succeed, the transaction is committed.

Contents