Simulate Failure and Rollback in a Transaction

Transaction with Conditional Check (Simulated Error Scenario)

Write a PostgreSQL query to perform an update within a transaction and rollback if a certain condition is not met (simulated with a manual ROLLBACK).

Solution:

-- Begin the transaction.
BEGIN;

-- Update the inventory count for a specific product.
UPDATE Inventory
SET quantity = quantity - 10
WHERE product_id = 301;

-- Simulate a condition failure and rollback the transaction.
ROLLBACK;

Explanation:

• Purpose of the Query:
• To illustrate how a transaction can be cancelled if a condition (e.g., insufficient inventory) fails.
• Demonstrates the use of ROLLBACK to undo changes when conditions are not met.
• Key Components:
• BEGIN starts the transaction block.
• The UPDATE statement performs the change.
• ROLLBACK cancels the operation, simulating an error scenario.
• Real-World Application:
• Useful in inventory management where transactional integrity is needed when constraints are violated.

Notes:

• In actual implementations, error handling mechanisms would trigger the rollback automatically.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to update an account's balance in the Accounts table and rollback if the new balance falls below zero.
• Write a PostgreSQL query to update inventory levels in the Inventory table and rollback if the total quantity drops below a defined threshold.
• Write a PostgreSQL query to update employee bonuses in the Employees table and rollback if the total bonus exceeds the company limit.
• Write a PostgreSQL query to insert a new order into the Orders table only if the product stock is sufficient, and rollback if stock is insufficient.

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

Previous PostgreSQL Exercise: Transaction for Deleting a Record Safely.