How to handle lock on database row when using tornado?

[Jimmy]

My application has a need to lock a row in database for update. But when another request comes to hit the same row, the query will be pending until the previous lock is release. But this will hold all requests in the same thread. 

I have searched for several ways of get around of this issue. Not sure which is the best way to handle it. I would like to hear your experience about this issue. My potential solutions:

1. move the locked query to backend job. and return the response when the job finished. 

    pro: clean and follow async manner of tornado

    con: it may keep the connect open for longer time

2. Use async db library to load. 

    pro: fast and follow async manner

    con: few async db library supports sqlalchemy for orm, which is important to us. I have tried twisted adbapi, and adb with MySQL. but the experience is not good (Maybe because I'm new to twisted). gevent with psycopg2 works with sqlalchemy, but I can't enjoy the async support from tornado (I can only use gevent.spawn and join for async, and tornado is only for wsgi application).

3. Set short timeout to each query. (This is the last thing I want to do)

    pro: easy and simple

    con: Bad user experience bcz of timeout. 

I would like to know if there is a general solution for issue like this. 

Thank you.

[Serge S. Koval]

Hi,

 Did you try https://github.com/FSX/momoko? Or you want to use ORM?

 As far as I know, there are no asynchronous ORMs with Tornado support. So, it is either background thread with a queue, which can use blocking ORM or raw SQL through asynchronous driver.

Serge.

[Jimmy]

I read the document of momoko. Seems, it doesn't support model object. And session management is not mentioned in the document.

Jimmy

[Frank Smit]

Momoko is just a wrapper for Psycopg2. You would have to make your own
session management library around it, but I would use Redis for
session myself.

An ORM is a lot of work, and I don't have the time (school and work)
to make one. It's on the to do list though.