views:

3359

answers:

4

When you run something similar to:

UPDATE table SET datetime = NOW();

on a table with 1 000 000 000 records and the query takes 10 seconds to run, will all the rows have the exact same time (minutes and seconds) or will they have different times? In other words, will the time be when the query started or when each row is updated?

I'm running MySQL, but I'm thinking this applies to all dbs.

+1  A: 

They should have the same time, the update is supposed to be atomic, meaning that whatever how long it takes to perform, the action is supposed to occurs as if all was done at the same time.

If you're experiencing a different behaviour, it's time to change for another DBMS.

gizmo
Not experiencing anything, just confirming before I go ahead and assume it will be.
Darryl Hein
+14  A: 

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_now

"NOW() returns a constant time that indicates the time at which the statement began to execute. (Within a stored routine or trigger, NOW() returns the time at which the routine or triggering statement began to execute.) This differs from the behavior for SYSDATE(), which returns the exact time at which it executes as of MySQL 5.0.13. "

micahwittman
SQL is SET oriented, which means that updates apply to the entire set or not at all. Difference between NOW() and SYSDATE() is insignificant here. In both cases field 'datetime' will be updated with a single/distinct value.
Chris Bednarski
+1  A: 

If the end-result is important to you - TEST IT FIRST, just because it 'ought' to work as documented, doesn't mean it will. If in doubt, test!

andora
Yes, tests are important (+1), but should not be the only base for the decision. If the tested behaviour was actually not documented it may change any time in future. We should rely only on the documented behaviour, but test it to make sure it works as documented.
Jacek Konieczny
A: 

$sql_kronologi = "UPDATE tbl_flm_tape " . " SET tag_tkh = NOW()," . " tag_username = '". $_SESSION['user']['usr_loginid']."'" . " WHERE flm_tape_id = '".$flm_tape_id."'.";

shaffiq