Python sqlite3模块中的提交行为和原子性

Question

如果我想创建一个表并在另一个表中插入新条目，这是否可以在sqlite模块中以原子方式完成？

参考文档：http://docs.python.org/2/library/sqlite3.html：

默认情况下，sqlite3模块会在数据修改语言（DML）语句（即INSERT / UPDATE / DELETE / REPLACE）之前隐式打开事务，并在非DML、非查询语句（即SELECT或上述语句之外的任何语句）之前隐式提交事务。因此，如果您在事务内部发出CREATE TABLE ...、VACUUM、PRAGMA等命令，则sqlite3模块将在执行该命令之前隐式提交。做出这样的决定有两个原因。第一个原因是其中一些命令不能在事务内部工作。另一个原因是sqlite3需要跟踪事务状态（是否存在活动事务）。

我不确定第二段是否适用于自动启动的事务还是手动和自动事务都适用。

Sqlite文档http://www.sqlite.org/lang_transaction.html告诉我们，手动事务直到显式提交才会提交：

使用BEGIN命令可以手动启动事务。这种事务通常持久到下一个COMMIT或ROLLBACK命令。

所以假设我们有这样的东西：

con = sqlite3.connect(fdb) 
cur = con.cursor()

sql = 'begin transaciton'
cur.execute(sql)    

sql = 'CREATE TABLE some-table ...
cur.execute(sql)

# *** is there an implicit commit at this point ?! ***

sql = 'INSERT INTO  another-table ...
cur.execute(sql)

con.commit()

这个操作是原子性的吗，还是Python SQLite会在create table语句后进行提交呢？ 有没有办法使其具有原子性？

- Basel Shishani

Answer 1

您无法原子地完成此操作。Python SQLite库在执行CREATE TABLE ..语句时会隐式发出COMMIT，因为SQLite不支持在交易处于活动状态时执行CREATE TABLE ..语句。

您可以通过在Python解释器和sqlite3命令行工具中打开数据库来测试这一点。只要您发出CREATE TABLE ..语句，就可以在sqlite3命令行工具中运行.schema命令并查看该语句的结果。

请注意，这意味着在CREATE TABLE ..语句之前的任何事情都已提交。换句话说，CREATE TABLE ..语句首先提交，然后启动一个全新的事务。

- Martijn Pieters

---

谢谢 - 所以顺序很重要吗？！如果我们先进行插入，然后再创建表，那么我们可以为这个特定的情况获得原子性吗？ - Basel Shishani

不，你仍然会得到两个单独的事务。表的创建可能会失败，而您的插入仍然会被提交。自己尝试一下；插入（不要提交），然后再次创建相同的表。您将收到操作错误（表已存在），但插入将已经被提交。 - Martijn Pieters

这只是Python库执行提交操作；当您处于显式事务中时，SQLite本身永远不会自动提交。 - CL.

SQLite在事务中支持任何DML和DDL语句。 - CL.

@CL：就我所知，它的行为是未定义的。例如，在事务中创建一个表和一个索引。DDL-in-transaction-support 最多只能说是未记录和不完整的。 - Martijn Pieters

Answer 2

Python的SQLite3库即使没有必要，也会自动插入提交。

为了使整个事务具有原子性，请使用任何其他Python SQLite封装程序，例如，例如APSW。

- CL.

---

我正在测试，通过打开两个单独的sqlite3 shells，在一个shell中运行事务并在另一个shell中检查结果。是的，你是正确的。 - Basel Shishani

如果sqlite3像这样压制原子性，他们至少应该在文档中突出这个问题。 - Basel Shishani

目前有一个关于这个问题的未解决的bug，但从评论的情况来看，对于Python 2.x（由于向后兼容性的考虑）和Python 3.x，似乎不太可能发生什么变化...嗯，已经过去了几年，似乎没有太多进展。值得注意的是（从该bug的评论中），仅在DML语句之前插入注释就会导致sqlite3模块的隐式提交（换句话说，它基本上是有问题的）。 - Dave Jones