Common Mistakes When Working with Transactions

In this lecture, we’re gonna focus on the typical mistakes that pop up when working with transactions, and how to dodge them. Trust me, even the flashiest SQL pro sometimes forgets to slap in a COMMIT! We’ll give you some tips to make transaction mistakes a rare thing.

Unfortunately (or maybe fortunately), databases aren’t some magic castle where everything always works perfectly. Mistakes with transactions happen all the time, especially for folks just starting out. Let’s break them down in detail.

Forgotten COMMIT or ROLLBACK Command

Forgetting to finish a transaction is a real “classic move.” Imagine a restaurant where you ordered food, but the waiter forgot to bring the check. In PostgreSQL world, this means the database is “stuck” in a transaction state, hogging resources and blocking other stuff.

Example of a mistake:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- Oops! We forgot to add COMMIT or ROLLBACK.

When a transaction “hangs,” the resource lock can spread to the whole table. If the database admin knows things are bad, they might have to kill it the “hard” way. But it’s way better not to let it get that far.

How to avoid it?

• Always finish your transaction explicitly: COMMIT or ROLLBACK.
• Use client tools that automatically remind you about hanging transactions.
• If a transaction isn’t finished and you restart your app, the database will auto-ROLLBACK, but that’s not always great for your system’s state.

Using the Wrong Isolation Level

Picking an isolation level might seem like a boring formality, but it’s actually key to stopping weird data anomalies. For example, if you use READ UNCOMMITTED for an important financial operation, you might read “dirty” data that could get rolled back later.

Example:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
-- Reading data that’s being changed by another transaction
SELECT balance FROM accounts WHERE account_id = 1;
-- Another transaction does ROLLBACK, and your data turns out invalid.

How to avoid it?

• Figure out how important your data is for your app.
• Use READ COMMITTED for most cases to avoid “dirty” reads.
• Go for stricter isolation levels like REPEATABLE READ or SERIALIZABLE when you really need to avoid changes or phantom data.

Transaction Conflicts and Locks

Sometimes two or more transactions try to change the same data. In that case, PostgreSQL will lock one of them until the other finishes. This can lead to a deadlock situation.

Example of a mistake:

-- First transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- Second transaction
BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 1;
-- Waiting for the first transaction...

If both transactions are holding resources the other needs, you get a deadlock. PostgreSQL will spot the deadlock, kill one of the transactions with an error, and show this message:

ERROR: deadlock detected

How to avoid it?

• Stick to a fixed order of operations in your transactions.
• Keep your transactions as short as possible to lower the chance of locks.
• Only use SERIALIZABLE isolation level when you really need it.

Mistakes with SAVEPOINT

SAVEPOINT is an awesome tool for partial rollbacks, but using it wrong can get confusing. For example, if you forget to release a savepoint (RELEASE SAVEPOINT), you might get extra locks or errors.

Example of a mistake:

BEGIN;
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
ROLLBACK TO SAVEPOINT my_savepoint;
-- Forgot to release the SAVEPOINT!

How to avoid it?

• Make sure you remove a SAVEPOINT if you don’t need it anymore.
• Try not to create too many savepoints so your queries don’t get messy.

Transaction Incompatibility with External Systems

Imagine a PostgreSQL transaction trying to interact with external systems: sending notifications, updating an API, etc. If something goes wrong with the external system, rolling back changes is tough.

Example:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- Can’t send notification: email-server isn’t responding.
COMMIT; -- Changes are saved, but the notification wasn’t sent.

How to avoid it?

• Whenever possible, isolate operations that interact with external systems.
• Use intermediate tables or task queues to coordinate actions with external systems.

Errors from Huge Transactions

Big transactions with tons of operations are more likely to run into trouble: locks, timeouts, and deadlocks.

Example:

BEGIN;
-- Several thousand update operations
UPDATE orders SET status = 'completed' WHERE delivery_date < CURRENT_DATE;
COMMIT; -- Might take a long time.

How to avoid it?

• Break up big transactions into smaller ones.
• Use batches to update data.
• Minimize the amount of data you change in a single transaction.

Forgotten Error Checking

Not every SQL query in a transaction will succeed. For example, if one operation throws an error, the whole transaction fails.

Example:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = -1; -- Error: account_id doesn’t exist.
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT; -- Won’t run because of the error.

How to avoid it?

• Always check the result of every operation.
• Use error handling in your queries or client code.

Misunderstanding How ROLLBACK Works

A lot of devs think ROLLBACK undoes changes and puts everything back to how it was. But ROLLBACK only works inside the current transaction.

Example of a misconception:

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
ROLLBACK; -- Error! This doesn’t work because the operation wasn’t in a transaction.

How to avoid it?

• Remember: BEGIN is your buddy, and without it, ROLLBACK is powerless.
• Always wrap critical operations in transactions.