ACID transactions (BEGIN/COMMIT)

The foundations of reliability for any database rest on the ACID acronym:

• Atomicity: an operation either completes entirely (Commit), or fails leaving no half-done traces behind (Rollback).
• Consistency: the database rules (e.g. constraints, Foreign Keys) are always respected.
• Isolation: transactions happening at the same time don't interfere with each other in uncontrolled ways.
• Durability: after a Commit, the change is irreversibly written to disk.

Transaction syntax

Normally each single UPDATE or DELETE query is treated as a standalone transaction (autocommit). But when you want to group multiple operations together, for example debiting the customer's account and placing the order, you have to do it explicitly using transaction syntax.

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
INSERT INTO orders (user_id, total) VALUES (1, 100);

COMMIT; -- Writes the changes

If during these complex executions the JavaScript app that sent the query realizes there's a problem (for example there isn't enough stock in the warehouse and it crashes), it would send a:

ROLLBACK; -- Cancels the changes and discards the 2 queries sent in the block

Try it yourself