提交事务后立即更新mysql看不到记录

Question

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

我有描述该场景的 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...之前等待500毫秒，则会找到该记录。

这是事务隔离级别:

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

Answer 1

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