A single SQL statement can only do one thing. But real operations often involve multiple steps that must succeed or fail together: recording a payment and upgrading a subscription, transferring credits between accounts, or inserting a stream record and updating a play count.

A transaction groups these steps into a single all-or-nothing unit, so the database never ends up in a half-finished state.

Insert these rows to follow along:

A transaction is a sequence of one or more SQL statements that the database treats as a single unit of work. Either every statement in the transaction succeeds and the changes become permanent, or none of them do. There is no middle ground.

Consider a common StreamFlow operation: Alice upgrades from the free tier to premium. This requires two changes:

If the payment insert fails after the tier update has already been applied, Alice gets premium access without paying. If the update succeeds but the payment records a wrong amount, the data is inconsistent. A transaction prevents both scenarios by bundling the two operations together.

The following diagram shows the two possible outcomes of a transaction:

If both statements succeed, COMMIT makes the changes permanent. If anything goes wrong, ROLLBACK undoes every change made since BEGIN, leaving the database exactly as it was before the transaction started.

These three commands control the lifecycle of a transaction.

BEGIN

Marks the start of a transaction. Every statement after BEGIN runs inside the transaction until you issue COMMIT or ROLLBACK.

COMMIT

Makes all changes since BEGIN permanent. Once committed, the changes survive server crashes, power failures, and restarts.

ROLLBACK

Discards all changes since BEGIN. The database reverts to the state it was in before the transaction started.

Here is Alice's upgrade as a complete transaction:

All three statements succeed or none of them take effect. After COMMIT, querying Alice's data shows the updated tier and the new payment record.

Now consider what happens when something goes wrong. Suppose the payment insert fails because of a constraint violation:

The ROLLBACK undoes the UPDATE to Carol's subscription tier. Her subscription_tier remains 'free', which is the correct outcome: no payment was recorded, so no upgrade should apply.

What Goes Wrong Without a Transaction?

Without wrapping these statements in a transaction, each one commits independently:

The UPDATE has already been committed. Carol now has premium access but no matching payment record exists. The data is inconsistent, and fixing it requires a manual correction.

By default, most databases run in auto-commit mode. Every individual statement is automatically wrapped in its own transaction: the database implicitly runs BEGIN before the statement and COMMIT after it succeeds (or ROLLBACK if it fails).

This means a standalone statement like:

is equivalent to:

Auto-commit works fine for single-statement operations. It becomes a problem when you need multiple statements to succeed or fail as a group. That is when you use an explicit BEGIN to start a transaction.

The following table shows how each major database handles auto-commit:

In PostgreSQL, there is no session-level auto-commit toggle. You start an explicit transaction with BEGIN each time you need one. MySQL lets you turn auto-commit off entirely for a session, which means every statement becomes part of a transaction that you must manually COMMIT or ROLLBACK. SQL Server's IMPLICIT_TRANSACTIONS setting works similarly: when enabled, the database starts a transaction automatically on each statement and waits for an explicit COMMIT.

Sometimes you want to undo part of a transaction without discarding everything. A SAVEPOINT creates a named checkpoint within a transaction. You can roll back to that checkpoint while keeping all the work done before it.

The syntax is straightforward:

Here is a practical example. Suppose you are batch-inserting payment records for several users and one of them fails:

After this transaction commits, the payments table contains records for Alice (payment_id 7) and Emma (payment_id 9). Carol's insert was rolled back. Without SAVEPOINT, the only option would be to ROLLBACK the entire transaction and lose Alice's payment too.

The following diagram illustrates the flow:

You can nest savepoints by creating new ones after rolling back to a previous one. Each SAVEPOINT name must be unique within the transaction, or the new one replaces the old one with the same name.

RELEASE SAVEPOINT

RELEASE SAVEPOINT removes a savepoint without rolling back to it. The changes made after the savepoint remain intact. This is useful when you set up a savepoint "just in case" and later determine that everything succeeded:

After RELEASE, you can no longer roll back to that savepoint. The changes between the savepoint and the release are now a permanent part of the transaction (though the whole transaction can still be rolled back with ROLLBACK).

Data manipulation statements (INSERT, UPDATE, DELETE) always participate in transactions. But what about data definition statements like CREATE TABLE, ALTER TABLE, or DROP TABLE? This is where databases differ significantly.

PostgreSQL supports transactional DDL. You can create a table, insert rows, and roll back the entire thing:

MySQL does not support transactional DDL. DDL statements cause an implicit commit before and after they execute. If you run a CREATE TABLE inside an open transaction, MySQL commits everything done so far, runs the DDL, and then auto-commits the DDL itself. This can cause surprising behavior:

The UPDATE to Carol's subscription tier is now permanent, even though the intent was to keep everything in a single transaction.

SQL Server supports transactional DDL for most DDL statements, similar to PostgreSQL.

This difference matters when writing migration scripts. In PostgreSQL, you can wrap an entire migration in a transaction: if any step fails, the whole migration rolls back cleanly. In MySQL, you need a different strategy, like running each DDL statement separately and having a compensating script to undo changes if something fails partway through.

Forgetting to COMMIT or ROLLBACK

An open transaction holds locks on the rows it has modified. If you start a transaction and walk away without committing or rolling back, those locks stay in place. Other sessions that try to modify the same rows will block, waiting for the lock to be released. In a production system, this can cascade into widespread slowdowns.

Most databases will eventually time out or kill idle-in-transaction sessions, but the damage from blocked queries can be significant in the meantime.

Long-Running Transactions

Even when you do commit, keeping a transaction open for a long time causes problems. The database holds locks and maintains internal state (like undo logs or version information) for the duration of the transaction. Long transactions increase lock contention, bloat internal storage, and can interfere with routine maintenance operations like autovacuum in PostgreSQL.

The general rule: keep transactions as short as possible. Do the work you need to do, then commit. Don't open a transaction, run a slow API call, wait for user input, and then commit.

Mixing DDL with DML in MySQL

Because MySQL implicitly commits on DDL, mixing CREATE/ALTER/DROP statements with INSERT/UPDATE/DELETE in a transaction leads to partially committed changes. If you need both DDL and DML changes to be atomic, use PostgreSQL or SQL Server, or handle the DDL outside the transaction in MySQL.