mysql update在提交事务后立即无法看到记录

Question

我正在使用连接执行事务。每当第二个连接立即尝试更新刚插入的行时，都找不到这些行。即使事务是在发布更新之前提交的。

我有mysql查询日志，它描述了这个场景。

最后一条语句失败，0行受到影响(UPDATE refresh_token...)：

2018-08-03T13:31:24.829038Z  1150 Query START TRANSACTION
2018-08-03T13:31:24.830026Z  1150 Prepare   INSERT INTO account (<redacted>) VALUES (<redacted>)
2018-08-03T13:31:24.830493Z  1150 Execute   INSERT INTO account (<redacted>) VALUES (<redacted>)
2018-08-03T13:31:24.831345Z  1150 Close stmt
2018-08-03T13:31:24.833228Z  1150 Prepare   INSERT INTO refresh_token (<redacted>) VALUES (<redacted>)
2018-08-03T13:31:24.833666Z  1150 Execute   INSERT INTO refresh_token (<redacted>) VALUES (<redacted>)
2018-08-03T13:31:24.834356Z  1150 Close stmt
2018-08-03T13:31:24.834477Z  1150 Prepare   INSERT INTO another (<redacted>) VALUES (<redacted>)
2018-08-03T13:31:24.835155Z  1150 Execute   INSERT INTO another (<redacted>) VALUES (<redacted>)
2018-08-03T13:31:24.835621Z  1150 Close stmt
2018-08-03T13:31:24.835747Z  1150 Query COMMIT
2018-08-03T13:31:24.840374Z  1150 Prepare   UPDATE refresh_token SET accessed = ? WHERE token = ?
2018-08-03T13:31:24.840799Z  1150 Execute   UPDATE refresh_token SET accessed = '<redacted>' WHERE token = '<redacted>'
2018-08-03T13:31:24.843346Z  1150 Close stmt

如果我在发出UPDATE refresh_token SET...之前等待500ms，则会找到该记录。

以下是事务隔离级别：

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
'REPEATABLE-READ', 'REPEATABLE-READ'

Answer 1

OK表明DATETIME列的粒度是以秒为单位的。因此，使用NOW()插入和更新会使记录看起来完全相同，所以mysql报告0 rows updated。