Issue: Closing SQLite3 db blocks
Author: ghost
State: closed
Created: 2023-01-12 15:36:23

hi,

I am writing a Kodi addon where I wanted to use peewee as my CRM. My Kodi build uses python3.8 internally. I packaged the latest peewee version available and it works fine. I can create databases write to them read from them etc.

But if I try to call close() on the database, it blocks forever and the code never exits.

The same code runs fine with my PC's python interpreter.

The code: https://pastebin.com/zCNreHF0

I tried to debug this. First I tried to create a simple file and close it. That worked just fine. Then I used sqlite3 to create a database, insert a record and close the database. Worked also fine. Then I was suspecting threading.RLock() as the causer, but that also works just fine.

Any idea what could be wrong? Can I force the lib to use sqlite3?

Your code works just fine for me, though I would note that you do **not** need to call `Database.db.commit()`. Peewee operates in autocommit mode unless you are explicitly using either an `atomic()` or `transaction()` block. This *may* be related to your issue or not, I'm not sure.

Example output when I run your code:

```
0
Closing
Done
```

(Note that it prints 0 because you are storing the result of the `.save()` call into `token` -- probably you meant to do something like this):

```python
token = Token(token_type=2, token_value="test", expires_at="2021-01-01 00:00:00")
token.save()
# Or:
# token = Token.create(token_type=2, token_value='test', expires_at='2021-01-01 00:00:00')
print(token)
```

Nonetheless, I don't know how this could be related to the lock. You might try to interrupt the program when its blocked and generate a traceback, but I'm not sure whether that will yield any clues.

I tried without commit as well. It always hangs, but just within the kodi context. I am not sure whether this is an issue related to kodi or peewee.

I cannot really get a traceback since the entire interpreter hangs at that point.

Ah, so it's running within Kodi? I'm not sure how the lock would affect that, either, to be honest. The lock is only held briefly when opening and closing the db to prevent race conditions. My guess is that Sqlite itself is preventing you from closing the connection, but I'm not sure.

I'd suggest trying something like the following:

```python
from peewee import Model, SqliteDatabase, CharField, DateTimeField, IntegerField, SQL
from pathlib import Path

db_path = Path.cwd()
db = SqliteDatabase(db_path.joinpath("database.db"))

class BaseModel(Model):
    class Meta:
        database = db

class Token(BaseModel):
    # TokenType is an enum
    token_type = IntegerField(
        primary_key=True, constraints=[SQL("ON CONFLICT REPLACE")]
    )
    token_value = CharField(null=False)
    expires_at = DateTimeField(null=False)

# When you need to use the database, use context managers.
with db:
    db.create_tables([Token])

# Create a record.
with db:
    token = Token.create(token_type=2, token_value="test", expires_at="2021-01-01 00:00:00")
    print(token)
```

Unfortunately it hangs this way as well. And yes, Kodi exposes a python API so one can write add-ons for the media center. It can do anything what a normal python installation can, but it runs embedded.

And I was suspecting logs like I wrote originally, but ditched that idea. Since I tested with the following snippet:

```py
with threading.RLock():
    f = open("test.txt")
    f.write("test")
    f.close()
print("Done")
```

And it worked. I also tested if I can open, write to and close an sqlite3 database using the sqlite3 lib, worked fine too. It's only peewee that causes a block.

If you instantiate your database with `thread_safe=False` does that fix it?

```
db = SqliteDatabase(db_path.joinpath("database.db"),
    thread_safe=False)
```

Also, just for reference, Peewee should work fine with Sqlite and threads, e.g. this runs 10 threads, inserting 10 records each. The output is 100 and the program ends cleanly:

```python
import threading
from peewee import *

db = SqliteDatabase('/tmp/foo.db')

class Reg(db.Model):
    key = TextField(primary_key=True, constraints=[SQL('on conflict replace')])

db.create_tables([Reg])

def work_in_thread(i):
    for j in range(i * 10, (i * 10) + 10):
        Reg.create(key='k%02d' % j)

threads = [threading.Thread(target=work_in_thread, args=(i,))
           for i in range(10)]
for t in threads: t.start()
for t in threads: t.join()

print(Reg.select().count())
db.close()
```

> If you instantiate your database with `thread_safe=False` does that fix it?

I tried that too, unfortunately without any luck. Same blocking is there on close. But literally only on closing. And it seems to keep the file open forever too.

>Same blocking is there on close. But literally only on closing. And it seems to keep the file open forever too.

Setting `thread_safe=False` removes all locking (the `threading.RLock` is replaced by a dummy implementation that no-ops). So I think that hypothesis can be safely thrown out.

My best guess is that you have unfinalized statements and are using an oldish version of the `libsqlite3` driver that does not use `sqlite3_close_v2`. See: https://www.sqlite.org/c3ref/close.html and https://github.com/python/cpython/blob/3.8/Modules/_sqlite/util.h -- however, the standard lib sqlite3 *should* be finalizing all statements when you close the connection, so I may be wrong about what's happening.

Is the database hosted on a networked filesystem, possibly?

Otherwise I'm about out of ideas.

It's on a local NVMe storage, local fs too. sqlite3 can close databases even after writing to them just fine. Is there any way to enable verbose logging?

Or any way to force the use of sqlite3? If I understood you correctly libsqlite3 and sqlite3 are two different things.

Peewee uses `sqlite3` (the python wrapper/driver), which is specific to your version of Python installed. `libsqlite3` is the sqlite3 library installed on your system, that the python driver is wrapping.

You can insert a breakpoint into your code that calls `.close()` and step into it to see where it's getting blocked. You can add print statements / logging to peewee, etc.