Learning of SQL Day 46

 

Day 46: ACID Properties and Transaction Management in NoSQL

Introduction:

• The ACID properties (Atomicity, Consistency, Isolation, Durability) ensure reliable processing of database transactions in relational databases. While NoSQL databases often prioritize other aspects like scalability and flexibility, understanding and managing ACID properties in NoSQL systems is still crucial for ensuring data integrity and reliability.

Key Concepts:

• Atomicity (A): Ensures that all operations in a transaction are completed successfully. If any operation fails, the transaction is aborted, and all changes are rolled back.

• Consistency (C): Ensures that a transaction brings the database from one valid state to another, maintaining data integrity.

• Isolation (I): Ensures that concurrent transactions do not interfere with each other and are executed in isolation.

• Durability (D): Ensures that once a transaction is committed, the changes are permanent and survive system failures.

ACID Properties in NoSQL Databases:

1. Atomicity:

   • NoSQL databases may not fully support atomic transactions across multiple documents or collections. However, many NoSQL databases provide atomic operations at the document or row level.

2. Consistency:

   • Some NoSQL databases provide strong consistency, while others offer eventual consistency. The choice depends on the specific use case and requirements for data consistency.

3. Isolation:

   • Isolation levels in NoSQL databases can vary. Some databases provide transaction isolation within a single document or partition, while others may have more relaxed isolation levels.

4. Durability:

   • NoSQL databases often ensure durability through replication and distributed storage. Committed transactions are written to multiple nodes to prevent data loss.

Examples and Commands:

1. Atomicity in MongoDB:

   • MongoDB supports atomic operations at the document level.

json

// Example of an atomic operation in MongoDB
db.students.updateOne(
    { "studentID": 1 },
    { $set: { "age": 21 }, $push: { "courses": { "courseID": 103, "courseName": "Chemistry" } } }
);

2. Consistency in Cassandra:

   • Cassandra offers tunable consistency levels, allowing you to choose between strong and eventual consistency.

sql

-- Example of setting a consistency level in Cassandra
CONSISTENCY QUORUM;
SELECT * FROM students WHERE studentID = 1;

3. Isolation in Redis:

   • Redis provides isolation through transactions using the MULTI and EXEC commands.

redis

// Example of a transaction in Redis
MULTI
SET student:1:age 21
SADD student:1:courses "Chemistry"
EXEC

4. Durability in Couchbase:

   • Couchbase ensures durability through replication and persistence.

json

// Example of a document insertion in Couchbase with durability options
bucket.upsert(
    "student::1",
    {
        "studentID": 1,
        "firstName": "John",
        "lastName": "Doe",
        "age": 21,
        "courses": [
            { "courseID": 101, "courseName": "Mathematics" },
            { "courseID": 102, "courseName": "Physics" }
        ]
    },
    { persist_to: 1, replicate_to: 1 }
);

Practice Exercise:

1. Atomicity: Write a MongoDB command to perform an atomic update on a student's document.

2. Consistency: Write a Cassandra command to read data with strong consistency.

3. Isolation: Write Redis commands to perform an isolated transaction.

4. Durability: Write a Couchbase command to insert a document with durability options.

json

// MongoDB command to perform an atomic update on a student's document
db.students.updateOne(
    { "studentID": 2 },
    { $set: { "age": 22 }, $push: { "courses": { "courseID": 104, "courseName": "Biology" } } }
);

sql

-- Cassandra command to read data with strong consistency
CONSISTENCY QUORUM;
SELECT * FROM students WHERE studentID = 2;

redis

// Redis commands to perform an isolated transaction
MULTI
SET student:2:age 22
SADD student:2:courses "Biology"
EXEC

json

// Couchbase command to insert a document with durability options
bucket.upsert(
    "student::2",
    {
        "studentID": 2,
        "firstName": "Alice",
        "lastName": "Johnson",
        "age": 22,
        "courses": [
            { "courseID": 103, "courseName": "Chemistry" },
            { "courseID": 104, "courseName": "Biology" }
        ]
    },
    { persist_to: 1, replicate_to: 1 }
);

Important Tips:

• Understand the specific ACID properties supported by your chosen NoSQL database and how they impact your application's data integrity and reliability.

• Configure consistency levels and durability options based on your application's requirements for data consistency and availability.

• Regularly test and monitor transactions to ensure they meet the desired ACID properties.

Mastering ACID properties and transaction management in NoSQL databases helps you design reliable and robust distributed systems.