BeginTx, Commit, and the Deferred Rollback

Archiving a task means two things happen: the row leaves tasks and arrives in archived_tasks. Do that as two separate db.Exec calls and there's a window — however small — where the row exists in neither table, because the delete committed and the insert hasn't run yet, or in both, if it's the other way round. Nothing else touching the database is supposed to be able to see that window. That's what a transaction buys you: two statements that commit together or not at all.

tx, err := db.Begin()
if err != nil {
	return fmt.Errorf("begin: %w", err)
}
defer tx.Rollback()

That defer goes immediately after the error check, before a single query runs. It looks like it contradicts the next few lines, which call tx.Commit() on success — doesn't deferring a rollback undo the commit? It doesn't, and the reason is worth knowing cold: once Commit succeeds, the transaction is done, and calling Rollback on a finished transaction returns sql.ErrTxDone and does nothing. The deferred call is a no-op on the happy path. What it actually guards against is every other path — an error return three lines down, a panic, a context cancellation — any of which would otherwise leave the transaction (and the connection it's holding) open and never resolved. Go has no exceptions and no finally; defer immediately after Begin is the only mechanism standing between "this either commits or it doesn't" and "this sometimes just hangs open."

What actually gets undone

if _, err := tx.Exec(`DELETE FROM tasks WHERE id = ?`, id); err != nil {
	return fmt.Errorf("removing task %d: %w", id, err)
}
if _, err := tx.Exec(`INSERT INTO archived_tasks (id, title) VALUES (?, ?)`, id, title); err != nil {
	return fmt.Errorf("archiving task %d: %w", id, err)
}
return tx.Commit()

If the DELETE succeeds and the INSERT then fails, the function returns without calling Commit — and the deferred Rollback undoes both statements, not just the one that errored. The DELETE genuinely ran; from inside this transaction, tasks genuinely has one fewer row. None of that becomes real until Commit says so. That's the whole point: correctness here isn't "no statement failed," it's "either every statement in the unit took effect, or the database looks exactly like none of them did."

Try it

Implement ArchiveTask. It must move the row atomically — no observer sees tasks short a row without archived_tasks having gained it, and a failure partway through must leave both tables exactly as they were.