3.4 Transactions: COMMIT and ROLLBACK

In SQL, transactions allow you to execute a series of SQL statements as a single unit of work. Transactions ensure data integrity and consistency by allowing changes to be committed (saved) or rolled back (reversed) based on the success or failure of the operations.

3.4.1 Overview of Transactions

A transaction is a sequence of operations performed as a single logical unit. It ensures that either all operations succeed or none of them do. This is known as the ACID (Atomicity, Consistency, Isolation, Durability) properties of database transactions.

• Atomicity: Ensures that all the operations in a transaction are completed; if one fails, the whole transaction is aborted.
• Consistency: Ensures that the database transitions from one valid state to another after a transaction.
• Isolation: Ensures that transactions are executed independently, even if they are run concurrently.
• Durability: Once a transaction is committed, its changes are permanent.

3.4.2 COMMIT Statement

The COMMIT statement is used to save all the changes made during the current transaction to the database. Once a transaction is committed, the changes become permanent and are visible to other users.

• Purpose: To make the changes permanent and finalize the transaction.
• Syntax:

  COMMIT;
  

• Example:
  After updating an employee's salary in a transaction, you commit the changes to make them permanent:

  BEGIN TRANSACTION;
  
  UPDATE employees
  SET salary = 60000
  WHERE employee_id = 101;
  
  COMMIT;
  

3.4.3 ROLLBACK Statement

The ROLLBACK statement is used to undo the changes made during the current transaction. If any error occurs during a transaction or if you want to cancel the changes, you can roll back the transaction, and all operations will be reversed to the state before the transaction started.

• Purpose: To undo changes made during a transaction if something goes wrong.
• Syntax:

  ROLLBACK;
  

• Example:
  If an error occurs while updating the salary, you can roll back the transaction:

  BEGIN TRANSACTION;
  
  UPDATE employees
  SET salary = 60000
  WHERE employee_id = 101;
  
  -- Some error happens here
  
  ROLLBACK;  -- Reverts the update and other changes
  

3.4.4 How Transactions Work

1. Start a Transaction:
   A transaction begins automatically with the first SQL statement, but you can explicitly start it with BEGIN TRANSACTION in some systems.
   • Example:

     BEGIN TRANSACTION;
     

2. Perform Operations:
   Multiple SQL operations (such as INSERT, UPDATE, DELETE) can be executed during a transaction. The changes are not visible to other transactions until the transaction is committed.
3. Commit or Rollback:
   At the end of the transaction, either COMMIT is called to save the changes or ROLLBACK is used to undo all changes made during the transaction.

3.4.5 Example of a Transaction Workflow

Consider a scenario where an employee's salary needs to be updated, but the update should only happen if certain conditions are met:

• Example:
  We will update the employee's salary and then transfer funds to the bank account only if the salary update is successful.

  BEGIN TRANSACTION;
  
  -- Update the salary of the employee
  UPDATE employees
  SET salary = 70000
  WHERE employee_id = 101;
  
  -- If the update is successful, transfer funds to the employee's bank account
  IF (SELECT salary FROM employees WHERE employee_id = 101) = 70000
  BEGIN
    UPDATE bank_accounts
    SET balance = balance + 70000
    WHERE account_id = (SELECT account_id FROM employees WHERE employee_id = 101);
    
    COMMIT;  -- Commit if both operations are successful
  END
  ELSE
  BEGIN
    ROLLBACK;  -- Rollback if the salary update fails
  END
  

In this example:

• If the salary update is successful, we proceed to transfer the funds, and the transaction is committed.
• If any error occurs (for example, if the salary update fails), the ROLLBACK is triggered, and no changes are saved to the database.

3.4.6 Best Practices for Transactions

1. Keep Transactions Short:
   Long transactions can lock database resources and impact performance. Aim to complete your transactions as quickly as possible.
2. Use BEGIN TRANSACTION and COMMIT/ROLLBACK:
   Explicitly define the beginning and end of a transaction to ensure it’s handled correctly. Always commit if everything is successful, and rollback in case of errors.
3. Ensure Data Integrity:
   Use transactions to ensure that all operations within the transaction are executed correctly or none at all. This prevents the database from being left in an inconsistent state.
4. Check for Errors:
   Always handle potential errors during a transaction. If something goes wrong, use ROLLBACK to undo changes and maintain consistency.

3.4.7 Real-World Use Cases

1. Banking System:
   A bank transaction involves both withdrawing money from an account and depositing it into another account. If any part of the transaction fails (e.g., insufficient funds), the entire transaction is rolled back.
   • Example:

     BEGIN TRANSACTION;
     
     UPDATE accounts
     SET balance = balance - 1000
     WHERE account_id = 'A123';
     
     UPDATE accounts
     SET balance = balance + 1000
     WHERE account_id = 'B456';
     
     COMMIT;  -- Only commit if both updates succeed
     

2. Order Processing System:
   In an e-commerce system, when an order is placed, several operations may be involved (e.g., updating the stock, recording the order, processing payment). A transaction ensures that all these operations happen together or not at all.
   • Example:

     BEGIN TRANSACTION;
     
     UPDATE inventory
     SET stock = stock - 1
     WHERE product_id = 101;
     
     INSERT INTO orders (customer_id, product_id, quantity) 
     VALUES (2001, 101, 1);
     
     COMMIT;  -- Commit only if both operations succeed
     

3.4.8 Risks of Improper Use

1. Data Inconsistency:
   If transactions are not properly managed (e.g., committing partial changes), the database may end up in an inconsistent state. Always ensure that operations inside a transaction are atomic.
2. Deadlocks:
   If two transactions are waiting on each other to release locks on resources, a deadlock can occur. To avoid this, ensure that transactions access resources in a consistent order.
3. Transaction Overhead:
   Keeping transactions open for too long can affect database performance and lead to contention on resources. Use transactions judiciously and ensure they are completed quickly.

Conclusion

SQL transactions using COMMIT and ROLLBACK are crucial for maintaining the integrity and consistency of data in a database. By using these statements, you can ensure that multiple operations are treated as a single logical unit, and either all changes are applied, or none at all in the case of an error. Proper use of transactions helps maintain a reliable and consistent database environment.