I found in the asyncpg documentation that every call to сonnection.execute() or connection.fetch() should be wrapped in async with connection.transaction():.
But in one of the repositories I saw the following code without wrapping it in a transaction:
async def bench_asyncpg_con():
start = time.monotonic()
for i in range(1, 1000):
con = await asyncpg.connect(user='benchmark_user', database='benchmark_db', host='127.0.0.1')
await con.fetchval('SELECT * FROM "Post" LIMIT 100')
await con.close()
end = time.monotonic()
print(end - start)
And it works. Can you explain to me when I should use transactions and when I shouldn't ?
The reason why the code in your example doesn't have a transaction is because it's just fetching data from the database. There are no changes happening to the database (no udpates, no inserted data, no deleting of data, etc..) Quoted from asyncpg docs:
When not in an explicit transaction block, any changes to the database will be applied immediately. This is also known as auto-commit.
From the quote above, when you use asyncpg to execute a query to change data in the database, it will be automatically committed unless you use a transaction. When you wrap your code in a transaction, you have to call commit to have those changes saved. Additionally, transactions allow you to execute queries and if any of those queries fail, you can rollback all of the executed queries that were wrapped in that transaction. Here is an example of a transaction that shows you the mechanics.
tr = connection.transaction()
await tr.start()
try:
...
except:
await tr.rollback()
raise
else:
await tr.commit()