What is a transaction in SQLite and how do you use BEGIN/COMMIT/ROLLBACK?

A transaction groups multiple statements so they either all take effect together, or none of them do — the standard atomicity guarantee. In SQLite, every statement outside an explicit transaction is actually wrapped in its own implicit, single-statement transaction automatically; BEGIN lets you group several statements into one larger transaction instead.

BEGIN TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;

If something goes wrong partway through, ROLLBACK undoes every change made since BEGIN, leaving the database exactly as it was before the transaction started. Wrapping multiple related writes in an explicit transaction is also significantly faster than letting each one run as its own implicit transaction, since SQLite only needs to sync to disk once at commit, rather than once per statement.

Invest now in Acorns!!! 🚀
Join Acorns and get your $5 bonus!

Acorns is a micro-investing app that automatically invests your "spare change" from daily purchases into diversified, expert-built portfolios of ETFs. It is designed for beginners, allowing you to start investing with as little as $5. The service automates saving and investing. Disclosure: I may receive a referral bonus.

Invest now!!! Get Free equity stock (US, UK only)!

Use Robinhood app to invest in stocks. It is safe and secure. Use the Referral link to claim your free stock when you sign up!.

The Robinhood app makes it easy to trade stocks, crypto and more.

Webull! Receive free stock by signing up using the link: Webull signup.