Issue: database locked even with minimal concurrency
Author: rayzorben
State: closed
Created: 2017-02-09 17:22:47

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.

```python
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:

```python
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?

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.

@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?

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. ;)



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

Sorry for necro but I think it's missed in a few places, unless that SqliteExtDatabase interface really does use seconds?
https://docs.peewee-orm.com/en/latest/peewee/database.html

EDIT: Also, this doesn't seem to be merged into master? Sorry if i'm asking something stupid here.
https://github.com/coleifer/peewee/blob/master/peewee.py#L3416

EDIT2: I see actually sqlite lists that timeout as seconds, so maybe it's correct now? Rather confusing

That comment is outdated - seconds are used everywhere for sqlite timeouts.