On Dec 6, 2007, at 8:57 AM, imgrey wrote:
> >> i can only make comments about fragments like this but i cant address >> your full design issue since you havent supplied a fully working >> illustration of what it is youre trying to do. > > > the daemon: http://dpaste.com/hold/27089/ > and required utils.py modele: http://dpaste.com/hold/27092/ > > it was running with revision 3863 OK, your Watch class is procesing events as they occur. a particular event is bounded by the run() method of the watch class; the reasonable place for you to create/destroy a Session is at the boundaries of handling those events - so "arrange" should open a session at the start of its run() method, and close() it at the end. the scope of transaction should also be bordered by the run() method. if you want to keep using scoped_session(), thats fine, but i think you need to understand what that means if you are going to use it. I see you have lock.acquire() and lock.release() pairs around every usage of the session - this doesn't do anything since the scoped_session is a "thread local" manager of sessions (it might achieve some locking against concurrent database access but database locks, but not really very well the way its laid out). heres a wikipedia article on "threadlocal": http://en.wikipedia.org/wiki/Thread-local_storage . your app is currently using a unique Session for every thread because you have configured scoped_session(). Theres nothing wrong with using scoped_session(), it means that the start of run() would say, Session(), to open a new session, and the end would say Session.close(), to close it out, release connections/ transactions, etc. This lifespan is described here: http://www.sqlalchemy.org/docs/04/session.html#unitofwork_contextual_lifespan >> > > How to open one session for all threads ? Using Lock with inserts/ > updates should help in this case, right ? you dont want to open one session for all threads. the session itself is not threadsafe (read the last item) : http://www.sqlalchemy.org/docs/04/session.html#unitofwork_using_faq . Theres no reasonable reason you'd want to share a session among threads, unless you are trying to use it as a cache. if you are trying to use it as a cache for many threads, this is not its intended purpose; id recommend using a plain dict or memcached for that instead. also id get the whole app to work completely before adding any caching. Also, its not typical to have one transaction/connection shared among threads either. if thats what youre trying to go for here, and thats why you have all the locking going on, that implies your app would just have one database connection in total. if you wanted to do that, then bind the session like this: the_one_connection = engine.connect() sess = Session(bind=the_one_connection) then youd have many sessions all commuicating to the same underlying connection. im not sure how well psycopg2 allows multiple threads to use a single connection (you certainly dont want to do it with mysqldb....). but id recommend going for a transaction-per-event approach instead. > > I was trying to represent this SQL statement in ORM: "DELETE FROM > fs_file WHERE path='/' and user_id=<theone.id>". If I'd need to delete > user I'll use session.query.delete(user), indeed. But how to perform > writing operations in proper way then, without execute() ? I was > looking, thinking, asking and thinking again but came to nithing yet ) if you had already looked up the Path with those attributes, and you have a Path instance, you can just session.delete(mypath). If not, then sure just issue the DELETE. as far as the SELECTs, if you issue your SELECT like this: SELECT * from file where path='/' AND user_id=id FOR UPDATE within a transaction, that statement will lock those rows explicitly against other transactions doing the exact same thing (i.e. they say FOR UPDATE also). Then, if you want to delete those rows: DELETE from file where path='/' AND user_id=id; COMMIT; the other transaction then wakes up after the first one issues COMMIT; its SELECT ..FOR UPDATE returns zero rows because the row was deleted (or it returns whatever rows still remain). this is generally how you should be achieving your locking against individual "file" records. if youd like to select using the Session with this method, use session.query(cls).with_lockmode('update').filter(...).first() . watch your SQL echoing and youll see it do a SELECT..FOR UPDATE in every case, locking every row it touches against other sessions also using FOR UPDATE. when that session is done working, commit() and close() it. one of the other sleeping transactions will wake up and do whatever it wanted to do. it seems like your arrange() method is generally SELECTing rows which are later deleted within LockingManager. I think if you used FOR UPDATE appropriately, that should handle all the locking requirements. I dont think you'd need any thread mutexing in the application in this case (unless you started adding a cache; access to a global cache should be mutexed). > > But I see that sqlalchemy beginning transaction anyway just gives you more explicit control, if you wanted it > > session != transaction ? the difference between them is presence in > session of cache ? just give the whole Session page a good read: http://www.sqlalchemy.org/docs/04/session.html as an alternative introduction you can also read Hibernate's session page, 90% of this applies to the SQLAlchemy session including optimistic/pessimistic concurrency: http://www.hibernate.org/hib_docs/v3/reference/en/html/transactions.html --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
