On Mar 30, 2011, at 6:48 PM, Ajay wrote: > We use sqlalchemy 0.4.6 with elixir 0.5.2 and zope. > > Below is some excerpt to show how we currently create/manage one > engine instance per zope thread. > > From what I understand from reading the documentation, the > 'connection' and 'transaction' objects themselves are not thread safe > - but the engine instance when bound to metadata can manage > connections from a pool and as long as we 'close' (release back to > pool) within each thread then it may be safe. > > We want to try to limit our application from consu,ing too many > database conections. > So can somebody confirm whether it's prudent to change our approach > and share the engine instance among multiple threads?
The engine is absolutely threadsafe. It has no state other than its reference to the pool, which is designed to work in a threaded environment (that said, I couldn't recall what issues have been fixed in the pool since 0.4.6, which was three years ago, but there have been several). There is no need to store engines per threads or to use the ThreadLocalMetaData object for pretty much anything. TLMD is built for the almost-never use case of an application that wants to use bound metadata (which is already a use case I've been de-emphasizing for three years) with a different DSN per thread, like a web application where each request deals with a different database. If anything the approach you have below will guarantee that the app would use as many connections as there are threads in the application, even when those threads are dormant. > > thanks > Aj > > > +++++++++++++++++++++++ > # global declarations > __session__ = > scoped_session(sessionmaker(twophase=False,transactional=True,autoflush=True)) > __metadata__ = ThreadLocalMetaData() > > _tld = threading.local() > > # Manage one engine instance per thread > if not hasattr(thread_local_data,'txm_orm_engine_dict'): > # THREAD LOCAL ATTR DOESN'T EXIST. CREATE IT. > thread_local_data.txm_orm_engine_dict = dict() > engine = sqlalchemy.create_engine(dsn,echo=True) > > # UPDATE DICTIONARY > thread_local_data.txm_orm_engine_dict[dsn] = engine > else: > # ALREADY THREAD LOCAL ENGINE DICTIONARY. > if thread_local_data.txm_orm_engine_dict.has_key(dsn): > # ALREADY AN ENGINE FOR THIS DSN. USE IT > engine = > thread_local_data.txm_orm_engine_dict[dsn] > > else: > # NO ENGINE FOR THIS DSN. CREATE IT > engine = > sqlalchemy.create_engine(dsn,echo=True) > # UPDATE DICTIONARY > thread_local_data.txm_orm_engine_dict[dsn] = > engine > > > __metadata__.bind = engine > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
