Why Are My Newly Created MySQL Sessions Not Immediately Visible After a Transaction?

Cached Results after MySQL Insert and Delete

In a Python/WSGI web app, sessions are stored in an InnoDB table in a local MySQL database. After authentication, old sessions are deleted, a new session is created, and the transaction is committed to create a new session ID for the cookie. However, sometimes the newly created session is not found in the database after a redirect.

Understanding the Issue

MySQL uses the default isolation level "REPEATABLE READ," meaning transactions will not see any changes made after the transaction started. Even if those changes were committed, they remain invisible.

Solution

To resolve this, there are two options:

1. End the Transaction:

   • Issue a COMMIT or ROLLBACK in the sessions where the cached results appear. This will end the transaction and allow the committed changes to become visible.

2. Change Isolation Level:

   • Change the isolation level for the sessions to "READ COMMITTED." This will ensure that changes made by other transactions are visible after they have been committed.

Additional Consideration

Check the MySQL manual for options to modify the default isolation level. This will allow all sessions to automatically use the preferred isolation level.

The above is the detailed content of Why Are My Newly Created MySQL Sessions Not Immediately Visible After a Transaction?. For more information, please follow other related articles on the PHP Chinese website!