I have a farm of servers, each server is regularly making an identical query to the database. These query results are cached in a shared cache, accessible to all servers.
How can I ensure that a newer query does not get overwritten by an older query? Is there a way of versioning the queries somehow, by time for example, so that this doesn't happen? How to deal with concurrent queries?
Thanks!
Edit: db is SQL Server. Query is a select statement. And, caching mechanism is very simple: simple write, with no locking. And that is because there is currently no way of telling the order of the select queries.
One approach is to have a global update counter in the database, either for updates or for reads (updates is more efficient, but also harder to get right).
So on each update, also increment the global counter. On each read, read the global counter, and put the data into the cache along with the counter value. Only overwrite the cache contents if the counter value is larger.
Because of database isolation, transactions should appear as if they happened in a serial manner (assuming you have chosen the SERIALIZABLE isolation level). That, in turn, will mean that strictly higher counter numbers relate to more recent data.