sqlite3 Operation Error when doing many commits rapidly

Question

I get

sqlite3.OperationalError: SQL logic error or missing database

when I run an application I've been working on. What follows is a narrowed-down but complete sample that exhibits the problem for me. This sample uses two tables; one to store users and one to record whether user information is up-to-date in an external directory system. (As you can imagine, the tables are a fair bit longer in my real application). The sample creates a bunch of random users, and then goes through a list of (random) users and adds them to the second table.

#!/usr/bin/env python

import sqlite3
import random

def random_username():
    # Returns one of 10 000 four-letter placeholders for a username
    seq = 'abcdefghij'
    return random.choice(seq) + random.choice(seq) + \
           random.choice(seq) + random.choice(seq)

connection = sqlite3.connect("test.sqlite")

connection.execute('''CREATE TABLE IF NOT EXISTS "users" (
    "entry_id"              INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL ,
    "user_id"               INTEGER NOT NULL ,
    "obfuscated_name"       TEXT NOT NULL)''')
connection.execute('''CREATE TABLE IF NOT EXISTS "dir_x_user" (
    "user_id"               INTEGER PRIMARY KEY NOT NULL)''')        

# Create a bunch of random users
random.seed(0)  # get the same results every time
for i in xrange(1500):
    connection.execute('''INSERT INTO users
        (user_id, obfuscated_name) VALUES (?, ?)''',
        (i, random_username()))
connection.commit()

#random.seed()
for i in xrange(4000):
    username = random_username()
    result = connection.execute(
        'SELECT user_id FROM users WHERE obfuscated_name = ?',
        (username, ))
    row = result.fetchone()
    if row is not None:
        user_id = row[0]
        print "  %4d %s" % (user_id, username)
        connection.execute(
            'INSERT OR IGNORE INTO dir_x_user (user_id) VALUES(?)',
            (user_id, ))
    else:
        print "     ? %s" % username
    if i % 10 == 0:
        print "i = %s; committing" % i
        connection.commit()
connection.commit()

Of particular note is the line near the end that says,

if i % 10 == 0:

In the real application, I'm querying the data from a network resource, and want to commit the users every now and then. Changing that line changes when the error occurs; it seems that when I commit, there is a non-zero chance of the OperationalError. It seems to be somewhat related to the data I'm putting in the database, but I can't determine what the problem is.

Most of the time if I read all the data and then commit only once, an error does not occur. [Yes, there is an obvious work-around there, but a latent problem remains.]

Here is the end of a sample run on my computer:

     ? cgha
i = 530; committing
     ? gegh
     ? aabd
     ? efhe
     ? jhji
     ? hejd
     ? biei
     ? eiaa
     ? eiib
     ? bgbf
   759 bedd
i = 540; committing
Traceback (most recent call last):
  File "sqlitetest.py", line 46, in <module>
    connection.commit()
sqlite3.OperationalError: SQL logic error or missing database

I'm using Mac OS X 10.5.8 with the built-in Python 2.5.1 and Sqlite3 3.4.0.

Answer 1

As the "lite" part of the name implies, sqlite3 is meant for light-weight database use, not massive scalable concurrency like some of the Big Boys. Seems to me that what's happening here is that sqlite hasn't finished writing the last change you requested when you make another request

So, some options I see for you are:

1. You could spend a lot of time learning about file locking, concurrency, and transaction in sqlite3
2. You could add some more error-proofing simply by having your app retry the action after the first failure, as suggested by some on this Reddit post, which includes tips such as "If the code has an effective mechanism for simply trying again, most of sqlite's concurrency problems go away" and "Passing isolation_level=None to connect seems to fix it".
3. You could switch to using a more scalable database like PostgreSQL

(For my money, #2 or #3 are the way to go.)