Is Engine instance object threadsafe?

[Ajay]

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?

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

[Michael Bayer]

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.

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>