Get Started with SQL Transactions in PostgreSQL

A thali order at a restaurant

Imagine you walk into a small restaurant in your town and order a full thali. The thali has rice, dal, two sabzis, roti, salad, and a sweet. You expect all of it to arrive together on one plate.

Now think what would happen if the kitchen served you only the rice and the dal, then said, "Sorry, we ran out of roti, so the rest is cancelled." You would be left with half a meal that makes no sense. A good restaurant does not do this. Either the whole thali comes out, or none of it does. You never pay for half a thali.

A database transaction works the same way. It is a group of steps that must all finish together. If even one step fails, the kitchen throws away the whole plate and starts fresh, so you are never left with a half-done mess.

In this article we will learn how transactions work in PostgreSQL. We will use the simple commands BEGIN, COMMIT, and ROLLBACK, and we will also see savepoints and isolation levels. We will keep the language simple and add C# code so you can use this in a real .NET app.

What a transaction is

A transaction is one complete piece of work made of one or more SQL statements. The most famous example is moving money between two bank accounts.

To move 100 rupees from Aarav to Rohan, the database must do two things:

1. Take 100 rupees out of Aarav's account.
2. Add 100 rupees to Rohan's account.

Both steps must happen together. If only the first step runs and then the power goes out, 100 rupees just vanished into thin air. Nobody gets it. That is a disaster. A transaction makes sure both steps either happen fully or not at all.

The three magic commands

PostgreSQL gives you three simple commands to control a transaction. They are easy to remember.

Here is the money transfer written as plain SQL. Read it slowly, line by line.

BEGIN;
 
UPDATE accounts SET balance = balance - 100 WHERE owner = 'Aarav';
UPDATE accounts SET balance = balance + 100 WHERE owner = 'Rohan';
 
COMMIT;

The BEGIN opens the transaction. The two UPDATE lines do the work. The COMMIT saves both updates at the same moment. Until you say COMMIT, nobody else can see your half-finished changes. If anything had gone wrong, you would type ROLLBACK; instead, and both updates would disappear as if they never happened.

ACID: the four promises

Databases make four promises about transactions. People remember them with the word ACID. Each letter stands for one promise.

Let us go through each one with our thali and bank stories.

Atomicity is the thali rule. The whole plate comes, or nothing comes. In the bank, both money steps happen or neither does.

Consistency means the rules of the data are never broken. If a rule says an account balance can never go below zero, then no committed transaction is allowed to break that rule. The database keeps things tidy.

Isolation means two people working at the same time do not trip over each other. If Aarav and Rohan both touch the same account at the same second, the database keeps their work separate so nothing gets mixed up.

Durability means that once you COMMIT, the change is safe forever. Even if the server loses power one second later, your saved change is still there when it comes back on.

What happens without a transaction

To really feel why transactions matter, picture the bank transfer with no transaction around it. Each UPDATE runs and saves on its own.

In this picture, the first update saved 100 rupees out of Aarav's account. Then the server crashed before the second update could add the money to Rohan. The 100 rupees is simply gone. Nobody has it. This is exactly the half-thali problem, and it is why we wrap both steps in one transaction.

With a transaction, the first update is only a draft until COMMIT. If the crash happens before COMMIT, PostgreSQL automatically rolls everything back when it restarts. Aarav keeps his money. No rupee is ever lost.

Using transactions from C#

Now let us see how to use a transaction in a real .NET app. We will use Npgsql, the popular PostgreSQL driver for .NET. The pattern is always the same: open a connection, begin a transaction, do your work, then commit. If anything throws, roll back.

using Npgsql;
 
await using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync();
 
await using var transaction = await connection.BeginTransactionAsync();
 
try
{
    await using (var cmd = new NpgsqlCommand(
        "UPDATE accounts SET balance = balance - 100 WHERE owner = 'Aarav'",
        connection, transaction))
    {
        await cmd.ExecuteNonQueryAsync();
    }
 
    await using (var cmd = new NpgsqlCommand(
        "UPDATE accounts SET balance = balance + 100 WHERE owner = 'Rohan'",
        connection, transaction))
    {
        await cmd.ExecuteNonQueryAsync();
    }
 
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

Read the shape of this code. We call BeginTransactionAsync to start. We run both updates inside a try block. If both succeed, we call CommitAsync to save them together. If anything throws an error, the catch block calls RollbackAsync, which undoes both updates, and then re-throws so the caller knows it failed.

If you use Entity Framework Core instead of raw SQL, the idea is the same but the code is shorter. EF Core wraps a single SaveChanges call in its own transaction for you. When you need several SaveChanges calls to act as one unit, you open an explicit transaction.

using var transaction = await dbContext.Database.BeginTransactionAsync();
 
try
{
    aarav.Balance -= 100;
    await dbContext.SaveChangesAsync();
 
    rohan.Balance += 100;
    await dbContext.SaveChangesAsync();
 
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

Here both SaveChangesAsync calls live inside one transaction. They commit together or roll back together. This is the safest way to group several saves into one all-or-nothing unit.

Savepoints: undo just part of the work

Sometimes a transaction has many steps, and you do not want one small failure to throw away everything. PostgreSQL gives you savepoints for this. A savepoint is a named bookmark inside a transaction. You can roll back to that bookmark without cancelling the whole transaction.

Think of it like writing an exam in pencil. If you make a mistake in question three, you erase only question three. You do not throw away the whole answer sheet.

BEGIN;
 
INSERT INTO orders (customer, total) VALUES ('Priya', 500);
 
SAVEPOINT before_discount;
 
UPDATE orders SET total = total - 1000 WHERE customer = 'Priya';
-- Oops, that made the total negative. Undo just this part.
ROLLBACK TO SAVEPOINT before_discount;
 
-- The order insert is still safe. Carry on.
UPDATE orders SET total = total - 50 WHERE customer = 'Priya';
 
COMMIT;

In this example, the order insert stays. We set a savepoint, tried a discount that was too big, then rolled back to the savepoint to undo only the bad discount. The insert from before the savepoint was never touched. We then applied a sensible discount and committed.

Isolation levels: how much others can see

When many people use the database at the same time, the I in ACID, Isolation, decides how much one transaction can see of another's unfinished work. PostgreSQL lets you pick how strict this should be.

There are three real levels in PostgreSQL. (The SQL standard lists four, but PostgreSQL treats "Read Uncommitted" the same as "Read Committed," so you really have three.)

READ COMMITTED is the default. Each statement sees only data that was already committed when the statement began. You never read someone's half-finished, uncommitted change. But if you read the same row twice, you might get two different values, because someone else committed a change in between.

REPEATABLE READ takes one frozen photo of the data when the transaction starts. Every read in that transaction sees the same photo, so a row you read twice gives the same answer both times.

SERIALIZABLE is the strictest. It makes transactions behave as if they ran one after another in a line, never overlapping. It is the safest, but it can cause more transactions to fail and need a retry.

You choose the level right after BEGIN:

BEGIN ISOLATION LEVEL REPEATABLE READ;
 
SELECT balance FROM accounts WHERE owner = 'Aarav';
-- Read it again later in the same transaction.
SELECT balance FROM accounts WHERE owner = 'Aarav';
-- Both reads give the same value, even if others committed changes.
 
COMMIT;

A common mistake: forgetting to commit

A mistake many beginners make is starting a transaction and then forgetting to commit it. They type BEGIN, run their updates, and walk away. Because there was no COMMIT, the changes are never saved, and worse, the open transaction can hold locks that block other people from working.

The rule is simple. Every BEGIN must end with either a COMMIT or a ROLLBACK. In code, the try/catch pattern we saw earlier handles this for you, because the catch always rolls back on failure and the happy path always commits. The using statement also disposes the transaction safely if you forget.

Keeping transactions short

One last tip. Keep your transactions short and quick. While a transaction is open, PostgreSQL may hold locks on the rows you touched. If your transaction stays open for a long time, other users have to wait, and your whole app feels slow.

Do the slow things, like calling another website or waiting for a user to click a button, outside the transaction. Open the transaction only when you are ready to read and write, do the database work fast, and commit. A short transaction is a happy transaction.

Here is the good habit in one small C# example. Notice the slow API call happens before the transaction starts.

// Do slow work FIRST, outside the transaction.
var exchangeRate = await currencyApi.GetRateAsync("INR", "USD");
 
// Now open a short, fast transaction.
await using var transaction = await connection.BeginTransactionAsync();
try
{
    await using var cmd = new NpgsqlCommand(
        "UPDATE wallets SET usd = usd + @amount WHERE id = @id",
        connection, transaction);
    cmd.Parameters.AddWithValue("amount", 100 * exchangeRate);
    cmd.Parameters.AddWithValue("id", walletId);
    await cmd.ExecuteNonQueryAsync();
 
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

The slow currency lookup runs before BeginTransactionAsync. The transaction itself is just one fast update and a commit. This keeps locks short and keeps your app fast for everyone.

Quick recap

• A transaction groups several SQL steps into one all-or-nothing unit, just like a thali that arrives whole or not at all.
• Use BEGIN to start, COMMIT to save everything, and ROLLBACK to throw everything away.
• Transactions give you ACID: Atomicity, Consistency, Isolation, and Durability.
• Without a transaction, a crash in the middle can leave your data half-changed and wrong.
• In .NET, use BeginTransactionAsync, wrap the work in try/catch, commit on success, and roll back on failure.
• Savepoints let you undo just part of a transaction without cancelling the whole thing.
• Isolation levels (READ COMMITTED, REPEATABLE READ, SERIALIZABLE) control how much one transaction sees of another's work. READ COMMITTED is the default.
• Always end every BEGIN with a COMMIT or ROLLBACK, and keep transactions short so others do not have to wait.

References and further reading

• PostgreSQL Documentation: Transactions
• PostgreSQL Documentation: Transaction Isolation
• PostgreSQL Documentation: BEGIN
• PostgreSQL Documentation: SAVEPOINT
• Npgsql Documentation: Transactions