Re: Bug or feature??? Timestamp field contains time of the beginning of the transaction

Hi!

On Jul 28, Mikhail Entaltsev wrote:
> Hi,
> 
> I have found one unclear place for me regarding to the timestamp field.
> The TIMESTAMP field contains transaction's beginning time,
> but I would expect to have there time when transaction finished.
> 
> Is it bug or feature?
> 
> Let's say I started 'update' statement at 15:00:00. But the row with id = 1
> is blocked by another transaction ("LOCK TABLES Test WRITE;"),
> so 'update' statement needs to wait till the end of the transaction.
> After 10 sec the block on the record with id = 1 is released ("UNLOCK
> TABLES;").
> So my 'update' finished.
> 
> select UpdateDate from Test where id = 1
> 
> gives me 20030728150000, but I would expect 20030728150010.
> 
> Thanks in advance.
> 
> Mikhail.

The following is the opinion of Peter Gulutzan - our SQL expert and the
author of "SQL Complete, Really" book:

  The SQL standard says we can do it either way:
  "The time of evaluation of the <datetime value function>
  during the execution of the SQL-statement is
  implementation-dependent". Of course this is not
  a reference to a TIMESTAMP column, it's to functions
  like CURRENT_TIMESTAMP, but such functions are implied.

  Suppose that with MySQL version 5 we can say
  "IF CURRENT_TIME < '12:00:00' THEN
  INSERT INTO TIMESTAMP_COLUMN VALUES (NULL) END IF"

  Suppose the time is 11:59:59.

  Suppose that the user's scenario occurs, that is,
  the insert starts but is locked out for ten seconds.
  Then, if we we do what the user wants and put the
  latest possible value in timestamp_column, it will
  contain '... 12:00:09'. In other words, we would
  have a value that the IF condition was supposed to
  avoid. 

  In fact, the SQL standard prohibits such a thing: 
  if there is more than one reference to a current
  time, then "all such references are effectively
  evaluated simultaneously". In that case, to be
  compatible, MySQL absolutely cannot wait to
  calculate the time until after the lock ends.
  It must do so during the IF condition test,
  and whatever value it finds during the IF
  condition test is the value it must use during
  the INSERT.

  Let us keep MySQL's current behaviour.

  Regards,
  PeterG

  Peter Gulutzan, Software Architect
  MySQL AB, www.mysql.com

Regards,
Sergei

-- 
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[email protected]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
       <___/  www.mysql.com

-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]