Standalone web2py-DAL with Pyramid - requests, threads and memory

Question

I'm building an app on Pyramid framework and would like to use the web2py-DAL with it. Firebird-embedded is the database of choice.

That works great until I try to call multiple views from one web page asynchronously. Different errors like "Invalid cursor state", "Invalid cursor reference" or "Attempt to reclose a closed cursor" are coming up from the kinterbasdb driver where sqlite just breaks down without any error messages and takes python with it. These view callables are doing nothing but simple reads by SELECTing.

This happens in the case that pyramids root factory returns the same DAL object with each request. It seems like threads form different requests are working with the same cursor object, so the cursor gets closed while the other thread assumes the cursor to be here.

If I create a new DAL object on each request I get an other problem - every new connection on each request allocates memory and this memory doesn't get free. So after some docents requests there are some hundred MB wasted memory.

Unfortunately Sqlalchemy is not an option for this project.

Are there any ideas?

Answer 1

The point was that the DAL object should be created on every request. But after that it must be closed manually.

I did it in that way: There is a

request.add_finished_callback

property of the request object, so I extended the DAL object in that way:

class Root(DAL):
    def __init__(self, request, uri):
        DAL.__init__(self, uri, pool_size=0)
        request.add_finished_callback(self._close)

    def _close(self, request):
        self._adapter.close_all_instances('commit')

A new Root-object is then returned by the root_factory on each request.

Thanks to the web2py-users group!