Transactions in the DB: commit and rollback

1. The “partial order” problem

If a multi-step operation fails in the middle, data can easily get stuck in a “half done” state — that is the “partial order” problem. Imagine a mini-shop: in code everything looks tidy — insert an order row, insert items, subtract the stock. But the database is not a telepath and does not read your “intended plot” from your facial expression. If an error happens between steps, the developer quickly starts wanting to move into the woods and grow potatoes.

Let’s look at the problem without philosophy, at the level of facts. We have these tables (from the previous day):

• customer_order — the root order record;
• order_item — order items;
• stock_item — stock levels.

Order processing should logically be atomic: either the order is created and the items are created and the stock is deducted, or nothing is created at all. But if you execute commands one by one and commit each one separately, you can get a story like this: the order and items are already saved, but the stock deduction failed — for example, there was not enough product or an error occurred. Result: the order “exists”, but the stock “did not decrease” or decreased incorrectly — and reality starts to drift apart.

To feel how unpleasant this is, just ask yourself a question: “What am I going to show the user?” If the order already exists in customer_order, then formally “the order was placed.” But if the stock was not deducted, the warehouse says: “I did not hand anything out.” That becomes a dispute between two tables. And unfortunately, the table with the wrong data always wins.

2. The autocommit mode

The word autocommit sounds friendly, almost like “autosave” in Google Docs. And in simple cases it really is convenient: you ran an INSERT — and it immediately became an “official fact” in the database. But as soon as a business operation consists of several steps, autocommit turns into a person who signs a contract after every sentence, even if the text later says “oops, this is a draft, do not sign.”

The meaning of autocommit is very simple: each SQL command runs in its own transaction and is automatically committed. That is:

• sent INSERT — the database executed it and immediately “committed” it;
• sent a second INSERT — the database executed it and committed again;
• sent UPDATE — and then an error happened... but the previous commands are already committed, there is no going back.

In JDBC terms (we are not diving into a JDBC course, just stating the fact), Connection often has autoCommit = true by default. Many SQL clients also have an Auto-commit checkbox, and it is usually enabled. In psql, commands also run as separate transactions until you explicitly do BEGIN.

Here is a minimal “dangerous” sequence (pseudocode) that looks like a business operation, but technically it is three independent commits:

// Enable autocommit: each command becomes a separate transaction.
connection.setAutoCommit(true);

execute("insert into customer_order ..."); // already committed (cannot be rolled back)
execute("insert into order_item ...");     // already committed (cannot be rolled back)
execute("update stock_item ...");          // error here — too late

From the database’s point of view, this is not “one order placement operation”. It is just three commands and three separate mini-lives. And if the third life ended tragically, the first two continue to exist as if nothing happened.

To make it crystal clear, here is a small table:

commit()

3. Explicit transaction: BEGIN/COMMIT/ROLLBACK

Now for the good news: databases invented transactions long before Spring, Hibernate, and all of our favorite annotations. And a database-level transaction is exactly the mechanism that lets you say: “These few commands are one logical block. Either everything happens, or nothing happens.” The most important point here is that we ourselves define the boundaries of this block.

In PostgreSQL, the classic form looks like this: BEGIN (or START TRANSACTION) — then commands — then COMMIT or ROLLBACK.

A mini example in plain SQL (very short, so the lecture does not turn into a separate order script):

begin; -- start the transaction: changes are still a "draft" for now

insert into customer_order (id, order_number, customer_email, total_amount)
values (700, 'ORD-700', 'alex@example.com', 399.98); -- step 1: create the order

update stock_item
set available_quantity = available_quantity - 2
where product_id = 10; -- step 2: deduct stock

commit; -- commit both steps as one whole

And if something goes wrong (an error or the application decides “we are not continuing the scenario”), then instead of commit you do rollback:

begin; -- start the transaction

insert into customer_order (id, order_number, customer_email, total_amount)
values (700, 'ORD-700', 'alex@example.com', 399.98); -- we managed to make a change

-- suppose a problem is detected here
rollback; -- cancel ALL changes in this transaction

It helps to keep a simple flowchart in mind:

flowchart TD
    A[BEGIN] --> B[Execute the business operation steps]
    B --> C{Are all steps successful?}
    C -- Yes --> D[COMMIT]
    C -- No --> E[ROLLBACK]

There is nothing “framework-specific” in this diagram. It is just the database. Frameworks later simply learn to do the same thing automatically, but right now we need to understand the foundation: a transaction is a container for several SQL actions.

4. Atomicity of a business operation

The word “atomicity” sounds as if quantum physics and Schrödinger’s cat are about to appear as a DBA. In reality it is simpler: atomicity means an operation looks indivisible. Either it happened completely, or it is considered not to have happened at all. In the data world this means that after the transaction finishes, there is no “halfway” state in the database.

The clearest analogy is a bank transfer. If you debit money from account A but do not credit account B, the money does not “float in the air” — it is lost, and you lose sleep. So a proper system must do this as one whole: the debit and the credit must be tied together by a transaction.

In a mini-shop, the situation is very similar. If we created the order but did not deduct stock, we effectively told the system: “The order exists,” but did not ensure its reality in the warehouse. And if it is the other way around — stock is deducted but the order was not saved — then the product disappears from the warehouse “into nowhere.” Both scenarios look equally bad for the business, only the accountant cries for different reasons.

It is important to catch the feeling: atomicity is not about “convenience”, but about data survivability. And it is the transaction that makes atomicity possible in multi-step scenarios.

5. COMMIT: committing changes

If BEGIN means “we started the story”, then COMMIT means “we publish it as the official version.” From the moment of COMMIT, changes become committed: they are stored in the database as part of the permanent state, and you cannot simply “change your mind” with ROLLBACK. After commit, cancellation is done only through a new business operation — for example, order cancellation or stock return — and that is a completely different logic.

It is very important to feel this: COMMIT is not just a technical finishing touch. It is a meaningful moment that should happen when the business operation is truly complete and all its steps have been done.

So in a normal scenario, COMMIT goes:

— not after the first INSERT, because that is not yet the order “as a whole”;
— not after inserting the items if stock has not been deducted yet;
— but at the end, when we are sure everything went successfully.

In behavioral terms, this can be phrased like this: commit confirms the entire set of changes in the transaction at once, as one “bundle.” That is practical atomicity.

If you want to picture it in a simple way, you can remember this thought: “COMMIT is the ‘Save’ button for the whole story, not for one command.”

6. ROLLBACK: rolling back changes

For a beginner, the word rollback sometimes feels like an emergency button on a nuclear power plant. In reality, ROLLBACK is a normal, built-in part of the scenario. Errors happen, conditions are not met, data may fail constraints, there may not be enough stock — and in such cases the correct behavior of the system is to roll back the changes and leave the database in its original state, as if no attempt had been made.

There are two important types of reasons for a rollback. The first is a technical error, when a command actually fails (for example, an FK violation or trying to write NULL into a NOT NULL field). The second is a business condition, when the command could formally succeed, but we decide ourselves “we are not continuing”: for example, the stock update affected no rows because there is not enough product, and we do not want to save the order.

PostgreSQL has a useful feature that is better to learn now than at 2 a.m. in production. If an SQL execution error happens inside a transaction, the transaction can move into a “broken” state, and until you do ROLLBACK, the database will answer roughly like this: “No, I am not doing anything else, roll back first.”

Here is a simple example:

begin; -- start the transaction

insert into customer_order (id, order_number, customer_email, total_amount)
values (1, 'ORD-1', 'a@b.c', 10.00); -- managed to create the order

-- suppose there is an error here (for example, FK violation)
insert into order_item (id, order_id, product_id, quantity, unit_price, line_total)
values (1, 999, 10, 1, 10.00, 10.00); -- order_id=999 does not exist => error

-- after an error, any commands may not run until we roll back
rollback; -- return to the state "as it was before begin"

The moral: ROLLBACK is not a “shameful part of the code.” It is a logical end to an unsuccessful attempt. In a healthy architecture, rollback is provided for just as explicitly as a successful commit.

7. Transaction boundaries

After we have met BEGIN/COMMIT/ROLLBACK, a natural question arises: “So where do we put transaction boundaries? For every command? For every table? For every method?” And here it is important not to go to extremes: a transaction should not be “minimal”, but “meaningful”.

If a transaction is too small, it stops solving the atomicity problem. For example, “insert order — commit; insert items — commit; deduct stock — commit” looks neat technically, but the business operation has already fallen apart into pieces, and breakage is possible between them. It is like assembling IKEA furniture and after every screw saying, “Okay, the wardrobe is done,” and then wondering why the doors are lying separately on the floor.

If a transaction is too large, it becomes a bag where everything is stuffed together. The database has to hold resources longer, and later it becomes harder for you to understand where exactly the operation started and where it ended. We are not diving deeply into isolation and locking now, but even at a basic level it is useful to remember a simple thought: a transaction is not a free infinite thing.

In our mini-shop, a good example of the “right size” is order processing. It is one business operation that includes several tables, and it should be one transaction. But, for example, reading the product list from the catalog is usually not such a state-changing operation (we are not going into details today, just capturing the intuition).

So the rule in plain human terms is this: the transaction boundary should match the boundary of a meaningful data change.

8. Transactions in Java without Spring

We are still in the SQL world, but it is useful to see how the commit/rollback buttons look from the Java side. Not so that you write JDBC code by hand for the whole course (we will not do that), but so that you understand: in the end, any tools in Java still come down to these basic operations.

Here is a minimal JDBC-level example. It is intentionally short and without architectural decorations, so that you see only the mechanics:

import java.sql.Connection;

// Disable autocommit: now we control the transaction boundaries ourselves.
connection.setAutoCommit(false);

try {
    // Inside the transaction, perform several steps of one business operation.
    execute(connection, "insert into customer_order ...");
    execute(connection, "insert into order_item ...");
    execute(connection, "update stock_item ...");

    // Commit everything at once if all steps succeed.
    connection.commit();
} catch (Exception ex) {
    // If any step fails, roll back EVERYTHING we managed to do.
    connection.rollback();
    throw ex;
}

What matters here is not the execute function (it could be PreparedStatement or anything else), but the structure itself: disable autocommit, execute a series of steps, commit with one command, and roll back on error.

If you want to see something a little more realistic, you can imagine that executeUpdate returns the number of changed rows, and we make a business decision at that level. For example, if UPDATE stock_item ... updated 0 rows, that means there was not enough stock, and we initiate a rollback ourselves (this is an important idea for the order scenario):

import java.sql.Connection;

// Switch to manual transaction control.
connection.setAutoCommit(false);

try {
    // Step 1: create the order (or part of it).
    execute(connection, "insert into customer_order ...");

    // Step 2: try to deduct stock; the result is how many rows were actually updated.
    int updated = executeUpdate(connection, "update stock_item ...");

    // If no rows were updated, treat the business operation as unsuccessful.
    if (updated == 0) throw new IllegalStateException("Not enough stock");

    // If everything is okay, commit the whole scenario.
    connection.commit();
} catch (Exception ex) {
    // Any error (technical or business) => roll back the transaction.
    connection.rollback();
    throw ex;
}

Notice the useful psychological effect: the transaction in code looks like one try/catch block. That is the idea of “unit of work”: we either reach commit(), or we roll everything back.

9. Testing ROLLBACK in practice

Transactions are easiest to understand when you have at least once seen the “as if nothing happened” effect by hand. So it is useful to keep in mind a small test you can do in an SQL client even without a second connection and without complex isolation modes.

The scenario is this: inside a transaction, we insert a row and immediately see it with a SELECT. Then we do ROLLBACK and run SELECT again — the row is gone.

Example (conditional, but very illustrative):

begin; -- start the transaction

insert into category (id, code, name, active)
values (10, 'BOOKS', 'Books', true); -- the insert is visible only "inside" the transaction for now

select count(*) from category where id = 10; -- 1 (inside the transaction we see our own insert)

rollback; -- roll back the insert

select count(*) from category where id = 10; -- 0 (after rollback it is as if nothing happened)

This experience helps the brain stop perceiving a transaction as some abstract thing from a textbook. You literally see it: inside the transaction, changes exist, but they have not yet become the “official story.” COMMIT makes them official, ROLLBACK throws the draft into the trash.

And that is exactly what we need for multi-step operations in a mini-shop: until we are sure all steps succeeded, we work in draft mode.

10. Common transaction mistakes

Transaction problems most often happen not because a person “does not know the COMMIT command”, but because they choose boundaries incorrectly and handle errors badly. So let’s talk through the most common pitfalls in a live style, so you can recognize them by the sound of approaching footsteps.

Mistake #1: relying on autocommit in a composite operation.
A beginner sees that INSERT and UPDATE “work anyway” and does not enable a transaction, thinking that it is “for complex systems.” As a result, when an error happens on step #3, steps #1-2 are already committed, and the database stores a “partial operation.” The cure is exactly one decision: as soon as you have more than one command that together form one meaning, you start thinking in terms of a transaction.

Mistake #2: doing commit in the middle of the scenario “just in case”.
Sometimes it seems logical to “commit the order and then deal with stock.” This creates an illusion of reliability, but in reality it breaks atomicity. After the first commit, you can no longer roll back order creation, even if everything fails later. The correct commit is at the end, when you have truly finished the business operation.

Mistake #3: forgetting rollback in the error branch or swallowing the exception.
In code this looks especially sneaky: you catch an exception, log it, continue working, and the transaction remains open or in an error state. As a result, later commands start behaving strangely, and you do not understand why. The mental model here must be strict: if the operation did not reach commit, you must do rollback — explicitly and immediately.

Mistake #4: thinking that an “SQL error” is the only reason for a rollback.
Very often rollback is needed not because a command crashed, but because of a business condition. For example, UPDATE stock_item ... with the condition available_quantity >= 2 may return 0 updated rows — that is not an SQL error, but a correct result meaning “not enough stock.” If you still do commit in that case, you commit the order without deducting stock. Here it is important to get used to this: a transaction is a tool not only for emergencies, but also for normal business logic of “do not execute the scenario.”

Mistake #5: mixing unrelated actions into one transaction.
Sometimes a developer thinks: “Since a transaction makes everything reliable, let’s stuff order creation, product catalog updates, and category changes into one transaction too.” You end up with a combi-transaction where the meaning is hard to understand and the system becomes less predictable. A transaction should correspond to one operation, not everything that “happened to be at hand.”

1

Task

Spring Data JPA, level 3, lesson 3

Locked

Order insertion with a subsequent rollback

Order insertion with a subsequent rollback

Open

1

Task

Spring Data JPA, level 3, lesson 3

Locked

Successful transaction with COMMIT

Successful transaction with COMMIT

Open