SQL Transaction

A SQL transaction groups database work into a unit that can be committed or rolled back. In DuckDB, BEGIN starts an explicit transaction, COMMIT makes its changes permanent, and ROLLBACK discards its changes. The relevant boundary is the database work participating in that transaction.

To replace a daily total, delete the existing day and insert the newly calculated value within one transaction. If insertion fails, roll back so the deletion is not left as a separate committed result. If a zero-row source should remove the old total, this delete-and-rebuild policy must be deliberate; an upsert that receives no row would otherwise leave the old value.

Atomicity means the transaction’s changes commit together or are discarded together. It does not prove that the selected date, currency, or formula was correct. Validate the affected rows and expected output before committing. Isolation determines what concurrent work can observe; inspect the engine’s guarantees rather than assuming every database behaves identically.

A transaction also does not make a repeated increment idempotent: adding 40 twice in two successful transactions still adds 80. Nor does a local rollback undo an email or external API effect. Coordinate those effects separately when required. On an error, stop and use the engine’s rollback and retry rules instead of blindly proceeding to COMMIT.

Reference: DuckDB documentation. Work through the examples in SQL from Zero to Working.