DeployU
Interviews / Databases / What are transactions and why are they useful in PostgreSQL?

Questions

What is the difference between `INNER JOIN`, `LEFT JOIN`, and `RIGHT JOIN`?
Q C
What are window functions and how do you use them in PostgreSQL?
Q C
What are common table expressions (CTEs) and how do you use them in PostgreSQL?
Q C
What is the difference between `UNION` and `UNION ALL` in PostgreSQL?
Q C
Your database query is slow. How do you optimize it?
Q C
What are indexes and how do they work in PostgreSQL?
Q C
What are transactions and why are they useful in PostgreSQL?
Q C
How do you set up replication in PostgreSQL?
Q C
How do you do a backup and restore of a PostgreSQL database?
Q C
What is the `JSONB` data type and how do you use it in PostgreSQL?
Q C
How do you do full-text search in PostgreSQL?
Q C
What is database normalization and why is it important?
Q C

What are transactions and why are they useful in PostgreSQL?

conceptual Transactions Interactive Quiz Code Examples

The Scenario

You are a backend engineer at a fintech company. You are writing a new service that needs to transfer money from one account to another.

The transfer consists of two separate operations: a UPDATE to the sender’s account to debit the money, and a UPDATE to the receiver’s account to credit the money.

You need to make sure that both operations are executed successfully, or that neither of them are.

The Challenge

Explain what transactions are in PostgreSQL and how you would use them to solve this problem. What are the key properties of a transaction (ACID)?

Wrong Approach

A junior engineer might try to solve this problem by just executing the two `UPDATE` statements one after the other. This would be a very risky solution, because if the second `UPDATE` statement fails, the money will be debited from the sender's account but not credited to the receiver's account.

Right Approach

A senior engineer would know that a transaction is the perfect tool for this job. They would be able to explain what a transaction is and how to use it to make sure that both `UPDATE` statements are executed successfully, or that neither of them are.

Step 1: Understand What Transactions Are

A transaction is a sequence of one or more operations that are executed as a single logical unit.

Step 2: The ACID Properties

A transaction must have the following four properties:

PropertyDescription
AtomicityAll the operations in a transaction are executed successfully, or none of them are.
ConsistencyA transaction brings the database from one valid state to another.
IsolationThe intermediate state of a transaction is not visible to other transactions.
DurabilityOnce a transaction has been committed, it will remain committed even in the event of a power failure.

Step 3: Use a Transaction

Here’s how we can use a transaction to transfer money from one account to another:

BEGIN;

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

COMMIT;

In this example, the BEGIN command starts a new transaction. The two UPDATE statements are then executed. If both UPDATE statements are successful, the COMMIT command will commit the transaction and make the changes permanent. If either UPDATE statement fails, the transaction will be rolled back and the changes will be undone.

Practice Question

You are in the middle of a transaction and you want to undo all the changes that you have made so far. Which of the following would you use?