Transaction

In SQL, a transaction is a logical unit of work that consists of one or more SQL statements. A transaction allows you to group multiple SQL operations into a single, indivisible unit, ensuring that either all the operations are executed successfully (committed) or none of them are executed (rolled back). The primary goal of transactions is to maintain the consistency and integrity of a database.

Transactions are governed by the principles of ACID, which stands for Atomicity, Consistency, Isolation, and Durability.

1. Atomicity (A)

   - Atomicity ensures that a transaction is treated as a single, indivisible unit of work. Either all the changes made by the transaction are committed to the database, or none of them are.

2. Consistency (C)

   - Consistency ensures that a transaction brings the database from one valid state to another. The database must satisfy certain integrity constraints before and after the transaction.

3. Isolation (I)

   - Isolation ensures that the execution of a transaction is isolated from other concurrent transactions. The result of a transaction should be the same whether it is executed in isolation or concurrently with other transactions.

4. Durability (D)

   - Durability ensures that once a transaction is committed, its changes are permanent and will survive any subsequent failures, such as system crashes or power outages.

Key Concepts and Keywords

1. BEGIN TRANSACTION

   - Marks the beginning of a transaction. SQL statements executed after this point are considered part of the transaction.

   BEGIN TRANSACTION;

2. COMMIT

   - Marks the successful end of a transaction. All changes made by the transaction are permanently saved to the database.

   COMMIT;

3. ROLLBACK

   - Rolls back the transaction, undoing any changes made by the transaction. It is used to cancel the transaction if an error occurs or if the transaction needs to be aborted for any reason.

   ROLLBACK;

4. SAVEPOINT

   - Defines a point within a transaction to which you can later roll back. It allows for partial rollbacks within a transaction.

   SAVEPOINT savepoint_name;

5. ROLLBACK TO SAVEPOINT

   - Rolls back the transaction to a specified savepoint.

   ROLLBACK TO SAVEPOINT savepoint_name;

Example

-- Begin a transaction

BEGIN TRANSACTION;

-- SQL statements within the transaction

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

INSERT INTO transactions (account_id, amount) VALUES (123, -100);

-- Check for a condition

IF some_condition

BEGIN

    -- Roll back the transaction if the condition is not met

    ROLLBACK;

END

ELSE

BEGIN

    -- Commit the transaction if the condition is met

    COMMIT;

END;

In this example, a transaction is initiated using `BEGIN TRANSACTION`, and a series of SQL statements are executed within the transaction. Depending on a condition, the transaction is either committed (`COMMIT`) or rolled back (`ROLLBACK`). This ensures that either all the changes are applied or none of them, maintaining the principles of ACID.