The Project | About Us | Contribute | Donations | License

HOME

In this section, we will explore the concept of transactions in SQL, which is crucial for ensuring data integrity and consistency in database operations. Transactions allow you to group multiple SQL statements into a single unit of work, which can be committed or rolled back as a whole.

Key Concepts

Transaction: A sequence of one or more SQL operations treated as a single unit.
Commit: Finalizes the transaction, making all changes permanent.
Rollback: Reverts all changes made during the transaction, restoring the database to its previous state.
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.
Isolation: Ensures that transactions are executed in isolation from one another.
Durability: Ensures that once a transaction is committed, the changes are permanent, even in the event of a system failure.

Basic Transaction Commands

Starting a Transaction

To start a transaction, you use the BEGIN TRANSACTION or START TRANSACTION command.

BEGIN TRANSACTION;
-- or
START TRANSACTION;

Committing a Transaction

To commit a transaction, making all changes permanent, you use the COMMIT command.

COMMIT;

Rolling Back a Transaction

To roll back a transaction, undoing all changes made during the transaction, you use the ROLLBACK command.

ROLLBACK;

Practical Example

Let's consider a scenario where you need to transfer money from one bank account to another. This operation involves two steps: debiting one account and crediting another. Both steps must succeed or fail together to maintain data integrity.

Step-by-Step Example

Start the Transaction:
```
BEGIN TRANSACTION;
```

Debit the Source Account:

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

Credit the Destination Account:

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;

Commit the Transaction:
```
COMMIT;
```

If any of the steps fail, you can roll back the transaction to ensure that no partial updates are made.

Handling Errors

To handle errors, you can use a conditional structure to roll back the transaction if any operation fails.

BEGIN TRANSACTION;

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

IF @@ERROR <> 0
BEGIN
    ROLLBACK;
    RETURN;
END

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;

IF @@ERROR <> 0
BEGIN
    ROLLBACK;
    RETURN;
END

COMMIT;

Exercise

Task

Write a transaction that performs the following operations:

Insert a new record into the employees table.
Update the departments table to increase the employee count for the relevant department.
If any operation fails, roll back the transaction.

Solution

BEGIN TRANSACTION;

-- Insert a new employee
INSERT INTO employees (employee_id, name, department_id, salary)
VALUES (101, 'John Doe', 3, 60000);

-- Check for errors
IF @@ERROR <> 0
BEGIN
    ROLLBACK;
    RETURN;
END

-- Update the department's employee count
UPDATE departments
SET employee_count = employee_count + 1
WHERE department_id = 3;

-- Check for errors
IF @@ERROR <> 0
BEGIN
    ROLLBACK;
    RETURN;
END

-- Commit the transaction
COMMIT;

Summary

In this section, we covered the basics of transactions in SQL, including how to start, commit, and roll back transactions. We also discussed the importance of transactions in maintaining data integrity and consistency. By using transactions, you can ensure that a series of operations are executed as a single unit, providing a robust mechanism for error handling and data management.

Next, we will delve into the ACID properties that underpin the reliability of transactions in SQL.

Introduction to Transactions

Key Concepts

Basic Transaction Commands

Starting a Transaction

Committing a Transaction

Rolling Back a Transaction

Practical Example

Step-by-Step Example

Handling Errors

Exercise

Task

Solution

Summary

SQL Course

Module 1: Introduction to SQL

Module 2: Basic SQL Queries

Module 3: Working with Multiple Tables

Module 4: Advanced Data Filtering

Module 5: Data Manipulation

Module 6: Advanced SQL Functions

Module 7: Subqueries and Nested Queries

Module 8: Indexes and Performance Optimization

Module 9: Transactions and Concurrency

Module 10: Advanced Topics

Module 11: SQL in Practice

Module 12: Final Project