为什么一些mysql连接在删除+插入后选择mysql数据库中的旧数据? [英] Why are some mysql connections selecting old data the mysql database after a delete + insert?

问题描述

我的 python/wsgi Web 应用程序中的会话有问题.2 个 wsgi 守护进程中的每个线程都有一个不同的、持久的 mysqldb 连接.有时，在删除旧会话并创建新会话后，某些连接仍然在选择中获取旧会话，这意味着它们无法验证会话并再次要求登录.

I'm having a problem with the sessions in my python/wsgi web app. There is a different, persistent mysqldb connection for each thread in each of 2 wsgi daemon processes. Sometimes, after deleting old sessions and creating a new one, some connections still fetch the old sessions in a select, which means they fail to validate the session and ask for login again.

详细信息:会话存储在本地 mysql 数据库的 InnoDB 表中.身份验证后(通过 CAS)，我删除该用户之前的所有会话，创建一个新会话(插入一行)，提交事务，并使用 cookie 中的新会话 ID 重定向到最初请求的页面.对于每个请求，都会根据数据库中的会话检查 cookie 中的会话 ID.

Details: Sessions are stored in an InnoDB table in a local mysql database. After authentication (through 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 with the new session id in the cookie. For each request, a session id in the cookie is checked against the sessions in the database.

有时，重定向后在数据库中找不到新创建的会话.相反，该用户的 old 会话仍然存在.(我通过在每个请求开始时选择并记录所有会话来检查这一点).不知何故，我得到了缓存的结果.我尝试使用 SQL_NO_CACHE 选择会话，但没有任何区别.

Sometimes, a newly created session is not found in the database after the redirect. Instead, the old session for that user is still there. (I checked this by selecting and logging all of the sessions at the beginning of each request). Somehow, I'm getting cached results. I tried selecting the sessions with SQL_NO_CACHE, but it made no difference.

为什么我会得到缓存的结果?缓存还能在哪里发生，如何停止或刷新缓存?基本上，为什么其他连接看不到新插入的数据?

Why am I getting cached results? Where else could the caching occur, and how can stop it or refresh the cache? Basically, why do the other connections fail to see the newly inserted data?

推荐答案

MySQL 默认隔离级别为REPEATABLE READ"，这意味着在事务开始后您将看不到事务中的任何更改 - 即使那些(其他)已提交更改.

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.

如果您在这些会话中发出 COMMIT 或 ROLLBACK，您应该会看到更改的数据(因为这将结束正在进行"的事务).

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").

另一个选项是将这些会话的隔离级别更改为READ COMMITTED".也许还有一个选项可以更改默认级别，但您需要查看手册.

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.

这篇关于为什么一些mysql连接在删除+插入后选择mysql数据库中的旧数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！