NOW() 如何与交易交互? [英] How does NOW() interact with transactions?

问题描述

我正在运行对一个表执行多次更新的事务(可能需要几秒钟)，然后对第二个表执行另一次更新.最终更新的时间戳使用 NOW():UPDATE KeyTimestamps SET timestamp=NOW() WHERE key=$key.

我担心更新以一种顺序发生，但时间戳看起来像是以另一种顺序发生的.所以我们有以下表格状态序列:

key |时间戳----+--------------------一个 |1970-01-01 00:00:00乙 |1970-01-01 00:00:00关键|时间戳----+--------------------一个 |2015-12-02 12:00:00乙 |1970-01-01 00:00:00关键|时间戳----+--------------------一个 |2015-12-02 12:00:00乙 |2015-12-02 11:59:59

这会发生吗?手册说<块引用>

NOW() 返回一个常数时间，指示语句开始执行的时间.

但是一个查询可以先于另一个查询开始执行，然后再完成执行吗?交易可以保护我免受这种影响吗?

解决方案

评论有点长.

如果您想知道插入到表中的顺序，请使用自动递增列.这保证永远不会有重复项，并且应该按插入顺序分配.然后，您可以将此排序与使用时间戳列的排序进行比较.

请注意，对于同时插入的两行，时间戳列可能相同.

I'm running transactions which perform many updates to one table (taking perhaps a couple of seconds), and then another update to a second table. The final update is timestamped with NOW(): UPDATE KeyTimestamps SET timestamp=NOW() WHERE key=$key.

I'm worried about updates happening in one order, but with timestamps that look like they happened in the other order. So that we have the following sequence of table states:

key | timestamp
----+--------------------
 A  | 1970-01-01 00:00:00
 B  | 1970-01-01 00:00:00

key | timestamp
----+--------------------
 A  | 2015-12-02 12:00:00
 B  | 1970-01-01 00:00:00

key | timestamp
----+--------------------
 A  | 2015-12-02 12:00:00
 B  | 2015-12-02 11:59:59

Can this ever happen? The manual says

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

but can one query start to execute before another, and finish executing later? Do transactions protect me against this?

解决方案

This is a bit long for a comment.

If you want to know the ordering of inserts into a table, then use an auto-incrementing column. This is guaranteed to never have duplicates and should be assigned in insertion order. You can then compare this ordering to the ordering using a time stamp column.

Note that the time stamp column could be identical for two rows inserted at the same time.

这篇关于NOW() 如何与交易交互?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！