On Jun 3, 2014, at 2:22 PM, [email protected] wrote: > Hi people, new here - hope my question okay. > > I have an issue with my tornado based web application using sqlalchemy for a > postgresql database, where commits sometimes is not actually persisted to the > database. An INSERT statement is done, the object gets and id and there are > no errors, but no row is actually written. Like it is only duing a flush(). > > It might be similar to the problem described in > https://groups.google.com/forum/#!searchin/sqlalchemy/tornado/sqlalchemy/A2qUbeWgbbY/U4HtFHSS7MgJ > except I don't get any errors. > > Here is what I think is the relevant code. I have tried with non-scoped > sessions, same issue. I can give more details if needed: > > # config.py > Session = scoped_session(sessionmaker()) > # request.py > class BaseHandler(RequestHandler): > def prepare(self): > self.session = Session() > def on_finish(self): > Session.remove() > > # api.py > class GroupService(BaseHandler): > def post(self, arguments): > ... > new_group = Group( > name=name, > manager_id=self.current_user.id, > ) > self.session.add(new_group) > self.session.commit()
to my knowledge, Tornado does not use threads. It bundles all requests into an async chain in one thread. So the usage of scoped_session() without any specific request context is not valid in that context, it will be linking a Session to the current thread which in an async system would be a disaster, the same session would get mixed into random requests. that is, the on_finish() handler of one request will blow away the current contextual session, which calls close(), which means an already executing thread that calls upon self.session.commit() would see an empty session. Please review http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#thread-local-scope for what "thread local" means in terms of SQLAlchemy. > > I have been running with echo=True to get some logging, and I can see that it > appears that when it goes wrong no BEGIN and COMMIT are performed, only the > INSERT. Here is the output (the POST log occurs at the end of the request): > 2014-06-03 16:50:04,343:INFO:sqlalchemy.engine.base.Engine:log:INSERT INTO > "group" (created_at, updated_at, name, manager_id) VALUES (%(created_at)s, > %(updated_at)s, %(name)s, %(manager_id)s) RETURNING "group".id > 2014-06-03 16:50:04,343:INFO:sqlalchemy.engine.base.Engine:log:{'created_at': > datetime.datetime(2014, 6, 3, 16, 50, 4, 343333, tzinfo=tzlocal()), > 'updated_at': datetime.datetime(2014, 6, 3, 16, 50, 4, 343347, > tzinfo=tzlocal()), 'manager_id': '38655', name': u'Group name'} > 2014-06-03 16:50:04,357:INFO:tornado.access:web:200 POST /groups > (xx.xx.xx.xx) 19.56ms > > And an example where the group is commited: > 2014-06-03 17:37:28,501:INFO:sqlalchemy.engine.base.Engine:log:BEGIN > (implicit) > 2014-06-03 17:37:28,503:INFO:sqlalchemy.engine.base.Engine:log:INSERT INTO > "group" (created_at, updated_at, name, manager_id) VALUES (%(created_at)s, > %(updated_at)s, %(name)s, %(manager_id)s) RETURNING "group".id > 2014-06-03 17:37:28,503:INFO:sqlalchemy.engine.base.Engine:log:{'name': > u'Other group name', 'created_at': datetime.datetime(2014, 6, 3, 17, 37, 28, > 503171, tzinfo=tzlocal()), 'updated_at': datetime.datetime(2014, 6, 3, 17, > 37, 28, 503184, tzinfo=tzlocal()), 'manager_id': '36972'} > 2014-06-03 17:37:28,503:INFO:sqlalchemy.engine.base.Engine:log:COMMIT > 2014-06-03 17:37:28,720:INFO:tornado.access:web:200 POST /groups > (xx.xx.xx.xx) 221.99ms > > I am probably looking at some sort of race condition and I know those can be > hard to help with, but would like to hear if anyone knows of anything that > can make sqlalchemy behave like that where a commit() for some reason doesn't > actually do the commit without any apparent errors. > > Best regards, > Jeppe > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
