Re: Wired connection problem

On May 15, 2012, at 8:13 PM, limodou wrote:

> Recently I found a wired problem in my application, something like
> this: I have a long time deamon program, it'll be an infinite loop,
> just like:
> 
> engine = create_engine('...')
> conn = engine.connect()
> while True:
>    for row in select:
>        update
>    sleep(xxx)
> 
> In the loop, I create conn at first, and then reuse it in the loop. If
> there is data in database, it'll fetch all matched records and update
> the flag, then do the next loop. So if there is no matched records,
> it'll just sleep and do nothing.
> 
> The problem is, because I used the shared connection object, so if
> there is no matched records for a while, and even I changed the
> database to make some records, it'll not fetch the records at all. And
> if I restart the program it'll get them at the first time, then if I
> changed the database, it'll not fetch the records again. But If I put
> the conn creation in the loop, it seems everything is ok.
> 
> And if I tried to use strategy='threadlocal', and get the conn like:
> conn = engine.contextual_connect(), even I put this line in the loop,
> the problem is still existed.
> 
> So I don't know if someone else has faced the same problem like me? I
> don't know if I need to recreate connection every time in the loop?

if you hold on to "conn", transactional state will accumulate as you do things like SELECT on it, that is, if
there is a high degree of transaction isolation in effect, a subsequent SELECT of the same criteria on the
same connection may not indicate changes which have occurred in other concurrent transactions.

When you say conn.close(), the underlying DBAPI resources are rolled back, which will revert
transactional state, and the underlying DBAPI connection is returned to the connection pool for re-use.  
So you should be calling engine.connect() for each series of operations between the "sleep".  Awaking
from the sleep you'd like to start with fresh transactional state.

The easiest pattern here would be:

while True:
    with engine.begin() as conn:
        for row in conn.execute(...):
            conn.execute(update...)
   sleep(...)

also I'd avoid using the threadlocal strategy and also definitely don't "share" a connection with any
other threads.   connections are best used locally, quickly, and atomically across a group of related operations.

> 
> -- 
> I like python!
> UliPad <<The Python Editor>>: http://code.google.com/p/ulipad/
> UliWeb <<simple web framework>>: http://code.google.com/p/uliweb/
> My Blog: http://hi.baidu.com/limodou
> 
> -- 
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlalchemy <at> googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
> 

--

-- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.