Writing data safely: DML and transactions

Insert, update and delete without losing rows you meant to keep, and use transactions, savepoints and the right upsert form to keep concurrent writers consistent.

INSERT, UPDATE and DELETE

INSERT INTO orders (customer_id, total, status)
VALUES (42, 19.90, 'paid');

INSERT INTO orders (customer_id, total, status) VALUES
  (43, 5.00, 'new'),
  (44, 7.25, 'new');

-- increment a daily total, creating the row the first time
INSERT INTO daily_totals (day, total)
SELECT day, total FROM (
  SELECT DATE(placed_at) AS day, SUM(total) AS total
  FROM orders GROUP BY DATE(placed_at)
) AS new
ON DUPLICATE KEY UPDATE total = daily_totals.total + new.total;

UPDATE orders
SET status = 'shipped', updated_at = CURRENT_TIMESTAMP(3)
WHERE id = 1042;

DELETE FROM order_items WHERE order_id = 1042 AND status = 'draft';

• Run the WHERE clause as a SELECT first. SELECT COUNT(*) on the same predicate is the cheapest safety check that exists.
• SQL_SAFE_UPDATES=1 refuses an update or delete whose WHERE does not use a key, which is a useful default in a shared environment.
• ON DUPLICATE KEY UPDATE names the columns to change. REPLACE deletes the conflicting row and inserts a new one, so unmentioned columns reset to their defaults and cascading children are removed.
• A large DELETE holds locks and fills the undo log. Delete in batches with DELETE ... LIMIT 1000 in a loop and let the server breathe.

Transactions and savepoints

autocommit is on by default, so every statement is its own transaction unless you open one. InnoDB provides atomicity, MVCC and row-level locking, so a transaction is the unit in which several statements either all happen or none do.

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

SAVEPOINT after_transfer;
INSERT INTO audit_log (note) VALUES ('transfer 1 -> 2');

-- undo only the log entry, keep both balance changes
ROLLBACK TO SAVEPOINT after_transfer;

COMMIT;

• Readers do not block writers. A plain SELECT reads a consistent snapshot taken at the transaction's first read, so long reports do not lock the rows they read.
• Locks are held until COMMIT or ROLLBACK. Two transactions that touch the same rows in a different order are how deadlocks happen.
• The default isolation level is REPEATABLE READ. Your own transaction sees its own uncommitted changes, plus the snapshot for everything else.
• innodb_lock_wait_timeout defaults to 50 seconds. On expiry, the statement is rolled back and an error is raised; the surrounding transaction is still open unless you abort it.
• DDL such as ALTER TABLE commits implicitly. Wrapping it in a transaction does not make it rollback-safe.

Patterns that stay safe under concurrency

FAQ

Related

Querying data: SELECT, joins and aggregation Connecting from applications: drivers and pooling

Last refreshed 2026-09-18.