SQL transactions and ACID: COMMIT and ROLLBACK

#Why it matters

SQL transactions and ACID guarantees are what let a database survive a crash halfway through your work. A nightly load that deletes last month and reloads it, a payment that debits one account and credits another, a migration that adds a column and backfills it: each is several statements that must all happen or none at all. If you do not wrap them in a transaction, a timeout, a killed laptop or a deploy restart leaves the data half changed, and nobody notices until the monthly report is short by 50,000 rows. SQL appears in 88% of Data Analyst and 80% of Data Engineer job descriptions we studied, and "what does ACID mean?" is asked in almost every backend and data engineering interview.

In this lesson you will kill a real load halfway, twice: once without a transaction (the month disappears) and once with one (nothing changes). Then you will make the load safe to re-run, handle a bad row without losing the batch, and see what an open transaction does to everyone else.

#The concept

#A transaction is all or nothing

A transaction is a group of statements the database treats as one unit:

BEGIN;            -- start (START TRANSACTION in MySQL, BEGIN TRANSACTION in SQL Server)
-- statements ...
COMMIT;           -- make every change permanent and visible
-- or
ROLLBACK;         -- undo every change since BEGIN

Until COMMIT, other sessions do not see your changes. If the session dies before COMMIT (crash, kill, network drop), the database rolls the transaction back for you.

#ACID with a bank transfer

Moving 500 from Asha to Ravi is two updates. ACID names the four promises that make it safe:

Here it is for real. Asha has 300, so the debit breaks the constraint:

CREATE TABLE accounts (
  name    text PRIMARY KEY,
  balance numeric(12,2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES ('asha', 300), ('ravi', 1000);

-- transfer 500 from asha to ravi
BEGIN;
UPDATE accounts SET balance = balance + 500 WHERE name = 'ravi';
UPDATE accounts SET balance = balance - 500 WHERE name = 'asha';
COMMIT;

SELECT * FROM accounts ORDER BY name;

CREATE TABLE
INSERT 0 2
BEGIN
UPDATE 1
psql:tbank.sql:10: ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"
DETAIL:  Failing row contains (asha, -200.00).
ROLLBACK
 name | balance 
------+---------
 asha |  300.00
 ravi | 1000.00
(2 rows)

Ravi's credit ran first and succeeded (UPDATE 1), yet his balance is still 1000. The failed debit doomed the whole transaction, and notice that our COMMIT came back as ROLLBACK: PostgreSQL refuses to commit a transaction that hit an error.

#Autocommit: the default you are already using

Outside BEGIN ... COMMIT, every statement is its own transaction and commits as soon as it finishes. That is autocommit, and it is the default almost everywhere:

The last row catches many people: a Python script that inserts rows and exits without commit() saves nothing.

#Errors abort the whole transaction in PostgreSQL

In PostgreSQL, once any statement in a transaction fails, the transaction is aborted. Every following statement is rejected with current transaction is aborted, commands ignored until end of transaction block, and the only way out is ROLLBACK (a COMMIT also just rolls back). MySQL and SQL Server behave differently by default: the failing statement is undone but the transaction stays open, so the next statements run and a COMMIT saves them. That difference is how half a batch ends up committed in a MySQL script that "worked" in PostgreSQL.

#SAVEPOINT: undo part of a transaction

A savepoint is a bookmark inside a transaction. ROLLBACK TO SAVEPOINT name undoes everything after it, clears the aborted state, and lets the transaction continue:

BEGIN;
-- step 1
SAVEPOINT before_step_2;
-- step 2 fails
ROLLBACK TO SAVEPOINT before_step_2;
COMMIT;   -- step 1 is saved

Savepoints are not nested transactions: nothing is permanent until the outer COMMIT. In psql, \set ON_ERROR_ROLLBACK interactive wraps each statement you type in a hidden savepoint, so a typo does not abort a long manual session.

#DDL inside transactions

In PostgreSQL (and SQL Server and SQLite), CREATE, ALTER and DROP are transactional: a migration that adds a column, creates a table and fails on the third step rolls back cleanly. A few PostgreSQL commands cannot run inside a transaction block at all: CREATE DATABASE, VACUUM and CREATE INDEX CONCURRENTLY.

MySQL and Oracle commit implicitly before and after most DDL. In MySQL, START TRANSACTION; DELETE ...; ALTER TABLE ...; ROLLBACK; cannot undo the DELETE, because the ALTER TABLE committed it. Never mix DDL into a data transaction in MySQL.

#Long-running and idle transactions hurt everyone

A transaction holds its locks until it ends, and in PostgreSQL an open transaction with a snapshot stops VACUUM from cleaning dead row versions created after it started. The classic culprit is a session idle in transaction: someone ran BEGIN; SELECT ... in a SQL client and went to lunch. Even that read holds a lock that blocks ALTER TABLE. Keep transactions short, never wait for a human inside one, and set idle_in_transaction_session_timeout on servers.

#Idempotent loads

A job is idempotent if running it twice gives the same result as running it once. Jobs get retried (a timeout, a scheduler that re-fires, a person clicking "rerun"), so every load must be. The standard pattern for a batch is: in one transaction, delete the slice you are about to load (a month, a day, a file), insert it, commit. Upserts on a key are the other pattern, covered in the next lesson on UPSERT and MERGE.

#Warehouses

• BigQuery runs each statement atomically. For several statements, use a multi-statement transaction inside a script: BEGIN TRANSACTION; ... COMMIT TRANSACTION;. DML on tables is supported in transactions; most DDL is not.

• Snowflake supports BEGIN ... COMMIT, but any DDL statement commits the open transaction first, as in MySQL.

• dbt and most orchestrators build a model into a new table and then swap it in, so readers never see a half-built table. The idea is the same: switch over in one atomic step.

#Worked example: a monthly load you can kill

We load the UCI Online Retail dataset (541,909 invoice lines, CC BY 4.0) into a sales table one month at a time, as a scheduled job would. PostgreSQL 16; every output below is from a real run.

CREATE TABLE raw_retail (
  invoice_no   text,
  stock_code   text,
  description  text,
  quantity     int,
  invoice_date text,
  unit_price   numeric(10,3),
  customer_id  int,
  country      text
);
\copy raw_retail FROM 'online-retail-dataset.csv' WITH (FORMAT csv, HEADER true)

-- typed staging table: the "files that arrived"
CREATE TABLE staging AS
SELECT invoice_no, stock_code, quantity, unit_price, customer_id, country,
       to_timestamp(invoice_date, 'MM/DD/YYYY HH24:MI')::timestamp AS invoiced_at
FROM raw_retail;

-- the target table, with a rule the business cares about
CREATE TABLE sales (
  invoice_no  text NOT NULL,
  stock_code  text NOT NULL,
  quantity    int  NOT NULL,
  unit_price  numeric(10,3) NOT NULL CHECK (unit_price >= 0),
  customer_id int,
  country     text,
  invoiced_at timestamp NOT NULL
);

-- history already loaded: December 2010 to July 2011
INSERT INTO sales SELECT * FROM staging WHERE invoiced_at < '2011-08-01';

CREATE TABLE
COPY 541909
SELECT 541909
CREATE TABLE
INSERT 0 285421

#Step 1: the naive load doubles data on a retry

The first version of the September job is a plain INSERT. The scheduler times out waiting for it and runs it again:

-- naive load of September, run twice (the second time is a "retry")
INSERT INTO sales
SELECT * FROM staging
WHERE invoiced_at >= '2011-09-01' AND invoiced_at < '2011-10-01';

INSERT INTO sales
SELECT * FROM staging
WHERE invoiced_at >= '2011-09-01' AND invoiced_at < '2011-10-01';

SELECT count(*) AS sep_lines
FROM sales
WHERE invoiced_at >= '2011-09-01' AND invoiced_at < '2011-10-01';

INSERT 0 50226
INSERT 0 50226
 sep_lines 
-----------
    100452
(1 row)

September now has 100,452 lines instead of 50,226, and September revenue in every dashboard is doubled. The job is not idempotent.

#Step 2: delete then insert, killed halfway, without a transaction

The fix everyone tries first: delete the month, then insert it. We run it in autocommit and kill it from a second session between the two statements. pg_sleep(10) stands in for an insert that takes a while, which real loads do. Run the two sessions side by side:

SET application_name = 'monthly_load';

DELETE FROM sales
WHERE invoiced_at >= '2011-09-01' AND invoiced_at < '2011-10-01';

SELECT pg_sleep(10);  -- stands in for a slow INSERT

INSERT INTO sales
SELECT * FROM staging
WHERE invoiced_at >= '2011-09-01' AND invoiced_at < '2011-10-01';

SET
DELETE 100452
psql:t4.sql:6: FATAL:  terminating connection due to administrator command
psql:t4.sql:6: server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
psql:t4.sql:6: error: connection to server was lost

SELECT pg_sleep(2);
SELECT pg_terminate_backend(pid) AS killed
FROM pg_stat_activity
WHERE application_name = 'monthly_load';

 pg_sleep 
----------
 
(1 row)
 
 killed 
--------
 t
(1 row)

Now count September:

SELECT count(*) AS sep_lines
FROM sales
WHERE invoiced_at >= '2011-09-01' AND invoiced_at < '2011-10-01';

 sep_lines 
-----------
         0
(1 row)

The DELETE committed the moment it finished. The insert never ran. September is gone, and anything reading sales now reports zero revenue for the month. Setting application_name is a good habit on its own: it is how you find your job in pg_stat_activity.

#Step 3: the same load in a transaction, killed halfway

Put September back once (INSERT 0 50226), then wrap the job in BEGIN ... COMMIT and kill it at the same point. This time session 2 also looks at the table while the load is running:

SET application_name = 'monthly_load';

BEGIN;

DELETE FROM sales
WHERE invoiced_at >= '2011-09-01' AND invoiced_at < '2011-10-01';

SELECT pg_sleep(10);  -- stands in for a slow INSERT

INSERT INTO sales
SELECT * FROM staging
WHERE invoiced_at >= '2011-09-01' AND invoiced_at < '2011-10-01';

COMMIT;

SET
BEGIN
DELETE 50226
psql:t8.sql:8: FATAL:  terminating connection due to administrator command
psql:t8.sql:8: server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
psql:t8.sql:8: error: connection to server was lost

SELECT pg_sleep(2);

-- what other sessions see while the load is running
SELECT count(*) AS sep_lines_during_load
FROM sales
WHERE invoiced_at >= '2011-09-01' AND invoiced_at < '2011-10-01';

SELECT pg_terminate_backend(pid) AS killed
FROM pg_stat_activity
WHERE application_name = 'monthly_load';

SELECT pg_sleep(1);

SELECT count(*) AS sep_lines_after_kill
FROM sales
WHERE invoiced_at >= '2011-09-01' AND invoiced_at < '2011-10-01';

 pg_sleep 
----------
 
(1 row)
 
 sep_lines_during_load 
-----------------------
                 50226
(1 row)
 
 killed 
--------
 t
(1 row)
 
 pg_sleep 
----------
 
(1 row)
 
 sep_lines_after_kill 
----------------------
                50226
(1 row)

Two ACID properties in one run:

• Isolation: session 1 had already deleted all 50,226 rows, yet session 2 still saw 50,226 during the load. Dashboards never see an empty month.

• Atomicity: the kill rolled the delete back. September is exactly as it was.

Run the same script without the kill and it commits, replacing September with a fresh copy. Run it again and you get the same 50,226 rows: the job is now idempotent and all or nothing.

#Step 4: a bad row aborts the whole month

August contains two "Adjust bad debt" lines with a negative price, which the CHECK constraint rejects. Here is the same transactional load for August, with a check query added before COMMIT:

BEGIN;

DELETE FROM sales
WHERE invoiced_at >= '2011-08-01' AND invoiced_at < '2011-09-01';

INSERT INTO sales
SELECT * FROM staging
WHERE invoiced_at >= '2011-08-01' AND invoiced_at < '2011-09-01';

SELECT count(*) FROM sales;

COMMIT;

BEGIN
DELETE 0
psql:t10.sql:8: ERROR:  new row for relation "sales" violates check constraint "sales_unit_price_check"
DETAIL:  Failing row contains (A563186, B, 1, -11062.060, null, United Kingdom, 2011-08-12 14:51:00).
psql:t10.sql:10: ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK

Read the output line by line. Two bad rows out of 35,284 stopped all of August, which is consistency doing its job: the table never holds a negative price. After the error, even a harmless SELECT count(*) is refused, and COMMIT reports ROLLBACK. If your job runner ignores errors and checks only that the script finished, it will report success for a month that never loaded. Use psql -v ON_ERROR_STOP=1 (or check the exit code) in every scheduled script.

#Step 5: SAVEPOINT keeps the good work

Suppose one batch loads October and then retries August. With a savepoint before August, its failure does not throw away October:

BEGIN;

INSERT INTO sales
SELECT * FROM staging
WHERE invoiced_at >= '2011-10-01' AND invoiced_at < '2011-11-01';

SAVEPOINT august;

INSERT INTO sales
SELECT * FROM staging
WHERE invoiced_at >= '2011-08-01' AND invoiced_at < '2011-09-01';

ROLLBACK TO SAVEPOINT august;

COMMIT;

SELECT date_trunc('month', invoiced_at)::date AS month, count(*) AS lines
FROM sales
WHERE invoiced_at >= '2011-07-01'
GROUP BY 1 ORDER BY 1;

BEGIN
INSERT 0 60742
SAVEPOINT
psql:t11.sql:11: ERROR:  new row for relation "sales" violates check constraint "sales_unit_price_check"
DETAIL:  Failing row contains (A563186, B, 1, -11062.060, null, United Kingdom, 2011-08-12 14:51:00).
ROLLBACK
COMMIT
   month    | lines 
------------+-------
 2011-07-01 | 39518
 2011-09-01 | 50226
 2011-10-01 | 60742
(3 rows)

October (60,742 lines) committed; August is still missing. In a script the ROLLBACK TO SAVEPOINT normally lives in error handling (a try/except in Python, EXCEPTION in a PL/pgSQL block) and is paired with an alert. Silently skipping a month is only acceptable if someone hears about it.

#Step 6: quarantine bad rows instead of failing

The production fix for August is to load the good rows and keep the bad ones in a rejects table for someone to review, all in one transaction so the two tables never disagree:

CREATE TABLE sales_rejects (LIKE staging, reason text NOT NULL);

BEGIN;

DELETE FROM sales
WHERE invoiced_at >= '2011-08-01' AND invoiced_at < '2011-09-01';
DELETE FROM sales_rejects
WHERE invoiced_at >= '2011-08-01' AND invoiced_at < '2011-09-01';

INSERT INTO sales
SELECT * FROM staging
WHERE invoiced_at >= '2011-08-01' AND invoiced_at < '2011-09-01'
  AND unit_price >= 0;

INSERT INTO sales_rejects
SELECT *, 'negative unit_price' FROM staging
WHERE invoiced_at >= '2011-08-01' AND invoiced_at < '2011-09-01'
  AND unit_price < 0;

COMMIT;

Running it twice in a row, as a retry would:

BEGIN
DELETE 0
DELETE 0
INSERT 0 35282
INSERT 0 2
COMMIT
BEGIN
DELETE 35282
DELETE 2
INSERT 0 35282
INSERT 0 2
COMMIT

SELECT (SELECT count(*) FROM sales
        WHERE invoiced_at >= '2011-08-01' AND invoiced_at < '2011-09-01') AS aug_loaded,
       (SELECT count(*) FROM sales_rejects) AS aug_rejected;

 aug_loaded | aug_rejected 
------------+--------------
      35282 |            2
(1 row)

The second run deleted exactly what the first inserted and put it back: 35,282 loaded plus 2 rejected is the full 35,284 lines of August, with nothing doubled. The rejects table needs its own delete for the month, or a retry would double the rejects instead.

#Step 7: DDL rolls back too (in PostgreSQL)

BEGIN;

ALTER TABLE sales ADD COLUMN loaded_at timestamptz DEFAULT now();
DROP TABLE sales_rejects;

ROLLBACK;

SELECT count(*) AS sales_columns,
       to_regclass('sales_rejects') AS rejects_table
FROM information_schema.columns
WHERE table_name = 'sales';

BEGIN
ALTER TABLE
DROP TABLE
ROLLBACK
 sales_columns | rejects_table 
---------------+---------------
             7 | sales_rejects
(1 row)

The new column is gone and the dropped table is back. In MySQL the same script would have committed at the ALTER TABLE, and the DROP TABLE would be permanent.

#Step 8: an idle transaction blocks a migration and VACUUM

Session 1 is a forgotten SQL client tab: it opened a transaction, ran one read and stopped. Session 2 is everyone else.

SET application_name = 'forgotten_tab';
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM sales;
-- ...and nothing more

SET
BEGIN
 count  
--------
 431671
(1 row)

SELECT pid, application_name, state,
       date_trunc('second', now() - xact_start) AS open_for
FROM pg_stat_activity
WHERE state = 'idle in transaction';

SET lock_timeout = '3s';
ALTER TABLE sales ADD COLUMN note text;

BEGIN;
DELETE FROM sales
WHERE invoiced_at >= '2011-02-01' AND invoiced_at < '2011-03-01';
INSERT INTO sales
SELECT * FROM staging
WHERE invoiced_at >= '2011-02-01' AND invoiced_at < '2011-03-01';
COMMIT;

VACUUM sales;

SELECT n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'sales';

 pid  | application_name |        state        | open_for 
------+------------------+---------------------+----------
 5533 | forgotten_tab    | idle in transaction | 00:00:01
(1 row)
 
SET
psql:tb.sql:7: ERROR:  canceling statement due to lock timeout
BEGIN
DELETE 27707
INSERT 0 27707
COMMIT
VACUUM
 n_dead_tup 
------------
      27707
(1 row)

Three things happened:

1. pg_stat_activity shows the culprit, its pid and how long it has been open. In production you would look for open_for in hours.

2. ALTER TABLE needs an exclusive lock, and the forgotten tab's plain SELECT holds a shared lock on sales until its transaction ends. Without lock_timeout the migration would wait forever, and worse, every new query on sales would queue behind the waiting ALTER. Always set a lock_timeout when you run migrations.

3. The February reload left 27,707 dead row versions (the deleted copies). VACUUM could not remove any of them, because the forgotten tab's snapshot might still need to see them. On a busy table this is how tables bloat to several times their size.

After the forgotten tab ends its transaction (or is terminated with pg_terminate_backend(5533)), the same VACUUM cleans up:

-- after the forgotten session has ended
VACUUM sales;

SELECT n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'sales';

VACUUM
 n_dead_tup 
------------
          0
(1 row)

#Common mistakes

• Running a multi-step change in autocommit. Step 2 lost a whole month to one kill. If statements belong together, put them in BEGIN ... COMMIT.

• Loads that are not idempotent. A plain INSERT doubled September on a retry. Delete the slice and insert it in one transaction, or upsert on a key.

• Ignoring errors in scripts. After an error PostgreSQL rolls back everything, but a runner that does not check the exit code reports success. Use ON_ERROR_STOP=1 and fail the job loudly.

• Expecting MySQL to behave like PostgreSQL. In MySQL a failed statement does not abort the transaction, and DDL commits whatever came before it.

• Forgetting commit() in application code. Python drivers open a transaction for you; the script exits and the rows vanish.

• Holding a transaction open while waiting. Calling an API, sleeping, or waiting for user input inside a transaction holds locks and blocks VACUUM. Do the slow work first, then open the transaction, write, and commit.

• Huge single transactions with no plan. Reloading three years in one transaction holds locks for an hour and produces millions of dead rows at once. Load in slices (a month, a day) with one transaction per slice, as this lesson does.

• Treating a savepoint as a commit. Nothing after BEGIN is permanent until the outer COMMIT, savepoints included.

#Interview angle

#Practice task

Use Olist's orders file (olist_orders_dataset.csv, CC BY-NC-SA 4.0) to build a monthly load you would trust in production.

1. Load the file into olist_orders, and create orders_fact (order_id PRIMARY KEY, customer_id, order_status, purchased_at) and load_log (month PRIMARY KEY, rows_loaded, loaded_at).

2. Write load_month.sql, which takes the month as a psql variable (psql -v month=2018-01-01 -f load_month.sql) and, in one transaction, replaces that month in orders_fact and writes its row in load_log.

3. Load January 2018 twice and February 2018 once. You should have one log row per month and no duplicates.

4. Add SELECT pg_sleep(10); before COMMIT, run March 2018, kill it from another session, and prove neither table changed.

CREATE TABLE olist_orders (
  order_id text PRIMARY KEY,
  customer_id text,
  order_status text,
  order_purchase_timestamp timestamp,
  order_approved_at timestamp,
  order_delivered_carrier_date timestamp,
  order_delivered_customer_date timestamp,
  order_estimated_delivery_date timestamp
);
\copy olist_orders FROM 'olist_orders_dataset.csv' WITH (FORMAT csv, HEADER true)

CREATE TABLE orders_fact (
  order_id     text PRIMARY KEY,
  customer_id  text NOT NULL,
  order_status text NOT NULL,
  purchased_at timestamp NOT NULL
);

CREATE TABLE load_log (
  month       date PRIMARY KEY,
  rows_loaded int NOT NULL,
  loaded_at   timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE
COPY 99441
CREATE TABLE
CREATE TABLE

-- usage: psql -v month=2018-01-01 -f load_month.sql
BEGIN;

DELETE FROM orders_fact
WHERE purchased_at >= date :'month'
  AND purchased_at <  date :'month' + interval '1 month';
DELETE FROM load_log WHERE month = date :'month';

INSERT INTO orders_fact
SELECT order_id, customer_id, order_status, order_purchase_timestamp
FROM olist_orders
WHERE order_purchase_timestamp >= date :'month'
  AND order_purchase_timestamp <  date :'month' + interval '1 month';

INSERT INTO load_log (month, rows_loaded)
SELECT date :'month', count(*)
FROM orders_fact
WHERE purchased_at >= date :'month'
  AND purchased_at <  date :'month' + interval '1 month';

COMMIT;

BEGIN
DELETE 0
DELETE 0
INSERT 0 7269
INSERT 0 1
COMMIT
BEGIN
DELETE 7269
DELETE 1
INSERT 0 7269
INSERT 0 1
COMMIT
BEGIN
DELETE 0
DELETE 0
INSERT 0 6728
INSERT 0 1
COMMIT

SELECT (SELECT count(*) FROM orders_fact) AS fact_rows,
       (SELECT count(*) FROM load_log) AS log_rows;
SELECT month, rows_loaded FROM load_log ORDER BY month;

 fact_rows | log_rows 
-----------+----------
     13997 |        2
(1 row)
 
   month    | rows_loaded 
------------+-------------
 2018-01-01 |        7269
 2018-02-01 |        6728
(2 rows)

SELECT pg_sleep(2);
SELECT pg_terminate_backend(pid) AS killed
FROM pg_stat_activity WHERE application_name = 'load_month';
SELECT pg_sleep(1);
SELECT (SELECT count(*) FROM orders_fact) AS fact_rows,
       (SELECT count(*) FROM load_log) AS log_rows;

BEGIN
DELETE 0
DELETE 0
INSERT 0 7211
INSERT 0 1
psql:load_slow.sql:21: FATAL:  terminating connection due to administrator command
psql:load_slow.sql:21: server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
psql:load_slow.sql:21: error: connection to server was lost

 fact_rows | log_rows 
-----------+----------
     13997 |        2
(1 row)

#Further reading

• PostgreSQL tutorial: transactions: BEGIN, COMMIT, ROLLBACK and savepoints with examples.

• PostgreSQL: SAVEPOINT and routine vacuuming.

• MySQL: statements that cause an implicit commit.

• BigQuery multi-statement transactions and Snowflake transactions.

• Earlier lessons: SQL INSERT, UPDATE and DELETE without breaking data for safe single statements and Database normalization: 1NF, 2NF, 3NF with examples for the table design these loads fill.

• UCI Online Retail dataset (CC BY 4.0), CSV copy: online-retail-dataset.csv.

• Olist datasets (CC BY-NC-SA 4.0).