Example of Using Transactions in Real-World Scenarios

Sometimes transactions are like characters from superhero movies. They save our databases from disaster when things crash, bugs pop up, or stuff just goes wrong. If you're working on a task that needs several operations that can't be split up, transactions make sure they all run as one. Let's break down how this works with a payment processing example.

Payment Processing

Picture the classic situation: you have two bank accounts, and you want to transfer money from one to the other. It's not just a "one-click" operation. You need to make sure you correctly take money from one account and add it to the other. Any mistake can be a disaster: either both accounts stay the same, or the balance gets messed up (like money disappearing or showing up "out of thin air").

Here's our code. Read it carefully, like it's a message from a galaxy far, far away:

-- Start the transaction
BEGIN;

-- Step 1. Subtract money from the sender's account
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

-- Step 2. Add money to the recipient's account
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;

-- Everything went well? Then save the changes!
COMMIT;

What's important here?

• If something goes wrong at Step 1 or Step 2 (like a query error or not enough funds), the transaction can be rolled back with ROLLBACK, and the data will stay as it was.
• COMMIT makes sure the changes only apply if ALL steps succeed.

Adding a Balance Check

But what if the sender doesn't have enough money to transfer? Let's add a balance check so we don't accidentally "put them in the red".

-- Start the transaction
BEGIN;

-- Get the sender's current balance
DO $$
DECLARE
    current_balance NUMERIC;
BEGIN
    SELECT balance INTO current_balance FROM accounts WHERE account_id = 1;

    -- Check if there's enough money
    IF current_balance >= 100 THEN
        -- If there's enough, do the transfer
        UPDATE accounts
        SET balance = balance - 100
        WHERE account_id = 1;

        UPDATE accounts
        SET balance = balance + 100
        WHERE account_id = 2;

        -- Save the changes
        COMMIT;
    ELSE
        -- If not enough money, roll back
        ROLLBACK;
        RAISE NOTICE 'Not enough funds for the transfer!';
    END IF;
END $$;

What's cooler here?

• We're using a PL/pgSQL block with an IF condition. If the balance is less than needed, the transaction gets rejected and nothing changes.
• ROLLBACK cancels changes if any were started (even though here there's nothing to roll back yet, but it's good practice).

Bulk Updating Data in a Transaction

Creating a transaction isn't just for money transfers. Let's say you have an online store database, where dozens of orders change their statuses every day, like from "in delivery" to "completed". How do you update a bunch of records at once so you can roll back if something fails? Of course, use a transaction.

Here's another scenario: updating order statuses.

Here's an example:

-- Start the transaction
BEGIN;

-- Step 1. Update orders with a past delivery date
UPDATE orders
SET status = 'completed'
WHERE delivery_date < CURRENT_DATE;

-- Step 2. Notify about successful update
RAISE NOTICE 'All order statuses updated successfully.';

-- Apply the changes
COMMIT;

What if something goes wrong?

There's always a chance of a mistake. For example, you accidentally forgot the WHERE condition, and now all orders changed their status to completed. To avoid stuff like that, it's important to finish the transaction or explicitly roll it back.

Here's a rollback scenario:

-- Start the transaction
BEGIN;

-- Step 1. Try to update orders without a condition (oops, mistake!)
UPDATE orders
SET status = 'completed';

-- Roll back the transaction because of the error
ROLLBACK;

-- Now the orders stayed the same

Adding Some "Flexibility" with SAVEPOINT

You don't always need to roll back the whole transaction. If your scenario has several actions, you might want to roll back just one part. That's where SAVEPOINT comes in handy.

Now our scenario is: handling several steps with the option to roll back just one of them.

Imagine you're processing an order with several steps: taking items from stock, updating the order status, sending a notification to the customer. If the notification doesn't send, you want to roll back just that step, but keep the database changes.

-- Start the transaction
BEGIN;

-- Step 1. Subtract items from stock
UPDATE products
SET stock = stock - 1
WHERE product_id = 101;

-- Save a rollback point
SAVEPOINT step1;

-- Step 2. Update the order status
UPDATE orders
SET status = 'shipped'
WHERE order_id = 202;

-- Try to send a notification to the customer
SAVEPOINT step2;
-- Oops, error while sending the notification!
ROLLBACK TO SAVEPOINT step2;

-- Decide it's safe to finish the transaction
COMMIT;

Conclusion

Transactions aren't just a technical tool, they're a guarantee your data stays solid. They protect you from the "domino effect" where one mistake can wreck the whole system. Every time you run a few related operations, ask yourself: "What if one of them fails?" If the answer is "disaster", it's time to use a transaction. Remember: it's better to spend a few minutes writing a transaction than a few hours recovering data after a crash. Your users (and your nerves) will thank you!

2

Task

SQL SELF, level 39, lesson 3

Locked

Bulk Update of Order Statuses

Bulk Update of Order Statuses

Open