Transaction

A transaction is a sequence of SQL statements that is atomic with respect to recovery. This means that if a failure occurs while a transaction is in progress, the effects of the transaction are erased so that the database is left in the state it was in before the transaction started. Atomic in this context means indivisible. Either the transaction runs to completion, or it aborts in such a way that any changes it made before the abort are undone.

All changes and queries are atomic, consistent, isolated and durable (ACID).

Transaction statements

First, open a transaction:

BEGIN TRANSACTION;

The transaction is open until it is explicitly committed or rolled back. Second, select or update data in database. Third, commit the changes:

COMMIT;

If not want to save the changes, you can roll back:

ROLLBACK;

Example

BEGIN TRANSACTION;

UPDATE accounts
SET balance = balance - 1000
WHERE account_no = 100;

INSERT INTO account_changes(account_no, flag, amount, changet_at)
VALUES(100, '-', 1000, datetime('now'));

COMMIT;