对 SQL 查询进行版本控制
我有一个服务器场，每个服务器定期对数据库进行相同的查询。 这些查询结果缓存在共享缓存中，可供所有服务器访问。
如何确保较新的查询不会被较旧的查询覆盖？ 有没有一种方法可以以某种方式对查询进行版本控制，例如按时间，以便这 没有发生吗？如何处理并发查询？
谢谢！
编辑：db 是 SQL Server。查询是一个选择语句。并且，缓存机制非常简单：简单的写入，没有锁定。这是因为目前无法告知选择查询的顺序。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
一种方法是在数据库中设置一个全局更新计数器，用于更新或读取（更新效率更高，但也更难正确执行）。
因此，在每次更新时，也会增加全局计数器。每次读取时，读取全局计数器，并将数据与计数器值一起放入缓存中。仅当计数器值较大时才覆盖缓存内容。
由于数据库隔离，事务应该看起来好像它们以串行方式发生（假设您已选择 SERIALIZABLE 隔离级别）。反过来，这意味着严格更高的计数器数字与更新的数据相关。
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.
缓存在哪里？是磁盘文件吗？或者数据库中的表？
当您说旧查询可能会覆盖新查询时，这是什么意思？最近完成的查询（或者可能是最近开始的查询）不是最新的吗？
您应该在执行每个查询之前锁定缓存容器，无论是文件还是表。每个服务器只有在可以获得锁的情况下才应该执行查询，否则应该等待锁定的资源。这样缓存将只包含最新的结果。
这符合你的要求吗？
Where is the cache? Is it a disk file? Or a table in the database?
And what does it mean when you say an older query might overwrite a newer one? Isn't it true that the most-recently-completed query (or maybe the most-recently-started) is the newest one?
You should lock the cache container before performing each query, whether that's a file or a table. Each server should only perform the query if it can obtain the lock, otherwise it should wait for the locked resource. That way the cache will contain only the most recent results.
Is that along the lines of what you are asking?