SQLite中的事务(Transactions in SQLite)

SQLite中的事务(Transactions in SQLite)

我现在知道在MySQL有一长串导致隐式提交的语句，比如CREATE ， ALTER等。我想知道SQLite是否有这样的东西。

换句话说，我想知道，例如， CREATE TABLE和其他一些构造是否会导致自动提交，因此无法回滚？

我自己测试了它，在我看来，像SQLite行为像MySQL ，但我不确定，我想要一些文档列出所有这些命令（我找不到一个）。

此外，我想知道是否可以调整一些sqlite参数，以防止它形成自动提交CREATE和其他语句。

编辑

一个额外的想法。 例如，我们都知道，不可能在SQLite中重命名字段或更改其类型（使用一个命令），但为了做到这一点，我们必须创建一个包含所需模式的新表并将数据导入到该表中。 显然，这样的操作应该在单个事务中实现，但是如果事实上CREATE TABLE和DROP TABLE命令会导致自动提交？

相反实施

import sqlite3 cnx = sqlite3.connect("test.db") cursor = cnx.cursor() cursor.execute("CREATE TABLE funky (attr_1_ integer)") cnx.rollback()

正如你所看到的，我没有使用任何特殊的编译指示，我甚至没有明确地提交，但是当我运行它然后转到sqlite3提示符时，我看到表格funky仍然存在。

I now know that in MySQL there is a long-long list of statements that cause implicit commit, like CREATE, ALTER etc. And I want to know if there is such a thing in SQLite.

In other words I want to know whether, for example, CREATE TABLE and some other constructs cause autocommit and therefore cannot be rollbacked?

I tested it myself and it seems to me, like SQLite behaves like MySQL, but I'm not sure of that and I want to have some reference to the documentation that lists all such commands (I could not find one).

Besides, I want to know if it is possible to tweak some sqlite parameters to prevent it form autocommitting CREATE and other statements.

EDIT

One extra thought. We all know for example, that it is impossible to rename a field in SQLite or to change its type (using one command), but in order to do that we have to create a new table with needed schema and import data to this table. Obviously, such an operation should be implemented in a single transaction, but how if in fact CREATE TABLE and DROP TABLE commands cause autocommit?

COUNTEREXAMPLE

import sqlite3 cnx = sqlite3.connect("test.db") cursor = cnx.cursor() cursor.execute("CREATE TABLE funky (attr_1_ integer)") cnx.rollback()

As you can see I do not use any special pragma and I do not even commit explicitly, but when I run it and then go to sqlite3 prompt, I see that the table funky still exists.

最满意答案

除了一些特殊的PRAGMA（ foreign_keys ， journal_mode ）之外， 所有 SQL命令都是完全事务性的 ：

除了在事务中，不能对数据库进行任何更改。

只有在不使用显式事务时，SQLite才会使用自动提交：

任何更改数据库的命令（基本上，除SELECT之外的任何SQL命令）都将自动启动事务（如果尚未生效）。 上次查询完成后，将提交自动启动的事务。

---

为了能够回滚CREATE TABLE，您必须

使用显式事务，和 （仅在Python中）禁用Python的自动提交模式 ：

import sqlite3

cnx = sqlite3.connect("test.db")
cnx.isolation_level = None
cursor = cnx.cursor()
cursor.execute("BEGIN")
cursor.execute("CREATE TABLE funky (attr_1_ integer)")
cursor.execute("ROLLBACK")
With the exception of some special PRAGMAs (foreign_keys, journal_mode), all SQL commands are fully transactional:
 
 
 
No changes can be made to the database except within a transaction.
 
 
SQLite will use autocommits only if you aren't using explicit transactions:
 
 
 
Any command that changes the database (basically, any SQL command other than SELECT) will automatically start a transaction if one is not already in effect. Automatically started transactions are committed when the last query finishes.
 
 
 
To be able to roll back a CREATE TABLE, you must
 
 
 use an explicit transaction, and 
 (only in Python) disable Python's autocommit mode: 
 
import sqlite3

cnx = sqlite3.connect("test.db")
cnx.isolation_level = None
cursor = cnx.cursor()
cursor.execute("BEGIN")
cursor.execute("CREATE TABLE funky (attr_1_ integer)")
cursor.execute("ROLLBACK")