52

I'm trying to port some code to Python that uses sqlite databases, and I'm trying to get transactions to work, and I'm getting really confused. I'm really confused by this; I've used sqlite a lot in other languages, because it's great, but I simply cannot work out what's wrong here.

Here is the schema for my test database (to be fed into the sqlite3 command line tool).

BEGIN TRANSACTION;
CREATE TABLE test (i integer);
INSERT INTO "test" VALUES(99);
COMMIT;

Here is a test program.

import sqlite3

sql = sqlite3.connect("test.db")
with sql:
    c = sql.cursor()
    c.executescript("""
        update test set i = 1;
        fnord;
        update test set i = 0;
        """)

You may notice the deliberate mistake in it. This causes the SQL script to fail on the second line, after the update has been executed.

According to the docs, the with sql statement is supposed to set up an implicit transaction around the contents, which is only committed if the block succeeds. However, when I run it, I get the expected SQL error... but the value of i is set from 99 to 1. I'm expecting it to remain at 99, because that first update should be rolled back.

Here is another test program, which explicitly calls commit() and rollback().

import sqlite3

sql = sqlite3.connect("test.db")
try:
    c = sql.cursor()
    c.executescript("""
        update test set i = 1;
        fnord;
        update test set i = 0;
    """)
    sql.commit()
except sql.Error:
    print("failed!")
    sql.rollback()

This behaves in precisely the same way --- i gets changed from 99 to 1.

Now I'm calling BEGIN and COMMIT explicitly:

import sqlite3

sql = sqlite3.connect("test.db")
try:
    c = sql.cursor()
    c.execute("begin")
    c.executescript("""
            update test set i = 1;
            fnord;
            update test set i = 0;
    """)
    c.execute("commit")
except sql.Error:
    print("failed!")
    c.execute("rollback")

This fails too, but in a different way. I get this:

sqlite3.OperationalError: cannot rollback - no transaction is active

However, if I replace the calls to c.execute() to c.executescript(), then it works (i remains at 99)!

(I should also add that if I put the begin and commit inside the inner call to executescript then it behaves correctly in all cases, but unfortunately I can't use that approach in my application. In addition, changing sql.isolation_level appears to make no difference to the behaviour.)

Can someone explain to me what's happening here? I need to understand this; if I can't trust the transactions in the database, I can't make my application work...

Python 2.7, python-sqlite3 2.6.0, sqlite3 3.7.13, Debian.

David Given
  • 13,277
  • 9
  • 76
  • 123

7 Answers7

50

For anyone who'd like to work with the sqlite3 lib regardless of its shortcomings, I found that you can keep some control of transactions if you do these two things:

  1. set Connection.isolation_level = None (as per the docs, this means autocommit mode)
  2. avoid using executescript at all, because according to the docs it "issues a COMMIT statement first" - ie, trouble. Indeed I found it interferes with any manually set transactions

So then, the following adaptation of your test works for me:

import sqlite3

sql = sqlite3.connect("/tmp/test.db")
sql.isolation_level = None
c = sql.cursor()
c.execute("begin")
try:
    c.execute("update test set i = 1")
    c.execute("fnord")
    c.execute("update test set i = 0")
    c.execute("commit")
except sql.Error:
    print("failed!")
    c.execute("rollback")
Matt
  • 27,170
  • 6
  • 80
  • 74
yungchin
  • 1,519
  • 2
  • 15
  • 17
  • I think it should be, since you'll fail at fnord and then run the rollback. – rsaxvc Jan 25 '15 at 22:06
  • Thanks. Out of all the many, many things written about transactions in sqlite/python, this is the only thing that let me do what I want (have an exclusive read lock on the database). – Jérémie Sep 21 '15 at 03:56
  • 7
    What's meaning of "autocommit" mode? According to its name, I guess any kind of MDL statement will be committed automatically. But according to your explanation, it seems my guess is wrong. Who could help me? And I searched it in google, couldn't get any useful answer also. – Clock ZHONG Mar 16 '17 at 07:50
  • 8
    ↑ I can answer the autocommit part of this. `isolation_level = None` disables the Python wrapper's automatic handling of issuing `BEGIN` etc. for you. What's left is the underlying C library, which does do "autocommit" by default. That autocommit, however, is disabled when you do a `BEGIN` (b/c you're signaling a transaction with that statement), which is why the above works the way it does. [SQLite's Docs on this](https://www.sqlite.org/c3ref/get_autocommit.html) – Thanatos Feb 06 '19 at 02:01
  • 1
    I fiddled with 2 of the lines since if they threw you wouldn't want to do the rollback - but a useful answer! – UKMonkey May 04 '19 at 12:17
  • 2
    I will add that you can set the transaction level too. For example `c.execute("begin exclusive transaction")`. – Marius Feb 11 '20 at 21:45
  • 1
    I'm confused. What's the difference between `c.execute("commit")` and `sql.commit()`? – HelloGoodbye Oct 15 '20 at 10:06
  • 1
    @HelloGoodbye there may not be a difference (I never really checked but from the documentation there shouldn't be a difference). I think that probably I was partly just trying to stay close to the last listing in the question, and partly I also prefer this aesthetically, because closing a transaction that opened with `c.execute("begin")` with `sql.commit()` looks a bit broken (even if it isn't). – yungchin Oct 16 '20 at 12:38
  • That makes sense. So, is there a also method in `sql` that corresponds to `c.execute("begin")`? Or is it only `c.execute("commit")` that has a corresponding method in `sql`? – HelloGoodbye Oct 18 '20 at 19:36
  • 1
    @HelloGoodbye If a transaction is in progress then `c.commit()` and `c.execute("commit")` have identical effect. The difference is only if a transaction is not in progress; in that case `c.commit()` silenly does nothing, while `c.execute("commit")` will throw an exception. `c.rollback()` is similar. There is no equivalent for beginning a transaction. – Jim Oldfield Dec 18 '20 at 17:19
  • 1
    Your code is right but the explanation is confusing. You say `isolation_level = None` puts the database in autocommit mode, but that's wrong, or at least misleading: the database is *always* in autocommit mode, regardless of `isolation_level`, so long as a transaction isn't currently open. It's possible to execute a statement outside a transaction, i.e. in autocommit mode, even when `isolation_level` is not `None`. – Jim Oldfield Dec 23 '20 at 08:55
23

Per the docs,

Connection objects can be used as context managers that automatically commit or rollback transactions. In the event of an exception, the transaction is rolled back; otherwise, the transaction is committed:

Therefore, if you let Python exit the with-statement when an exception occurs, the transaction will be rolled back.

import sqlite3

filename = '/tmp/test.db'
with sqlite3.connect(filename) as conn:
    cursor = conn.cursor()
    sqls = [
        'DROP TABLE IF EXISTS test',
        'CREATE TABLE test (i integer)',
        'INSERT INTO "test" VALUES(99)',]
    for sql in sqls:
        cursor.execute(sql)
try:
    with sqlite3.connect(filename) as conn:
        cursor = conn.cursor()
        sqls = [
            'update test set i = 1',
            'fnord',   # <-- trigger error
            'update test set i = 0',]
        for sql in sqls:
            cursor.execute(sql)
except sqlite3.OperationalError as err:
    print(err)
    # near "fnord": syntax error
with sqlite3.connect(filename) as conn:
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM test')
    for row in cursor:
        print(row)
        # (99,)

yields

(99,)

as expected.

unutbu
  • 842,883
  • 184
  • 1,785
  • 1,677
  • 1
    +1, this works except that whatever is executed by a `executescript` will not be rolled back even if it is in the 'with-statement' block, because `executescript` issues a COMMIT first. – MLister May 07 '15 at 03:43
  • 2
    -1 Opening a new connection for every transaction is highly inefficient and totally unnecessary. You have also not said when a transaction begins (e.g. did you know that your first block, that drops and creates the table, actually has three separate transactions?) – Jim Oldfield Dec 23 '20 at 09:07
17

Python's DB API tries to be smart, and begins and commits transactions automatically.

I would recommend to use a DB driver that does not use the Python DB API, like apsw.

Bernardo Ramos
  • 4,048
  • 30
  • 28
CL.
  • 173,858
  • 17
  • 217
  • 259
  • 4
    Thank-you, apsw is exactly what I'm looking for. I'm still confused, though; if python-sqlite3's transaction handling is this broken, why hasn't anyone noticed and fixed it, given it seems to be the default Sqlite binding for Python? Surely transactions are a core competency of any SQL library? – David Given Apr 09 '13 at 11:04
  • It is the *Python* API that is broken; and it cannot be changed easily for backwards compatibility reasons. – CL. Apr 09 '13 at 16:27
  • Well, my question still stands --- why is the Python API doing this? – David Given Apr 09 '13 at 22:39
  • 4
    It is how some other databases work with their Python drivers. The Python DBAPI spec tries to make it look like all databases function the same with the same semantics which is why pysqlite works that way. Of course SQLite is *very* different which is why I wrote APSW in the first place. See http://apidoc.apsw.googlecode.com/hg/pysqlite.html – Roger Binns Apr 10 '13 at 03:31
  • 1
    So does setting `isolation_level = None` in `sqlite3` library (per @yungchin answer) solve this problem? Or it's still better to use APSW? (python 3.5 if it matters.) – max Mar 12 '16 at 20:11
  • @max That depends on which API you prefer. – CL. Mar 12 '16 at 20:45
  • 1
    Well the problem is the other way around: `executescript()` works actually raw way, so you need BEGIN ... COMMIT inside the script "as usual elsewhere" for explicit ROLLBACK option in `except`. With smart `.execute()`'s and the other functions and `with conn:` there is usually also no problem - its more comfortable, safe and consistent. Just for protected read-modify-write transaction a explicit BEGIN is necessary. See details in my other post. `isolation_level = None` is already the default. – kxr May 18 '17 at 12:43
  • 6
    As of python3.6, the docs read: >Changed in version 3.6: sqlite3 used to implicitly commit an open transaction before DDL statements. This is no longer the case. – Jacob Lee Oct 05 '18 at 20:39
11

Here's what I think is happening based on my reading of Python's sqlite3 bindings as well as official Sqlite3 docs. The short answer is that if you want a proper transaction, you should stick to this idiom:

with connection:
    db.execute("BEGIN")
    # do other things, but do NOT use 'executescript'

Contrary to my intuition, with connection does not call BEGIN upon entering the scope. In fact it doesn't do anything at all in __enter__. It only has an effect when you __exit__ the scope, choosing either COMMIT or ROLLBACK depending on whether the scope is exiting normally or with an exception.

Therefore, the right thing to do is to always explicitly mark the beginning of your transactional with connection blocks using BEGIN. This renders isolation_level irrelevant within the block, because thankfully it only has an effect while autocommit mode is enabled, and autocommit mode is always suppressed within transaction blocks.

Another quirk is executescript, which always issues a COMMIT before running your script. This can easily mess up the transactional with connection block, so your choice is to either

  • use exactly one executescript within the with block and nothing else, or
  • avoid executescript entirely; you can call execute as many times as you want, subject to the one-statement-per-execute limitation.
Rufflewind
  • 8,545
  • 2
  • 35
  • 55
  • "use exactly one `executescript` within a transaction" This isn't right because, as you already said, `executescript` issues a commit beforehand so putting it *within* a transaction isn't possible. I think you meant to say the reverse: put a transaction *within* the `executescript`. – Jim Oldfield Dec 23 '20 at 08:57
  • I think when I wrote "a transaction" I really meant "a single `with connection:` block". – Rufflewind Dec 24 '20 at 20:44
  • Good research to get the concise behaviour: "Therefore, the right thing to do is to always explicitly mark the beginning of your transactional with connection blocks using BEGIN" – rfmoz Jan 19 '21 at 05:38
2

Normal .execute()'s work as expected with the comfortable default auto-commit mode and the with conn: ... context manager doing auto-commit OR rollback - except for protected read-modify-write transactions, which are explained at the end of this answer.

sqlite3 module's non-standard conn_or_cursor.executescript() doesn't take part in the (default) auto-commit mode (and so doesn't work normally with the with conn: ... context manager) but forwards the script rather raw. Therefor it just commits a potentially pending auto-commit transactions at start, before "going raw".

This also means that without a "BEGIN" inside the script executescript() works without a transaction, and thus no rollback option upon error or otherwise.

So with executescript() we better use a explicit BEGIN (just as your inital schema creation script did for the "raw" mode sqlite command line tool). And this interaction shows step by step whats going on:

>>> list(conn.execute('SELECT * FROM test'))
[(99,)]
>>> conn.executescript("BEGIN; UPDATE TEST SET i = 1; FNORD; COMMIT""")
Traceback (most recent call last):
  File "<interactive input>", line 1, in <module>
OperationalError: near "FNORD": syntax error
>>> list(conn.execute('SELECT * FROM test'))
[(1,)]
>>> conn.rollback()
>>> list(conn.execute('SELECT * FROM test'))
[(99,)]
>>> 

The script didn't reach the "COMMIT". And thus we could the view the current intermediate state and decide for rollback (or commit nevertheless)

Thus a working try-except-rollback via excecutescript() looks like this:

>>> list(conn.execute('SELECT * FROM test'))
[(99,)]
>>> try: conn.executescript("BEGIN; UPDATE TEST SET i = 1; FNORD; COMMIT""")
... except Exception as ev: 
...     print("Error in executescript (%s). Rolling back" % ev)
...     conn.executescript('ROLLBACK')
... 
Error in executescript (near "FNORD": syntax error). Rolling back
<sqlite3.Cursor object at 0x011F56E0>
>>> list(conn.execute('SELECT * FROM test'))
[(99,)]
>>> 

(Note the rollback via script here, because no .execute() took over commit control)


And here a note on the auto-commit mode in combination with the more difficult issue of a protected read-modify-write transaction - which made @Jeremie say "Out of all the many, many things written about transactions in sqlite/python, this is the only thing that let me do what I want (have an exclusive read lock on the database)." in a comment on an example which included a c.execute("begin"). Though sqlite3 normally does not make a long blocking exclusive read lock except for the duration of the actual write-back, but more clever 5-stage locks to achieve enough protection against overlapping changes.

The with conn: auto-commit context does not already put or trigger a lock strong enough for protected read-modify-write in the 5-stage locking scheme of sqlite3. Such lock is made implicitely only when the first data-modifying command is issued - thus too late. Only an explicit BEGIN (DEFERRED) (TRANSACTION) triggers the wanted behavior:

The first read operation against a database creates a SHARED lock and the first write operation creates a RESERVED lock.

So a protected read-modify-write transaction which uses the programming language in general way (and not a special atomic SQL UPDATE clause) looks like this:

with conn:
    conn.execute('BEGIN TRANSACTION')    # crucial !
    v = conn.execute('SELECT * FROM test').fetchone()[0]
    v = v + 1
    time.sleep(3)  # no read lock in effect, but only one concurrent modify succeeds
    conn.execute('UPDATE test SET i=?', (v,))

Upon failure such read-modify-write transaction could be retried a couple of times.

kxr
  • 4,841
  • 1
  • 49
  • 32
  • -1 You use the phrase "auto-commit mode" to mean the practice of the Python sqlite3 module to start transactions automatically. But that phrase means something very specific and very different (almost opposite!): that the underlying sqlite library itself will automatically put a mini-transaction around each individual statement not in a transaction. So whenever the Python module automatically starts a transaction it's actually taking the database **out** of autocommit mode! But you're right that transactions are not automatically started by `SELECT` statements. – Jim Oldfield Dec 23 '20 at 09:13
1

You can use the connection as a context manager. It will then automatically rollback the transactions in the event of an exception or commit them otherwise.

try:
    with con:
        con.execute("insert into person(firstname) values (?)", ("Joe",))

except sqlite3.IntegrityError:
    print("couldn't add Joe twice")

See https://docs.python.org/3/library/sqlite3.html#using-the-connection-as-a-context-manager

Gabriel Saca
  • 150
  • 6
-1

This is a bit old thread but if it helps I've found that doing a rollback on the connection object does the trick.

Saliya Ekanayake
  • 387
  • 3
  • 10