What Is a Transaction?

LinkedIn Hook

"Your bank transfer debited the sender, then the database crashed. The receiver never got the money. Your code had no transaction."

Most developers learn SQL one statement at a time. SELECT, INSERT, UPDATE, DELETE — each runs, each succeeds or fails on its own, and the database moves on. That mental model works fine until the day you need two statements to either both happen or neither happen, and you discover that without a transaction, "neither" is a state your database is perfectly happy to skip past.

A transaction is the database's promise that a group of statements will be treated as a single, indivisible unit of work. Either every statement commits together, or every statement rolls back together — there is no middle ground where the sender lost $100 and the receiver gained nothing. This is the bedrock of every system that touches money, inventory, bookings, or anything else where partial failure is worse than total failure.

The mechanics are three keywords: BEGIN starts the work, COMMIT makes it permanent, ROLLBACK throws it away. On top of that, savepoints let you create checkpoints inside a transaction so you can undo part of the work without abandoning all of it. And lurking underneath is autocommit mode — the default in most SQL clients — which silently wraps every single statement in its own one-statement transaction unless you say otherwise.

In Lesson 11.1, I break down what a transaction actually is: the unit-of-work concept, BEGIN/COMMIT/ROLLBACK in PostgreSQL, the bank transfer pattern, savepoints for partial rollback, and the autocommit mode that decides whether your statements run inside transactions you wrote or transactions the driver wrote for you.

Read the full lesson -> [link]

#SQL #Database #PostgreSQL #Transactions #ACID #BackendDevelopment #InterviewPrep

---

---

What You'll Learn

• What a transaction is and why "unit of work" is the only mental model that matters
• How BEGIN, COMMIT, and ROLLBACK control the lifetime of a transaction in PostgreSQL
• The canonical bank transfer example and why every textbook starts there
• How savepoints let you partially undo work without abandoning the whole transaction
• Autocommit mode — the default that wraps every statement in its own transaction
• How PostgreSQL and MySQL differ in transaction syntax and default behavior
• The difference between an explicit transaction and an implicit single-statement transaction
• How application code should structure transactions to avoid leaking connections

---

The ATM Transfer Analogy — All or Nothing

Imagine you walk up to an ATM and ask it to transfer $100 from your checking account to your savings account. Behind the scenes, the ATM has to do two things: subtract $100 from checking, then add $100 to savings. Now picture the worst possible moment for a power cut — right between those two steps. Checking is down $100. Savings is unchanged. The bank just made $100 vanish into thin air.

No real bank operates this way. The ATM treats the transfer as a single indivisible action. Either both halves succeed and the receipt prints, or both halves are undone and you walk away with the same balances you started with. There is no scenario in which the bank "almost transferred" the money. The atomicity is the entire point — if it could fail halfway, customers would lose trust in five minutes.

A database transaction is the exact same idea. You wrap a group of statements in BEGIN ... COMMIT, and the database promises that either every statement in the group is permanently saved, or none of them are. If anything goes wrong — a crash, a constraint violation, a deadlock, an explicit ROLLBACK from your code — the database rewinds time and erases every change the transaction made, as if you never typed any of those statements. Your other tables, your other rows, your other sessions never see the half-done state.

+---------------------------------------------------------------+
|           THE ATM TRANSFER                                    |
+---------------------------------------------------------------+
|                                                                |
|   WITHOUT A TRANSACTION                                        |
|   +-----------+         CRASH         +-----------+            |
|   | Checking  | -$100  -------->      | Savings   |            |
|   |  $500     |                       |  $200     |            |
|   +-----------+                       +-----------+            |
|        |                                    ^                  |
|        v                                    |                  |
|     Checking = $400      Savings still = $200                  |
|     $100 LOST FOREVER                                          |
|                                                                |
|   WITH A TRANSACTION                                           |
|   BEGIN;                                                       |
|     UPDATE checking ... -$100;                                 |
|     UPDATE savings  ... +$100;     <- crash here               |
|   COMMIT;                <- never reached                      |
|                                                                |
|     Database rewinds: checking back to $500.                   |
|     Both balances are exactly as they were.                    |
|     ALL OR NOTHING.                                            |
|                                                                |
+---------------------------------------------------------------+

This guarantee — that a group of statements either all commit or all roll back — is called atomicity, and it is the A in ACID. The other three letters (Consistency, Isolation, Durability) are the next lesson; for now, focus on the unit of work.

---

BEGIN, COMMIT, ROLLBACK — The Three Keywords

A PostgreSQL transaction has exactly three control statements. Everything else is just SQL running inside the bracket they create.

• BEGIN (or START TRANSACTION) opens a new transaction. Every subsequent statement on this connection runs inside it, until you end the transaction.
• COMMIT ends the transaction and permanently saves every change it made. After COMMIT returns, the data is durable — it survives crashes, power loss, and reboots.
• ROLLBACK ends the transaction and discards every change it made. After ROLLBACK, the database looks exactly as it did before BEGIN. None of your INSERTs, UPDATEs, or DELETEs are visible to anyone, ever.

-- Sample table used throughout this lesson
CREATE TABLE accounts (
  id       SERIAL PRIMARY KEY,
  owner    TEXT NOT NULL,
  balance  NUMERIC(12, 2) NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts (owner, balance) VALUES
  ('Alice', 500.00),
  ('Bob',   200.00);

-- A successful transaction: both updates commit together
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE owner = 'Alice';
  UPDATE accounts SET balance = balance + 100 WHERE owner = 'Bob';
COMMIT;

-- After COMMIT:
SELECT * FROM accounts;
-- id | owner | balance
-- ---+-------+--------
--  1 | Alice | 400.00
--  2 | Bob   | 300.00

If anything between BEGIN and COMMIT fails, you call ROLLBACK and the database undoes everything:

-- A transaction that gets rolled back: nothing is saved
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE owner = 'Alice';
  -- Application detects a problem: Bob's account is frozen, fraud alert,
  -- the API call to verify the receiver failed, etc.
  -- We must not leave Alice down $100.
ROLLBACK;

-- After ROLLBACK:
SELECT * FROM accounts;
-- id | owner | balance
-- ---+-------+--------
--  1 | Alice | 400.00    <- unchanged from the previous commit
--  2 | Bob   | 300.00    <- unchanged from the previous commit

The mental model: BEGIN opens a sandbox, COMMIT publishes it, ROLLBACK throws it away. While you are inside the sandbox, your changes are visible only to your own session. No other connection sees them, no other transaction can read them, until you COMMIT. This isolation is the I in ACID and is the topic of later lessons.

Note on syntax: PostgreSQL accepts BEGIN, BEGIN TRANSACTION, and START TRANSACTION interchangeably — they all do the same thing. The SQL standard prefers START TRANSACTION, but BEGIN is shorter and is what you will see in most production code. MySQL also supports both, with the same meaning.

---

The Bank Transfer — The Canonical Example

Every transactions textbook starts with a bank transfer because it is the simplest possible scenario where the cost of partial failure is obvious and unacceptable. Let us walk through the full PostgreSQL version with error handling that an application would actually use.

-- Reset the example
UPDATE accounts SET balance = 500 WHERE owner = 'Alice';
UPDATE accounts SET balance = 200 WHERE owner = 'Bob';

-- Transfer $300 from Alice to Bob, atomically
BEGIN;

  -- Step 1: debit the sender
  UPDATE accounts
     SET balance = balance - 300
   WHERE owner = 'Alice';

  -- Step 2: credit the receiver
  UPDATE accounts
     SET balance = balance + 300
   WHERE owner = 'Bob';

  -- Step 3 (optional): verify both accounts still satisfy invariants.
  -- The CHECK constraint on balance >= 0 already prevents Alice from
  -- going negative, but explicit verification makes intent obvious.
  -- If anything is wrong, ROLLBACK and tell the caller.

COMMIT;

-- After COMMIT:
SELECT owner, balance FROM accounts ORDER BY id;
-- owner | balance
-- ------+--------
-- Alice | 200.00
-- Bob   | 500.00

Now let's see what happens when the transfer would push Alice into the red. The CHECK constraint on balance >= 0 rejects the UPDATE, the transaction enters an error state, and the entire thing rolls back:

-- Reset
UPDATE accounts SET balance = 500 WHERE owner = 'Alice';
UPDATE accounts SET balance = 200 WHERE owner = 'Bob';

-- Try to transfer $999 (more than Alice has)
BEGIN;
  UPDATE accounts SET balance = balance - 999 WHERE owner = 'Alice';
  -- ERROR:  new row for relation "accounts" violates check constraint
  --         "accounts_balance_check"
  -- DETAIL: Failing row contains (1, Alice, -499.00).

  -- In PostgreSQL, after an error the transaction is in an "aborted"
  -- state. Any further statements return:
  --   ERROR: current transaction is aborted, commands ignored until
  --          end of transaction block
  -- You MUST issue ROLLBACK (or COMMIT, which is also treated as ROLLBACK
  -- in this state) to leave the transaction.
  UPDATE accounts SET balance = balance + 999 WHERE owner = 'Bob';
  -- ERROR: current transaction is aborted, commands ignored until end
  --        of transaction block
ROLLBACK;

-- After ROLLBACK: nothing changed
SELECT owner, balance FROM accounts ORDER BY id;
-- owner | balance
-- ------+--------
-- Alice | 500.00
-- Bob   | 200.00

This is critical PostgreSQL behavior to internalize: once any statement in a transaction errors, every subsequent statement is rejected until you ROLLBACK (or use a savepoint to recover). MySQL is more lenient — by default it lets you keep issuing statements after an error and only rolls back the failing statement. The Postgres behavior is safer because it forces you to acknowledge the error explicitly, but it is also stricter and surprises developers coming from MySQL.

---

Savepoints — Partial Rollback Inside a Transaction

Sometimes "all or nothing" is too coarse. You want to try an operation, and if it fails, undo just that operation while keeping everything else the transaction has done so far. This is what savepoints are for. A savepoint is a named checkpoint inside a transaction. You can roll back to a savepoint without abandoning the whole transaction, then keep going.

-- Reset
UPDATE accounts SET balance = 500 WHERE owner = 'Alice';
UPDATE accounts SET balance = 200 WHERE owner = 'Bob';
INSERT INTO accounts (owner, balance) VALUES ('Carol', 100.00)
  ON CONFLICT DO NOTHING;

-- Multi-step batch where one optional step is allowed to fail
BEGIN;

  -- Step 1: definite transfer from Alice to Bob
  UPDATE accounts SET balance = balance - 50 WHERE owner = 'Alice';
  UPDATE accounts SET balance = balance + 50 WHERE owner = 'Bob';

  -- Step 2: try to give Carol a bonus, but it's optional
  SAVEPOINT bonus_attempt;
    UPDATE accounts SET balance = balance + 9999 WHERE owner = 'Carol';
    -- Suppose the application now decides this bonus is too generous
    -- and wants to undo just this step, NOT the Alice/Bob transfer.
  ROLLBACK TO SAVEPOINT bonus_attempt;

  -- Step 3: continue with other work
  UPDATE accounts SET balance = balance + 1 WHERE owner = 'Carol';

COMMIT;

-- After COMMIT:
SELECT owner, balance FROM accounts ORDER BY id;
-- owner | balance
-- ------+--------
-- Alice | 450.00    <- step 1 stuck
-- Bob   | 250.00    <- step 1 stuck
-- Carol | 101.00    <- step 2 undone, step 3 stuck

Savepoints also rescue you from the "aborted transaction" state in PostgreSQL. If you wrap a risky statement in a savepoint and it errors, you can ROLLBACK TO that savepoint and the transaction returns to a normal (non-aborted) state, ready to continue.

BEGIN;
  INSERT INTO accounts (owner, balance) VALUES ('Dave', 50.00);

  SAVEPOINT try_insert;
    -- This will fail (CHECK constraint: balance >= 0)
    INSERT INTO accounts (owner, balance) VALUES ('Eve', -10.00);
    -- ERROR: violates check constraint
  ROLLBACK TO SAVEPOINT try_insert;

  -- Without the savepoint, the whole transaction would be aborted.
  -- With the savepoint, only Eve's failed insert is undone, and we
  -- can keep going.
  INSERT INTO accounts (owner, balance) VALUES ('Eve', 10.00);

COMMIT;

-- After COMMIT: Dave and Eve (with 10) both inserted; the bad Eve insert is gone.

You can also RELEASE SAVEPOINT name to drop a savepoint without rolling back to it — the changes since that savepoint stay, and the savepoint itself is forgotten. Most ORMs implement nested transactions internally using savepoints with auto-generated names.

+---------------------------------------------------------------+
|           SAVEPOINTS — A LADDER INSIDE A TRANSACTION          |
+---------------------------------------------------------------+
|                                                                |
|   BEGIN                          <- transaction starts         |
|     UPDATE ... (kept)                                          |
|     SAVEPOINT a                                                |
|       UPDATE ... (kept)                                        |
|       SAVEPOINT b                                              |
|         UPDATE ... (will undo)                                 |
|       ROLLBACK TO b              <- only the inner UPDATE gone |
|       UPDATE ... (kept)                                        |
|     RELEASE a                    <- savepoint a forgotten      |
|     UPDATE ... (kept)                                          |
|   COMMIT                         <- everything still kept hits |
|                                                                |
+---------------------------------------------------------------+

---

Autocommit Mode — The Default You Did Not Choose

Here is the trap that catches most developers exactly once: by default, PostgreSQL's psql client and almost every database driver run in autocommit mode. Autocommit means that every single statement you issue is automatically wrapped in its own one-statement transaction that commits the moment the statement returns. You never typed BEGIN, you never typed COMMIT, but every statement is a transaction whether you like it or not.

-- In autocommit mode (the default):
UPDATE accounts SET balance = balance - 100 WHERE owner = 'Alice';
-- Driver implicitly does: BEGIN; UPDATE ...; COMMIT;
-- The change is permanent the instant this returns.

UPDATE accounts SET balance = balance + 100 WHERE owner = 'Bob';
-- Driver implicitly does: BEGIN; UPDATE ...; COMMIT;
-- This is a SEPARATE transaction.

-- If the process crashes between these two lines, Alice is down $100
-- and Bob never received it. Two separate one-statement transactions
-- give you no atomicity across them.

Autocommit is convenient for ad-hoc queries and one-off scripts. It is dangerous for any operation that must be atomic across multiple statements. The fix is to be explicit:

-- Force a multi-statement transaction by typing BEGIN first.
-- The driver sees BEGIN and stops auto-committing until COMMIT or ROLLBACK.
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE owner = 'Alice';
  UPDATE accounts SET balance = balance + 100 WHERE owner = 'Bob';
COMMIT;

In application code, the pattern is the same — your driver or ORM exposes a method like connection.beginTransaction() (node-postgres, mysql2), db.Begin() (Go's database/sql), connection.begin() (SQLAlchemy), or with transaction.atomic(): (Django). Calling that method disables autocommit on the connection until you commit or roll back.

// node-postgres example: explicit transaction wrapper
async function transfer(client, fromId, toId, amount) {
  // Take a single connection from the pool and use it for the whole txn.
  // Do NOT use the pool directly across statements -- you would get
  // different connections and they would not share a transaction.
  try {
    await client.query('BEGIN');
    await client.query(
      'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
      [amount, fromId],
    );
    await client.query(
      'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
      [amount, toId],
    );
    await client.query('COMMIT');
  } catch (err) {
    // ANY error inside the transaction must trigger ROLLBACK,
    // otherwise the connection is returned to the pool in an
    // aborted state and the next user of it will see errors.
    await client.query('ROLLBACK');
    throw err;
  }
}

The single most common bug in transaction code is forgetting the ROLLBACK in the error path. Without it, a thrown exception leaves the connection inside an open transaction, the connection goes back to the pool, the next request grabs it, and now their queries are running inside your half-failed transaction. Always wrap transactions in try/catch (or your language's equivalent) with ROLLBACK in the catch.

Napkin AI Visual Prompt: "Dark navy gradient (#0a0f1f -> #111a2e). Two side-by-side execution diagrams. LEFT box labeled 'Autocommit -> Two Separate Transactions' shows two small boxes each containing 'BEGIN; UPDATE; COMMIT;' with a rose (#ff5c8a) lightning bolt between them and the caption 'CRASH HERE -> $100 LOST'. RIGHT box labeled 'Explicit Transaction -> Atomic' shows one large sky blue (#4fc3f7) box containing 'BEGIN; UPDATE; UPDATE; COMMIT;' with both updates inside, captioned 'CRASH HERE -> ROLLBACK SAFE'. White monospace labels throughout."

---

PostgreSQL vs MySQL — Differences to Remember

The transaction concept is universal, but the syntax and defaults differ in small ways that bite portability.

+---------------------------------------------------------------+
|           POSTGRES vs MYSQL TRANSACTION DEFAULTS              |
+---------------------------------------------------------------+
|                                                                |
|   BEHAVIOR              | PostgreSQL    | MySQL (InnoDB)       |
|   ----------------------+---------------+---------------       |
|   Autocommit default    | ON            | ON                   |
|   BEGIN keyword         | BEGIN / START | BEGIN / START        |
|   On statement error    | TXN aborted,  | Only failing stmt    |
|                         | reject all    | rolled back; rest    |
|                         | until ROLLBACK| of txn keeps going   |
|   DDL inside txn        | YES (CREATE,  | NO -- DDL implicitly |
|                         | DROP, ALTER   | commits the current  |
|                         | are trans.)   | transaction          |
|   Savepoints            | YES           | YES                  |
|   Default isolation     | READ          | REPEATABLE READ      |
|                         | COMMITTED     |                      |
|                                                                |
+---------------------------------------------------------------+

The "DDL inside transaction" difference is huge in practice: in PostgreSQL you can write a migration that creates a table, inserts data, alters another table, and either commits the whole thing or rolls everything back if the script crashes halfway. In MySQL, the moment you issue CREATE TABLE inside a transaction, MySQL silently commits whatever was open and starts a fresh transaction — your earlier work is now permanent and unrollback-able. Schema migration tools paper over this difference, but you should know it exists.

---

Common Mistakes

1. Forgetting ROLLBACK in error handling. A try block that issues BEGIN and statements but no ROLLBACK in catch leaves the connection in an open or aborted transaction. The connection returns to the pool poisoned, and the next request that grabs it sees "current transaction is aborted" errors that have nothing to do with their code. Always wrap transactional code in try/catch (or equivalent) and call ROLLBACK in every error path.

2. Using different connections for different statements in one "transaction". Transactions are a property of a connection, not of a database session as you imagine it. If you call pool.query('BEGIN') then pool.query('UPDATE ...'), the pool may hand you a different connection for the second call, and your UPDATE runs outside any transaction at all. Always check out a single client/connection from the pool and use it for the entire BEGIN-to-COMMIT lifetime.

3. Assuming autocommit gives you atomicity across statements. Autocommit wraps each statement in its own transaction. Two statements in autocommit mode are two separate transactions, with no atomicity between them. If a crash, network drop, or exception happens between statement one and statement two, statement one is permanently committed and statement two never runs. Multi-statement atomicity always requires an explicit transaction.

4. Holding transactions open across user input or external API calls. A common anti-pattern: BEGIN, fetch a row, ask the user a question (or call an external API), then COMMIT after the response. You just held an open transaction — and any locks it acquired — for the entire duration of the user's coffee break. Other sessions waiting on those rows pile up, the connection pool exhausts, and the database falls over. Transactions should be as short as possible. Read what you need, do the writes, commit immediately. Never await user input inside a transaction.

5. Mixing PostgreSQL and MySQL error semantics in your head. Postgres aborts the entire transaction on the first error; MySQL only rolls back the failing statement. Code ported from MySQL to Postgres often issues "fix-up" statements after an error and is shocked when they all fail with "current transaction is aborted." If you want MySQL-like recovery in Postgres, wrap risky statements in savepoints — that is exactly what they are for.

---

Interview Questions

1. "What is a database transaction and why does it matter?"

A transaction is a group of one or more SQL statements that the database treats as a single, indivisible unit of work. The database guarantees that either every statement in the group is permanently saved (committed), or none of them are (rolled back) — there is no possible state in which some statements committed and others did not. This property is called atomicity, the A in ACID. It matters because real-world operations almost always involve multiple statements that must succeed or fail together: a bank transfer is two updates, an order is an insert into orders plus updates to inventory plus an insert into payments, a user signup is an insert into users plus an insert into profiles. Without transactions, any failure between those statements — a crash, a constraint violation, a network drop — leaves the database in an inconsistent state that someone has to clean up by hand. With transactions, the database cleans it up itself by rewinding the partial work as if it never happened.

2. "Walk me through BEGIN, COMMIT, and ROLLBACK in PostgreSQL. What happens after each?"

BEGIN (or START TRANSACTION) opens a new transaction on the current connection. From that point on, every statement you issue runs inside the transaction sandbox: changes are visible to your own session but invisible to every other connection. COMMIT ends the transaction successfully and makes every change permanent and durable — after COMMIT returns, the changes survive crashes, power loss, and reboots, and they become visible to other sessions. ROLLBACK ends the transaction by discarding every change it made, returning the database to exactly the state it was in before BEGIN. In PostgreSQL, there is one important gotcha: after any statement inside the transaction errors, the transaction enters an "aborted" state, and every subsequent statement is rejected with "current transaction is aborted, commands ignored until end of transaction block." You must issue ROLLBACK to leave that state — even COMMIT in an aborted transaction is treated as ROLLBACK. This is stricter than MySQL, which only rolls back the failing statement and lets you keep going.

3. "What are savepoints and when would you use them?"

A savepoint is a named checkpoint inside a transaction that lets you perform partial rollback. You declare one with SAVEPOINT name, do some work, and if that work goes wrong you issue ROLLBACK TO SAVEPOINT name — only the changes made after the savepoint are undone, and the transaction itself stays open and usable. You can also RELEASE SAVEPOINT name to forget the savepoint without rolling back. Savepoints are useful in three main situations: first, for optional steps inside a larger transaction where you want to try something but keep going if it fails (for example, attempting a bonus credit that is allowed to be rejected without aborting the main transfer). Second, for recovering from errors in PostgreSQL — wrapping a risky statement in a savepoint lets you ROLLBACK TO that savepoint and continue, instead of aborting the entire transaction. Third, ORMs use savepoints internally to implement "nested transactions" — the outer transaction is real, and each nested level is a savepoint with an auto-generated name.

4. "What is autocommit mode and what is the danger of relying on it?"

Autocommit mode is the default behavior in psql and almost every database driver, in which each statement you issue is automatically wrapped in its own one-statement transaction that commits the instant the statement returns. You never type BEGIN or COMMIT, but every statement is still a transaction — just a one-statement one. This is convenient for ad-hoc queries and isolated INSERTs/UPDATEs, but it gives you zero atomicity across multiple statements. Two UPDATEs issued back-to-back in autocommit mode are two separate transactions: if your process crashes, your network drops, or an exception is thrown between them, the first one is permanently committed and the second never runs. Anything that needs multi-statement atomicity — a bank transfer, an order with inventory updates, a multi-table user signup — must be wrapped in an explicit transaction with BEGIN ... COMMIT (or your driver's transaction API), which disables autocommit on the connection for the duration of the transaction.

5. "Why is it a bug to wait for user input or call an external API in the middle of an open transaction?"

Because transactions hold locks. The moment you UPDATE a row inside a transaction, PostgreSQL takes a row-level lock on that row and holds it until you COMMIT or ROLLBACK. Any other transaction that wants to write to the same row blocks, waiting for you to finish. If you then await user input — show a confirmation dialog, wait for a webhook, call a slow third-party API — you are holding those locks for the entire duration of the wait, which could be seconds, minutes, or "forever" if the user goes to lunch. Other sessions queue up behind your locks, your connection pool fills with waiting sessions, and eventually the database becomes unresponsive even though the actual workload is tiny. Transactions should be short and synchronous: read what you need, decide, write, commit, done. Anything that involves waiting on something outside the database — user input, network calls to other services, file uploads — should happen before BEGIN or after COMMIT, never inside the transaction. The pattern is "do all the slow work first, then open a short transaction at the very end to apply the writes."

---

Quick Reference — Transactions Cheat Sheet

+---------------------------------------------------------------+
|           TRANSACTIONS CHEAT SHEET                            |
+---------------------------------------------------------------+
|                                                                |
|  CONTROL STATEMENTS (PostgreSQL):                              |
|    BEGIN;                       -- start a transaction         |
|    START TRANSACTION;           -- same thing, SQL standard    |
|    COMMIT;                      -- save all changes            |
|    ROLLBACK;                    -- discard all changes         |
|                                                                |
|  SAVEPOINTS:                                                   |
|    SAVEPOINT name;              -- create a checkpoint         |
|    ROLLBACK TO SAVEPOINT name;  -- undo to checkpoint          |
|    RELEASE SAVEPOINT name;      -- forget the checkpoint       |
|                                                                |
|  AUTOCOMMIT:                                                   |
|    Default ON in psql and most drivers                         |
|    Each statement = its own one-statement transaction          |
|    BEGIN explicitly to get multi-statement atomicity           |
|                                                                |
|  POSTGRES vs MYSQL:                                            |
|    Postgres: error -> txn aborted, must ROLLBACK               |
|    MySQL:    error -> only failing stmt rolled back            |
|    Postgres: DDL is transactional                              |
|    MySQL:    DDL implicitly commits                            |
|                                                                |
|  APPLICATION CODE:                                             |
|    1. Check out ONE connection                                 |
|    2. BEGIN                                                    |
|    3. Run statements; ROLLBACK on any error                    |
|    4. COMMIT on success                                        |
|    5. Release the connection                                   |
|                                                                |
+---------------------------------------------------------------+

+---------------------------------------------------------------+
|           KEY RULES                                            |
+---------------------------------------------------------------+
|                                                                |
|  1. A transaction is an atomic unit -- all or nothing          |
|  2. BEGIN opens, COMMIT publishes, ROLLBACK throws away        |
|  3. Autocommit gives ZERO multi-statement atomicity            |
|  4. Always ROLLBACK in error paths, no exceptions              |
|  5. Use one connection for the entire BEGIN..COMMIT lifetime   |
|  6. Postgres: errors abort the txn -- savepoints can recover   |
|  7. Savepoints = named checkpoints for partial rollback        |
|  8. Keep transactions SHORT -- never await user input inside   |
|  9. DDL is transactional in Postgres, not in MySQL             |
| 10. The shorter the txn, the smaller the lock window           |
|                                                                |
+---------------------------------------------------------------+

Concern	Wrong Way	Right Way
Multi-statement atomicity	Two autocommit statements	BEGIN; ...; COMMIT;
Error handling	No ROLLBACK in catch	ROLLBACK in every error path
Connection usage	pool.query per statement	One checked-out client per txn
Optional sub-step	All-or-nothing only	SAVEPOINT + ROLLBACK TO
Postgres after error	Issue more statements	ROLLBACK or ROLLBACK TO savepoint
Long-running work	API call inside txn	Do work first, then short txn
Migrations on Postgres	Many small txns	One big transactional migration
Migrations on MySQL	Assume DDL rolls back	Plan for implicit commits

---

Prev: Lesson 10.5 -- BCNF Next: Lesson 11.2 -- ACID Properties

---

This is Lesson 11.1 of the Database Interview Prep Course -- 12 chapters, 58 lessons.