python mysql - is commit() required for SELECT?

I have a very simple python code getting some data from mysql:

    version = 1
    with conn.cursor() as cur:
        query = f'select latest_version from version'
        cur.execute(query)
        for row in cur:
            version = int(row[0])      
    cur.close()
    print(version)

That code is executed from AWS Lambda accessing my private mysql instance (but none of that should matter imho).

It was working perfectly fine, then i have updated version values in DB using SQL:

UPDATE version set latest_version=11;
COMMIT;
FLUSH TABLES;

The problem: after running above SQL code when i run my python code i was getting the old value from DB (10 instead of 11), like some caching....

Now when i have added to my python code commit() like this:

    version = 1
    with conn.cursor() as cur:
        query = f'select latest_version from version'
        cur.execute(query)
        conn.commit()
        for row in cur:
            version = int(row[0])      
    cur.close()
    print(version)

It all started to work fine and i got a new version.

This problem occurred for me several times with different SQL tables (and had to use above "workaround"). Always the same results, tested many times.

I have quite some other code operating on that DB running a lot of inserts on other tables (but i never insert on version table via python code). Also i am quite intensive running those workers in many parallel threads operating on the same DB as the same time (but no dependencies, should not have any deadlocks, all workers finishing work in time).

Could you please help me understand why this is happening ?

One of my guesses was that i did not close connections to mysql correctly in other scripts, but in such case i guess i would run out of connections/db hanlders(workers) and would not be able to connect anymore, not get cached values....So i am clueless here. Any ideas ?

Thanks,

What's happening is that your default transaction isolation is REPEATABLE-READ, so repeated queries in the same transaction never see an updated view of the database.

You should set your transaction isolation to READ-COMMITTED, so your SELECT always sees the current committed data. Do the following before you do your first query:

cur.execute("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED")

If that doesn't work, then do one COMMIT after changing the isolation level.

You don't need to do FLUSH TABLES in this case.