sqlite ON CONFLICT ROLLBACK 和连续查询 [英] sqlite ON CONFLICT ROLLBACK and consecutive queries

问题描述

我使用 sqlite ON CONFLICT ROLLBACK 子句来保存可能被占用的资源表.这是通过重复语句完成的，这些语句在事务中非常有效.像这样:

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;

位置受 ... place UNIQUE ON CONFLICT ROLLBACK ... 子句的约束.

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

但是，对于这种用途，交易似乎并不健全.事实上，如果一个UNIQE 冲突触发，事务就会回滚，即使我们没有点击COMMIT 语句.COMMIT 和 COMMIT 之前的以下语句似乎被执行，现在作为带有隐式提交的单个语句.这种行为是不是有点无用?我希望在调用 COMMIT 之前不会发生任何事情，并且 ROLLBACK 会导致 BEGIN 之前的状态被触发.

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.

这是为什么?

推荐答案

我确认从 SQLite version 3.27.2 开始使用 .Net binding System.Data.SQLite 1.0.110.0，问题中提到的 ON CONFLICT 子句符合 OP 的要求，并且作为 文档说明一>.

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.

问题中既没有提供特定版本(sqlite3 标记除外)，也没有提供足够的 DDL 代码，也没有提供有关编码环境的任何详细信息，以确切了解为什么该语句没有按预期工作.因此，此答案是对主要问题的更新参考，而不是针对 OP 特定情况的解决方案.

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.

但是，在使用上面给出的最新版本直接在 C# 代码中测试 ... UNIQUE ON CONFLICT ROLLBACK 子句之前，我确实遇到了问题中描述的类似行为.我首先尝试使用 3rd 方工具测试代码SQLiteStudio v3.2.1(使用 SQLite 3.24.0)和DB Browser for SQLite 3.11.2(使用 SQLite 3.27.2).经过许多令人沮丧的测试，我现在假设这两个工具都实现了自己的语句/事务处理和/或编译选项，这些选项似乎完全搞乱了 SQLite 的底层事务行为.对于这两个工具，似乎 CREATE TABLE 语句的 ON CONFLICT 子句会导致错误的、不正确的事务行为.例如，DB Broswer 允许提交假定事务中的单个语句！即使该事务看似回滚并返回错误.在这方面，我遇到了问题中描述的问题，但事实证明问题不是直接与 SQLite 有关，而是与第 3 方工具有关.

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.

直到我在 C# 代码中的直接测试(在连接上没有任何其他活动)之前，我才能够确认 SQLite3 正确处理了表列上的 ON CONFLICT 声明.这也是对尝试自动化 SQLite 数据库管理的 3rd 方工具的警告.为提供管理功能而对数据库进行的所有额外调用都会干扰其他有效的 SQL 语句和事务.

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.

我首先使用以下 SQL 语句设置了一个表:

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;

在单独执行每个准备好的语句(在单独的 SQLite.SQLiteCommand 对象中)时，指示的语句失败，导致事务回滚.我通过检查查询 SQLiteConnection.AutoCommit 属性(如果在显式事务中返回 false)的连接状态来验证回滚，但也通过尝试显式 ROLLBACK失败，错误提示没有事务处于活动状态.为了进一步验证没有其他语句被意外提交，我从另一个进程查询数据库，发现没有插入和提交额外的行.

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.

这篇关于sqlite ON CONFLICT ROLLBACK 和连续查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！