No reads or writes occur except within a transaction.

Any command that accesses the database will automatically start a transaction if one is not already in effect. Automatically started transactions are committed when the last SQL statement finishes.

Syntax:

  • query.sql 
      BEGIN;
    -- Do one or more steps like select, or update/set/delete
  COMMIT;

Or use ROLLBACK; instead of COMMIT;`

SQLite

Notes for SQLite:

  • Alternatively use the more verbose BEGIN TRANSACTION.
  • END TRANSACTION is an alias for COMMIT.
  • Transactions can be DEFERRED, IMMEDIATE, or EXCLUSIVE. The default transaction behavior is DEFERRED.

Example from tutorial.

  • query.sql 
      BEGIN TRANSACTION;

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

    UPDATE accounts
       SET balance = balance + 1000
     WHERE account_no = 200;

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

    INSERT INTO account_changes(account_no,flag,amount,changed_at) 
    VALUES(200,'+',1000,datetime('now'));

  COMMIT;