Handling Transaction Rollbacks in SQL

Rollback a Failed Transaction

Write a SQL query to rollback a transaction if an error occurs during execution.

Solution:

BEGIN TRANSACTION; -- Begin the transaction.

-- Attempt to insert data into the Employees table.
INSERT INTO Employees (EmployeeID, Name, Age, Salary) 
VALUES (2, 'Bob Smith', 25, 45000);

-- Simulate an error (e.g., duplicate key violation).
IF @@ERROR <> 0 
BEGIN
    ROLLBACK TRANSACTION; -- Rollback if an error occurs.
    PRINT 'Transaction rolled back due to an error.';
END
ELSE
BEGIN
    COMMIT TRANSACTION; -- Commit if no errors occur.
    PRINT 'Transaction committed successfully.';
END

Explanation:

1. Purpose of the Query :

1. The goal is to rollback a transaction if an error occurs during execution, ensuring data consistency.

2. Key Components :

1. BEGIN TRANSACTION : Starts the transaction.
2. ROLLBACK TRANSACTION : Undoes changes if an error occurs.
3. COMMIT TRANSACTION : Saves changes if no errors occur.

3. Why use Rollback?

1. Rollbacks ensure that partial updates do not leave the database in an inconsistent state.

4. Real-World Application :

1. For example, in an e-commerce system, you might rollback a transaction if a payment fails to ensure inventory levels remain accurate.

Additional Notes:

• Rollbacks are essential for handling errors gracefully in database operations.
• Scenarios where rollbacks are appropriate include:
• Handling failed inserts, updates, or deletes.
• Ensuring data integrity during complex operations.
• Important Considerations :
• Always include error-handling logic to detect and respond to failures.

For more Practice: Solve these Related Problems:

• Write a SQL query to rollback a transaction if an attempt to update a non-existent record fails.
• Write a SQL query to handle a failed delete operation within a transaction and ensure that all changes are rolled back.
• Write a SQL query to simulate a transaction failure due to a foreign key constraint violation and rollback the transaction.
• Write a SQL query to rollback a transaction if a duplicate entry error occurs during an insert operation.

Go to:

• SQL Transactions and Concurrency Control Exercises Home ↩
• SQL Exercises Home ↩

PREV : Demonstrate Isolation Level SERIALIZABLE.
NEXT : Demonstrate Isolation Level REPEATABLE READ.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.