[sqlalchemy] Re: problems with threadlocal strategy and ResultProxy

[Michael Bayer]

On Apr 10, 2007, at 7:44 AM, Manlio Perillo wrote:


 Yes.
  From the documentation it is not clear if this is allowed.


the word threadlocal means that a resource is tied to the current  
thread.  therefore, if you take resources which expect this behavior  
and then send them off to three different threads during their  
lifecycle, they will not have consistent access to the resources they  
require.  threadlocal strategies are not compatible with sharing  
those objects between threads - they are designed to allow global  
resources that are automatically held within the scope of a thread.   
with twisted, nothing except your initial engine (and metadata,  
Tables) should be held in a global scope, everything else (sessions,  
connections, transactions) should be declared and torn down within a  
local scope.




--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~--~~~~--~~--~--~---

[sqlalchemy] Re: problems with threadlocal strategy and ResultProxy

[Michael Bayer]

id very much prefer a test case that isnt using Twisted since Ive no  
idea what twisted does with threads.  it looks like the result proxy  
is being shared between threads (which i believe is an effect of the  
twisted reactor anyway?).

if youre playing with threads to that degree the threadlocal  
strategy may be too simplistic.


On Apr 9, 2007, at 5:41 PM, Manlio Perillo wrote:


 Hi.

 I have found some problems with threadlocal strategy and the  
 ResultProxy.

 Here is the code that reproduces the problem, Twisted and  
 PostgreSQL are
 required.

 I'm using SQLAlchemy 0.3.6-1 from Debian Etch.

 I don't know if this is a bug of SQLAlchemy.
 Is the ResultProxy thread safe?



 #! /usr/bin/env python
 import sys

 from twisted.python import log
 from twisted.internet import defer, threads, reactor

 import sqlalchemy as sq


 def callback(result, i):
  # Is the ResultProxy thread safe?
  if USE_CURSOR:
  rows = list(result)

  log.msg('callback', i)

 def errback(reason):
  log.err(reason)

 def f():
  l = []

  for i in range(N):
  log.msg('request', i)
  d = threads.deferToThread(db.execute, QUERY)
  d.addCallbacks(callback, errback, callbackArgs=(i,))
  l.append(d)

  return defer.DeferredList(l)

 def main():
  return f().addCallbacks(lambda _: None, lambda r: log.err(r)
  ).addBoth(lambda _: reactor.stop())


 POOL_SIZE = 1
 N = 10
 QUERY = 'SELECT CURRENT_TIMESTAMP'
 URL = 'postgres://manlio:[EMAIL PROTECTED]/test'
 STRATEGY = 'threadlocal'
 USE_CURSOR = False


 reactor.suggestThreadPoolSize(POOL_SIZE)
 db = sq.create_engine(URL, pool_size=POOL_SIZE, strategy=STRATEGY,
echo_pool=False)

 log.startLogging(sys.stdout)
 reactor.callWhenRunning(main)

 reactor.run()



 When USE_CURSOR is False, I got (at random) the error:
File
 /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line  
 718,
 in execute
  connection = self.contextual_connect 
 (close_with_result=True)
File
 /usr/lib/python2.4/site-packages/sqlalchemy/engine/threadlocal.py,
 line 148, in contextual_connect
  return self.session.get_connection(**kwargs)
File
 /usr/lib/python2.4/site-packages/sqlalchemy/engine/threadlocal.py,
 line 21, in get_connection
  return TLConnection(self,  
 close_with_result=close_with_result)
File
 /usr/lib/python2.4/site-packages/sqlalchemy/engine/threadlocal.py,
 line 63, in __init__
  base.Connection.__init__(self, session.engine,
 close_with_result=close_with_result)
File
 /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line  
 368,
 in __init__
  self.__connection = connection or engine.raw_connection()
File
 /usr/lib/python2.4/site-packages/sqlalchemy/engine/threadlocal.py,
 line 127, in raw_connection
  return self.connection_provider.get_connection()
File
 /usr/lib/python2.4/site-packages/sqlalchemy/engine/default.py, line
 19, in get_connection
  return self._pool.connect()
File /usr/lib/python2.4/site-packages/sqlalchemy/pool.py,
 line 150, in connect
  return
 self._threadconns[thread.get_ident()].connfairy().checkout()
  exceptions.AttributeError: 'NoneType' object has no attribute
 'checkout'


 When USE_CURSOR is True, I obtain (at random) any of these two errors:
File ./concurrency.twisted.py, line 12, in callback
  rows = list(result)
File
 /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line  
 921,
 in __iter__
  row = self.fetchone()
File
 /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line  
 997,
 in fetchone
  self.close()
File
 /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line  
 862,
 in close
  self.cursor.close()
File /usr/lib/python2.4/site-packages/sqlalchemy/pool.py,
 line 312, in close
  if self in self.__parent._cursors:
  exceptions.TypeError: iterable argument required


 and:
File
 /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line  
 719,
 in execute
  return connection.execute(statement, *multiparams,  
 **params)
File
 /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line  
 444,
 in execute
  return Connection.executors[c](self, object,  
 *multiparams,
 **params)
File
 /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line  
 458,
 in execute_text
  cursor = self._execute_raw(statement, parameters)
File
 /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line  
 540,
 in _execute_raw
  cursor = self.__engine.dialect.create_cursor 
 (self.connection)
File