Database locked

[scottyeager]

It's possible to end up in a state where basically every request to load some data results in an error like this:

[+] peppermint: INFO:     2a10:b600:1::cc4:7a30:3a9c:41168 - "GET /?id_input=20&select=node&show_empty=&sort_by=period HTTP/1.1" 200 OK
[+] peppermint: INFO:     2a10:b600:1::cc4:7a30:3a9c:41168 - "GET /node/20?show_empty=False HTTP/1.1" 500 Internal Server Error
[-] peppermint: ERROR:    Exception in ASGI application
[-] peppermint: Traceback (most recent call last):
[-] peppermint:   File "/root/peppermint/venv/lib/python3.12/site-packages/uvicorn/protocols/http/httptools_impl.py", line 401, in run_asgi
[-] peppermint:     result = await app(  # type: ignore[func-returns-value]
[-] peppermint:              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
[-] peppermint:   File "/root/peppermint/venv/lib/python3.12/site-packages/uvicorn/middleware/proxy_headers.py", line 60, in __call__
[-] peppermint:     return await self.app(scope, receive, send)
[-] peppermint:            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
[-] peppermint:   File "/root/peppermint/venv/lib/python3.12/site-packages/starlette/applications.py", line 113, in __call__
[-] peppermint:     await self.middleware_stack(scope, receive, send)
[-] peppermint:   File "/root/peppermint/venv/lib/python3.12/site-packages/starlette/middleware/errors.py", line 187, in __call__
[-] peppermint:     raise exc
[-] peppermint:   File "/root/peppermint/venv/lib/python3.12/site-packages/starlette/middleware/errors.py", line 165, in __call__
[-] peppermint:     await self.app(scope, receive, _send)
[-] peppermint:   File "/root/peppermint/venv/lib/python3.12/site-packages/starlette/middleware/sessions.py", line 85, in __call__
[-] peppermint:     await self.app(scope, receive, send_wrapper)
[-] peppermint:   File "/root/peppermint/venv/lib/python3.12/site-packages/starlette/middleware/exceptions.py", line 62, in __call__
[-] peppermint:     await wrap_app_handling_exceptions(self.app, conn)(scope, receive, send)
[-] peppermint:   File "/root/peppermint/venv/lib/python3.12/site-packages/starlette/_exception_handler.py", line 62, in wrapped_app
[-] peppermint:     raise exc
[-] peppermint:   File "/root/peppermint/venv/lib/python3.12/site-packages/starlette/_exception_handler.py", line 51, in wrapped_app
[-] peppermint:     await app(scope, receive, sender)
[-] peppermint:   File "/root/peppermint/venv/lib/python3.12/site-packages/starlette/routing.py", line 715, in __call__
[-] peppermint:     await self.middleware_stack(scope, receive, send)
[-] peppermint:   File "/root/peppermint/venv/lib/python3.12/site-packages/starlette/routing.py", line 735, in app
[-] peppermint:     await route.handle(scope, receive, send)
[-] peppermint:   File "/root/peppermint/venv/lib/python3.12/site-packages/starlette/routing.py", line 288, in handle
[-] peppermint:     await self.app(scope, receive, send)
[-] peppermint:   File "/root/peppermint/venv/lib/python3.12/site-packages/starlette/routing.py", line 76, in app
[-] peppermint:     await wrap_app_handling_exceptions(app, request)(scope, receive, send)
[-] peppermint:   File "/root/peppermint/venv/lib/python3.12/site-packages/starlette/_exception_handler.py", line 62, in wrapped_app
[-] peppermint:     raise exc
[-] peppermint:   File "/root/peppermint/venv/lib/python3.12/site-packages/starlette/_exception_handler.py", line 51, in wrapped_app
[-] peppermint:     await app(scope, receive, sender)
[-] peppermint:   File "/root/peppermint/venv/lib/python3.12/site-packages/starlette/routing.py", line 73, in app
[-] peppermint:     response = await f(request)
[-] peppermint:                ^^^^^^^^^^^^^^^^
[-] peppermint:   File "/root/peppermint/venv/lib/python3.12/site-packages/fasthtml/core.py", line 446, in _endp
[-] peppermint:     if not resp: resp = await _wrap_call(self.f, req, self.sig.parameters)
[-] peppermint:                         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
[-] peppermint:   File "/root/peppermint/venv/lib/python3.12/site-packages/fasthtml/core.py", line 427, in _wrap_call
[-] peppermint:     return await _handle(f, wreq)
[-] peppermint:            ^^^^^^^^^^^^^^^^^^^^^^
[-] peppermint:   File "/root/peppermint/venv/lib/python3.12/site-packages/fasthtml/core.py", line 233, in _handle
[-] peppermint:     return (await f(*args, **kwargs)) if is_async_callable(f) else await run_in_threadpool(f, *args, **kwargs)
[-] peppermint:                                                                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
[-] peppermint:   File "/root/peppermint/venv/lib/python3.12/site-packages/starlette/concurrency.py", line 39, in run_in_threadpool
[-] peppermint:     return await anyio.to_thread.run_sync(func, *args)
[-] peppermint:            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
[-] peppermint:   File "/root/peppermint/venv/lib/python3.12/site-packages/anyio/to_thread.py", line 56, in run_sync
[-] peppermint:     return await get_async_backend().run_sync_in_worker_thread(
[-] peppermint:            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
[-] peppermint:   File "/root/peppermint/venv/lib/python3.12/site-packages/anyio/_backends/_asyncio.py", line 2405, in run_sync_in_worker_thread
[-] peppermint:     return await future
[-] peppermint:            ^^^^^^^^^^^^
[-] peppermint:   File "/root/peppermint/venv/lib/python3.12/site-packages/anyio/_backends/_asyncio.py", line 914, in run
[-] peppermint:     result = context.run(func, *args)
[-] peppermint:              ^^^^^^^^^^^^^^^^^^^^^^^^
[-] peppermint:   File "/root/peppermint/main.py", line 79, in get
[-] peppermint:     receipts = make_node_minting_periods(node_id, receipt_handler.get_node_receipts(node_id))
[-] peppermint:                                                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
[-] peppermint:   File "/root/peppermint/receipts.py", line 256, in get_node_receipts
[-] peppermint:     return self.fetch_and_process_node(node_id)
[-] peppermint:            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
[-] peppermint:   File "/root/peppermint/receipts.py", line 214, in fetch_and_process_node
[-] peppermint:     self.save_receipt(receipt)
[-] peppermint:   File "/root/peppermint/receipts.py", line 136, in save_receipt
[-] peppermint:     conn.execute(
[-] peppermint: sqlite3.OperationalError: database is locked

This seems to coincide with the release of new minting receipts, which would make sense because it results in more writes of new receipts.

Some thoughts:

• Are we using WAL mode for reads such that writes don't get blocked by reads?
• Is there any effort to avoid multiple writes in the connection pooling we use?
• Maybe it would be better to have a single job that does all fetching and writing of receipts. We could just precache the full set to start and periodically fetch new receipts for all nodes in an intelligent way. This would remove any synchronous dependence on the receipts API, thus improving robustness and performance in cases where a call to the receipts API is currently made

[scottyeager]

Two improvements have been implemented:

1. Add a 5 second timeout on database accesses
2. If a database locked error happens anyway, return an error to the user rather than aborting

I don't know that this addresses the root issue though, which is that the database seems to get persistently locked (by some failed task perhaps?).

Also of interest for documentation is a way to test the behavior when the database is locked:

1. Delete or move the existing receipts.db, such that any operation results in new writes
2. Start up the web server
3. Open a write transaction in a separate process, such as an sqlite3 shell:

CREATE TABLE IF NOT EXISTS test_table (                                                                                                                                    
     id INTEGER PRIMARY KEY,                                                                                                                                                
     some_column TEXT                                                                                                                                                       
 );                                                                                                                                                                         
INSERT INTO test_table (id, some_column) VALUES (1, 'initial_value');                                                                                                      
BEGIN;                                                                                                                                                                     
UPDATE test_table SET some_column = 'locked_value' WHERE id = 1;

4. Now try some operation in Peppermint, which will fail due to db being locked