此 UPDATE 语句中是否存在可能的竞争条件？
我正在编写一个同步器软件，它将获取一个数据库中的所有更改并将它们同步到另一个数据库。为此，我在表 T 中添加了两列：
alter table T add LastUpdate rowversion, LastSync binary(8) not null default 0
现在我可以轻松选择自上次同步以来已更改的所有行：
select * from T where LastUpdate > LastSync
但是在执行同步后，我应该使两个字段相等。但是更新行也会更新时间戳，所以我必须这样做：
update T set LastSync=@@DBTS+1 where ID=@syncedId
但我想知道 - 这总是有效吗？如果我读取了 @@DBTS 的值，然后另一个用户在提交我的行之前设法在某处插入/更新了一行，该怎么办？这是有风险的代码吗？如果是的话——如何才能做得更好？
I'm writing a synchronizer software which will take all changes in one DB and synchronize them to another DB. To this end I've added in my table T two columns:
alter table T add LastUpdate rowversion, LastSync binary(8) not null default 0
Now I can easily select all rows that have changed since the last synchronization:
select * from T where LastUpdate > LastSync
However after performing the synchronization I should make the two fields equal. But updating the row also updates the timestamp, so I must do this:
update T set LastSync=@@DBTS+1 where ID=@syncedId
But I'm wondering - will this always work? What if I read the value of @@DBTS and then another user manages to insert/update a row somewhere before my row is committed? Is this risky code? And if yes - how could it be made better?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
将“LastSync”与真实数据存储在同一个表中可能根本不是一个好主意。
尝试将其存储在另一个没有行版本的表中。这样您就可以避免“更新行也会更新时间戳”的问题。
然后，您的同步器软件可以这样工作：
同步运行时修改的条目将具有比 max() 查询更高的 rowversion 值。它们将在下次调用同步器时同步。
Storing "LastSync" in the same table as the real data is maybe not a good idea at all.
Try storing it in another Table that doesn't have a rowversion. That way you avoid the "updating the row also updates the timestamp"-problem.
Your synchronizer software can then work this way:
Entries that are modified while the synchronization is running will have a higher rowversion value than the max() query. They will be synchronized the next time your synchronizer is called.
如果您在可序列化事务中运行此操作，则其他读/写操作将无法影响这些表。
RepeateableRead也可以完成这项工作......If you run this in a
Serializabletransaction then no other reads/writes will be able to affect these tables.RepeateableReadmay also do the job...