SQL Transactions, Isolation Levels and Safe Writes: Upserts, Soft Deletes

Two people click "buy" for the last two Jazz Night tickets within the same millisecond. Both requests read remaining = 2, each buys one ticket and each writes remaining = 1. Two tickets were sold, the counter says only one was, and no error was raised. Concurrency bugs are like this: every statement is correct on its own, and the harm appears only in the gap between them. This lesson shows what a transaction guarantees, what it does not, and which write patterns close the gaps.

Atomicity: all of it or none of it

A transaction groups statements so that they commit together or not at all. The classic guarantee set is ACID: atomic (all or nothing), consistent (constraints hold), isolated (concurrent transactions do not see each other's half-finished work, to a degree set by the isolation level) and durable (committed data survives a crash). The Python snippet below buys tickets with two statements, an order insert and a stock decrement, inside one transaction. The second purchase asks for more tickets than exist, the stock CHECK fails, and the order insert that came first must disappear too.

import sqlite3

con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE tickets (event TEXT PRIMARY KEY, remaining INTEGER NOT NULL CHECK (remaining >= 0));
CREATE TABLE orders  (order_id INTEGER PRIMARY KEY, event TEXT NOT NULL, qty INTEGER NOT NULL);
INSERT INTO tickets VALUES ('Jazz Night', 3);
""")

def buy(event, qty):
    try:
        with con:        # BEGIN ... COMMIT, or ROLLBACK if anything inside raises
            con.execute("INSERT INTO orders (event, qty) VALUES (?, ?)", (event, qty))
            con.execute("UPDATE tickets SET remaining = remaining - ? WHERE event = ?", (qty, event))
        return "committed"
    except sqlite3.IntegrityError as exc:
        return f"rolled back ({exc})"

print("buy 2:", buy("Jazz Night", 2))
print("buy 5:", buy("Jazz Night", 5))
print("tickets left:", con.execute("SELECT remaining FROM tickets").fetchone()[0])
print("orders stored:", con.execute("SELECT COUNT(*) FROM orders").fetchone()[0])

buy 2: committed
buy 5: rolled back (CHECK constraint failed: remaining >= 0)
tickets left: 1
orders stored: 1

Only the first purchase left a trace. The rejected one inserted an order row before the update failed, but the rollback removed it, which is the point: without the transaction you would have an order for tickets that do not exist.

What can go wrong between transactions

Atomicity protects one transaction from its own failure. Isolation governs how concurrent transactions see each other. Concurrent transactions can interfere in these ways:

The SQL standard defines four isolation levels by which of the first three anomalies they allow. Real products are often stricter than the standard's minimum and differ in details, so use the table as a list of questions to ask your database.

Lost updates and the patterns that prevent them

The script below opens two independent connections to one SQLite file, like two web requests. It first reproduces the lost update, then fixes it with an atomic conditional update, and finally shows snapshot behaviour and the single-writer lock.

import os, sqlite3, tempfile

path = os.path.join(tempfile.mkdtemp(), "shop.db")
setup = sqlite3.connect(path, isolation_level=None)
setup.execute("PRAGMA journal_mode = WAL")      # lets readers keep a stable snapshot
setup.executescript("CREATE TABLE stock (sku TEXT PRIMARY KEY, qty INTEGER NOT NULL);"
                    "INSERT INTO stock VALUES ('mug', 5);")
setup.close()

# two independent sessions (think: two web requests); isolation_level=None = no implicit transactions
a = sqlite3.connect(path, isolation_level=None, timeout=0)
b = sqlite3.connect(path, isolation_level=None, timeout=0)
qty = lambda c: c.execute("SELECT qty FROM stock WHERE sku = 'mug'").fetchone()[0]

print("--- lost update: read, compute in the app, write back")
seen_a, seen_b = qty(a), qty(b)                          # both read 5
a.execute("UPDATE stock SET qty = ? WHERE sku = 'mug'", (seen_a - 1,))
b.execute("UPDATE stock SET qty = ? WHERE sku = 'mug'", (seen_b - 1,))
print("two mugs sold, stock is now", qty(a))             # should be 3

def sell_one(c):
    cur = c.execute("UPDATE stock SET qty = qty - 1 WHERE sku = 'mug' AND qty >= 1")
    return cur.rowcount                                  # 1 = sold, 0 = refused

print("--- atomic conditional update")
a.execute("UPDATE stock SET qty = 5")
print("sales with 5 in stock:", [sell_one(a), sell_one(b)], "-> stock", qty(a))
a.execute("UPDATE stock SET qty = 1")
print("sales with 1 in stock:", [sell_one(a), sell_one(b)], "-> stock", qty(a))

print("--- snapshot inside a transaction (WAL mode)")
a.execute("UPDATE stock SET qty = 5")
a.execute("BEGIN")
print("A reads:", qty(a))
b.execute("UPDATE stock SET qty = 4")                    # B commits immediately (autocommit)
print("A reads again, same transaction:", qty(a))
a.execute("COMMIT")
print("A reads after commit:", qty(a))

print("--- one writer at a time")
a.execute("BEGIN IMMEDIATE")
try:
    b.execute("BEGIN IMMEDIATE")
except sqlite3.OperationalError as exc:
    print("B:", exc)
a.execute("ROLLBACK")
a.close(); b.close()

--- lost update: read, compute in the app, write back
two mugs sold, stock is now 4
--- atomic conditional update
sales with 5 in stock: [1, 1] -> stock 3
sales with 1 in stock: [1, 0] -> stock 0
--- snapshot inside a transaction (WAL mode)
A reads: 5
A reads again, same transaction: 5
A reads after commit: 4
--- one writer at a time
B: database is locked

The first section is the bug: both sessions read 5, each wrote 4, and two mugs were sold while the stock fell by one. The fix is to let the database do the arithmetic and the check in one statement: SET qty = qty - 1 WHERE qty >= 1, then look at the affected row count. With five in stock both sales succeed; with one left the second affects zero rows, which your code should treat as "sold out" instead of overselling.

The third section shows a stable snapshot. In this SQLite WAL setup, session A's repeated read inside its transaction still sees 5 even after B committed 4, and only after A commits does it see the new value. That is repeatable-read behaviour. In PostgreSQL's default READ COMMITTED, the second read inside A would see B's committed value; under REPEATABLE READ it would see the snapshot value, like here. MySQL's InnoDB default, REPEATABLE READ, also gives a stable snapshot for ordinary reads. The last section shows SQLite refusing a second simultaneous writer with database is locked; other databases usually make the second writer wait or fail on a conflicting row instead.

Upserts: insert or update in one statement

"Insert if missing, otherwise update" written as a SELECT followed by an INSERT or UPDATE is a race: two sessions can both see "missing" and both insert. An upsert does it in one atomic statement, relying on a unique key. The syntax is INSERT ... ON CONFLICT (key) DO UPDATE SET ... in SQLite and PostgreSQL, INSERT ... ON DUPLICATE KEY UPDATE in MySQL, and MERGE in SQL Server and Oracle. Inside the update, excluded (SQLite and PostgreSQL) refers to the row you tried to insert.

CREATE TABLE gate_counts (
  gate  TEXT    NOT NULL,
  hour  TEXT    NOT NULL,
  scans INTEGER NOT NULL,
  PRIMARY KEY (gate, hour)
);

-- first scan batch inserts, later batches add to the same row
INSERT INTO gate_counts (gate, hour, scans) VALUES ('A', '19:00', 120)
ON CONFLICT (gate, hour) DO UPDATE SET scans = scans + excluded.scans;

INSERT INTO gate_counts (gate, hour, scans) VALUES ('A', '19:00', 45)
ON CONFLICT (gate, hour) DO UPDATE SET scans = scans + excluded.scans;

INSERT INTO gate_counts (gate, hour, scans) VALUES ('B', '19:00', 80)
ON CONFLICT (gate, hour) DO UPDATE SET scans = scans + excluded.scans;

-- an import that must be safe to run twice: skip rows that already exist
INSERT INTO gate_counts (gate, hour, scans) VALUES ('B', '19:00', 999)
ON CONFLICT (gate, hour) DO NOTHING;

SELECT * FROM gate_counts ORDER BY gate;

gate | hour  | scans
-----+-------+------
A    | 19:00 |   165
B    | 19:00 |    80

Gate A's two batches (120, then 45) were folded into one row of 165. The final statement used DO NOTHING, which makes an import safe to rerun: the existing B row was left alone. Both forms need the conflict target to be backed by a primary key or unique index.

Soft deletes: hide rows instead of removing them

Deleting a row loses the history, breaks references from other tables and cannot be undone. A soft delete marks the row instead, usually with a deleted_at timestamp, and every normal query filters on deleted_at IS NULL. A view that hides deleted rows keeps that filter in one place. The sharp edge is uniqueness: if email is simply UNIQUE, a person who was soft-deleted can never register again. A partial unique index limits uniqueness to live rows.

CREATE TABLE attendees (
  attendee_id INTEGER PRIMARY KEY,
  email       TEXT NOT NULL,
  deleted_at  TEXT              -- NULL = active, otherwise when it was soft-deleted
);

-- uniqueness only among live rows
CREATE UNIQUE INDEX ux_attendees_live_email
  ON attendees (email) WHERE deleted_at IS NULL;

CREATE VIEW live_attendees AS
  SELECT attendee_id, email FROM attendees WHERE deleted_at IS NULL;

-- soft delete: mark, do not remove
UPDATE attendees SET deleted_at = '2025-07-01' WHERE email = 'nia@example.com';

-- the same person registers again later: allowed, because the old row is no longer live
INSERT INTO attendees (email) VALUES ('nia@example.com');

SELECT * FROM attendees ORDER BY attendee_id;

attendee_id | email            | deleted_at
------------+------------------+-----------
          1 | nia@example.com  | 2025-07-01
          2 | omar@example.com | NULL
          3 | nia@example.com  | NULL

SELECT * FROM live_attendees ORDER BY attendee_id;

attendee_id | email
------------+-----------------
          2 | omar@example.com
          3 | nia@example.com

INSERT INTO attendees (email) VALUES ('omar@example.com');

Error: UNIQUE constraint failed: attendees.email

The old Nia row stays for audit, the new registration is accepted, the view shows only active attendees, and a second live Omar is rejected. Partial indexes exist in SQLite and PostgreSQL (and as filtered indexes in SQL Server) but not in MySQL, where people use other workarounds, such as a generated column that is NULL for deleted rows. Soft deletes are also not a privacy tool: if you must erase personal data, you need a real delete or anonymisation, and every report and join must remember the filter.

Common mistakes

• Read-modify-write in application code. Symptom: counters that drift below the real value under load, only in production. Push the arithmetic into one UPDATE.
• Ignoring the affected row count. The statement succeeded, but updated zero rows, and the code carried on. Check rowcount after conditional updates.
• Long transactions. A transaction left open across a network call or a user's think time holds locks and snapshots; symptoms are timeouts, database is locked or lock-wait errors, and bloat. Keep transactions short and do slow work outside them.
• Retrying nothing under SERIALIZABLE. The database may abort one transaction with a serialization failure; the correct response is to rerun the whole transaction, not to show the user an error.
• Forgetting the soft-delete filter. Symptom: deleted records reappear in a report or count towards a limit. Route reads through a view or a repository method.

How this shows up in real projects and interviews

Inventory, booking, payments and anything with counters meet these problems in production, usually as a rare duplicate or a negative stock figure that nobody can reproduce. Interviewers ask "what is a lost update", "how would you prevent overselling" and "what isolation level do you use and why", and a good answer names the anomaly, then picks an atomic update, a lock or a version check, and mentions retries. The constraints that make these patterns work (unique keys, CHECKs) are covered in SQL Fundamentals, and the indexes that back upserts and partial uniqueness are covered in Database Indexing.