4

i have a real headache from trying to understand the cause of the following problem. We are using a combination of the following libraries:

The SQLAlchemy was first using NullPool and now is configured to utilize QueuePool. I am also using the following idiom to have a new DB session firing up for each thread (as per my understanding)

Session = sessionmaker(bind=create_engine(classes.db_url, poolclass=QueuePool))

@contextmanager
def session_scope():
   session = Session()
   try:
      yield session
      session.commit()
   except:
      session.rollback()
      raise
   finally:
      session.close()

@bot.message_handler(content_types=['document'])
def method_handler:
   with session_scope() as session:
      do_database_stuff_here(session)

Nevertheless I am still getting this annoying exception: (sqlite3.ProgrammingError) SQLite objects created in a thread can only be used in that same thread

Any ideas? ;) In particular, i don't get how it is possible for another tread to get somewhere in between the db operations...this is probably the reason of the pesky exception

update 1: if i change the poolclass to SingletonThreadPool, then there seems to be no more errors coming up. However, the documentation of SQLAlchemy tells that it's not production rife.

Tibebes. M
  • 6,940
  • 5
  • 15
  • 36
d56
  • 825
  • 1
  • 9
  • 26
  • Do you mean `NullPool` when you say `NullThreadPool`? Why do you need to use `QueuePool`? – xli Oct 22 '16 at 18:43
  • From SQLAlchemy documentation: "When a file-based database is specified, the dialect will use NullPool as the source of connections. This pool closes and discards connections which are returned to the pool immediately. SQLite file-based connections have extremely low overhead, so pooling is not necessary. The scheme also prevents a connection from being used again in a different thread and works best with SQLite’s coarse-grained file locking." – xli Oct 22 '16 at 18:44
  • As xli says, why are you trying to use a `QueuePool`? The [docs](http://docs.sqlalchemy.org/en/rel_1_1/dialects/sqlite.html#threading-pooling-behavior) are quite clear that you can use `NullPool`, `SingletonThreadPool`, or `StaticPool`. – Peter Brittain Oct 22 '16 at 21:49
  • @xli, yes i mean `NullPool` ;) thank for the correction. The idea to use `QueuePool` was to have several (reading) threads available at the same time. Why would it be wrong? @xli, @Peter Brittain, the default implementation of `SqlAlchemy` uses `NullPool` but i have been getting the exceptions (as posted above) when using it. I have no idea why i get them, since through the use of @contextmanager it should be guaranteed that i open one connection per thread... – d56 Oct 23 '16 at 14:55
  • I got the error you show with multiple threads doing database operations with `QueuePool`, but it went away with `NullPool`. Could you be having multiple threads within you `with` block, or could you describe what you're doing there? – xli Oct 23 '16 at 15:10
  • @xli, the method `method_handler()` is accessed by multiple threads, but there are no new thread calls inside of `do_database_stuff_here()`. That is why i don't get why it does not work. But why do you get the error with `QueuePool`? accoding to my reasoning, although the threads are reused, there is still one thread serving the `method_handler()` isn't it? – d56 Oct 24 '16 at 08:23
  • The same SQLite connection cannot be used in more than one thread. `QueuePool` will re-use connections and give the same connection to another thread. But `NullPool` should avoid this issue because it makes a new connection each time, and using `NullPool` caused the error to go away when I reproduced it. – xli Oct 24 '16 at 15:25
  • i am checking it now with `NullPool`. One question aside though. Does the use of `with session_scope() as session` imply that i have a blocking code segment here as long the the `NullPool` is used for some other thread's connection? I read that Sqlite allos WAL nowdays for concurrent reads. Is there a way to activate it in SQLAlchemy – d56 Oct 26 '16 at 08:52
  • @xli, the reason is that the `with ... session` block is a pretty long-running segment with more than just a db connection happening there (which i cannot decouple) – d56 Oct 26 '16 at 08:55
  • No - it isn't blocking. It relies on the locking done by sqlite, which as you can see [here](https://www.sqlite.org/lockingv3.html) will continue to allow concurrent read access. However, you might find write access is blocked if you keep a transation open for these long-running processes. If that's unavoidable, [WAL mode](https://www.sqlite.org/wal.html) may be your best option. – Peter Brittain Oct 26 '16 at 09:40
  • @xli, thanks again. As far as i can see from the logs, the error does not come up any more. Setting the poolclass to `NullPool` has done the trick! – d56 Nov 14 '16 at 16:08

2 Answers2

5

As you can see in the source, sqlite will raise this exception inside pysqlite_check_thread if the connection object is reused across any threads.

By using a QueuePool, you are telling SQLAchemy it is safe to reuse connections across multiple threads. It will therefore just pick a connection from the pool for any session no matter which thread it is on. This is why you're hitting the error. The first time you create and use a connection, you'll be fine; however the next use will probably be on a different thread and so fail the check.

This is why SQLAlchemy mandates the use of other pools such as SingletonThreadPool and NullPool.

Assuming you are using a file based database, you should use the NullPool. This will give you good concurrency on reads. Write access concurrency is always going to be an issue for sqlite; if you need this, you probably want a diffenet database.

Peter Brittain
  • 13,489
  • 3
  • 41
  • 57
4

Something that may be worth trying: use scoped_session instead of your contextmanager; scoped_session implicitly creates a thread-local session when it is accessed from a different thread. Be sure also to use NullPool.

from sqlalchemy.orm import scoped_session
sessionmaker(bind=create_engine(classes.db_url, poolclass=NullPool))
session = scoped_session()

Note that you can use this scoped session directly as if it were just a regular session, even though it is actually creating thread-local sessions behind the scenes when it is being used.

For scoped_session, should call session.remove() for after you're done (i.e., after each method_handler) and explicitly call session.commit() as needed.

In theory, your context manager should work in giving each thread its own session, but, for lack of better explanation, I wonder if there are multiple threads accessing that session within the context.

xli
  • 1,298
  • 1
  • 9
  • 30