确定自SQL Server上次访问以来的行更改 [英] Determining row changes since last access in SQL Server
问题描述
我们有一个多用户系统，其中的用户保存到中央SQL Server 2005数据库中.我们遇到了一个问题，一个用户刷新数据库中的更改，而另一个用户保存新数据.当前收集更改的方式是在每个表上都有一个timestamp列，该列在每次插入/更新时都被填充.另一个用户将在客户端上存储一个时间戳，这是他最后一次从数据库中提取数据.
We have a multiuser system with users saving to a central SQL Server 2005 database. We have encountered an issue where a user refreshes in changes from the db while another user saves new data. The way we are currently collecting the changes is we have a timestamp column on each table which is filled at every row insert/update. Another user will have a timestamp stored on the client which is the last time he did a pull from the database.
每次保存都是在事务中完成的.我们正在处理的示例如下:
Each save is done in a transaction. The example we are dealing with is as follows:
- User1开始保存，打开一个事务并插入/修改行，更改其时间戳.
- 在User1提交更改之前，User2从数据库刷新，从而导致User2的时间戳更新.
- 用户1提交事务和所有更改.
- User2再次从数据库刷新，但是因为他的时间戳先前已更新，所以只有User1提交并拉入的更改的后半部分会导致错误和应用程序崩溃.
这使我们认为，时间戳不一定是自前端系统上次访问以来确定数据库更改的最佳方法.什么是更好的解决方案?
This is making us think that timestamps aren't necessarily the best method to use to determine database changes since the last access by the front-end system. What would a better solution be?
更多示例
- User1开始保存，打开事务并插入/修改行并更新其时间戳.
- User2开始另一个保存，打开一个事务，插入/修改其他行以更新其时间戳，然后提交他的事务.
- User3从数据库刷新并下拉User2提交的所有数据，将其LastRefreshTimestamp更新为User2在数据库中创建的最后一个时间戳.
- User1提交他的交易.
- 用户3再次从数据库刷新，但正在基于其LastRefreshTimestamp拉动用户2的事务结束与用户1的事务结束之间的所有更改，从而错过了用户1开始进行事务之前用户1的事务所提交的所有更改.
推荐答案
有趣的问题，我想不出一个简单的基于T-SQL的简单解决方案，但这正是变更跟踪所面临的同步挑战的类型.创建SQL 2008的目的是为了... http://msdn.microsoft.com/en-us/library/bb933875.aspx
Interesting problem, and I can't think of a simple clean T-SQL-based solution, but this is exactly the type of synchronization challenge that Change Tracking in SQL 2008 was created for... http://msdn.microsoft.com/en-us/library/bb933875.aspx
Nice very-high-level overview of change tracking vs Change Data Capture in this blog/article: http://blogs.technet.com/b/josebda/archive/2009/03/24/sql-server-2008-change-tracking-ct-and-change-data-capture-cdc.aspx
如果您的总体目标是保留存储库的客户端副本，则可以将其与Microsoft Sync Framework结合使用:
And you can potentially combine this with Microsoft Sync Framework if your general aim is to keep client-side copies of the repository: http://msdn.microsoft.com/en-us/sync/bb887608
这篇关于确定自SQL Server上次访问以来的行更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
