There is this documentation for sqlite: https://www.sqlite.org/lang_transaction.html which doesn't say anything about what happens in case I have case like:
BEGIN;
INSERT INTO a (x, y) VALUES (0, 0);
INSERT INTO b (x, y) VALUES (1, 2); -- line 3 error here, b doesn't have column x
COMMIT;
What happens in this case? Does it commit or rollback if there is error in line 3? I would expect automatic rollback, but I would like to be sure about it.
SQL statements are executed individually.
When a statement fails, any effects of this single statement are rolled back, but the transaction stays open and active. When the application receives the error code, it must decide whether it wants to roll back the transaction, or retry, or do something else.
If you are using a function that executes multiple SQL statements, nothing changes; the effect is the same as if you had executed all statements up to the failing one individually.
ON CONFLICT ROLLBACK to many types of constraints, if you want an error on the constraint to roll back the transaction automatically. INSERT and UPDATE also allow OR ROLLBACK, ex. INSERT OR ROLLBACK.