Obafemi Emmanuel

What is a Transaction?

A transaction in SQL is a sequence of operations performed as a single logical unit of work. A transaction must be executed completely or not executed at all to maintain database integrity. Transactions are widely used in databases to ensure data consistency and reliability.

A transaction follows the ACID properties (Atomicity, Consistency, Isolation, Durability), which will be discussed later in this article.

Example of a Transaction

Consider a banking system where you want to transfer money from one account to another:

START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
COMMIT;

If any of the queries fail (e.g., the sender's account does not have enough balance), the entire transaction is rolled back to maintain consistency.

Using COMMIT and ROLLBACK

COMMIT Statement

The COMMIT statement is used to save all changes made by the transaction permanently to the database. Once a COMMIT is executed, the changes cannot be undone.

Syntax:

COMMIT;

ROLLBACK Statement

The ROLLBACK statement is used to undo all changes made by the transaction. If an error occurs or a condition is not met, a rollback ensures that the database remains in its previous state.

Syntax:

ROLLBACK;

Example of COMMIT and ROLLBACK

START TRANSACTION;
UPDATE employees SET salary = salary + 500 WHERE employee_id = 101;
IF some_condition THEN
    COMMIT;
ELSE
    ROLLBACK;
END IF;

If the condition is met, the changes are saved using COMMIT; otherwise, they are discarded using ROLLBACK.

Understanding ACID Properties

Transactions in SQL must follow the ACID properties to ensure data integrity:

1. Atomicity - Ensures that a transaction is either fully completed or fully failed. If one part of the transaction fails, the entire transaction is rolled back.
2. Consistency - Ensures that a transaction transforms the database from one valid state to another, maintaining all integrity constraints.
3. Isolation - Ensures that concurrent transactions do not interfere with each other. SQL provides different isolation levels to control this.
4. Durability - Ensures that once a transaction is committed, the changes remain in the database permanently, even in the event of a system crash.

Using SAVEPOINT

A SAVEPOINT is used to create intermediate points within a transaction. It allows partial rollbacks instead of rolling back the entire transaction.

Syntax:

SAVEPOINT savepoint_name;

Example of SAVEPOINT and ROLLBACK

START TRANSACTION;
UPDATE orders SET status = 'Processing' WHERE order_id = 10;
SAVEPOINT sp1;
UPDATE orders SET status = 'Shipped' WHERE order_id = 10;
ROLLBACK TO sp1;
COMMIT;

In this example, if an issue occurs after setting the status to Shipped, we can roll back to the Processing state using the SAVEPOINT.

Conclusion

Transactions and concurrency control are crucial in database management. By using COMMIT, ROLLBACK, ACID properties, and SAVEPOINT, you can ensure that your database operations remain consistent, reliable, and efficient. Proper transaction handling is essential for maintaining data integrity and avoiding unintended data loss.