Why are some mysql connections selecting old data the mysql database after a delete + insert? Why are some mysql connections selecting old data the mysql database after a delete + insert? mysql mysql

Why are some mysql connections selecting old data the mysql database after a delete + insert?


MySQL defaults to the isolation level "REPEATABLE READ" which means you will not see any changes in your transaction that were done after the transaction started - even if those (other) changes were committed.

If you issue a COMMIT or ROLLBACK in those sessions, you should see the changed data (because that will end the transaction that is "in progress").

The other option is to change the isolation level for those sessions to "READ COMMITTED". Maybe there is an option to change the default level as well, but you would need to check the manual for that.


Yes, it looks like the assumption is that you are only going to perform a single transaction and then disconnect. If you have a different need, then you need to work around this assumption. As mentioned by @a_horse_with_no_name, you can put in a commit (though I would use a rollback if you are not actually changing data). Or you can change the isolation level on the cursor - from this discussion I used this:

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

Or, it looks like you can set auto commit to true on the connection:

dbconn.autocommit(True)

Though, again, this is not recommended if actually making changes in the connection.