Transactions

Introduction

In the world of databases, ensuring data integrity is paramount. Data integrity refers to maintaining accurate and reliable data throughout its lifecycle. One of the fundamental ways to achieve this is through transactions. A transaction in SQL is a sequence of operations performed as a single unit of work that must either complete entirely or have no effect at all.

Transactions are crucial because they help maintain the ACID properties, which are essential for ensuring data integrity and consistency:

• Atomicity: Ensures that a transaction is treated as an atomic unit. If any part of the transaction fails, the entire transaction is rolled back, leaving the system in its original state.
• Consistency: Guarantees that a transaction brings the database from one valid state to another, maintaining all integrity constraints.
• Isolation: Ensures that concurrent execution of transactions leaves the database in the same state as if they were executed sequentially.
• Durability: Ensures that once a transaction has been committed, it will remain so, even in the event of power failure or other system failures.

Concept

Transactions are typically used to ensure that multiple operations are completed successfully before any changes are made to the database. This is particularly important in scenarios where data integrity and consistency are critical, such as financial transactions or inventory management systems.

In SQL, you can use the BEGIN TRANSACTION, COMMIT, and ROLLBACK statements to manage transactions:

• BEGIN TRANSACTION: Starts a new transaction.
• COMMIT: Commits all changes made during the transaction, making them permanent.
• ROLLBACK: Rolls back all changes made during the transaction, reverting the database to its previous state.

Examples

Let's explore some practical examples to understand how transactions work in SQL.

Example 1: Simple Transaction

Suppose you have a banking application where you need to transfer money from one account to another. You want to ensure that both operations (deducting from the source account and adding to the destination account) are completed successfully, or neither is applied if something goes wrong.

BEGIN TRANSACTION;

-- Deduct amount from source account
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- Add amount to destination account
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

COMMIT;

In this example, if either of the UPDATE statements fails, the transaction will not be committed, and both accounts will remain unchanged.

Example 2: Handling Errors with ROLLBACK

Let's consider a scenario where you need to update multiple tables within a single transaction. If any operation fails, you want to roll back all changes.

BEGIN TRANSACTION;

-- Update product price
UPDATE products SET price = 50 WHERE product_id = 101;

-- Update inventory count
UPDATE inventory SET stock = stock - 10 WHERE product_id = 101;

-- Simulate an error condition
IF (SELECT stock FROM inventory WHERE product_id = 101) < 0 THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;

In this example, if the stock becomes negative after the update, the transaction is rolled back to prevent invalid data.

Example 3: Using SAVEPOINT

Sometimes, you might want to create intermediate points within a transaction where you can roll back to a specific point rather than the entire transaction. This is useful for complex operations that involve multiple steps.

BEGIN TRANSACTION;

-- Update product price
UPDATE products SET price = 50 WHERE product_id = 101;

-- Savepoint before updating inventory
SAVEPOINT update_inventory;

-- Update inventory count
UPDATE inventory SET stock = stock - 10 WHERE product_id = 101;

-- Simulate an error condition
IF (SELECT stock FROM inventory WHERE product_id = 101) < 0 THEN
    ROLLBACK TO SAVEPOINT update_inventory;
ELSE
    COMMIT;
END IF;

In this example, if the stock becomes negative, you can roll back to the update_inventory savepoint, undoing only the inventory update while keeping the product price change.

What's Next?

Understanding transactions is crucial for ensuring data integrity and consistency in your applications. In the next section, we will explore indexing, which helps improve query performance by organizing data in a way that allows faster retrieval.

Stay tuned!