Abstract

At the moment we are using a hacky way to have some form of concurrency at the DB access. We are using a single DB connection. And we are using the sqlite progress handler to initiate gevent context switches, so that gevent switches its context to other greenlets and not have to wait for very long DB reads.

For DB writes we have introduced the concept of a critical section which means that:

1. Before going into the DB write all progress handlers should have exited and nothing else should be open
2. When going into the write transaction progress handler is disabled. So we never context switch so long as the write transaction is ongoing. Once committed we re-enable the progress handler

This works. But with caveats. It has been the cause of many weird bugs, and lately with changes in sqlite very hard to trace crashes.

And since sqlite considers this hacky we can't rely on it. We need a different way. That one woudl be connection pooling with progress handler.

Task

Create a connection pool for the DBs. Each DB will have multiple connections it can give to anyone requesting it. We should edit the conn.write_ctx and conn.read_ctx to essentially ask for a connection and return a cursor to that connection. If we do that, and also move it to the DB level (and not the connection level anymore) then it should all just work out.

Each connection should still have the progress handler logic, and it should still geven.sleep(0) to context switch out of the greenlet. But since doing so it woudl only context switch to a different connection and the same connection would never, ever be accessed by different greenlets (even for reading) then we should "hopefully" avoid an entire range of problems we are seeing with segfaults on gevent + sqlite.