Introduction to Transactions: Ensuring Data Consistency in PostgreSQL

Previously in this course, we learned about deleting records safely. While that taught us how to remove data, we often need to perform multiple related operations—like updating inventory and creating an order—as a single, inseparable unit. This is where Transactions come in.

A transaction is a sequence of SQL operations treated as a single "all or nothing" unit of work. Without them, if your application crashes halfway through updating your database, you might end up with an order created but the inventory never decremented. Transactions provide the safety net to prevent such corrupt states.

Understanding ACID Properties

To guarantee reliability, relational databases adhere to ACID properties, which you can explore in more depth in our guide on ACID properties in relational databases.

• Atomicity: The entire transaction succeeds or fails as a single unit. There is no such thing as a "half-finished" transaction.
• Consistency: A transaction takes the database from one valid state to another, maintaining all defined rules and constraints.
• Isolation: Transactions occur independently. One transaction shouldn't see the incomplete, intermediate results of another.
• Durability: Once a transaction is committed, the changes are permanent, even in the event of a power loss or system crash.

Controlling Transactions with SQL

In PostgreSQL, you control transactions using three primary commands:

1. BEGIN: Marks the start of a transaction block.
2. COMMIT: Saves all changes made during the transaction permanently.
3. ROLLBACK: Aborts the transaction and discards all changes made since the BEGIN statement.

A Worked Example: The Store Purchase

Imagine a customer is buying a product. We need to do two things: deduct the item from the products table and create a record in our orders table.

SQL
-- Start the transaction block
BEGIN;

-- 1. Deduct inventory
UPDATE products 
SET stock_quantity = stock_quantity - 1 
WHERE id = 101;

-- 2. Create the order
INSERT INTO orders (customer_id, product_id, order_date) 
VALUES (5, 101, CURRENT_DATE);

-- If everything went well, save the changes
COMMIT;

If you realize halfway through that the customer didn't have enough balance or the product was suddenly out of stock, you simply use ROLLBACK; instead of COMMIT;. The database will revert the UPDATE as if it never happened.

Hands-on Exercise

Open your psql terminal or pgAdmin Query Tool connected to your store database. Try the following "safe" transaction:

1. Start a transaction with BEGIN;.
2. Update the price of a product (e.g., UPDATE products SET price = 19.99 WHERE id = 1;).
3. Check the result with SELECT * FROM products WHERE id = 1;. You will see the new price.
4. Realize you made a mistake and execute ROLLBACK;.
5. Run SELECT * FROM products WHERE id = 1; again. The price has reverted to its original value!

Common Pitfalls

• Forgetting to COMMIT: If you run BEGIN but close your connection or the application crashes without sending COMMIT, your changes are never saved.
• Leaving Transactions Open: An uncommitted transaction can "lock" rows in your database, preventing other users from updating that data. Always close your transactions promptly.
• Implicit Transactions: In some database tools, every single query is automatically wrapped in a transaction. In standard PostgreSQL, you must explicitly use BEGIN to group multiple statements.

FAQ

Q: Do I need BEGIN for a single INSERT? A: No. By default, PostgreSQL wraps every single statement in an implicit transaction. You only need explicit BEGIN blocks when you want to group multiple statements together.

Q: Can I nest transactions? A: PostgreSQL supports "subtransactions" via SAVEPOINT, but we will cover that in our next lesson on advanced transaction management.

Q: What happens if the server crashes during a transaction? A: Because of the Durability property, the database keeps a write-ahead log (WAL). Upon restart, it will automatically roll back any transactions that were in progress but not committed.

Recap

Transactions allow us to bundle operations into atomic units, ensuring our store database remains consistent even when errors occur. By using BEGIN, COMMIT, and ROLLBACK, we ensure that our data never enters a partial or corrupted state. This is foundational for building reliable systems that prevent data anomalies and maintain integrity.

Up next: We will dive into Advanced Transaction Management to handle complex scenarios like handling errors within blocks and using savepoints.