How to make a tornado request atomic in the Database

Question

I have a python app written in the Tornado Asynchronous framework. When an HTTP request comes in, this method gets called:

@classmethod
def my_method(cls, my_arg1):

    # Do some Database Transaction #1
    x = get_val_from_db_table1(id=1, 'x')
    y = get_val_from_db_table2(id=7, 'y')
    x += x + (2 * y) 

    # Do some Database Transaction #2
    set_val_in_db_table1(id=1, 'x', x)

    return True

The three database operations are interrelated. And this is a concurrent application so multiple such HTTP calls can be happening concurrently and hitting the same DB.

For data-integrity purposes, its important that the three database operations in this method are all called without another processes reading or writing to those database rows in between.

How can I make sure this method has database atomicity? Does Tornado have a decorator for this?

Answer 1

Synchronous database access

You haven't stated how you access your database. If, which is likely, you have synchronous DB access in get_val_from_db_table1 and friends (e.g. with pymysql) and my_method is blocking (doesn't return control to IO loop) then you block your server (which has implications on performance and responsiveness of your server) but effectively serialise your clients and only one can execute my_method at a time. So in terms of data consistency you don't need to do anything, but generally it's a bad design. You can solve both with @xyres's solution in short term (at cost of keeping in mind thread-safely concerns because most of Tornado's functionality isn't thread-safe).

Asynchronous database access

If you have asynchronous DB access in get_val_from_db_table1 and friends (e.g. with tornado-mysql) then you can use tornado.locks.Lock. Here's an example:

from tornado import web, gen, locks, ioloop


_lock = locks.Lock()

def synchronised(coro):
    async def wrapper(*args, **kwargs):  
        async with _lock:
            return await coro(*args, **kwargs)

    return wrapper


class MainHandler(web.RequestHandler):

    async def get(self):
        result = await self.my_method('foo')
        self.write(result)

    @classmethod
    @synchronised
    async def my_method(cls, arg):
        # db access
        await gen.sleep(0.5)
        return 'data set for {}'.format(arg)


if __name__ == '__main__':
    app = web.Application([('/', MainHandler)])
    app.listen(8080)
    ioloop.IOLoop.current().start()

---

Note that the above is said about normal single-process Tornado application. If you use tornado.process.fork_processes, then you can only go with multiprocessing.Lock.