If I have a pool of connections in a multi-threaded client sending out requests to the database server, can I rely on transaction or statement level timestamps to determine which data is most current?

For example, let's say I have a pair of transactions T1 and T2 which both return the same row which is updated in some way. Suppose T1 starts first. Is it possible for T2 to return stale data or complete earlier despite T1's timestamp being earlier? For instance, if T1 is slow and expensive, can it finish later and/or return data that is somehow more current than the data returned by T2?

Specific use case: Let's say we have a web server, a database server, and two different user agents connected to the web server. Let's say each user agent submits a mutation "simultaneously", and the web server wants to update the cache accordingly. How can we cache the correct result on the web server? By "correct," I mean the final result of these two mutations instead of the intermediate result.

  • Yes, that can happen. Please explain your need for a certain ordering and define the ordering you need. – Laurenz Albe Feb 11 at 7:15
  • I'd just like to know which response is considered most current from the client's perspective. In particular, if I'm caching results in the client side, I'd like to know how to choose between the results. If timestamp can't be used, is there another mechanism that allows me this discernment? – user2959071 Feb 11 at 17:02
  • If the client timestamp is the criterion, use the client timestamp. Essentially, it is up to you how you define the ordering. Anything that fits your use case! Perhaps you could add same sample SQL to the question that illustrates your problem. – Laurenz Albe Feb 11 at 17:05
  • It's not about ordering, it's about determining which row is actually stored in the database to avoid caching stale data. – user2959071 Feb 12 at 4:28
  • 1
    How about treating any query results that have a timestamp from less than a second ago as not yet reliable? – Laurenz Albe Feb 12 at 6:56

I assume that your problem is that when you see a row with a certain timestamp in the table, you want to to be certain that a later query cannot find a new row with a smaller timestamp.

There is no good way to guarantee that unless you serialize processing, which would be bad for performance.

You might use a workaround: if you know that none of your transactions can take longer than 0.1 seconds, consider rows with a timestamp later than current_timestamp - INTERVAL '1 second' as not yet reliable. This is a heuristic that can go wrong (I/O overload might make your transactions slower than you think), but it should work out most of the time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.