Use InnoDB transactions

InnoDB is the normal storage engine for application tables. It supports transactions, row-level locking, crash recovery, and foreign keys.

A transaction groups statements so they succeed or fail together. Creating a note and attaching its tag is one logical change; you never want the note without the tag row:

START TRANSACTION;

INSERT INTO notes (title) VALUES ('Plan the week');
INSERT INTO note_tags (note_id, tag_id) VALUES (LAST_INSERT_ID(), 1);

COMMIT;

Until COMMIT, other sessions do not see either row. If anything goes wrong in between, ROLLBACK undoes both inserts and the database returns to its previous state. Verify this yourself: run the inserts, issue ROLLBACK instead of COMMIT, and SELECT shows the note never existed.

By default MySQL runs with autocommit enabled, so each standalone statement is already its own small transaction. START TRANSACTION is how you make a bigger unit.

Keep the transaction focused. It holds row locks and one connection from START TRANSACTION to COMMIT, and everyone else who needs those rows waits. Do not call an external API, send an email, or wait for user input inside a transaction.

When transactions collide

Concurrent transactions can fail in two documented ways:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

A deadlock means two transactions each hold a lock the other needs, so InnoDB kills one to unblock the other. A lock-wait timeout means your transaction waited too long for a lock and gave up.

Both errors are transient, and the server itself tells you the fix: roll back the whole transaction, then retry it a small number of times with a short delay. Retry only these lock errors, never validation or syntax errors — retrying a genuinely broken statement just fails again.

Access rows in a consistent order when possible, for example always parent table before join table. That reduces deadlocks, but your application must still handle them. Under enough concurrency they are a normal event, not a bug.