CODEWITHSUNDEEP
sqlite
Jacobian
Jacobian

Reputation: 10882

Transactions in SQLite

I now know that in MySQL there is a 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 rolled back?

I tested it myself and it seems to me that 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.

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?

Counter-example:

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.

Upvotes: 4

Views: 3038

Answers (1)

CL.
CL.

Reputation: 180300

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")

Upvotes: 3

Related Questions