if you are having deadlocks within one thread, that is almost certainly because you are using two simultaneous connections from the database, one of which has a lock on a table (due to a transaction) which the other is trying to reach. if you are using a plain strategy='default', this is a very likely thing to happen since each call to connect(), as well as contextual_connect(), returns a distinct connection from the pool.

using QueuePool with "threadlocal=True" should eliminate this problem as long as you are operating within one thread. there is a unittest which has always passed for this in test/pool.py, testthreadlocal_del and testthreadlocal_close, which insures that multiple connections pulled from a threadlocal pool in the same thread are in fact the same connection. also, if you create_engine() with strategy='threadlocal' and deal with contextual_connect(), it should also be using QueuePool with threadlocal....i just added a test for that and it passes (also unsetting the 'threadlocal' flag on the pool makes it fail which verifies the test).

if you use strategy='threadlocal' and use the TLEngine in exactly the same way you did with 0.1, i.e. there is no explicit Connection object and you just call execute()/begin()/commit() off the Engine directly, im pretty sure it should now be exactly the same. if you use explicit Connections only with TLEngine, that should work pretty well at the moment also. if you mix both approaches, i.e. explicit Connections with begin()/commit() calls on the Engine directly, that still might get screwed up.

without my having to learn twisted in one day, let me ask you this:

1. does your application use mutliple threads or just a single thread ?
2. do you wish for your application to use a single PG connection throughout, or are you trying to manage mulitple connections in one thread ?

I might add that in SA0.1 it was pretty much impossible to have multiple connections in one thread unless you were playing with push_session()/pop_session()...

On May 28, 2006, at 2:24 PM, Valentino Volonghi aka Dialtone wrote:



On Sun, 28 May 2006 12:19:04 -0400, Michael Bayer <[EMAIL PROTECTED]> wrote:

creating the postgres engine with the separate QueuePool, like youre doing, is a good idea to get a better result here. the "threadlocal" strategy uses this silly "TLEngine" subclass which is only trying to do that old "engine.begin()/engine.commit()" thing, which id rather get rid of, as soon as people tell me they can use the Transaction object that whole thing is gone. if youre having weird transactions hanging open types of problems i would suspect that might be where its happening, as it does try to maintain state (any chance I can get a fully working test program ?). The regular ComposedSQLEngine does not maintain any state (the underlying pool of course does).

But it seems that creating the pool on my own has its own share of problems because now even using plain strategy causes the deadlocks.

anyway your problem using the pool is just the "pool" argument not being propigated, and also there needed to be an exception raise for a bad URL, which if you SVN up to changeset 1533 youll get. the program below illustrates how to connect to postgres with your own pool:

Indeed this works but has the same deadlock problem now.

please try it out and let me know how it goes. the QueuePool in version 0.2 had to change slightly from its design in 0.1 to support "checkin/checkout" counters, as a result of the new requirement that people can explicitly call "close()" on a returned ConnectionFairy as opposed to waiting for __del__() to take care of things cleanly...other than that it is the same code. The regular ComposedSQLEngine in turn doesnt have anything to say about those connections, its totally stateless. anything producing a deadlock is something i need to know about and be able to reproduce ASAP....thanks.

Technically it's here: http://72.232.181.139/hg/stiq.cgi

you'll need sqlalchemy, psycopg2, postgres8, twisted, nevow and formal to run the database tests.

formal is here: svn://pollenation.net/forms/trunk

but I doubt you have the time to prepare all these things. I'll try to see if I can manage to provide a test that only needs the first 4 elements of the list.

Anyway the source in postgres.py module in stiq/storage/postgres.py can be used without the rest of the system and the unittests are pretty simple.

HTH


-------------------------------------------------------
All the advantages of Linux Managed Hosting--Without the Cost and Risk! Fully trained technicians. The highest number of Red Hat certifications in
the hosting industry. Fanatical Support. Click to learn more
http://sel.as-us.falkag.net/sel? cmd=lnk&kid=107521&bid=248729&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users



-------------------------------------------------------
All the advantages of Linux Managed Hosting--Without the Cost and Risk!
Fully trained technicians. The highest number of Red Hat certifications in
the hosting industry. Fanatical Support. Click to learn more
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=107521&bid=248729&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users

Reply via email to