PostgreSQL/Transactions

All data-changing operations like INSERT, UPDATE, or DELETE must run within a surrounding construct which is called a TRANSACTION. Transactions are created with the SQL command BEGIN and finished with either COMMIT or ROLLBACK. During the lifetime of the transaction the changes to the database are written only preliminarily. At the end, COMMIT finishes the transaction regularily and commits all intended data changes, or ROLLBACK aborts the transaction and reverts all those preliminary changes.

In addition to this explicit usage of SQL keywords to manage transactions, some of PostgreSQL's client libraries create implicitly a new transaction if one of the data-changing operations doesn't run in an explicitly created transaction. In this case, the operation is automatically committed immediately after its execution.

BEGIN; -- establish a new transaction
UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Bob';
COMMIT; -- finish the transaction

-- this UPDATE runs as the only command of an implicitly created transaction ...
UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice';

-- ... and this one runs in another transaction
UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Bob';

Hint: When working within a procedure or function, there is a DECLARE ... BEGIN ... END; construct to define 'blocks'. In this context the meaning of BEGIN (no semikolon after BEGIN!) differs from what is explained here. You can avoid the ambiguity by using the keywords START TRANSACTION; as an alternative for BEGIN; in the context of transactions. Besides that, START TRANSACTION; conforms to the SQL standard.

Transactions generate a great relief for applications. Especially for business logic that must execute many statements as a consistent unit - like the above money transfer from one bank account to another -, there is no need to take individual actions after an error occurred in the middle of a transaction. In many cases it's enough to restart the transaction or to handle errors in a unified way.

Transactions in PostgreSQL guarantee that all requirements of the ACID paradigm are fulfilled, see next chapter.

Sub-Transactions

Within a transaction the keyword SAVEPOINT defines and denotes a position, to which the transaction may be rolled-back.

-- The transaction will insert the values 1 and 3, but not 2.
BEGIN;
INSERT INTO my_table VALUES (1);
SAVEPOINT my_savepoint;
INSERT INTO my_table VALUES (2);
ROLLBACK TO SAVEPOINT my_savepoint;
INSERT INTO my_table VALUES (3);
COMMIT;