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?
See Question&Answers more detail:os