什么可能导致 mysql db read 返回陈旧数据 [英] What could cause mysql db read to return stale data

问题描述

我正在解决一个 mysql 应用程序的问题.在某些时候，我的客户端使用包含在 START TRANSACTION; 中的查询插入一些数据；.... COMMIT; 语句.紧接着另一个客户端来读回数据，它不存在(我确定事情的顺序).

I am chasing a problem on a mysql application. At some point my client INSERTs some data, using a query wrapped in a START TRANSACTION; .... COMMIT; statement. Right after that another client comes are read back the data, and it is not there (I am sure of the order of things).

我正在运行 nodejs、express、mysql2，并使用连接池和多个语句查询.

I am running nodejs, express, mysql2, and use connection pooling, with multiple statements queries.

有趣的是，我在 mysqlworkbench 上看到了一些奇怪的东西.我只有一个工作台实例，它也看不到新插入的数据.我打开第二个，它看到了新数据.几分钟后，第一个实例仍然看不到新数据.点击重新连接到 DBMS"，现在它看到了.工作台行为，如果应用于我的节点客户端，将解释我在节点/mysql2 中看到的糟糕结果.

What is interesting is that I see weird things on mysqlworkbench. I just had a workbench instance which would not see the newly inserted data either. I opened a second one, it saw the new data. Minutes later, the first instance would still not see the new data. Hit 'Reconnect to DBMS', and now it sees it. The workbench behaviour, if applied to my node client, would explain the bad result I see in node / mysql2.

某处正在进行某种缓存......不知道从哪里开始:-(任何指针?谢谢！

There is some sort of caching going on somewhere... no idea where to start :-( Any pointers? Thanks!

推荐答案

听起来您的客户生活在他们自己的数据库快照中，如果他们有一个使用 REPEATABLE-READ 隔离级别的打开事务，情况就会如此.换句话说，该客户端开始其事务后提交的任何数据都不会对该客户端可见.

It sounds like your clients are living in their own snapshot of the database, which would be true if they have an open transaction using the REPEATABLE-READ isolation level. In other words, no data committed after that client started its transaction will be visible to that client.

一种解决方法是强制启动新事务.只需在客户端会话中运行 COMMIT，它似乎正在查看陈旧数据.这将解决任何打开的事务，下一个查询将启动一个新事务.

One workaround is to force a new transaction to start. Just run COMMIT in the client session where it appears to be viewing stale data. That will resolve any open transaction and the next query will start a new transaction.

另一种测试方法是使用 锁定读取查询，例如SELECT ... FOR UPDATE.无论客户端的事务隔离级别如何，这都会读取最近提交的数据.也就是说，即使客户端使用 REPEATABLE-READ 启动了他们的事务，锁定读取的行为就像他们使用 READ-COMMITTED 启动了他们的事务一样.

Another way you can test is to use a locking read query such as SELECT ... FOR UPDATE. This will read the most recently committed data, regardless of the client's transaction isolation level. That is, even if the client had started their transaction using REPEATABLE-READ, a locking read behaves as if they had started their transaction with READ-COMMITTED.

这篇关于什么可能导致 mysql db read 返回陈旧数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！