[jimlikeslimes]

I ran into a fun bug last week. I wrote a piece of .Net Core software that accepts a batch of transactions. The transactions are shovelled into Sql Server without much processing. Each transaction had a timestamp set to DateTime.UtcNow in C#. I didn't expect the transactions to end up with duplicate timestamps but DateTime.UtcNow only has a resolution of between 0.5 and 15 milliseconds. So in a tight loop I got duplicates and couldn't determine the order my transactions were processed when querying the db, which is important!

In the end I used Sql Server's SYSUTCDATETIME which is precise to 100 nanoseconds. More importantly I gave all transactions in the batch the same timestamp, added a batch order int column, and a unique contraint on the timestamp, batchOrder columns.

Turns out DateTime.UtcNow is not really designed to give you a precise value for a messaging or transaction system! More of a sometime that day, probably.

Edit: don't trust timestamps, especially from different sources.

[Philip-J-Fry]

Timestamps no matter how precise don't show you the true order. If ordinality is so important then you should be using something else that guarantees ordinality, like an incrementing ID or something, not timestamps.

[jimlikeslimes]

Yes I think in the end I'll likely go for an GUID Id column, an integer InsertOrder column, and a timestamp which is non-essential meta-data.

[skitter]

Timestamps aren't necessarily monotonic anyways – what if the system time changes?