You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
MySQL semantics are to fix the current time at the start of a transaction, however there are many situations where we can end up with different NOW() values.
Reproduction Steps
Scenario 1
START TRANSACTION;
SELECT NOW();
application sleeps for a 2 seconds
SELECT`id`, NOW() FROM`users`where`id`=1;
COMMIT;
The first NOW() runs in vtgate, and the second runs on MySQL. The two evaluations will return different values, contrary to using native MySQL.
Suppose that this is a multi-shard insert. The transactions don't necessary start at the same time on the underlying MySQL instances, causing the two values for NOW to be different. Maybe we say this an acceptable trade-off for multi-shard transactions, but possibly the 2-phase implementation should handle this correctly, at least. The issue is of course worse if we do:
Looking this over, I don't think it's correct that now() is pinned at the transaction start time. It seems to me that it's only per statement and not per transaction:
NOW() returns a constant time that indicates the time at which the statement began to execute.
This could still be a problem - if we are firing queries across multiple shards, they might (probably) not get the same timestamp, and this includes SELECT.
Another issue that came up when I was researching this is that now() is influenced in the session by SET TIMESTAMP, in other words, each session might have a different view of now().
Yeh, I could be wrong here for sure. Another example is a select with a join that takes multiple seconds running multiple queries. That would cause different now values, within the same top level statement.
Overview of the Issue
MySQL semantics are to fix the current time at the start of a transaction, however there are many situations where we can end up with different
NOW()
values.Reproduction Steps
Scenario 1
application sleeps for a 2 seconds
The first
NOW()
runs in vtgate, and the second runs on MySQL. The two evaluations will return different values, contrary to using native MySQL.Scenario 2
Suppose that this is a multi-shard insert. The transactions don't necessary start at the same time on the underlying MySQL instances, causing the two values for NOW to be different. Maybe we say this an acceptable trade-off for multi-shard transactions, but possibly the 2-phase implementation should handle this correctly, at least. The issue is of course worse if we do:
application sleeps for a 2 seconds
Binary Version
Operating System and Environment details
Log Fragments
No response
The text was updated successfully, but these errors were encountered: