aiomysql returns wrong data when calling pool.acquire multiple times about aiomysql HOT 3 OPEN

I made a mistake in what I wrote above. I'm not using two pools, the difference is I'm calling acquire in two different places. The sanic request/response middleware already calls acquire before and after my endpoint implementation runs, then during my endpoint core I call acquire once more. I feel like that's something that should be fine.

from aiomysql.

In the additional context, I posted a screenshot. Here the code immediately breaks in the first loop of the while True. So this gives me zero rows, when there obviously should be rows. This flips each time I call my endpoint, so one time there are rows, next time they are not there. This starts happening when I run the update statement after having done the select statement. It keeps happening when I update again and set the value back to what it was initially.

from aiomysql.

This morning I woke up an realized this has got to do with transaction isolation levels...

When I commit after my select query, this problem disappears.
Or when I create my pool with autocommit=True, it of course also solves this.

But I'm still not sure if this is expected behaviour. If I do pool.acquire() I expect to get a connection based on the current truth of whatever is committed to the database when acquiring. Pools are re-using connections, so is there a state bug in here? of it my responsability to reset or commit? Also really don't get why the explicit acquire in my update statement shows this problem and not the conn I already acquired in the request middleware. The only thing I can think of.. is that acquire always gives the next available or new connection, which is always a different that the last connection it handed out, and two realities start existing. One where I run the update query and commit, and other connections, that are never committed and thus show the old state of the database.

@Nothing4You any idea?

from aiomysql.