What is transaction in SQL?

In SQL, a transaction is a single logical unit of work that comprises one or more operations (SQL statements) which are treated as a single, indivisible sequence. The primary goal of transactions is to ensure data integrity and consistency within a database, even in the presence of failures or concurrent access.

What is a Transaction?

A transaction bundles a set of operations into a single logical unit. If all operations within the unit are successful, the transaction is committed, and all changes are permanently saved to the database. If any operation fails, or if a decision is made to undo the changes, the entire transaction is rolled back, and the database returns to its state before the transaction began. This all-or-nothing principle is crucial for maintaining data consistency.

The ACID Properties

Transactions are typically characterized by the ACID properties, which are a set of fundamental principles guaranteeing reliability and consistency in database operations:

• Atomicity: Ensures that all operations within a transaction are completed successfully, or none are. It's an 'all or nothing' proposition. If the transaction completes successfully, it commits; otherwise, it aborts, and all changes are undone.
• Consistency: Guarantees that a transaction brings the database from one valid state to another. It ensures that all data integrity rules (like primary key constraints, foreign key constraints, triggers, etc.) are maintained after the transaction completes.
• Isolation: Ensures that concurrent transactions execute independently without interfering with each other. The intermediate state of a transaction is not visible to other transactions until it is committed. This prevents anomalies like dirty reads, non-repeatable reads, and phantom reads.
• Durability: Guarantees that once a transaction has been committed, its changes are permanent and will survive any subsequent system failures (e.g., power outages, crashes). Committed changes are written to persistent storage.

Transaction Control Language (TCL) Commands

SQL provides specific commands to manage transactions:

• BEGIN TRANSACTION / START TRANSACTION: Marks the beginning of a transaction.
• COMMIT: Saves all changes made during the current transaction permanently to the database.
• ROLLBACK: Undoes all changes made during the current transaction, reverting the database to its state before the transaction started.
• SAVEPOINT: Allows you to set a point within a transaction to which you can later roll back. This is useful for partial rollbacks within a larger transaction.

Example of a SQL Transaction

Consider a scenario of transferring money between two bank accounts. This operation requires two updates that must succeed or fail together.

START TRANSACTION;

-- Deduct amount from account A
UPDATE Accounts
SET balance = balance - 100
WHERE account_id = 'A123';

-- Add amount to account B
UPDATE Accounts
SET balance = balance + 100
WHERE account_id = 'B456';

-- Check if both updates were successful and balances are non-negative
-- (In a real scenario, more robust error checking would be present)
-- For simplicity, let's assume success if no error was raised

COMMIT; -- If both operations succeed, make changes permanent

-- In case of an error, we would ROLLBACK:
-- If an error occurred (e.g., insufficient funds in A123):
-- ROLLBACK;

In this example, if the first UPDATE statement succeeds but the second fails (e.g., due to an error, or a constraint violation), the entire transaction can be rolled back, ensuring that the money is not deducted from one account without being added to the other. This maintains the consistency and integrity of the financial data.