Understanding Database Transactions, ACID Properties, and Stored Procedures in SQL

Posted on Sep 7, 2024 in Computers

Database Transactions

A database transaction is a sequence of one or more SQL operations treated as a single unit of work. It ensures that either all operations are completed successfully or none are applied, preserving data integrity.

ACID Properties of Transactions

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

Consistency: Ensures that the database moves from one valid state to another, maintaining data integrity.

Isolation: Ensures that transactions are executed independently without interference, even when executed concurrently.

Durability: Ensures that once a transaction is committed, its changes are permanent, even in the event of a system failure.

Transaction Diagram Explanation

To visualize a database transaction, we can use a transaction diagram:

  • Active State: The transaction begins execution. Operations are performed, such as reading or writing data.
  • Partially Committed State: All operations in the transaction are executed, but the transaction has not yet been committed.
  • Committed State: The transaction is successfully committed, and all changes are saved to the database.
  • Failed State: If any operation fails during the transaction, it moves to the failed state, triggering a rollback.
  • Aborted State: The transaction has been rolled back, meaning all changes are undone, and the database returns to its previous consistent state.

Example of a Transaction

BEGIN TRANSACTION;

-- Step 1: Deduct $100 from Account A
UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 'A';

-- Step 2: Add $100 to Account B
UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 'B';

-- If both operations succeed
COMMIT;

-- If any operation fails
ROLLBACK;

Explanation

  • BEGIN TRANSACTION: Marks the start of the transaction.
  • UPDATE Statements: Perform the necessary operations (debit and credit).
  • COMMIT: If both operations succeed, the transaction is committed, and the changes are saved permanently.
  • ROLLBACK: If any operation fails, the transaction is rolled back, undoing all changes.

Assertions and Triggers in SQL

Assertions in SQL

Assertions enforce constraints on data across multiple tables or conditions that can’t be easily expressed using CHECK constraints. However, not all database systems support assertions directly (e.g., MySQL does not support assertions, but Oracle and PostgreSQL do).

Example of an Assertion

Suppose we have two tables, Employees and Departments, and we want to ensure that the total number of employees in each department does not exceed 100.

CREATE ASSERTION EmployeeLimit
CHECK (
    (SELECT COUNT(*)
     FROM Employees
     GROUP BY DepartmentID
     HAVING COUNT(*) > 100) IS NULL
);

Triggers in SQL

Triggers are procedural code that automatically executes in response to certain events (INSERT, UPDATE, or DELETE) on a particular table or view. They are used for tasks like enforcing complex constraints, auditing changes, and synchronizing data between tables.

Example of a Trigger

Let’s say we have a table Accounts to manage bank accounts, and we want to ensure that the balance never goes negative after any withdrawal.

CREATE TABLE Accounts (
    AccountID INT PRIMARY KEY,
    AccountHolder VARCHAR(100),
    Balance DECIMAL(10, 2)
);

CREATE TRIGGER PreventNegativeBalance
BEFORE UPDATE ON Accounts
FOR EACH ROW
WHEN (NEW.Balance < 0)
BEGIN
    RAISE_APPLICATION_ERROR(-20001, 'Insufficient funds: Balance cannot be negative.');
END;

System Log in Database Transactions

A System Log is a critical mechanism used to ensure data integrity, particularly during failures. The log records every transaction that affects the database, tracking the changes made to data. This enables the database system to recover to a consistent state in case of a crash by replaying or undoing these logged transactions.

Components of a System Log

  • Transaction Start (<Tn, start>): Marks the beginning of a transaction Tn.
  • Write Operation (<Tn, X, V1, V2>): Indicates that transaction Tn has changed the value of data item X from V1 to V2.
  • Transaction Commit (<Tn, commit>): Indicates that transaction Tn has successfully completed and the changes should be made permanent.
  • Transaction Abort (<Tn, abort>): Indicates that transaction Tn has been aborted, and any changes should be undone.

ACID Properties of Database Transactions

ACID is a set of properties that ensure data integrity and consistency in database transactions.

Atomicity (All or Nothing)

A transaction is treated as a single unit. Either all operations within the transaction are executed successfully, or none of them are. This prevents partial updates that could leave the database in an inconsistent state.

Consistency (Data Integrity)

The database must be in a consistent state before and after a transaction. This means that the transaction must adhere to all defined rules and constraints. For example, a transfer of funds between two accounts should ensure that the total balance remains unchanged.

Isolation (Concurrent Transactions)

Transactions should be isolated from each other. This prevents conflicts and ensures that each transaction sees a consistent view of the database, as if it were the only one executing.

Durability (Permanent Changes)

Once a transaction is committed, its changes are permanent and should survive system failures. This means that the database system must ensure that the changes are written to non-volatile storage.

Stored Procedures in SQL

Stored procedures are precompiled collections of SQL statements stored in the database server. They can be executed by name, providing a way to encapsulate and reuse common database operations. This can improve performance, modularity, and security.

Benefits of Stored Procedures

  • Performance: Stored procedures are precompiled, so they execute faster than executing individual SQL statements.
  • Modularity: They break down complex database operations into smaller, reusable units.
  • Security: Stored procedures can be granted specific permissions, limiting access to sensitive data.
  • Maintainability: Changes to common database operations can be made in one place, simplifying maintenance.

Syntax

The syntax for creating a stored procedure in SQL Server is:

CREATE PROCEDURE procedure_name
AS
BEGIN
    -- SQL statements
END

Executing a Stored Procedure

To execute a stored procedure, you use the EXEC or EXECUTE statement. For example:

EXEC CalculateEmployeeSalary 123;

1. Creating a Stored Procedure

CREATE PROCEDURE UpdateEmployeeSalary
    @EmployeeID INT,
    @NewSalary DECIMAL(10, 2)
AS
BEGIN
    -- Update the salary in the Employees table
    UPDATE Employees
    SET Salary = @NewSalary
    WHERE EmployeeID = @EmployeeID;

    -- Check if the update was successful
    IF @@ROWCOUNT = 0
    BEGIN
        PRINT 'No employee found with the given ID.';
    END
    ELSE
    BEGIN
        PRINT 'Salary updated successfully.';
    END
END;

2. Executing the Stored Procedure

EXEC UpdateEmployeeSalary @EmployeeID = 101, @NewSalary = 75000.00;