SQL Transaction Interview Questions

SQL Transaction Interview Questions

Understanding Transactions

Transactions are the fundamental unit of work in SQL databases, ensuring data integrity and consistency. A transaction consists of a set of operations (commands) that are executed together. If any part of the transaction fails, the entire transaction is rolled back to maintain data consistency.

Types of Transactions

  1. Explicit Transactions

    • Defined using BEGIN TRANSACTION and COMMIT/ROLLBACK.
    • Required to explicitly manage the transaction lifecycle.
    • Example:
      BEGIN TRANSACTION;  
UPDATE employees SET salary = 5000 WHERE department = 'Sales';  
COMMIT;

  2. Implicit Transactions

    • Automatically started when a new query is executed.
    • Ends when the next query is executed (unless explicitly rolled back).
    • Example:
      UPDATE employees SET salary = 5000;

Isolation Levels

Transactions can interact with other transactions through isolation levels, which determine the degree of isolation from external transactions. The standard isolation levels are:

  1. READ UNCOMMITTED

    • The lowest isolation level.
    • Allows a transaction to read uncommitted data (e.g., other transactions' changes).

  2. READ COMMITTED

    • Prevents reading uncommitted data.
    • Ensures that a transaction sees only committed data from other transactions.

  3. REPEATABLE READ

    • Ensures that a transaction sees the same data throughout its execution.
    • Prevents dirty reads and non-repeatable reads.

  4. SERIALIZABLE

    • The highest isolation level.
    • Treats the transaction as a separate, isolated database.

ACID Properties

The ACID properties ensure that transactions are reliable and maintain data integrity:

  1. Atomicity

    • All operations in a transaction are completed successfully, or none are.
    • Example: A transaction may include updating two tables; if one fails, the entire transaction is rolled back.

  2. Consistency

    • Transactions must bring the database from one valid state to another.
    • Ensures that data remains consistent across all operations.

  3. Isolation

    • Transactions are isolated from each other to prevent conflicts.
    • Ensures that transactions do not interfere with each other.

  4. Durability

    • Once a transaction is committed, the changes are permanent.
    • Even if a system fails, the transaction's results remain intact.

Common Interview Questions

  1. What is a transaction in SQL?

    • A transaction is a set of one or more SQL statements that are executed together.

  2. What are the types of transactions in SQL?

    • Explicit (manual) and implicit (automatic).

  3. What is the purpose of isolation levels in SQL transactions?

    • To control how transactions interact with other transactions, preventing conflicts.

  4. How do you handle transaction failures in SQL?

    • Use ROLLBACK to revert changes, or COMMIT to save changes.

  5. What is the difference between BEGIN TRANSACTION and START TRANSACTION?

    • Both are equivalent, but START TRANSACTION is often used in stored procedures.

  6. What are the ACID properties of SQL transactions?

    • Atomicity, Consistency, Isolation, Durability.

  7. How do you ensure data consistency in a transaction?

    • Use COMMIT to save changes and ROLLBACK to revert.

  8. What is a deadlock in SQL transactions?

    • A situation where two or more transactions are waiting for each other to release locks, leading to a blockage.

  9. How do you handle conflicts in a transaction?

    • Use READ COMMITTED isolation level to prevent dirty reads, or SERIALIZABLE for strict isolation.

  10. What is the role of the ISOLATION LEVEL in SQL transactions?

    • Determines how transactions interact with other transactions, ensuring data integrity.

Practical Examples

  1. Handling a Transaction Failure

    BEGIN TRANSACTION;  
UPDATE orders SET status = 'PAID' WHERE order_id = 101;  
IF @@ERROR != 0 BEGIN  
    ROLLBACK TRANSACTION;  
    RETURN;  
END  
COMMIT TRANSACTION;

  2. Using Isolation Levels

    BEGIN TRANSACTION;  
SELECT * FROM employees WHERE department = 'HR' FOR UPDATE;  
IF @@ERROR != 0 BEGIN  
    ROLLBACK TRANSACTION;  
    RETURN;  
END  
COMMIT TRANSACTION;

  3. ACID Property Example

    • A transaction that updates two tables:
      BEGIN TRANSACTION;  
UPDATE table1 SET value = 100 WHERE id = 1;  
UPDATE table2 SET value = 200 WHERE id = 1;  
COMMIT;

Best Practices

  • Use BEGIN TRANSACTION and COMMIT/ROLLBACK explicitly.
  • Choose appropriate isolation levels based on application requirements.
  • Avoid using implicit transactions unless necessary.
  • Monitor transaction logs for recovery and rollback scenarios.

This structured approach ensures a thorough understanding of SQL transaction concepts, aligning with interview expectations and practical database management.