Execute Multiple Operations in One Transaction

Begin Transaction with Custom Isolation Level for Multiple Operations

Write a PostgreSQL query to start a transaction with a custom isolation level and perform several operations before committing.

Solution:

-- Begin a transaction with a custom isolation level.
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Insert a new record.
INSERT INTO Orders (order_id, customer_id, order_date, amount)
VALUES (101, 5, '2025-03-02', 150.00);

-- Update an existing record.
UPDATE Customers
SET last_order_date = '2025-03-02'
WHERE customer_id = 5;

-- Commit the transaction.
COMMIT;

Explanation:

• Purpose of the Query:
• The goal is to perform multiple DML operations under the READ COMMITTED isolation level.
• This demonstrates transactional grouping with consistent data visibility.
• Key Components:
• BEGIN; and COMMIT; : Define the transaction boundaries.
• SET TRANSACTION ISOLATION LEVEL READ COMMITTED; : Sets the isolation level for the transaction.
• Real-World Application:
• Ensures that either all related changes are committed or none, maintaining data integrity in order processing systems.

Notes:

• Combining multiple operations in one transaction prevents partial updates.
• Isolation levels determine the data consistency seen during the transaction.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to begin a transaction with READ COMMITTED isolation, insert a new product in the Products table, and update its stock, then commit.
• Write a PostgreSQL query to start a transaction with REPEATABLE READ isolation, insert a new sale in the Sales table, update customer data in the Customers table, then commit.
• Write a PostgreSQL query to begin a transaction with SERIALIZABLE isolation, update account balances in the Accounts table, and log the transaction in a Log table before committing.
• Write a PostgreSQL query to start a transaction with READ COMMITTED isolation, perform an INSERT into the Orders table and an UPDATE on the Inventory table, then commit.

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

Previous PostgreSQL Exercise: Set Session Default Isolation Level.
Next PostgreSQL Exercise: Read Latest Committed Data in READ COMMITTED Mode.