Cx_Oracle, generators and threads in Python

Question

What is the behavior of cx_Oracle cursors when a connection object is used by different threads? How do generators influence this behavior? In particular...

Change The original function of the example was incorrect; the generator was returned by a subfunction; yield not used directly in the loop. This clarifies when finally (after return ) is executed, but still does not answer whether the cursor can be used if another thread starts using the connection object from which the pointer was created. Actually it seems (at least in python 2.4), try...finally with yield raises a syntax error.

 def Get() conn = pool.get() try: cursor = conn.cursor() cursor.execute("select * from table ...") return IterRows(cursor) finally: pool.put(conn) def IterRows(cursor): for r in cursor: yield r 

Get() is a function called multiple threads. Connections are created using the threaded=False argument.

I wonder ...

• Is cursor 1 object still useful if stream 2 comes in and uses the same connection object? If not, what could happen?

The behavior I see is an exception in cx_Oracle talking about a protocol error, and then segfault follows.

Answer 1

See documents : threadsafety are threadsafety , and I quote,

Currently 2, which means that threads can share module and connections, but not cursors.

Thus, your cursor pool design (where one cursor can be used by different threads) seems to be higher than threadsafety . This is not a connection exchange problem (this is normal since you successfully passed threaded in the connection designer), but cursors. You may want to save each cursor in threading.local after the first thread has used it, so that each thread can have its own "pool" with 1 cursor (but not key optimization, though: creating a new cursor is not hard work).

In answer to your question 2, the finally clause is executed when the generator object (built by calling your Get generator function) is executed - either because it raises StopIteration , or because it is garbage collection (usually because the last link to it just disappeared). For example, if the caller:

 def imthecaller(): for i, row in enumerate(Get()): print i, row if i > 1: break # this is the moment the generators' finally-clause runs print 'bye' 

finally is executed after (no more) three lines: yield ed.