Right, let’s talk about the conversation you’re having with your database. Most of the time, you’re just chatting, sending individual statements over (SELECT, INSERT, UPDATE). The database executes them one by one, immediately committing the result. This is an implicit transaction. Each statement is its own tiny, all-or-nothing event. It’s fast, it’s simple, and for many operations, it’s perfectly fine. It’s like buying a single piece of candy—you hand over the coin, you get the candy, the interaction is over.

But sometimes, you need to do something more complex. You need to buy the entire candy store. You can’t just hand over one coin and get the keys; you need a series of steps: agree on a price, sign a contract, transfer funds, get the keys. If the fund transfer fails after you’ve signed the contract, you’re in a world of hurt. You need all these steps to succeed as a single, atomic unit, or you need to roll the entire thing back as if it never happened. This is where you stop asking politely and you tell the database, “Hey, we’re doing a thing now. Pay attention.” You issue a BEGIN (or in some systems, START TRANSACTION). This starts an explicit transaction.

The Mechanics of Explicit Control

Once you utter BEGIN, you’ve taken the reins. The database now queues up every change you make. It’s all happening in a temporary workspace. The data isn’t permanently changed; it’s staged. Other users might not even see your changes yet, depending on your isolation level (a topic for another day, but just know it gets weird and wonderful).

You do your work: a series of INSERTs, UPDATEs, DELETEs. If you’re happy with everything, you call COMMIT. This is the moment of truth. The database takes all your staged changes and applies them permanently, all at once. It’s atomic: the entire transaction either succeeds completely and becomes visible, or it fails completely and vanishes.

If something goes wrong—your application logic detects a problem, a constraint is violated, you just changed your mind—you call ROLLBACK. This is the giant “undo” button. The database discards every change you made in this transaction. It’s a get-out-of-jail-free card, reverting the world to the state it was in just before you said BEGIN.

-- Example of a simple explicit transaction
BEGIN; -- "Let's do this."

UPDATE accounts SET balance = balance - 100.00 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100.00 WHERE user_id = 2;

-- Imagine some application logic here to check everything is cool...

COMMIT; -- "Make it so."
-- or, if something was wrong...
-- ROLLBACK; -- "Abort! Abort!"

When the Database Thinks For You (Implicit Transactions)

Here’s the part that trips up countless developers. Some databases, most notably PostgreSQL and Oracle, operate in an autocommit mode by default. This means every single statement you send is automatically wrapped in its own tiny transaction. If it succeeds, it’s automatically committed. You don’t need to say BEGIN and COMMIT for one statement; the database does it for you. It’s implicit.

Other systems, like SQL Server and SQLite, will happily let you run multiple statements without a BEGIN, but they’re still treating each one as an implicit transaction. The behavior is largely the same: one statement, one transaction.

The crucial thing to remember is this: the moment you issue a BEGIN, you turn off this autocommit behavior until you issue a COMMIT or ROLLBACK. You’ve explicitly taken control, and the database will wait for your explicit command to finish the job.

The Pitfall of Abandoned Transactions

This leads us to the most common and insidious pitfall: the abandoned transaction. Let’s say your application code starts a transaction with BEGIN, does some work, and then… the code crashes. The network connection drops. Your dog chews through the power cable. Whatever. A COMMIT or ROLLBACK is never sent.

What happens? The transaction remains open. In most databases, it will hold locks on any rows or tables it modified. These locks prevent other transactions from reading or writing those rows. Your database slowly grinds to a halt as transactions pile up waiting for locks held by a transaction that effectively no longer exists. It’s a ghost locking your data.

Always, always, always ensure your application code has robust error handling that guarantees a transaction is either committed or rolled back. Use try-catch-finally blocks, context managers, or whatever your language provides to make this bulletproof. An open transaction is a loaded gun; never leave one lying around.

Savepoints: Your Transaction’s Undo Stack

Sometimes, within a large explicit transaction, you want to be able to roll back part of your work without ditching the whole thing. This is where savepoints come in. They’re like setting a bookmark in your transaction that you can roll back to.

Think of it like playing a video game. Your explicit transaction is the entire level. A savepoint is a quicksave. You can make a risky move, and if it blows up in your face, you can roll back to your quicksave instead of having to restart the entire level (the whole transaction).

BEGIN;
INSERT INTO orders (user_id, total) VALUES (1, 50.00);

SAVEPOINT after_order_created; -- Quicksave here

UPDATE inventory SET stock = stock - 1 WHERE product_id = 101;
-- Oh no! The above statement fails because stock is zero!

ROLLBACK TO SAVEPOINT after_order_created; -- Reload quicksave
-- The failed inventory update is undone, but the order insert is still there.

-- Let's try a different product
UPDATE inventory SET stock = stock - 1 WHERE product_id = 102;

COMMIT;

It’s an incredibly powerful tool for complex business logic. You can nest them, too (SAVEPOINT point_a, SAVEPOINT point_b, ROLLBACK TO point_a). Just remember, rolling back to a savepoint doesn’t release any locks you’ve acquired since then. Use them, but use them wisely. They’re a clear sign your transaction might be getting a bit too complex, but hey, sometimes reality is complex. The designers got this one right.