In the same query INSERT, can two VALUES being NOW() return a different time?

Question

I met a query similar to

INSERT INTO mytable (id, Created, Updated) VALUES (null, NOW(), NOW())

having column definitions

| Created   | datetime |
| Updated   | datetime |

Will in this case MySQL set the NOW() value to the current time and return that same values to both calls, or will that query run the (improbable) risk of having two slightly different times for Created and Updated?

Note: I can't change the field types.

Answer 1

The time returned by NOW(), and other date time functions, is derived from the start time of the query. The THD class here is used to contain all the information for the connection. The NOW() function implementation grabs this value and returns it into the now_time structure.

MySQL docs for NOW() also state:

NOW() returns a constant time that indicates the time at which the statement began to execute. ...

You will always get the same NOW() value from anywhere in the query.

MariaDB [test]> select now(),sleep(10),now();
+---------------------+-----------+---------------------+
| now()               | sleep(10) | now()               |
+---------------------+-----------+---------------------+
| 2021-03-22 14:17:05 |         0 | 2021-03-22 14:17:05 |
+---------------------+-----------+---------------------+

Worth noting, that this isn't per transaction:

MariaDB [test]> start transaction
    -> ;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]>  select now();
+---------------------+
| now()               |
+---------------------+
| 2021-03-22 14:20:53 |
+---------------------+
1 row in set (0.00 sec)
MariaDB [test]> select sleep(2);
+----------+
| sleep(2) |
+----------+
|        0 |
+----------+
1 row in set (2.00 sec)
MariaDB [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-03-22 14:21:00 |
+---------------------+
1 row in set (0.00 sec)

Answer 2

Another aspect: SYSDATE() can be different, but NOW() cannot.

mysql> select now(6), sysdate(6), now(6), sysdate(6);
+----------------------------+----------------------------+----------------------------+----------------------------+
| now(6)                     | sysdate(6)                 | now(6)                     | sysdate(6)                 |
+----------------------------+----------------------------+----------------------------+----------------------------+
| 2021-03-21 22:02:28.983211 | 2021-03-21 22:02:28.983368 | 2021-03-21 22:02:28.983211 | 2021-03-21 22:02:28.983370 |
+----------------------------+----------------------------+----------------------------+----------------------------+
1 row in set (0.00 sec)

I'm using (6) so we can see the time down to the microsecond. Note how the NOW calls are identical and the smallest values. Meanwhile, the SYSDATE calls are later and not the same as each other.

----- 2006-03-31 5.0.20 & not yet released 5.1.8 -- Functionality Added or Changed -- -----

Added the --sysdate-is-now option to mysqld to enable SYSDATE() to be treated as an alias for NOW(). See Date and Time Functions. (Bug #15101)

----- 2005-09-22 5.0.13 Release Candidate -- Bugs Fixed -- -----

The SYSDATE() function now returns the time at which it was invoked. In particular, within a stored routine or trigger, SYSDATE() returns the time at which it executes, not the time at which the stored routine or triggering statement began to execute. (Bug #12480)

Answer 3

Didn't find the answer from @nbk satisfying enough for my needs (especially "No, as long as you have no micro seconds in your datetime and a really very slow server, it could be possible").

So I simulated a kind of very slow server.

My Linux desktop MB has 6 cores, I made this C program

#define COUNT 10000000000L
double busy() {
     volatile double z = rand() / M_PI;
 for(long l=0 ; l<COUNT ; l++) {
      z += sqrt(M_PI);
 }
 return z;

}

and in the main

 int n = atoi(*++argv); // input 10
while(n--) {
      if ( ! fork()) {
            printf("%d => %lf\n", n, busy());
            exit(0);
      }
 }
pid_t wpid;
 int status = 0;
 while ((wpid = wait(&status)) > 0);

That makes n threads very busy. With n = 10, the desktop was really busy...

While that was running (as root, to ensure the system doesn't try to salvage some resources for itself :) I had prepared this earlier

create table a (num int auto_increment primary key,
   d1 datetime(6), d2 datetime(6), d3 datetime(6), d4 datetime(6));

and a file made of 1000 lines

insert into a values(null,now(6),now(6),now(6),now(6));

So when the PC was running 10 threads busy() on 6 cores [all were at 100%], I injected the 1000 inserts into the table, took a bit longer than usual, then

select * from a where d1 <> d2 or d1 <> d3 or d1 <> d4;
Empty set (0.00 sec)

Yes! MySQL is really well made! (seriously, I'm always amazed at the quality in algorithms and system programming behind this DBMS).

Answer 4

No, the will be no difference

as MysQL Writes in the manual

NOW() returns a constant time that indicates the time at which the statement began to execute.

This seems to be a diffrent behaviour as all other functions, which are run column by column.

CREATE TABLE mytable (id INT AUTO_INCREMENT PRIMARY KEY, Created DATETIME , Updated DATETIME)

INSERT INTO mytable (id, Created, Updated) VALUES (null, NOW(), NOW())

INSERT INTO mytable (id, Created, Updated) VALUES (null, NOW(3), NOW(3))

SELECT MICROSECOND(Created),MICROSECOND(Updated) FROM mytable

MICROSECOND(Created) | MICROSECOND(Updated)
-------------------: | -------------------:
                   0 |                    0
                   0 |                    0

CREATE TABLE mytable2 (id INT AUTO_INCREMENT PRIMARY KEY, Created DATETIME(3) , Updated DATETIME(3))

INSERT INTO mytable2 (id, Created, Updated) VALUES (null, NOW(3), NOW(3))

SELECT MICROSECOND(Created),MICROSECOND(Updated) FROM mytable2

MICROSECOND(Created) | MICROSECOND(Updated)
-------------------: | -------------------:
              186000 |               186000

db<>fiddle here