SQL / TRANSACTIONS AND CONCURRENCY
Grouping statements with BEGIN, COMMIT, and ROLLBACK
Wrap several statements in one transaction with BEGIN, end it deliberately with COMMIT or ROLLBACK, and predict what a mid-block error leaves behind.
What you will learn
- Open an explicit block with BEGIN and close it exactly once with COMMIT or ROLLBACK
- Spot autocommit: with no BEGIN, each statement commits itself and ROLLBACK does nothing
- Read a COMMIT answered with ROLLBACK as proof Postgres discarded the whole block
- Keep blocks short: open blocks hold locks and old row versions until they end
Understanding Grouping statements with BEGIN, COMMIT, and ROLLBACK
A server never runs a statement outside a transaction. With autocommit on, which is the default in psql, in MySQL clients and in almost every driver, each statement gets its own private transaction that opens before it and commits the instant it succeeds. BEGIN does not switch transactions on; it tells the server to stop closing the transaction after every statement and to wait for you to say where the block ends. COMMIT then publishes everything the block did as one step, and ROLLBACK throws all of it away, leaving the database as it was when you typed BEGIN.
Between BEGIN and COMMIT your changes exist, but only for you: your own session sees its uncommitted rows, other sessions do not, and nothing is durable yet. That is why the length of a block is a design decision and not a matter of style, because the server has to keep the superseded row versions and the locks alive for as long as the block stays open, so a BEGIN left sitting while somebody goes to lunch costs far more than a BEGIN followed by three quick statements. A block also ends exactly once: after COMMIT or ROLLBACK you are back in autocommit, and a second BEGIN inside an open block does not nest, since Postgres answers with a warning that a transaction is already in progress and keeps the one you had.
What a mid-block error does is the part that differs most between engines, and it is worth knowing before you lean on it. Postgres marks the entire block as aborted: every later statement is refused with "current transaction is aborted", and a COMMIT comes back with the tag ROLLBACK because discarding is the only ending still available. MySQL and SQL Server usually undo only the statement that failed and let the block carry on, so the decision to abandon the work belongs to your code, and MySQL additionally commits the block implicitly the moment you run DDL such as CREATE TABLE. The practical rule is to inspect each statement's result and choose COMMIT or ROLLBACK yourself rather than assuming the engine chose for you.
placeholder
-- PostgreSQL (psql), one session
CREATE TABLE seats (id int PRIMARY KEY, holder text);
INSERT INTO seats VALUES (1, NULL), (2, NULL);
BEGIN;
UPDATE seats SET holder = 'ana' WHERE id = 1;
SELECT id, holder FROM seats ORDER BY id; -- visible inside the block
ROLLBACK;
SELECT id, holder FROM seats ORDER BY id; -- the update never happened
BEGIN;
UPDATE seats SET holder = 'ana' WHERE id = 1;
COMMIT;
SELECT id, holder FROM seats ORDER BY id;BEGIN, COMMIT and ROLLBACK do not switch transactions on; they move the boundary so a group of statements shares one ending that you choose.
Worked examples
A COMMIT that comes back as ROLLBACK
Shows how a single failing statement poisons a Postgres block so that COMMIT can only discard it.
-- PostgreSQL (psql)
CREATE TABLE tags (name text PRIMARY KEY);
BEGIN;
INSERT INTO tags VALUES ('sql');
INSERT INTO tags VALUES ('sql'); -- duplicate key: this statement fails
INSERT INTO tags VALUES ('joins'); -- refused: the block is already aborted
COMMIT;
SELECT count(*) FROM tags;Example explained
Line 1The first INSERT reports INSERT 0 1, so at that moment the block really does hold one new row.
Line 2The second INSERT violates tags_pkey, and Postgres reacts by marking the whole block aborted, not just that statement.
Line 3The third INSERT is never executed: "commands ignored until end of transaction block" means the server refuses work until you close the block.
Line 4COMMIT is answered with the tag ROLLBACK and count(*) is 0, so the row that inserted cleanly went away with the rest.
A ROLLBACK that arrives too late
Demonstrates that without BEGIN a statement commits itself, leaving nothing for ROLLBACK to undo.
-- PostgreSQL (psql), autocommit on
CREATE TABLE notes (id int, body text);
INSERT INTO notes VALUES (1, 'keep me');
DELETE FROM notes; -- no BEGIN in front of it
ROLLBACK;
SELECT count(*) FROM notes;Example explained
Line 1DELETE FROM notes runs in its own implicit transaction, which commits as soon as the statement succeeds.
Line 2The WARNING tells you the ROLLBACK found no open block, so the ROLLBACK tag after it is a formality.
Line 3count(*) is 0, meaning the safety net had to be opened with BEGIN before the DELETE, never after it.
Important notes
A second BEGIN inside an open block does not create a nested transaction; you stay in the first one, and undoing only part of the work needs savepoints.
Spellings differ: START TRANSACTION is the standard, BEGIN TRANSACTION in SQL Server, bare BEGIN in Postgres and SQLite, and Oracle has no opening statement at all because the first DML starts the transaction.
Common mistakes
Assuming a failed statement rolls the block back for you: in Postgres the block sits aborted and every later statement is refused, while in MySQL the earlier statements are still pending and a later COMMIT keeps them.
Running CREATE TABLE or ALTER TABLE inside a block on MySQL or Oracle, where DDL commits the transaction implicitly, so the ROLLBACK typed afterwards has nothing left to undo.
Closing the client with the block still open: drivers roll back on disconnect, so inserts you watched succeed vanish and it looks as if the write never reached the database.
Try it yourself
Change, predict, then run
Create basket(item text, qty int) with three rows, then run BEGIN; DELETE FROM basket; SELECT count(*) FROM basket; ROLLBACK; and select the count again. Explain why the two counts differ, then repeat the same block ending in COMMIT.
Open the SQL workspaceCheck your understanding
In psql you run BEGIN, an INSERT that succeeds, an INSERT that violates a unique constraint, then COMMIT — and the server answers the COMMIT with the tag ROLLBACK. Why?
- COMMIT stored the first insert and undid only the failing one, so the tag describes that single statement.
- The failed insert marked the whole block aborted, so the only ending Postgres will accept discards the first insert as well.
- The client rewrote COMMIT as ROLLBACK; sending COMMIT a second time would store the first insert.
- Autocommit had already stored the first insert, so COMMIT had no work left and echoed the failed statement.
Show answer
Once any statement fails, a Postgres transaction is poisoned: further commands are refused and COMMIT is downgraded to a rollback, so the cleanly inserted row disappears too. The first option is tempting because MySQL behaves roughly that way, undoing only the failing statement and leaving the block usable, but Postgres offers no partial ending unless you set a savepoint before the risky statement.