Is _creationTime monotonically increasing across transactions?
Is it possible for two rows in the same table, created in different transactions, to have the same
_creationTime?
My assumption is that they cannot, because of ACID. But I wanted to check before I write code that relies on this!3 Replies
hey @charlie -- we don't provide this guarantee, so
_creationTime may be out of order across transactions. so, if you'd like a monotonically increasing number, you can use our ACID guarantees to implement it.
for example, if you have a messages table, you could add a messageNumber field with an index on it. then, when inserting a message, you can query for the message with the highest message number and insert with one plus that, if present. then, you can be guaranteed that messageNumber is strictly monotonically increasing across different messages.
there are a few reasons for the system not providing this guarantee. happy to dive into more detail if you'd like -- let me know!
- we don't want to provide this guarantee from Date.now() within database functions. so, in rare cases, clock skew may cause timestamps to not be synchronized across function invocations.
- we also don't want to implement this in the database either. if we ensured that _creationTime was strictly monotonic, it'd force all insertions to a table to happen sequentially, and insert performance would be a lot worse. and, the system providing the guarantee wouldn't inherently be any more performant than the developer implementing themselves with the approach outlined above. (but, definitely understood that it'd be more ergonomic if it did!)
- many other database systems (e.g. fauna, cloud spanner, and foundationDB) do not provide monotonically increasing increments, and others have them with lots of caveats (e.g. mysql and postgres).awesome, thanks @sujayakar !
for example, if you have a messages table, you could add a messageNumber field with an index on it. then, when inserting a message, you can query for the message with the highest message number and insert with one plus that, if present. then, you can be guaranteed that messageNumber is strictly monotonically increasing across different messages.If messages can be deleted, though, then you could end up with a messageNumber being reused. This would mean clients querying for messages "since" a certain messageNumber wouldn't see the message that reused an old messageNumber. So in this case, I might need to hold on to this monotonically increasing number in a separate table -- does that sound right?
That would work, or mark messages as
isDeleted: true rather than deleting the document