8

If I want to create a table and insert a new entry in another table, can this be made atomic in the sqlite module?

Refering to the docs at http://docs.python.org/2/library/sqlite3.html:

By default, the sqlite3 module opens transactions implicitly before a Data Modification Language (DML) statement (i.e. INSERT/UPDATE/DELETE/REPLACE), and commits transactions implicitly before a non-DML, non-query statement (i. e. anything other than SELECT or the aforementioned).

So if you are within a transaction and issue a command like CREATE TABLE ..., VACUUM, PRAGMA, the sqlite3 module will commit implicitly before executing that command. There are two reasons for doing that. The first is that some of these commands don’t work within transactions. The other reason is that sqlite3 needs to keep track of the transaction state (if a transaction is active or not).

I'm not sure if this second paragraph is meant to apply to automatically started transactions or to both manual and automatic ones.

Sqlite docs http://www.sqlite.org/lang_transaction.html tell us that manual transactions would not commit until an explicit COMMIT:

Transactions can be started manually using the BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command.

So suppose we have something like this:

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

Would this be atomic, or would python sqlite make a commit after the create table statement? Is there a way to make it atomic?

Improve this question

2 Answers 2

Reset to default
7

You cannot do this atomically. The Python SQLite library implicitly issues a COMMIT whenever you execute a CREATE TABLE .. statement, because SQLite does not support executing the CREATE TABLE .. statement while a transaction is active.

You can test this by opening the database in both the python interpreter and the sqlite3 command line tool. As soon as you issue the CREATE TABLE .. statement, you can run a .schema command in the sqlite3 command line tool and see the result of that statement.

Note that this means that anything you did in the transaction before the CREATE TABLE .. statement will also have been committed. To look it in another way, the CREATE TABLE .. statement first commits, then starts a completely new transaction.

Improve this answer
Sign up to request clarification or add additional context in comments.

13 Comments

Thanks - so is order significant?! If we do the insert first and then the create table, then we can get atomicity for this specific case?
No, you'd still get two separate transactions. The table creation could fail, and your inserts are still being committed. Try it yourself; do an insert (do not commit), then create the same table again. You'll get an operational error (table already exists), but the insert will have been committed.
It's just the Python library that does the commit; SQLite itself will never do an automatic commit when you are in an explicit transaction.
@CL.: You are correct, I misread the quoted documentation. Updated my answer.
SQLite supports any DML and DDL statements inside a transaction.
|
3

The Python SQLite3 libary inserts automatic commits even where none are needed.

To make your entire transaction atomic, use any other Python SQLite wrapper, such as, e.g., APSW.

Improve this answer

3 Comments

I'm testing by opening two separate sqlite3 shells and running transactions in one and checking results in the other. And yes you're correct.
If sqlite3 is quashing atomicity just like that they should at least highlight such an issue in the documentation.
There's currently an open bug regarding this issue, but from the looks of the comments nothing's likely to happen for Python 2.x (due to backwards compatibility concerns) and for Python 3.x ... well, it's been several years and there doesn't seem to be much movement on it. It's also worth noting (from that bug's comments) that merely inserting comments before DML statements causes implicit commits with the sqlite3 module (in other words, it's basically broken).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.