CODEWITHSUNDEEP
sqlitetransactions
dronus
dronus

Reputation: 11272

sqlite ON CONFLICT ROLLBACK and consecutive queries

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?

Upvotes: 0

Views: 3029

Answers (2)

C Perkins
C Perkins

Reputation: 3886

I confirm that as of SQLite version 3.27.2 using the .Net binding System.Data.SQLite 1.0.110.0, the ON CONFLICT clause mentioned in the question works as the OP desires and as documentation explains.

Neither the specific version (other than the sqlite3 tag), nor sufficient DDL code, nor any details about the coding environment was given in the question to know for sure why the statement did not work as expected. Thus, this answer is meant as an updated reference to the primary question rather than a solution to the OP's particular situation.

I did however experience similar behavior as described in the question prior to testing the ... UNIQUE ON CONFLICT ROLLBACK clause directly in C# code using the latest versions given above. I had first attempted to test the code with 3rd-party tools SQLiteStudio v3.2.1 (using SQLite 3.24.0) and DB Browser for SQLite 3.11.2 (using SQLite 3.27.2). After many frustrating tests, I now assume that both of those tools implement their own statement/transaction handling and/or compilation options that seem to completely mess up the underlying transaction behavior of SQLite. For both of those tools, it seemed as though the ON CONFLICT clauses of the CREATE TABLE statement causes buggy, incorrect transaction behavior. For instance, DB Broswer was allowing individual statements within a supposed transaction to be committed!, even when the transaction was seemingly rolled back and an error was returned. In that regard, I experienced problems just as described in the question, but it turns out that the problem is not with SQLite directly, rather the 3rd-party tools.

It wasn't until my direct tests in C# code (without any other activity on the connection) was I able to confirm that SQLite3 properly handles the ON CONFLICT declarations on the table columns. This is also a caution about 3rd party tools attempt to automate management of SQLite databases. All of the extra calls to the database for providing the management features can interfere with otherwise valid SQL statements and transactions.

I first setup a table using the following SQL statements:

CREATE TABLE places (
    id     INTEGER    PRIMARY KEY
                      AUTOINCREMENT
                      NOT NULL
                      UNIQUE,
    place  INTEGER    NOT NULL
                      UNIQUE ON CONFLICT ROLLBACK);

INSERT INTO places (place) VALUES (10);
INSERT INTO places (place) VALUES (18);
INSERT INTO places (place) VALUES (5);

Next I executed additional inserts inside an explicit transaction:

BEGIN TRANSACTION;
INSERT INTO places (place) VALUES (17);
INSERT INTO places (place) VALUES (18); --Should fail due to non-unique value
INSERT INTO places (place) VALUES (19);
COMMIT;

Upon executing each prepared statement separately (within separate SQLite.SQLiteCommand objects), the indicated statement failed, causing a ROLLBACK of the transaction. I verified the rollback by checking the status of the connection querying the SQLiteConnection.AutoCommit property (which returns false if within an explicit transaction), but also by attempting an explicit ROLLBACK which failed with an error stating that no transaction was active. To further verify that no other statements had been unexpectedly committed, I queried the database from another process and found that no additional rows had been inserted and committed.

Upvotes: 1

CL.
CL.

Reputation: 180060

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)

Upvotes: 1

Related Questions