That's a bit of a complicated topic. Database consistency is generally ensured by using transactions with an isolation level that is appropriate for your usage. Each thread/connection will be operating in a transaction. The transaction isolation level determines when one thread will see the results that another thread has written:
https://en.wikipedia.org/wiki/Isolation_(database_systems)#Isolation_levels https://sqlite.org/isolation.html https://www.postgresql.org/docs/9.1/static/transaction-iso.html Simon On Mon, Jan 15, 2018 at 9:39 AM, <ginger...@gmail.com> wrote: > Simon, > > Thanks for your kind answer. > So when I have several threads trying to insert/update the same database > (pgsql or sqlite) at the same time, how can I ensure consistency? By using > scoped session? This will rely on the DB implementation behind, right? > > 在 2018年1月15日星期一 UTC+8下午5:21:42，Simon King写道： >> >> On Mon, Jan 15, 2018 at 8:34 AM, <ging...@gmail.com> wrote: >> > Hello, >> > >> > Sessions are not thread safe. >> > But for the scoped sessions, each thread will have its own session, my >> > question is, even the scoped session itself rely on the thread safety >> > feature of the DB behind? >> > for example, PGSQL server may handle multiple connections and resolving >> > the >> > update/insert requests, but for SQLITE3, normally it's NOT thread safe, >> > how >> > scoped session will behave towards such DB system? >> > >> > Not sure if I'm stating clearly here, Thanks! >> >> A session (scoped or otherwise) normally operates on a single >> connection, either because it was explicitly bound to a connection, or >> because it was bound to an engine and it checked a connection out of >> the engine's connection pool. This usually ensures that a connection >> is never used simultaneously by multiple threads. >> >> SQLAlchemy does have some special behaviour for SQLite, described here: >> >> >> http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#threading-pooling-behavior >> >> It says that connections will never be shared between threads, and for >> file-based SQLite databases, the default is for there to be no >> connection pooling (it uses a NullPool). Whenever a connection is >> requested from the pool, it will always be freshly created, and >> discarded when it is returned to the pool. >> >> I assume that sqlite itself will be operating in "Multi-thread" mode >> as described at https://sqlite.org/threadsafe.html >> >> Hope that helps, >> >> Simon -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
