DB API and thread safety

Robin Haswell wrote:

Hey guys

I've been reading http://www.python.org/peps/pep-0249.html and I don't
quite get what level of thread safety I need for my DB connections.

If I call db = FOOdb::connect() at the start of my app, and then every
thread does it's own c = db.cursor() at the top, what level of thread
safety do I need to avoid threads stepping on each other? Hopefully the
answer to this question will get me oriented enough to understand the
other options :-)

Assuming the cursor created in the thread is never accessed by another
thread then you need a dbapi module that supports threadsafety level 2 -
"threads may share the module and connections".

- Matt

--
__
/ \__ Matt Goodall, Pollenation Internet Ltd
\__/ \ w: http://www.pollenation.net
__/ \__/ e: ma**@pollenation.net
/ \__/ \ t: +44 (0)113 2252500
\__/ \__/
/ \ Any views expressed are my own and do not necessarily
\__/ reflect the views of my employer.

Robin Haswell wrote:

Hey guys

I've been reading http://www.python.org/peps/pep-0249.html and I don't
quite get what level of thread safety I need for my DB connections.

If I call db = FOOdb::connect() at the start of my app, and then every
thread does it's own c = db.cursor() at the top, what level of thread
safety do I need to avoid threads stepping on each other? Hopefully the
answer to this question will get me oriented enough to understand the
other options :-)

Even if you can share the connection between threads, it will be the
rare database which can actually work concurrently on one connection.
Most drivers promising "threads may share the module and connections"
will use a lock so that only one operation is active at a time.

For maximum throughput (at the cost of using more ressources on the
database), you'd use one connection per thread. If you want to use
transactions, then it is the only way.

Daniel