{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"transactions-and-rollback","title":"Transactions: making a mistake reversible","url":"https://addaly.com/learn/data-and-sql/transactions-and-rollback","position":59,"of":66,"minutes":9,"answer":"A transaction makes several writes one unit and makes any of them reversible until COMMIT; know whether your tool autocommits, and remember that in Postgres one failed statement aborts the whole block until ROLLBACK.","keyIdea":"A transaction makes several writes one unit and makes any of them reversible until COMMIT; know whether your tool autocommits, and remember that in Postgres one failed statement aborts the whole block until ROLLBACK."},"body":{"format":"markdown","text":"## All of it, or none of it\n\nA fee payment is recorded as two writes: insert a row into `payments`, and reduce `balance_due` on the student's row. If the first succeeds and the second fails, the student has paid and still owes. If the second succeeds and the first fails, the debt is gone and there is no record of the money. Neither half is acceptable alone.\n\n```sql\nBEGIN;\nINSERT INTO payments (reference, student_id, amount, paid_on, method)\nVALUES ('P-10500', 812, 4500, '2026-03-14', 'upi');\nUPDATE students SET balance_due = balance_due - 4500 WHERE id = 812;\nCOMMIT;\n```\n\nBetween `BEGIN` and `COMMIT` the two writes are one unit. If the connection drops after the INSERT, the database throws the INSERT away when it recovers. If the UPDATE fails a constraint, nothing from the INSERT survives. Other users see neither change until the COMMIT, and then they see both. That is what a transaction is: a boundary inside which partial results do not exist.\n\n## The undo button\n\nThe same mechanism is the safety net for the previous lesson:\n\n```sql\nBEGIN;\nUPDATE orders SET status = 'pending' WHERE status = 'pendng';\n-- UPDATE 40000   <- not the 3 you rehearsed\nROLLBACK;\n```\n\n`ROLLBACK` discards everything since `BEGIN`. The 40,000 rows are as they were. Nothing happened, as far as anybody else can tell. This is why the habit is to open a transaction before any write on a database that matters: the cost is one word, and it turns a catastrophe into a `ROLLBACK`.\n\n## Autocommit, and which mode you are in\n\nWhen you do not write `BEGIN`, most tools wrap each statement in its own transaction and commit it immediately. That is autocommit, and it means a bare `UPDATE` cannot be rolled back, because it was committed before the prompt returned. `psql` is in autocommit by default. DBeaver can be switched to manual commit, where nothing is saved until you press the commit button, and shows a counter of uncommitted statements. Application libraries vary: Python's `psycopg` opens a transaction implicitly and needs `commit()` called; some others autocommit. Before writing anything on a new tool, find out which mode it is in, because the two modes produce opposite mistakes: work that vanished because nobody committed, and work that cannot be undone because everything did.\n\n## The error that aborts the whole thing\n\nIn Postgres, an error inside a transaction puts the transaction into an aborted state. Every subsequent statement fails with `current transaction is aborted, commands ignored until end of transaction block`, even a harmless `SELECT 1`, until you `ROLLBACK`. People meet this in a script that continues after a failed statement and cannot understand why everything after it is failing too. The behaviour is deliberate: a transaction with one failed step is not in a state anyone can reason about, so Postgres refuses to let you commit it.\n\n`SAVEPOINT` gives a partial undo. After `SAVEPOINT before_risky;`, a failure can be answered with `ROLLBACK TO before_risky;`, which discards only the work since the savepoint and lets the transaction continue. Batch loaders use it to skip a bad row and keep going.\n\n## What a transaction costs\n\nWhile it is open, its locks are held. An `UPDATE` inside a transaction that then waits for a human to read the result and type `COMMIT` holds a lock on those rows for as long as the human takes, and every other writer that needs them waits. A transaction left open over lunch is a common cause of a queue of stuck requests, and the performance module's lesson on waiting shows how to find one. Keep transactions short: rehearse outside the transaction, then open it, write, check the count, commit.\n\n## Schema changes inside a transaction\n\nPostgres treats `CREATE TABLE`, `ALTER TABLE` and `DROP TABLE` as transactional. A migration that creates two tables and fails on the third leaves nothing behind, and can be tested with a `ROLLBACK` at the end. MySQL commits implicitly before and after every schema statement, so a failed migration there leaves whatever it got through, and has to be cleaned up by hand. Oracle and SQL Server sit between. It is one of the larger practical differences between engines and it decides how you write a migration.\n\n## The habit\n\nOpen a transaction before a write you have not done a hundred times. Check the row count. Commit or roll back. If a tool commits for you, know that it does before you press enter."},"check":{"question":"A Postgres script runs `BEGIN`, an INSERT that fails on a constraint, and then several SELECT statements that each fail with \"current transaction is aborted, commands ignored until end of transaction block\". What is going on?","options":[{"text":"The failed INSERT locked the table, and the SELECTs are being refused because they cannot obtain a read lock while the failed write holds it.","correct":false,"tempting":"A failed statement holds no lock that blocks reads, and the message names the transaction's state, not a lock."},{"text":"The SELECTs reference the row the INSERT tried to create, which does not exist, so each one errors on the missing row.","correct":false,"tempting":"Selecting a row that does not exist returns zero rows, not an error; the message here is about the transaction, not the data."},{"text":"Postgres requires a COMMIT after every failed statement to clear the error before continuing.","correct":false,"tempting":"COMMIT on an aborted transaction performs a rollback anyway; the required action is ROLLBACK, and the error is a state, not a counter to clear."},{"text":"The error put the transaction into an aborted state, and Postgres refuses every further statement until a ROLLBACK ends the block.","correct":true}]},"prev":{"slug":"update-and-delete-without-regret","title":"UPDATE and DELETE: rehearse it as a SELECT","url":"https://addaly.com/learn/data-and-sql/update-and-delete-without-regret"},"next":{"slug":"concurrent-writes","title":"Two people, one row: lost updates and how the database prevents them","url":"https://addaly.com/learn/data-and-sql/concurrent-writes"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Transactions: making a mistake reversible', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/transactions-and-rollback (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Transactions: making a mistake reversible","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":6,"slug":"changing-data-safely","title":"Changing data safely"},"answer":null,"keyIdea":"A transaction makes several writes one unit and makes any of them reversible until COMMIT; know whether your tool autocommits, and remember that in Postgres one failed statement aborts the whole block until ROLLBACK.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"all-of-it-or-none-of-it","runs":[{"text":"All of it, or none of it"}]},{"t":"p","runs":[{"text":"A fee payment is recorded as two writes: insert a row into "},{"text":"payments","code":true},{"text":", and reduce "},{"text":"balance_due","code":true},{"text":" on the student’s row. If the first succeeds and the second fails, the student has paid and still owes. If the second succeeds and the first fails, the debt is gone and there is no record of the money. Neither half is acceptable alone."}]},{"t":"code","lang":"sql","label":"SQL","text":"BEGIN;\nINSERT INTO payments (reference, student_id, amount, paid_on, method)\nVALUES ('P-10500', 812, 4500, '2026-03-14', 'upi');\nUPDATE students SET balance_due = balance_due - 4500 WHERE id = 812;\nCOMMIT;"},{"t":"p","runs":[{"text":"Between "},{"text":"BEGIN","code":true},{"text":" and "},{"text":"COMMIT","code":true},{"text":" the two writes are one unit. If the connection drops after the INSERT, the database throws the INSERT away when it recovers. If the UPDATE fails a constraint, nothing from the INSERT survives. Other users see neither change until the COMMIT, and then they see both. That is what a transaction is: a boundary inside which partial results do not exist."}]},{"t":"h2","id":"the-undo-button","runs":[{"text":"The undo button"}]},{"t":"p","runs":[{"text":"The same mechanism is the safety net for the previous lesson:"}]},{"t":"code","lang":"sql","label":"SQL","text":"BEGIN;\nUPDATE orders SET status = 'pending' WHERE status = 'pendng';\n-- UPDATE 40000   <- not the 3 you rehearsed\nROLLBACK;"},{"t":"p","runs":[{"text":"ROLLBACK","code":true},{"text":" discards everything since "},{"text":"BEGIN","code":true},{"text":". The 40,000 rows are as they were. Nothing happened, as far as anybody else can tell. This is why the habit is to open a transaction before any write on a database that matters: the cost is one word, and it turns a catastrophe into a "},{"text":"ROLLBACK","code":true},{"text":"."}]},{"t":"h2","id":"autocommit-and-which-mode-you-are-in","runs":[{"text":"Autocommit, and which mode you are in"}]},{"t":"p","runs":[{"text":"When you do not write "},{"text":"BEGIN","code":true},{"text":", most tools wrap each statement in its own transaction and commit it immediately. That is autocommit, and it means a bare "},{"text":"UPDATE","code":true},{"text":" cannot be rolled back, because it was committed before the prompt returned. "},{"text":"psql","code":true},{"text":" is in autocommit by default. DBeaver can be switched to manual commit, where nothing is saved until you press the commit button, and shows a counter of uncommitted statements. Application libraries vary: Python’s "},{"text":"psycopg","code":true},{"text":" opens a transaction implicitly and needs "},{"text":"commit()","code":true},{"text":" called; some others autocommit. Before writing anything on a new tool, find out which mode it is in, because the two modes produce opposite mistakes: work that vanished because nobody committed, and work that cannot be undone because everything did."}]},{"t":"h2","id":"the-error-that-aborts-the-whole-thing","runs":[{"text":"The error that aborts the whole thing"}]},{"t":"p","runs":[{"text":"In Postgres, an error inside a transaction puts the transaction into an aborted state. Every subsequent statement fails with "},{"text":"current transaction is aborted, commands ignored until end of transaction block","code":true},{"text":", even a harmless "},{"text":"SELECT 1","code":true},{"text":", until you "},{"text":"ROLLBACK","code":true},{"text":". People meet this in a script that continues after a failed statement and cannot understand why everything after it is failing too. The behaviour is deliberate: a transaction with one failed step is not in a state anyone can reason about, so Postgres refuses to let you commit it."}]},{"t":"p","runs":[{"text":"SAVEPOINT","code":true},{"text":" gives a partial undo. After "},{"text":"SAVEPOINT before_risky;","code":true},{"text":", a failure can be answered with "},{"text":"ROLLBACK TO before_risky;","code":true},{"text":", which discards only the work since the savepoint and lets the transaction continue. Batch loaders use it to skip a bad row and keep going."}]},{"t":"h2","id":"what-a-transaction-costs","runs":[{"text":"What a transaction costs"}]},{"t":"p","runs":[{"text":"While it is open, its locks are held. An "},{"text":"UPDATE","code":true},{"text":" inside a transaction that then waits for a human to read the result and type "},{"text":"COMMIT","code":true},{"text":" holds a lock on those rows for as long as the human takes, and every other writer that needs them waits. A transaction left open over lunch is a common cause of a queue of stuck requests, and the performance module’s lesson on waiting shows how to find one. Keep transactions short: rehearse outside the transaction, then open it, write, check the count, commit."}]},{"t":"h2","id":"schema-changes-inside-a-transaction","runs":[{"text":"Schema changes inside a transaction"}]},{"t":"p","runs":[{"text":"Postgres treats "},{"text":"CREATE TABLE","code":true},{"text":", "},{"text":"ALTER TABLE","code":true},{"text":" and "},{"text":"DROP TABLE","code":true},{"text":" as transactional. A migration that creates two tables and fails on the third leaves nothing behind, and can be tested with a "},{"text":"ROLLBACK","code":true},{"text":" at the end. MySQL commits implicitly before and after every schema statement, so a failed migration there leaves whatever it got through, and has to be cleaned up by hand. Oracle and SQL Server sit between. It is one of the larger practical differences between engines and it decides how you write a migration."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"Open a transaction before a write you have not done a hundred times. Check the row count. Commit or roll back. If a tool commits for you, know that it does before you press enter."}]}],"check":{"question":[{"text":"A Postgres script runs "},{"text":"BEGIN","code":true},{"text":", an INSERT that fails on a constraint, and then several SELECT statements that each fail with “current transaction is aborted, commands ignored until end of transaction block”. What is going on?"}],"options":[{"text":[{"text":"The failed INSERT locked the table, and the SELECTs are being refused because they cannot obtain a read lock while the failed write holds it."}],"correct":false,"tempting":[{"text":"A failed statement holds no lock that blocks reads, and the message names the transaction’s state, not a lock."}]},{"text":[{"text":"The SELECTs reference the row the INSERT tried to create, which does not exist, so each one errors on the missing row."}],"correct":false,"tempting":[{"text":"Selecting a row that does not exist returns zero rows, not an error; the message here is about the transaction, not the data."}]},{"text":[{"text":"Postgres requires a COMMIT after every failed statement to clear the error before continuing."}],"correct":false,"tempting":[{"text":"COMMIT on an aborted transaction performs a rollback anyway; the required action is ROLLBACK, and the error is a state, not a counter to clear."}]},{"text":[{"text":"The error put the transaction into an aborted state, and Postgres refuses every further statement until a ROLLBACK ends the block."}],"correct":true,"tempting":null}],"order":[0,2,3,1],"why":null},"prev":{"slug":"update-and-delete-without-regret","title":"UPDATE and DELETE: rehearse it as a SELECT","minutes":8},"next":{"slug":"concurrent-writes","title":"Two people, one row: lost updates and how the database prevents them","minutes":9}}}