database is locked

[Zsailer]

We're seeing the following issue when using jupyter-collaboration:

asctime:  2024-06-05 18:21:52,721
    exc_info:    + Exception Group Traceback (most recent call last):
  |   File "/pythonpath/lib/python3.9/site-packages/pycrdt_websocket/yroom.py", line 223, in start
  |     self._task_group.start_soon(self._broadcast_updates)
  |   File "/pythonpath/lib/python3.9/site-packages/anyio/_backends/_asyncio.py", line 678, in __aexit__
  |     raise BaseExceptionGroup(
  | exceptiongroup.ExceptionGroup: unhandled errors in a TaskGroup (1 sub-exception)
  +-+---------------- 1 ----------------
    | Traceback (most recent call last):
    |   File "/pythonpath/lib/python3.9/site-packages/pycrdt_websocket/ystore.py", line 466, in write
    |     await cursor.execute(
    |   File "/pythonpath/lib/python3.9/site-packages/sqlite_anyio/sqlite.py", line 58, in execute
    |     real_cursor = await to_thread.run_sync(self._real_cursor.execute, sql, parameters, limiter=self._limiter)
    |   File "/pythonpath/lib/python3.9/site-packages/anyio/to_thread.py", line 56, in run_sync
    |     return await get_async_backend().run_sync_in_worker_thread(
    |   File "/pythonpath/lib/python3.9/site-packages/anyio/_backends/_asyncio.py", line 2144, in run_sync_in_worker_thread
    |     return await future
    |   File "/pythonpath/lib/python3.9/site-packages/anyio/_backends/_asyncio.py", line 851, in run
    |     result = context.run(func, *args)
    | sqlite3.OperationalError: database is locked

when this happens, the whole notebook server becomes unusable. None of the documents can be opened.

This looks related to an issue we saw in jupyter_server_fileid: https://github.com/jupyter-server/jupyter_server_fileid/pull/77

I'll investigate further, but at a minimum, we need to handle this exception to avoid locking down the whole server.

[davidbrochart]

Since all SQLite operations run in a thread with a capacity limiter of 1, there cannot be any concurrent operation from a single connection. Are you accessing the database externally? If not, we could allow passing a timeout to the connection, and increase it (default value is 5 seconds), but I'm not sure it will solve the issue. It looks more like an operation doesn't complete, maybe because there is an error while reading or writing to the file. We could pass an optional exception handler to the connection, like we did in pycrdt-websocket, and use that to just log the exception instead of raising it, what do you think?

[Zsailer]

Are you accessing the database externally?

Good question, but no, we don't have any logic accessing the database externally.

It looks more like an operation doesn't complete, maybe because there is an error while reading or writing to the file.

I agree here. It looks like some exception was raised. In typical sqlite3, the Connection object is also a context manager, and this context manager can roll back any uncommitted changes if an exception is raised.

With this library, could we have something similar? aiosqlite has an async context manager API making this possible. Ideally we could have that here too.

[davidbrochart]

Sure we could do that, but what happens if we don't store updates? This could lead to documents loosing changes.

[Zsailer]

Sure we could do that, but what happens if we don't store updates? This could lead to documents loosing changes.

I'm not sure. I can test this a bit.

Either way, though, losing some changes (and logging some errors) is less bad (I think) than blocking the database entirely. In Jupyter Collaboration, this locks up the server until it is restarted.

[davidbrochart]

It's debatable wether loosing changes and keeping the server up with a false feeling that everything is working fine, is better than crashing.

[davidbrochart]

I opened #4.