NOW() for DATETIME InnoDB Transaction guaranteed?

Question

Does using NOW() in 2+ queries in a single InnoDB transaction guarantee that the inserted datetime value will be exact in the database?

In other words, is the NOW(), even if you have more than 20 queries in a single transaction using it always going to be the same, or will it change?

score 5 · Accepted Answer · answered Apr 11 '12 at 20:22

5

Apparently it is not guaranteed across a transaction but can change from statement to statement. There is a workaround you can use as shown here:

BEGIN;
SELECT @now := NOW();
INSERT ... VALUES (..., @now, ...);
INSERT ... VALUES (..., @now, ...);
UPDATE ... @now ...;
COMMIT;

If you want to avoid that altogether just set the current date and time into a PHP variable and use that instead.

answered Apr 11 '12 at 20:22

John Conde

Hmm.. I opened up this question going with the php variable route, do you have any idea how to accomplish it? http://stackoverflow.com/questions/10129180/get-datetime-in-php-and-post-it-to-mysql-for-transaction-consistency – Maverick Apr 12 '12 at 18:21
I was under the mistaken impression that `NOW()` was fixed at the start of a transaction to a specific value, but I see that's not true. When using MySQL replication, the value returned by `NOW()` is set by the replication thread, but its value can change between statements within a single transaction. I think I was confused by the `NOW()` behavior because of the replication semantics, which is the only place I can find where there is any real source of confusion in the MySQL documentation. – Christopher Schultz Dec 30 '14 at 16:56

1 Answers1