[sqlalchemy] Re: Long running transaction issues with commit
Yes its all so very clear now, kind of obvious :) Thank you for taking out time to help me. Regards Aalok Sood. On Jun 2, 9:20 pm, Michael Bayer wrote: > On Jun 2, 2011, at 11:35 AM, Aalok Sood wrote: > > > > > > > > > > > Hello Michael > > > Thanks a lot for your quick response. > > I changed this loop to: > > or i in range(1000): > > : sleep(15) > > : print "commiting" > > : mm.name=u'old name' > > : s.commit() > > : sleep(25) > > : mm.name=u'new name' > > : print "commiting2" > > : s.commit() > > > That is to say, I always have something in s.dirty when i commit. This > > makes session interact with DB for sure. This loop works fine w/o any > > exceptions. > > So what you say will definitely solve the issue. However my doubts are > > more theoretical in nature. > > > SQLAlchemy documentation says that if autocommit=False, on every > > commit(), a new transaction is begun. So even if I do not have any > > dirty changes and session need not send any data to the DB, still it > > would have to contact the DB to begin a new transaction. > > The behaviour as we saw, however, does not comply with this logic. > > Can you please tell me where the loophole in my understanding of the > > whole thing is. > > > Once again, Thanks a lot for your help. I really appreciate it. > > The confusion here is that the documentation for "commit", which I'm assuming > you're reading ishttp://www.sqlalchemy.org/docs/orm/session.html#committing, > refers to the term "transaction", which, while it's not deeply described at > that point for the sake of clarity, is not the same thing as an actual > transaction on an individual DBAPI connection connected to a database across > a network. > > An in-depth description of what "transaction" means with regards to the > session is later down the page > athttp://www.sqlalchemy.org/docs/orm/session.html#managing-transactions, > pretty much the first paragraph. > > To restate, a "transaction" with regards to the Session is a boundary which > will be terminated upon the next call to commit() or rollback(). The > transaction itself is an in-memory structure which includes a collection of > zero or more DBAPI connections. When the transaction is committed or rolled > back, the commit() or rollback() method is ultimately called upon each DBAPI > connection that is present. A DBAPI connection becomes present in the > current "transaction" when it is first asked to participate in a statement > execution. For those engines for which no statement execution has occurred > within the scope of the Session's "transaction", no DBAPI connection is > procured and no network communication occurs. > > Hope this clears it up ! > > > > > > > > > > > Regards > > Aalok Sood > > > On Jun 2, 8:17 pm, Michael Bayer wrote: > >> The Session doesn't interact with the database until statements are first > >> emitted, so while its being put into a new transaction each time with your > >> block of code, probably nothing is being sent to the DB. If you stuck a > >> line: s.execute("select 1") in there, that would likely wake it up. > > >> On Jun 2, 2011, at 9:32 AM, Aalok Sood wrote: > > >>> Hello Everyone > > >>> My mysql server wait_timeout is set to 35. > >>> and if i run this code: > > >>> # Session s made with autocommit=False > >>> mm=s.query(ss.Machine).get(1) > > >>> In [9]: > > >>> In [10]: for i in range(1000): > >>> : sleep(15) > >>> : print "commiting" > >>> : s.commit() > >>> : sleep(25) > >>> : mm.name=u'new name' > >>> : print "commiting2" > >>> : s.commit() > > >>> Even though the second sleep is only for 25 seconds, I see an error > >>> while commiting which says > >>> 'Mysql server has gone away' > > >>> The SQLAlchemy documentation says that a new transaction is begun on a > >>> commit(). If that is the case, I should not see the above error. > >>> Maybe its an issue with commiting w/o any changes to the loaded > >>> instances. > > >>> Can anyone throw some light on this. > >>> Any help would be mu
[sqlalchemy] Re: Long running transaction issues with commit
Hello Michael Thanks a lot for your quick response. I changed this loop to: or i in range(1000): : sleep(15) : print "commiting" : mm.name=u'old name' : s.commit() : sleep(25) : mm.name=u'new name' : print "commiting2" : s.commit() That is to say, I always have something in s.dirty when i commit. This makes session interact with DB for sure. This loop works fine w/o any exceptions. So what you say will definitely solve the issue. However my doubts are more theoretical in nature. SQLAlchemy documentation says that if autocommit=False, on every commit(), a new transaction is begun. So even if I do not have any dirty changes and session need not send any data to the DB, still it would have to contact the DB to begin a new transaction. The behaviour as we saw, however, does not comply with this logic. Can you please tell me where the loophole in my understanding of the whole thing is. Once again, Thanks a lot for your help. I really appreciate it. Regards Aalok Sood On Jun 2, 8:17 pm, Michael Bayer wrote: > The Session doesn't interact with the database until statements are first > emitted, so while its being put into a new transaction each time with your > block of code, probably nothing is being sent to the DB. If you stuck a > line: s.execute("select 1") in there, that would likely wake it up. > > On Jun 2, 2011, at 9:32 AM, Aalok Sood wrote: > > > > > > > > > Hello Everyone > > > My mysql server wait_timeout is set to 35. > > and if i run this code: > > > # Session s made with autocommit=False > > mm=s.query(ss.Machine).get(1) > > > In [9]: > > > In [10]: for i in range(1000): > > : sleep(15) > > : print "commiting" > > : s.commit() > > : sleep(25) > > : mm.name=u'new name' > > : print "commiting2" > > : s.commit() > > > Even though the second sleep is only for 25 seconds, I see an error > > while commiting which says > > 'Mysql server has gone away' > > > The SQLAlchemy documentation says that a new transaction is begun on a > > commit(). If that is the case, I should not see the above error. > > Maybe its an issue with commiting w/o any changes to the loaded > > instances. > > > Can anyone throw some light on this. > > Any help would be much appreciated. > > > -- > > 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 > > sqlalchemy+unsubscr...@googlegroups.com. > > For more options, visit this group > > athttp://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@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Long running transaction issues with commit
Hello Everyone My mysql server wait_timeout is set to 35. and if i run this code: # Session s made with autocommit=False mm=s.query(ss.Machine).get(1) In [9]: In [10]: for i in range(1000): : sleep(15) : print "commiting" : s.commit() : sleep(25) : mm.name=u'new name' : print "commiting2" : s.commit() Even though the second sleep is only for 25 seconds, I see an error while commiting which says 'Mysql server has gone away' The SQLAlchemy documentation says that a new transaction is begun on a commit(). If that is the case, I should not see the above error. Maybe its an issue with commiting w/o any changes to the loaded instances. Can anyone throw some light on this. Any help would be much appreciated. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.