SQL 69 🛢️ Transaction Control: BEGIN, COMMIT, and ROLLBACK
The previous chapter described what a transaction is and what it guarantees. This chapter is about the commands that control it: BEGIN, COMMIT, ROLLBACK, and the SAVEPOINT that sits between them. These four statements are the entire user-facing API for transactions. The BEGIN opens the transaction, the COMMIT closes it and makes the changes permanent, the ROLLBACK closes it and discards the changes, and the SAVEPOINT marks a point inside the transaction that can be rolled back to without aborting the whole thing. The commands are simple in isolation, but the lifecycle rules, the autocommit behavior, and the failure modes are where the subtlety lives.
The lifecycle is a small state machine. The session is in the idle state when no transaction is active. The BEGIN moves it to the in transaction state. The COMMIT and the ROLLBACK return it to the idle state. A SAVEPOINT inside the transaction creates a named marker, and the ROLLBACK TO SAVEPOINT returns to the marker while keeping the transaction open. The state matters because the commands behave differently depending on it: a BEGIN inside an active transaction is a warning, a COMMIT outside a transaction is a no-op, and a ROLLBACK outside a transaction is a warning. The state is visible in the psql prompt (=# for the idle, *# for the in-transaction) and in the pg_stat_activity query state.
The error handling is the second subtlety. In PostgreSQL, when a statement inside a transaction fails, the transaction enters the aborted state. The subsequent statements are rejected with the “current transaction is aborted, commands ignored until end of transaction block” error until the ROLLBACK is issued. The SAVEPOINT is the escape hatch: the ROLLBACK TO SAVEPOINT returns the transaction to the usable state, and the subsequent statements can run. The aborted state is the source of the “why is my transaction dead” confusion, and the SAVEPOINT is the fix.
This chapter covers three areas. First, why the transaction control commands exist — the lifecycle problem and the state machine. Second, the commands in detail — the BEGIN, the COMMIT, the ROLLBACK, the SAVEPOINT, the RELEASE, and the variants like the BEGIN WORK, the START TRANSACTION, and the END. Third, the practical patterns — the explicit transactions, the autocommit, the error handling with the savepoints, the retry logic, and the nested transactions via the savepoints. The chapter ends with a complete example session, a quick reference, best practices, common pitfalls, real-world examples, and diagrams showing the transaction state machine.
Key point: The BEGIN starts a transaction, the COMMIT makes the changes permanent, and the ROLLBACK discards them. The SAVEPOINT marks a point inside the transaction, and the ROLLBACK TO SAVEPOINT returns to it. In PostgreSQL, a failed statement aborts the transaction, and the subsequent statements are rejected until the ROLLBACK or the ROLLBACK TO SAVEPOINT. The autocommit mode treats each statement as its own transaction, and the BEGIN disables it until the COMMIT or the ROLLBACK.
Why the transaction control commands exist
The lifecycle problem. A transaction is not a permanent state; it is a bounded scope. It begins, it ends, and the database must know when each happens. The BEGIN is the marker for the beginning, and the COMMIT and the ROLLBACK are the markers for the end. Without them, the database would not know when to write the changes, when to discard them, and when to release the locks. The commands are the protocol that the application and the database agree on.
The state machine problem. The session has a state, and the commands transition it. The idle state has no active transaction. The in-transaction state has an active transaction. The aborted state has a failed transaction that must be rolled back. The BEGIN moves the idle to the in-transaction, the COMMIT and the ROLLBACK move the in-transaction to the idle, and the ROLLBACK TO SAVEPOINT moves the aborted to the in-transaction. The state is the contract, and the commands are the transitions.
The autocommit problem. The databases have the autocommit mode, where each statement is its own transaction. The INSERT outside a BEGIN is committed immediately, and the ROLLBACK after it does nothing. The BEGIN disables the autocommit for the duration of the transaction, and the COMMIT or the ROLLBACK re-enables it. The autocommit is the default in the PostgreSQL, the MySQL, and the Oracle, and the difference between the autocommit and the explicit transaction is the source of the “why did my rollback not work” confusion.
The savepoint problem. A long transaction may have several logical steps, and the failure of one step should not undo the whole transaction. The savepoint marks the point, and the ROLLBACK TO SAVEPOINT undoes the changes since the point while keeping the earlier changes. The savepoint is the partial rollback, and it is the mechanism for the error recovery within a transaction.
The error-recovery problem. In PostgreSQL, a failed statement aborts the transaction. The subsequent statements are rejected, and the only way out is the ROLLBACK or the ROLLBACK TO SAVEPOINT. The savepoint is the way to recover from the expected errors without aborting the whole transaction. The pattern is to set a savepoint before the risky statement, and to roll back to it if the statement fails.
The trade-off. The explicit transactions add the complexity: the application must manage the BEGIN and the COMMIT, the error handling, and the retries. The autocommit is the simpler default for the single statements. The trade-off is between the simplicity of the autocommit and the correctness of the multi-statement transactions. For the operations that must be atomic, the explicit transaction is the requirement.
a. The BEGIN
The BEGIN starts a transaction. The START TRANSACTION and the BEGIN WORK are the synonyms.
BEGIN;
-- or
START TRANSACTION;
-- or
BEGIN WORK;The BEGIN disables the autocommit until the COMMIT or the ROLLBACK. The statements between the BEGIN and the end are part of the transaction.
The BEGIN inside an active transaction produces a warning:
WARNING: there is already a transaction in progressThe warning is harmless, but it indicates the nested BEGIN was ignored. The transaction is still the outer one.
The BEGIN with the isolation level sets the level for the transaction:
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- or
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;The BEGIN ISOLATION LEVEL is the shorthand. The SET TRANSACTION is the separate statement, and it must come before any query in the transaction.
The read-only transaction. The BEGIN READ ONLY starts a transaction that cannot write. The BEGIN READ WRITE is the default.
BEGIN READ ONLY;
SELECT * FROM accounts;
COMMIT;The read-only transaction is the optimization for the reports and the analytics: the database can skip the write-related work, and the read-only replicas can serve it.
The deferrable transaction. The BEGIN DEFERRABLE starts a SERIALIZABLE read-only transaction that can wait for the safe snapshot. The BEGIN NOT DEFERRABLE is the default.
BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;
SELECT * FROM accounts;
COMMIT;The deferrable transaction is the optimization for the long-running reports that can wait for the snapshot that will not be aborted.
b. The COMMIT and the ROLLBACK
The COMMIT makes the changes permanent and ends the transaction. The END is the synonym.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- or
END;The COMMIT writes the changes to the write-ahead log, releases the locks, and returns the session to the idle state. The COMMIT outside a transaction is a no-op with a warning:
WARNING: there is no transaction in progressThe COMMIT AND CHAIN starts a new transaction with the same characteristics immediately after the commit:
COMMIT AND CHAIN;
-- The new transaction starts with the same isolation level.The COMMIT AND CHAIN is the pattern for the batch processing: the loop commits each item and starts the next transaction.
The ROLLBACK discards the changes and ends the transaction. The ROLLBACK outside a transaction is a no-op with a warning.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- The error, or the decision to abort.
ROLLBACK;The ROLLBACK AND CHAIN starts a new transaction with the same characteristics:
ROLLBACK AND CHAIN;The error handling. In PostgreSQL, the failed statement aborts the transaction, and the subsequent statements are rejected:
BEGIN;
INSERT INTO accounts VALUES (3, 'Carol', 100);
INSERT INTO accounts VALUES (3, 'Dave', 200); -- the duplicate key error
SELECT * FROM accounts; -- ERROR: current transaction is aborted
ROLLBACK;The ROLLBACK is the only way out (unless the savepoint is used). The aborted transaction cannot commit, even if the subsequent statements are valid.
c. The SAVEPOINT
The SAVEPOINT marks a point within the transaction. The ROLLBACK TO SAVEPOINT undoes the changes since the point, and the RELEASE SAVEPOINT removes the point.
BEGIN;
INSERT INTO orders (id, customer_id) VALUES (1, 1);
SAVEPOINT after_order;
INSERT INTO order_items (order_id, product_id) VALUES (1, 1);
-- The error.
ROLLBACK TO SAVEPOINT after_order;
-- The order is still inserted.
COMMIT;The after_order savepoint marks the point after the order is inserted. The failed order_items insert is rolled back to the savepoint, and the order remains. The transaction is still active, and the COMMIT writes the order.
The savepoint in the error recovery. The savepoint is the way to recover from the expected errors:
BEGIN;
SAVEPOINT before_insert;
INSERT INTO accounts VALUES (3, 'Carol', 100);
-- The error: the duplicate key.
ROLLBACK TO SAVEPOINT before_insert;
-- The transaction is usable again.
INSERT INTO accounts VALUES (4, 'Dave', 200);
COMMIT;The before_insert savepoint is set before the risky insert. The duplicate key error aborts the transaction, and the ROLLBACK TO SAVEPOINT returns to the usable state. The subsequent insert succeeds, and the COMMIT writes it.
The RELEASE SAVEPOINT. The RELEASE SAVEPOINT removes the savepoint. The changes since the savepoint become part of the transaction, and the savepoint can no longer be rolled back to.
SAVEPOINT after_order;
-- The statements.
RELEASE SAVEPOINT after_order;The nested savepoints. The savepoints can be nested. The ROLLBACK TO SAVEPOINT rolls back to the named savepoint and destroys all the savepoints created after it.
BEGIN;
SAVEPOINT sp1;
SAVEPOINT sp2;
ROLLBACK TO SAVEPOINT sp1;
-- The sp2 is destroyed.
-- The sp1 still exists.
ROLLBACK TO SAVEPOINT sp1; -- the error: the sp1 is the current
RELEASE SAVEPOINT sp1;
COMMIT;The ROLLBACK TO SAVEPOINT sp1 destroys the sp2 and keeps the sp1. The subsequent ROLLBACK TO SAVEPOINT sp1 is an error because the sp1 is the current savepoint. The RELEASE SAVEPOINT sp1 removes it.
The savepoint overhead. The savepoint has the cost: the database must track the state at the savepoint, and the ROLLBACK TO SAVEPOINT undoes the changes since the point. The cost is the reason the savepoints are not free, and the excessive savepoints can slow the transaction.
Complete Example Session
-- ============================================
-- PART 1: THE BASIC TRANSACTION
-- ============================================
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
SELECT * FROM accounts;
-- ============================================
-- PART 2: THE ROLLBACK
-- ============================================
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- The decision to abort.
ROLLBACK;
SELECT * FROM accounts; -- unchanged
-- ============================================
-- PART 3: THE ABORTED TRANSACTION
-- ============================================
BEGIN;
INSERT INTO accounts VALUES (3, 'Carol', 100);
INSERT INTO accounts VALUES (3, 'Dave', 200); -- the duplicate key error
SELECT * FROM accounts;
-- ERROR: current transaction is aborted, commands ignored until end of
-- transaction block
ROLLBACK;
-- The transaction is closed, and the session is idle.
-- ============================================
-- PART 4: THE SAVEPOINT
-- ============================================
BEGIN;
INSERT INTO orders (id, customer_id) VALUES (1, 1);
SAVEPOINT after_order;
INSERT INTO order_items (order_id, product_id) VALUES (1, 1);
-- The error.
ROLLBACK TO SAVEPOINT after_order;
-- The order is still inserted.
COMMIT;
-- ============================================
-- PART 5: THE SAVEPOINT IN THE ERROR RECOVERY
-- ============================================
BEGIN;
SAVEPOINT before_insert;
INSERT INTO accounts VALUES (3, 'Carol', 100);
-- The error: the duplicate key.
ROLLBACK TO SAVEPOINT before_insert;
-- The transaction is usable again.
INSERT INTO accounts VALUES (4, 'Dave', 200);
COMMIT;
-- ============================================
-- PART 6: THE RELEASE SAVEPOINT
-- ============================================
BEGIN;
SAVEPOINT after_order;
INSERT INTO order_items (order_id, product_id) VALUES (1, 1);
RELEASE SAVEPOINT after_order;
-- The savepoint is removed.
COMMIT;
-- ============================================
-- PART 7: THE NESTED SAVEPOINTS
-- ============================================
BEGIN;
SAVEPOINT sp1;
INSERT INTO accounts VALUES (5, 'Eve', 300);
SAVEPOINT sp2;
INSERT INTO accounts VALUES (6, 'Frank', 400);
ROLLBACK TO SAVEPOINT sp1;
-- The sp2 is destroyed, and the Frank insert is undone.
-- The Eve insert is still there.
COMMIT;
SELECT * FROM accounts;
-- ============================================
-- PART 8: THE BEGIN VARIANTS
-- ============================================
BEGIN WORK;
-- The same as the BEGIN.
COMMIT;
START TRANSACTION;
-- The same as the BEGIN.
COMMIT;
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- The transaction with the SERIALIZABLE level.
COMMIT;
BEGIN READ ONLY;
-- The transaction that cannot write.
COMMIT;
-- ============================================
-- PART 9: THE COMMIT AND CHAIN
-- ============================================
BEGIN;
INSERT INTO accounts VALUES (7, 'Grace', 500);
COMMIT AND CHAIN;
-- The new transaction starts immediately.
INSERT INTO accounts VALUES (8, 'Henry', 600);
COMMIT;
-- ============================================
-- PART 10: THE COMPLETE ERROR HANDLING
-- ============================================
BEGIN;
-- The savepoint before the risky block.
SAVEPOINT before_transfer;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- The check.
DO $$
DECLARE
v_balance NUMERIC;
BEGIN
SELECT balance INTO v_balance FROM accounts WHERE id = 1;
IF v_balance < 0 THEN
RAISE EXCEPTION 'The balance is negative: %', v_balance;
END IF;
END $$;
-- The success.
COMMIT;
-- The failure case:
-- ROLLBACK TO SAVEPOINT before_transfer;
-- The transfer is undone, and the transaction is still usable.The ten parts show the basic transaction, the rollback, the aborted transaction, the savepoint, the savepoint in the error recovery, the release savepoint, the nested savepoints, the BEGIN variants, the COMMIT AND CHAIN, and the complete error handling.
Quick Reference
The Commands
| Command | Purpose |
|---|---|
BEGIN | Start the transaction |
START TRANSACTION | The synonym for the BEGIN |
BEGIN WORK | The synonym |
COMMIT | Write the changes |
END | The synonym for the COMMIT |
ROLLBACK | Undo the changes |
SAVEPOINT name | Mark the point |
ROLLBACK TO SAVEPOINT name | Undo to the point |
RELEASE SAVEPOINT name | Remove the point |
COMMIT AND CHAIN | Commit and start the new transaction |
ROLLBACK AND CHAIN | Rollback and start the new transaction |
The BEGIN Variants
| Command | Purpose |
|---|---|
BEGIN ISOLATION LEVEL level | Set the level |
BEGIN READ ONLY | The read-only transaction |
BEGIN READ WRITE | The read-write (the default) |
BEGIN DEFERRABLE | The deferrable SERIALIZABLE |
BEGIN NOT DEFERRABLE | The default |
The Transaction States
| State | Meaning | The psql prompt |
|---|---|---|
| The idle | No transaction | =# |
| The in-transaction | The active transaction | *# |
| The aborted | The failed transaction | !# |
The Error Handling
| Situation | The response |
|---|---|
| The failed statement | The transaction is aborted |
| The subsequent statement | The error, rejected |
The ROLLBACK | The recovery |
The ROLLBACK TO SAVEPOINT | The recovery, the transaction stays |
The COMMIT after the error | The error, rejected |
The Warnings
| Command | The warning |
|---|---|
The BEGIN inside the transaction | “there is already a transaction in progress” |
The COMMIT outside the transaction | “there is no transaction in progress” |
The ROLLBACK outside the transaction | “there is no transaction in progress” |
The SAVEPOINT outside the transaction | The error |
Best Practices
✅ Do This:
-- Use the BEGIN and the COMMIT for the multi-statement operations
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- ✅
-- Use the ROLLBACK on the error
BEGIN;
-- the statements
ROLLBACK; -- ✅
-- Use the SAVEPOINT for the error recovery
SAVEPOINT before_insert;
INSERT INTO accounts VALUES (...);
-- The error.
ROLLBACK TO SAVEPOINT before_insert; -- ✅
-- Use the RELEASE to remove the savepoint
RELEASE SAVEPOINT name; -- ✅
-- Use the consistent order for the locks
-- The ascending id -- ✅
-- Keep the transactions short
-- The commit as soon as possible -- ✅
-- Check the isolation level
SELECT current_setting('transaction_isolation'); -- ✅❌ Don’t Do This:
-- Don't leave the transaction open
BEGIN;
-- the statements
-- the COMMIT is missing // ❌
-- Don't use the SAVEPOINT outside the transaction
SAVEPOINT sp1; -- the error // ❌
-- Don't rely on the ROLLBACK in the autocommit
INSERT INTO accounts VALUES (...);
ROLLBACK; -- the insert is already committed // ❌
-- Don't hold the transaction open during the user interaction
BEGIN;
-- the long wait
-- the locks are held // ❌
-- Don't use the excessive savepoints
-- The overhead // ❌
-- Don't forget the SAVEPOINT name
ROLLBACK TO SAVEPOINT; -- the error // ❌
-- Don't mix the BEGIN and the autocommit
SET AUTOCOMMIT = off; -- the MySQL only // ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| The transaction is left open | The missing COMMIT | Always commit or rollback |
| The rollback does nothing | The autocommit | Use the BEGIN |
| The aborted transaction | The failed statement | Use the ROLLBACK or the savepoint |
| The savepoint error | The wrong name | Check the name |
| The nested savepoint error | The destroyed savepoint | Track the savepoints |
| The locks are held | The open transaction | Commit as soon as possible |
The COMMIT after the error | The aborted state | The ROLLBACK first |
Real-World Examples
1. The Basic Transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;2. The Rollback
BEGIN;
-- the statements
ROLLBACK;3. The Savepoint
SAVEPOINT before_insert;
ROLLBACK TO SAVEPOINT before_insert;4. The Release
RELEASE SAVEPOINT after_order;5. The Read Only
BEGIN READ ONLY;
SELECT * FROM accounts;
COMMIT;6. The Isolation Level
BEGIN ISOLATION LEVEL SERIALIZABLE;7. The Chain
COMMIT AND CHAIN;8. The Aborted Recovery
BEGIN;
-- the failed statement
ROLLBACK TO SAVEPOINT before_insert;
-- the transaction is usable
COMMIT;9. The Nested Savepoints
SAVEPOINT sp1;
SAVEPOINT sp2;
ROLLBACK TO SAVEPOINT sp1;10. The Complete Error Handling
BEGIN;
SAVEPOINT before_transfer;
-- the statements
-- the error
ROLLBACK TO SAVEPOINT before_transfer;
COMMIT;Visual
The Transaction State Machine
┌──────────────────────────────────────────────────────────────┐
│ THE TRANSACTION STATE MACHINE │
│ │
│ ┌──────────────┐ │
│ │ IDLE │ ← no transaction │
│ └──────────────┘ │
│ │ │
│ │ BEGIN │
│ ▼ │
│ ┌──────────────┐ │
│ │ IN │ ← the active transaction │
│ │ TRANSACTION │ │
│ └──────────────┘ │
│ │ │
│ ├── COMMIT → the changes are written → the IDLE │
│ │ │
│ ├── ROLLBACK → the changes are undone → the IDLE │
│ │ │
│ └── The failed statement │
│ │ │
│ ▼ │
│ ┌──────────────┐ │
│ │ ABORTED │ ← the transaction is dead │
│ └──────────────┘ │
│ │ │
│ ├── ROLLBACK → the IDLE │
│ │ │
│ └── ROLLBACK TO SAVEPOINT → the IN TRANSACTION │
│ │
└──────────────────────────────────────────────────────────────┘The SAVEPOINT
┌──────────────────────────────────────────────────────────────┐
│ THE SAVEPOINT │
│ │
│ BEGIN; │
│ INSERT INTO orders VALUES (1, 1); │
│ SAVEPOINT after_order; ← the marker │
│ INSERT INTO order_items VALUES (1, 1); │
│ -- The error. │
│ ROLLBACK TO SAVEPOINT after_order; ← the undo to the marker│
│ -- The order is still inserted. │
│ COMMIT; │
│ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The transaction timeline: │ │
│ │ │ │
│ │ BEGIN ──► order ──► SAVEPOINT ──► order_items │ │
│ │ │ │ │ │
│ │ │ ▼ │ │
│ │ │ ERROR │ │
│ │ │ │ │ │
│ │ ▼ ▼ │ │
│ │ ROLLBACK TO ◄────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ the transaction continues │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ COMMIT │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ The order is kept, the order_items is undone. │
│ │
└──────────────────────────────────────────────────────────────┘The Aborted Transaction
┌──────────────────────────────────────────────────────────────┐
│ THE ABORTED TRANSACTION │
│ │
│ BEGIN; │
│ INSERT INTO accounts VALUES (3, 'Carol', 100); │
│ INSERT INTO accounts VALUES (3, 'Dave', 200); │
│ -- ERROR: the duplicate key │
│ SELECT * FROM accounts; │
│ -- ERROR: current transaction is aborted, commands ignored │
│ -- until end of transaction block │
│ ROLLBACK; │
│ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The transaction timeline: │ │
│ │ │ │
│ │ BEGIN ──► INSERT ──► INSERT ──► ERROR │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ the ABORTED state │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ all the subsequent │ │
│ │ statements are │ │
│ │ REJECTED │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ROLLBACK │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ The savepoint is the way to avoid the aborted state. │
│ │
└──────────────────────────────────────────────────────────────┘The Autocommit vs the Explicit Transaction
┌──────────────────────────────────────────────────────────────┐
│ THE AUTOCOMMIT vs THE EXPLICIT TRANSACTION │
│ │
│ THE AUTOCOMMIT: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ INSERT INTO accounts VALUES (...); │ │
│ │ -- committed immediately │ │
│ │ │ │
│ │ ROLLBACK; │ │
│ │ -- WARNING: there is no transaction in progress │ │
│ │ -- the insert is already committed │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ THE EXPLICIT TRANSACTION: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ BEGIN; │ │
│ │ -- the autocommit is disabled │ │
│ │ │ │
│ │ INSERT INTO accounts VALUES (...); │ │
│ │ -- not committed yet │ │
│ │ │ │
│ │ ROLLBACK; │ │
│ │ -- the insert is undone │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ The BEGIN disables the autocommit until the COMMIT or the │
│ ROLLBACK. │
│ │
└──────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
BEGIN | Start the transaction |
COMMIT | Write the changes |
ROLLBACK | Undo the changes |
SAVEPOINT | Mark the point |
ROLLBACK TO SAVEPOINT | Undo to the point |
RELEASE SAVEPOINT | Remove the point |
| The states | The idle, the in-transaction, the aborted |
| The autocommit | The default; each statement is its own transaction |
The BEGIN | Disables the autocommit |
| The aborted state | The subsequent statements are rejected |
| The recovery | The ROLLBACK or the ROLLBACK TO SAVEPOINT |
Key takeaways:
- The
BEGINstarts a transaction, theCOMMITmakes the changes permanent, and theROLLBACKdiscards them. TheBEGINdisables the autocommit until theCOMMITor theROLLBACK. TheCOMMITand theROLLBACKoutside a transaction are the no-ops with the warnings . - The session has three states: the idle, the in-transaction, and the aborted. The
BEGINmoves the idle to the in-transaction, theCOMMITand theROLLBACKmove the in-transaction to the idle, and the failed statement moves the in-transaction to the aborted. TheROLLBACKrecovers the aborted state . - The failed statement aborts the transaction in PostgreSQL. The subsequent statements are rejected with the “current transaction is aborted” error until the
ROLLBACKor theROLLBACK TO SAVEPOINT. The savepoint is the way to recover from the expected errors . - The
SAVEPOINTmarks a point within the transaction. TheROLLBACK TO SAVEPOINTundoes the changes since the point while keeping the earlier changes. TheRELEASE SAVEPOINTremoves the point, and the changes since the point become part of the transaction . - The savepoints can be nested. The
ROLLBACK TO SAVEPOINTrolls back to the named savepoint and destroys all the savepoints created after it. TheRELEASE SAVEPOINTremoves the savepoint and all the savepoints created after it . - The
BEGINhas the variants. TheBEGIN ISOLATION LEVELsets the level, theBEGIN READ ONLYstarts the read-only transaction, and theBEGIN DEFERRABLEstarts the deferrableSERIALIZABLEtransaction . - The
COMMIT AND CHAINand theROLLBACK AND CHAINstart a new transaction immediately. The new transaction has the same characteristics as the just-ended one. The pattern is the batch processing . - The savepoint has the overhead. The database must track the state at the savepoint, and the excessive savepoints can slow the transaction. Use the savepoints for the expected errors, not for the routine flow .
Remember: The transaction control is the BEGIN, the COMMIT, the ROLLBACK, and the SAVEPOINT. The BEGIN starts the transaction, the COMMIT writes the changes, and the ROLLBACK undoes them. The SAVEPOINT marks the partial rollback point, and the ROLLBACK TO SAVEPOINT recovers the aborted transaction. The states are the idle, the in-transaction, and the aborted, and the commands are the transitions. The result is the reliable control over the multi-statement operations, which is the foundation of the data integrity.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!