SQL Transactions

Introduction

In this chapter, we will focus on transactions in SQL. Transactions are a fundamental concept in database management systems (DBMS) that ensure the integrity and consistency of data. This chapter will cover the definition, properties, syntax, and provide examples to help you understand how to use transactions effectively.

What is a Transaction?

A transaction in SQL is a sequence of one or more SQL operations treated as a single unit of work. A transaction ensures that all operations within it are completed successfully; otherwise, none of them are applied. This is crucial for maintaining data integrity and consistency.

Properties of Transactions (ACID)

Transactions have four key properties, known as ACID properties:

1. Atomicity: Ensures that all operations within the transaction are completed; if any operation fails, the entire transaction is rolled back.
2. Consistency: Ensures that a transaction brings the database from one valid state to another valid state.
3. Isolation: Ensures that concurrent transactions do not interfere with each other.
4. Durability: Ensures that the results of a committed transaction are permanent and survive system failures.

Syntax for Transactions

Basic Syntax

BEGIN TRANSACTION;
-- SQL operations
COMMIT; -- or ROLLBACK;

Example Syntax

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT; -- or ROLLBACK;

Step-by-Step Example

Sample Tables

First, let's create sample tables named accounts to demonstrate transactions.

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    account_name VARCHAR(50),
    balance DECIMAL(10, 2)
);

Inserting Sample Data

INSERT INTO accounts (account_id, account_name, balance)
VALUES
(1, 'Ramesh', 1000.00),
(2, 'Sita', 1500.00);

Performing a Transaction

Let's perform a transaction to transfer 100 units from Ramesh's account to Sita's account.

BEGIN TRANSACTION;

-- Subtracting 100 from Ramesh's account
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

-- Adding 100 to Sita's account
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;

-- Commit the transaction
COMMIT;

Rolling Back a Transaction

If an error occurs during the transaction, we can roll it back to ensure data integrity.

BEGIN TRANSACTION;

-- Subtracting 100 from Ramesh's account
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

-- Simulate an error
-- Uncomment the line below to simulate an error
-- ERROR;

-- Adding 100 to Sita's account
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;

-- Rollback the transaction if an error occurs
ROLLBACK;

Ensuring Atomicity with TRY...CATCH (SQL Server)

In SQL Server, you can use TRY...CATCH to handle errors and ensure atomicity.

BEGIN TRY
    BEGIN TRANSACTION;

    -- Subtracting 100 from Ramesh's account
    UPDATE accounts
    SET balance = balance - 100
    WHERE account_id = 1;

    -- Simulate an error
    -- Uncomment the line below to simulate an error
    -- ERROR;

    -- Adding 100 to Sita's account
    UPDATE accounts
    SET balance = balance + 100
    WHERE account_id = 2;

    -- Commit the transaction
    COMMIT;
END TRY
BEGIN CATCH
    -- Rollback the transaction if an error occurs
    ROLLBACK;
    -- Print error message
    PRINT ERROR_MESSAGE();
END CATCH;

Viewing the Result

To verify the result of the transaction, query the accounts table:

SELECT * FROM accounts;

Output

account_id	account_name	balance
1	Ramesh	900.00
2	Sita	1600.00

If the transaction was committed successfully, the balances will reflect the changes. If it was rolled back, the balances will remain as they were before the transaction.

Conclusion

Transactions are essential for ensuring the integrity and consistency of data in a database. This chapter covered the basic syntax for using transactions, provided examples to illustrate their use, and demonstrated how to handle errors to maintain data integrity. Understanding how to use transactions effectively will greatly enhance your ability to manage and manipulate data in a relational database.