SQLite Multithreading issue

[arash afshar]

I am using sqlalchemy version 0.9.9 with SQLite backend which stores data on a file on disk.

Moreover, I am using ORM model for all my queries and handle sessions using scoped_session to work with multiple threads.

The application is a desktop application which should be platform independent.

The problem is that I receive an error which I cannot debug or find its origin!

On some operating systems (Windows 7 and Ubuntu 14.04) I get a "single" error message:

      'no handlers could be found for logger "sqlalchemy.pool.nullpool"'

while in another OS (CentOS), I get an error that says a session created in one thread cannot be used in another thread ....

These error messages are shown only once but they does "not" hinder the normal operation of my python program.

I have gone over the code many times and I am certain that I am not passing the session to another thread. Any ideas why this happens?

Here is the workflow of the application:

1) I have a main thread which calls the scoped_session and stores its result in a module level variable called "ScopedSession"

2) The main thread creates two listener threads.

3) Each of these two listener threads, spawn multiple worker threads as needed.

4) In any of the above threads (main, listener, and workers) whenever I need to get a session, I call ScopedSession()

5) At the end of worker threads, I manually call to ScopedSession().commit() or ScopedSession().rollback() followed by ScopedSession().close()

based on the documentation, I do not call ScopedSession().remove() since it is supposed to be done through garbage collection. I also do not pass the session of a worker thread to any other thread.

Any help in resolving this problem is appreciated.

Thanks

[Mike Bayer]

On 4/6/15 7:12 PM, arash afshar wrote:
> I am using sqlalchemy version 0.9.9 with SQLite backend which stores
> data on a file on disk.
> Moreover, I am using ORM model for all my queries and handle sessions
> using scoped_session to work with multiple threads.
> The application is a desktop application which should be platform
> independent.
>
> The problem is that I receive an error which I cannot debug or find
> its origin!
>
> On some operating systems (Windows 7 and Ubuntu 14.04) I get a
> "single" error message:
> 'no handlers could be found for logger "sqlalchemy.pool.nullpool"'

that's a warning, not an error. the pool wants to say that it had
trouble shutting down a connection.

> while in another OS (CentOS), I get an error that says a session
> created in one thread cannot be used in another thread ....

That's not an error message that SQLAlchemy generates, that's more of a
SQLite issue. The exact text and stack trace would help.

> These error messages are shown only once but they does "not" hinder
> the normal operation of my python program.

that's a hint that this trace is occurring during Python garbage
collection, which is why it just warns.

>
> I have gone over the code many times and I am certain that I am not
> passing the session to another thread. Any ideas why this happens?

A stack trace would show more but it sounds like SQLite connections,
cursors, or transactions are being left open and then collected by
Python's garbage collector asynchronously (e.g. in a different thread),
generating warnings like these.

> Here is the workflow of the application:
> 1) I have a main thread which calls the scoped_session and stores its
> result in a module level variable called "ScopedSession"
> 2) The main thread creates two listener threads.
> 3) Each of these two listener threads, spawn multiple worker threads
> as needed.
>
> 4) In any of the above threads (main, listener, and workers) whenever
> I need to get a session, I call ScopedSession()
> 5) At the end of worker threads, I manually call to
> ScopedSession().commit() or ScopedSession().rollback() followed by
> ScopedSession().close()

The worker threads need to ensure that they use a Session in which they
have also created and that they close it in that same thread, since the
Pysqlite module here is forcing you to use a certain connection only in
one thread.

[arash afshar]

Excellent insight.

Thank you :)