postgresql - How to do transactions in python with asyncio and postgres? -

there 2 operations in rpc method:

async def my_rpc(self, data):     async self.engine() conn:         await conn.execute("select ... mytable");         ...  # seems table mytable can changed rpc         await conn.execute("updata mytable ..."); 

another rpc method can change db before operation "my_rpc" done (between 2 awaits of sql queries). how avoid situation?

code of self.engine (calls engine aiopg.sa.create_engine):

class connectioncontextmanager(object):     def __init__(self, engine):         self.conn = none         self.engine = engine      async def __aenter__(self):         if self.engine:             self.conn = await self.engine.acquire()             return self.conn      async def __aexit__(self, exc_type, exc, tb):         try:             self.engine.release(self.conn)             self.conn.close()         finally:             self.conn = none             self.engine = none 

firstly, aiopg works in autocommit mode, meaning have use transaction in manual mode. read more details.

secondly, have use select update lock row read in first statement. select update locks select rows while until transaction completes. read more details.

async def my_rpc(self, data):     async self.engine() conn:         await conn.execute("begin")         await conn.execute("select ... mytable some_clause = some_value update")         ...  # seems table mytable can changed rpc         await conn.execute("update mytable set some_clause=...")         await conn.execute("""commit""")