Part 7 · 8 chapters · ~20 min

DML, transactions, concurrency

Writing is where SQL stops being declarative and starts interacting with other people's writes. Every statement here is correct in isolation and some of them are wrong under concurrency — which is the only condition that matters in production.

INSERT variants

code

-- single row
INSERT INTO t (a, b) VALUES (1, 'x');

-- multi-row: ONE statement, one round trip, one transaction
INSERT INTO t (a, b) VALUES (1,'x'), (2,'y'), (3,'z');

-- from a query
INSERT INTO archive (id, total)
SELECT id, total FROM orders WHERE created_at < '2025-01-01';

-- ignore errors (MySQL) — note what it hides
INSERT IGNORE INTO t (a, b) VALUES (1, 'x');

INSERT IGNORE ignores too much

It does not only skip duplicate keys. It downgrades every error in the statement to a warning — data truncation, foreign key violations, invalid values. A row you believe was inserted may have been silently dropped, or inserted with truncated data.

Use ON DUPLICATE KEY UPDATE or ON CONFLICT DO NOTHING, which target duplicates specifically and leave other errors as errors.

batch size matters more than people expect

One thousand single-row inserts are roughly a thousand round trips and a thousand transactions. One multi-row insert of a thousand values is one of each — commonly 10–100× faster. But batches too large hold locks longer and generate huge undo; 500–1000 rows per statement is a good working range, in explicit transactions of a few thousand rows.

UPSERT and its hazards

"Insert if absent, update if present." Every engine spells it differently and each spelling behaves differently.

code

-- MySQL
INSERT INTO counters (k, n) VALUES ('hits', 1)
  ON DUPLICATE KEY UPDATE n = n + 1;

-- MySQL 8.0.19+ preferred alias form (VALUES() is deprecated)
INSERT INTO counters (k, n) VALUES ('hits', 1) AS new
  ON DUPLICATE KEY UPDATE n = counters.n + new.n;

-- Postgres
INSERT INTO counters (k, n) VALUES ('hits', 1)
  ON CONFLICT (k) DO UPDATE SET n = counters.n + excluded.n;

-- Postgres: insert-or-ignore
INSERT INTO t (k) VALUES ('x') ON CONFLICT DO NOTHING;

Three hazards

It needs a unique constraint. Without one there is no conflict to detect and you get duplicates. Postgres makes you name the conflict target, which is safer; MySQL uses whichever unique key conflicts, so a table with several unique indexes can update on a key you did not intend.
It consumes auto-increment values even when it updates. Under the default lock mode the id is allocated before the conflict is detected, so a heavily-upserted table burns through ids and leaves visible gaps (MySQL module, ch 56).
It still deadlocks. Concurrent upserts of different keys can deadlock on gap locks in the same index range — the chapter 55 mechanism from the MySQL module. Keep a retry.

the counter pattern done correctly

For counters specifically, a single atomic statement beats read-modify-write every time: UPDATE c SET n = n + 1 WHERE k = ? is correct under any concurrency, because the increment happens inside the engine with the row locked. Reading the value into the application and writing back a new one is the lost-update bug (Part 1 of the MySQL module, ch 48).

UPDATE with a join

code

-- MySQL: join directly in the UPDATE
UPDATE orders o
  JOIN customers c ON c.id = o.customer_id
   SET o.region = c.region
 WHERE o.region IS NULL;

-- Postgres: UPDATE ... FROM
UPDATE orders o
   SET region = c.region
  FROM customers c
 WHERE c.id = o.customer_id
   AND o.region IS NULL;

-- portable: a correlated subquery
UPDATE orders o
   SET region = (SELECT c.region FROM customers c WHERE c.id = o.customer_id)
 WHERE o.region IS NULL;

the fan-out problem applies to UPDATE too

If the join matches several rows on the right, the behaviour is undefined — the row is updated repeatedly and the final value depends on order. MySQL applies one of them arbitrarily; Postgres picks one non-deterministically. Neither raises an error.

Before any join-based update, confirm the right side is unique on the join key. If it is not, aggregate it first, exactly as in chapter 15.

always know the row count first

Run the SELECT with the same WHERE clause before the UPDATE. If it returns far more rows than expected, you have just avoided an incident. For large updates, batch by primary key range and commit between batches — one enormous transaction holds locks and grows undo (MySQL module, ch 45).

DELETE vs TRUNCATE vs DROP

	DELETE	TRUNCATE	DROP
Removes	Matching rows	All rows	The table itself
Type	DML	DDL	DDL
Transactional	Yes — rollback works	No — implicit commit	No
Fires triggers	Yes	No	—
Speed	Slow — row by row, all logged	Fast — drops and recreates	Fast
Auto-increment	Preserved	Reset	—
Frees disk	No (module ch 29)	Yes	Yes

TRUNCATE is DDL, so it cannot be rolled back

BEGIN; TRUNCATE t; ROLLBACK; does not restore the rows. In MySQL, TRUNCATE causes an implicit commit of the enclosing transaction first. The safety net you assume a transaction gives you is not there.

code

-- deleting a lot of rows safely: batch it
SET @rows = 1;
WHILE @rows > 0 DO
DELETE FROM events
   WHERE created_at < '2025-01-01'
ORDER BY id
   LIMIT 5000;
  SET @rows = ROW_COUNT();
  -- sleep briefly between batches to let replicas catch up
END WHILE;

-- better still, if the data is time-series: partition by month
-- and ALTER TABLE ... DROP PARTITION. instant, no undo.
-- (MySQL module, ch 108)

RETURNING

Get the affected rows back from a write, in the same statement — no second query, and no race between writing and reading.

code

-- Postgres, SQLite, MariaDB, standard since SQL:2023
INSERT INTO orders (customer_id, total)
VALUES (42, 99.00)
RETURNING id, created_at;

UPDATE jobs SET status = 'running'
WHERE status = 'queued'
RETURNING id, payload;     -- claim and fetch atomically
DELETE FROM sessions WHERE expires_at < NOW()
RETURNING user_id;          -- know exactly who was logged out

MySQL has no RETURNING

For inserts, LAST_INSERT_ID() gives the first id of the statement — and for a multi-row insert the rest follow consecutively only under innodb_autoinc_lock_mode 0 or 1, not under the default 2. For updates and deletes there is no equivalent; you must select the rows first, inside the same transaction, which is why the job-queue pattern in the MySQL module (ch 50) uses SELECT ... FOR UPDATE SKIP LOCKED followed by an UPDATE.

MERGE and its races

SQL:2003's general form: match a source against a target and act differently on matched and unmatched rows.

code

MERGE INTO inventory t
USING shipment s ON t.sku = s.sku
WHEN MATCHED THEN
UPDATE SET t.qty = t.qty + s.qty
WHEN NOT MATCHED THEN
INSERT (sku, qty) VALUES (s.sku, s.qty);

Available in Postgres 15+, Oracle, SQL Server and DB2. Not in MySQL — use ON DUPLICATE KEY UPDATE.

MERGE is not atomic against concurrent inserts

The classic race: two transactions both evaluate NOT MATCHED for the same key, both proceed to insert, and one gets a unique violation — or, without a unique constraint, you get duplicate rows and no error at all.

MERGE reads the target and then acts, and nothing locks the absence of a row except a gap lock. This is why ON CONFLICT and ON DUPLICATE KEY are safer for the simple upsert case: they are implemented against the unique index rather than a prior read. Reserve MERGE for batch ETL where you control concurrency.

Savepoints

A named point inside a transaction you can roll back to without abandoning the whole thing.

code

BEGIN;
  INSERT INTO orders ...;              -- must succeed
SAVEPOINT after_order;
  INSERT INTO loyalty_points ...;       -- nice to have
-- if that fails:
ROLLBACK TO SAVEPOINT after_order;   -- order survives
RELEASE SAVEPOINT after_order;
COMMIT;

this is how ORMs fake nested transactions

SQL has no nested transactions. When an ORM offers them, it is issuing a savepoint for the inner "transaction" and rolling back to it on failure. Worth knowing because it explains the behaviour: an inner rollback does not undo the outer work, and a failure in the outer transaction discards everything regardless of inner commits. The ORM module covers this properly.

Savepoints are not free — each one holds resources for the transaction's life, so a loop creating thousands of them is its own problem.

Concurrency-safe SQL patterns

The consolidated list. Each of these is correct under concurrent execution; the obvious alternative to each is not.

1. Compute in the statement, never read-modify-write

unsafe

app SELECT bal FROM a WHERE id=1;
    -- 100
app newBal = 100 - 10
app UPDATE a SET bal=90 WHERE id=1;
    -- another tx's change is lost

safe

app UPDATE a
      SET bal = bal - 10
    WHERE id = 1
      AND bal >= 10;
    -- atomic. and the guard means
    -- ROW_COUNT()=0 signals insufficient
    -- funds without a separate check.

2. Lock the read when you will write based on it

code

BEGIN;
  SELECT stock FROM items WHERE id = ? FOR UPDATE;
  -- now nobody else can read-for-update this row until we commit
UPDATE items SET stock = stock - ? WHERE id = ?;
COMMIT;

3. Make writes idempotent with a natural key

code

-- a retry must not double-charge. the unique key makes the
-- second attempt a no-op rather than a duplicate.
INSERT INTO payments (idempotency_key, order_id, amount)
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE idempotency_key = idempotency_key;
--   the no-op update keeps it a single atomic statement

4. Claim work with SKIP LOCKED

The full pattern is in the MySQL module, chapter 50. The principle: claim inside a short transaction, commit, then do the slow work.

5. Order writes consistently

Touch rows in a defined order — ascending primary key — so two transactions cannot form a cycle. This is the single highest-value deadlock prevention (MySQL module, ch 60).

the test that catches these

None of these bugs appear in a single-threaded test. Write one test that runs the same operation from two connections with a deliberate interleaving — a barrier between the read and the write — and assert the invariant afterwards. That one test finds lost updates, upsert races and deadlock-order problems that no amount of sequential testing will.

Part 8 covers the frontiers: JSON, arrays, full-text, temporal tables, set operations and the views-and-procedures question.