Why do some mysql connections select old data of mysql database after delete+insert?

Question

I'm having issues with sessions in my python/wsgi web application. Each thread in the 2 wsgi daemons has a different, persistent mysqldb connection. Sometimes, after deleting the old session and creating a new one, some connections still get the old session in the selection, meaning they fail to authenticate the session and ask to log in again.

Details: Sessions are stored in an InnoDB table in the local mysql database. After authenticating (via CAS), I delete any previous sessions for that user, create a new session (insert a row), commit the transaction, and redirect to the originally requested page using the new session ID from the cookie. For each request, the session ID in the cookie is checked against the session in the database.

Sometimes, newly created sessions are not found in the database after redirection. Instead, the user's old session still exists. (I checked this by selecting and logging all sessions at the beginning of each request). Somehow I get cached results. I tried using SQL_NO_CACHE to select the session but it made no difference.

Why do I get cached results? Where else can caching occur and how can I stop it or flush the cache? Basically, why can't other connections see the newly inserted data?

Answer 1

Yes, it looks like it assumes you will only perform one transaction and then disconnect. If you have different needs, then you need to address this assumption. As @a_horse_with_no_name mentioned, you can commit (although I would use rollback if you didn't actually change the 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")

Alternatively, it looks like you can set autocommit to true on the connection:

dbconn.autocommit(True)

However, this is not recommended if you are actually making changes to the connection.

Answer 2

MySQL default isolation level "REPEATABLE READ", which means you will not see any changes made after the transaction was started - even if these (other) changes have been committed.

If you issue a COMMIT or ROLLBACK in these sessions, you should see the changed data (as this will end the "ongoing" transaction).

Another option is to change the isolation level of these sessions to "READ COMMITTED". There may also be an option to change the default level, but you'll need to check the manual.