cx_Oracle, generators, and threads in Python

Question

Hi all,

What is the behavior of cx_Oracle cursors when the connection object is used by different threads? How would generators affect this behavior? Specifically...

Edit: The original example function was incorrect; a generator was being returned by a sub function, yield wasn't used directly in the loop. This clarifies when finally is executed (after return does), but still doesn't answer whether a cursor can be used if another thread starts using the connection object the cursor was created from. It actually seems (in python 2.4, at least), try...finally with yield causes 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 by multiple threads. The connections are created with the threaded=False argument.

I'm wondering...

1. Is thread 1's cursor object still usable if thread 2 comes along and uses the same connection object? If not, what might happen?

The behavior i'm seeing is an exception in cx_Oracle talking about a protocol error, and then a segfault follows.

Answer 1

See the docs: threadsafety is, and I quote,

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

So your "pool of cursors" construct (where one cursor may be used by different threads) seems to be beyond the threadsafety level. It's not an issue of sharing connections (that's OK since you've passed threaded properly in the connection's constructor) but cursors. You may want to store each cursor in threading.local after the first time a thread has used it, so that each thread can have its own 1-cursor "pool" (not a key optimization, though: making a new cursor is not a heavy-duty operation).

Wrt your question 2, the finally clause executes when the generator object (built by a call to your generator function Get) is all done -- either because it's raising StopIteration, or because it's being garbage collected (typically because the last reference to it has just gone away). E.g if the caller is:

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'

the finally executes after (at most) 3 rows have been yielded.