所有客户端的数据库数据实时同步
保持数据库服务器的所有客户端同步的最佳策略是什么？
该场景涉及一个数据库服务器和连接到该服务器以查看和修改数据的动态数量的客户端。
我需要在所有客户端之间实时同步数据 - 如果添加、删除或更新数据，我希望所有客户端都能实时看到更改，而不需要通过连续轮询对数据库引擎造成太大压力具有几百万行的表中的更改。
现在我正在使用 Firebird 数据库服务器，但我愿意采用最好的技术来完成这项工作，所以我想知道是否有任何类型的现有框架适用于这种场景，它使用什么数据库引擎以及它涉及什么？
What's the best strategy to keep all the clients of a database server synchronized?
The scenario involves a database server and a dynamic number of clients that connect to it, viewing and modifying the data.
I need real-time synchronization of the data across all the clients - if data is added, deleted, or updated, I want all the clients to see the changes in real-time without putting too much strain on the database engine by continuous polling for changes in tables with a couple of million rows.
Now I am using a Firebird database server, but I'm willing to adopt the best technology for the job, so I want to know if there is any kind of already existing framework for this kind of scenario, what database engine does it use and what does it involve?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
Firebird 有一个名为
EVENT的功能，您可以使用它来通知客户端数据库的更改。这个想法是，当表中的数据发生更改时，触发器会发布一个事件。 Firebird 负责通知所有已注册对该活动感兴趣的客户的姓名。一旦收到通知，每个客户端负责通过查询数据库来刷新自己的数据。客户端无法从事件中获取有关新值或旧值的信息。这是设计使然，因为无法通过事务隔离来解决此问题。您的客户端也不能使用通配符注册事件。因此，您必须相当广泛地设计服务器到客户端的通知，并让客户端更新以查看到底发生了什么变化。
请参阅http://www.firebirdsql.org/doc/whitepapers/events_paper.pdf
您没有提及您正在使用的客户端平台或语言，因此我无法就您将使用的具体 API 提供建议。我建议您根据您使用的语言搜索“firebird event java”或“firebird event php”或类似内容。
由于您在评论中说您正在使用 WPF，因此这里有一个指向某些注册事件通知的 .NET 应用程序代码的代码示例的链接：
http://www.firebirdsql.org/index.php?op=devel&sub=netprovider&id=examples#3
回复您的评论：是的，Firebird 事件机制承载信息的能力是有限的。这是必要的，因为它可能携带的任何信息都可能被取消或回滚。例如，如果触发器发布了一个事件，但生成该触发器的操作违反了约束，则会取消该操作，但不会取消该事件。因此，事件只能是一种“暗示”，表明可能发生了一些有趣的事情。其他客户当时需要刷新他们的数据，但他们不知道要寻找什么。这至少比民意调查要好。
所以你基本上描述了一个发布/订阅机制——一个消息队列。我不确定是否会使用 RDBMS 来实现消息队列。这是可以做到的，但你基本上是在重新发明轮子。
以下是一些广受好评的消息队列产品：
这意味着当一个客户端以其他人可能需要了解的方式修改数据时，该客户端 < em>还必须将消息发布到消息队列。当消费者客户端看到他们感兴趣的消息时，他们知道刷新某些数据的副本。
Firebird has a feature called
EVENTthat you may be able to use to notify clients of changes to the database. The idea is that when data in a table is changed, a trigger posts an event. Firebird takes care of notifying all clients who have registered an interest in the event by name. Once notified, each client is responsible for refreshing its own data by querying the database.The client can't get info from the event about the new or old values. This is by design, because there's no way to resolve this with transaction isolation. Nor can your client register for events using wildcards. So you have to design your server-to-client notification pretty broadly, and let the client update to see what exactly changed.
See http://www.firebirdsql.org/doc/whitepapers/events_paper.pdf
You don't mention what client platform or language you're using, so I can't advise on the specific API you would use. I suggest you google for instance "firebird event java" or "firebird event php" or similar, based on the language you're using.
Since you say in a comment that you're using WPF, here's a link to a code sample of some .NET application code registering for notification of an event:
http://www.firebirdsql.org/index.php?op=devel&sub=netprovider&id=examples#3
Re your comment: Yes, the Firebird event mechanism is limited in its ability to carry information. This is necessary because any information it might carry could be canceled or rolled back. For instance if a trigger posts an event but then the operation that spawned the trigger violates a constraint, canceling the operation but not the event. So events can only be a kind of "hint" that something of interest may have happened. The other clients need to refresh their data at that time, but they aren't told what to look for. This is at least better than polling.
So you're basically describing a publish/subscribe mechanism -- a message queue. I'm not sure I'd use an RDBMS to implement a message queue. It can be done, but you're basically reinventing the wheel.
Here are a few message queue products that are well-regarded:
This means that when one client modifies data in a way that others may need to know about, that client also has to post a message to the message queue. When consumer clients see the message they're interested in, they know to refresh their copy of some data.
SQL Server 2005 及更高版本支持基于数据源缓存过期的通知。
SQL Server 2005 and higher support notification based data source caching expiry.