transactions - sqlite ON CONFLICT ROLLBACK and consecutive queries

Question

I use an sqlite ON CONFLICT ROLLBACK clause for keeping a table of ressources that may be occupied. This is done with repeated statements, that run quite eficcent within an transaction. Like this:

BEGIN TRANSACTION;
INSERT INTO places (place) VALUES(17);
INSERT INTO places (place) VALUES(18);
INSERT INTO places (place) VALUES(19);
COMMIT;

The places are constrained by an ... place UNIQUE ON CONFLICT ROLLBACK ... clause.

However, it seems transactions are not sane for this use. In fact, if one UNIQE conflict triggers, the transacion is rolled back, even if we don't hit the COMMIT statement. The following statements preceding COMMIT and COMMIT itself are executed it seems, now as single statements with implicit commit. Isn't this behaviour kind of useless? I would expect nothing to happen until COMMIT is invoked, and the ROLLBACK resulting in the state before BEGIN if triggered.

Why is this?

Answer 1

The scope of a conflict resolution clause always is the current statement, not any following statements. This works as documented.

It is common for programs, when they encounter an error, to fix whatever caused the error and to retry the statement, or to execute another statement. Therefore, the database assumes that any command that you execute is a command that you actually want to execute.

The ON CONFLICT ROLLBACK clause does not make sense for your application. You should use the normal ON CONFLICT ABORT algorithm, and either handle conflicts by not executing any following commands:

try:
    db.execute("BEGIN")
    db.execute("INSERT INTO places(place) VALUES(17)")
    db.execute("INSERT INTO places(place) VALUES(18)")
    db.execute("INSERT INTO places(place) VALUES(19)")
    db.execute("COMMIT")
except:
    db.execute("ROLLBACK")

or insert all values in a single statement so that any ABORT automatically rolls back the other values:

INSERT INTO places(place) VALUES (17), (18), (19)