Database / SQLite Interview questions
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.
More Related questions...
