Bug Report: Inconsistent `NOW()` behaviour

[GrahamCampbell]

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

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.

Scenario 2

START TRANSACTION;
INSERT INTO `users` (`id`, `created_at`) VALUES (1, NOW()), (2, NOW());
COMMIT;

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:

START TRANSACTION;
INSERT INTO `users` (`id`, `created_at`) VALUES (1, NOW());

application sleeps for a 2 seconds

INSERT INTO `users` (`id`, `created_at`) VALUES (2, NOW());
COMMIT;

Binary Version

v21 32edb28baed02b49cb56c4fba02026e5267f8eba

Operating System and Environment details

N/A

Log Fragments

No response

[systay]

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:

From the mysql manual

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

[GrahamCampbell]

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.