Peewee: database locked even with minimal concurrency

[rayzorben]

I am trying to insert multi threaded. I have 4 worker threads that pull data off a queue (producer/consumer model) and does an insert_many on that data.

def run(self):
        while True:
            try:
                r = self.q.get_nowait()
            except Empty:
                r = None

            if r is None:
                #TODO: signalling
                time.sleep(0.05)
                continue

            self.process(r)

    def process(self, item):
        table, data = item
        with db.transaction():
            table.insert_many(data).execute()

Here is how I setup my connection:

db = SqliteDatabase(database, autocommit=False, threadlocals=True, pragmas=(('journal_mode', 'WAL'), ('cache_size', 10000)))

No matter how I set it up, I occasionally get peewee.OperationalError: database is locked.

I am guessing that the insert_many is holding the lock too long, and a timeout is being hit. Am I doing this incorrectly or should I be somehow handling the database is locked error and retrying?

[coleifer]

I wrote a blog post on this subject, http://charlesleifer.com/blog/multi-threaded-sqlite-without-the-operationalerrors/

But, tl;dr is that you probably are running up against timeouts while waiting for the write lock...possibly?

You can try increasing the timeout, by passing timeout=10 or more to the initialization method of SqliteDatabase. Or you can try out SqliteQueueDatabase which works as a drop-in replacement for SqliteDatabase. Note that you cannot use transactions if you're using SqliteQueueDatabase, however.

I'd suggest taking a look at the post. I'd also suggest that, if you're planning to use SQLite in a multi-threaded application, you read the docs on how locking works. The SQLite docs are really good.

[rayzorben]

@coleifer Thanks for that, will look at it. In order to diagnose I ran it single threaded, and I can see the problem really clearly. Most writes happen in .02-.04 seconds. However every once in a while I will get writes that take 1-7 seconds, even up to 49 seconds!

Obviously even a timeout of 10 won't work, so hopefully the post will help out, but is there any way to diagnose why these writes are taking so long comparatively?

[evsasse]

Posting here because it is the last issue related to the database locked error. I've been dealing with this issue the last few months on my application, and here it is how I solved it. It was actually quite simple.

Reading on the web about the problem, most people seem to be solving it by using timeout=10. Specifically my app runs on very old/cheap computers and this was simply not cutting. I needed to pass an absurdly large value to timeout to make it work, timeout=100000.

It did raise me the question, is timeout in seconds or milliseconds? I have a couple writes that take more than a second, and thought that setting it to 10 would solve the problem(becaus I thought it was 10 seconds), but it was only solved after it being larger than 1000.

Keep reading if you are using Qt.

This problem was terribly inconsistent for me, because somehow the way Qt handles its threads would prevent it from happening most of the time. And it was impossible to debug it, because I couldn't reproduce it whenever I wanted. The solution was also quite simple, create some python threads (not Qt threads), and run a bunch of concurrent writes, tweak the timeout number until it stops locking.

Hope this helps some other adventurous developers dealing with incredibly bad computers. ;)

[coleifer]

Thanks for bringing this to my attention. I've updated the interfaces and docs so that milliseconds are used everywhere: 171af17f5cced96c15d5e32e832474aa1618ab8c