在数据库外部存储主键生成器的安全方法？
我们有一个将数据存储在 SQL Server 中的应用程序。每个表都有一个 bigint 主键。我们过去专门按需生成这些，即当您要插入新行时，首先调用以生成下一个 ID，然后再执行插入。
我们添加了对在离线模式下运行的支持：如果您的连接断开（或 SQL Server 断开），它会将数据保存到本地文件，直到您重新上线，然后同步您此后所做的所有操作。
这需要能够在客户端生成 ID。现在，它不再向 SQL 询问下一个 ID，而是询问接下来的一百个、一千个或 10,000 个 ID，然后将范围存储在本地，因此在这 10,000 个 ID 用完之前，它不必询问更多。它实际上会将它们分成更小的块，因此当 5000 个用完时，它仍然有 5000 个缓冲区，并且它可以再请求 5000 个。
问题是，一旦上线，我们就开始收到主键违规的报告。我们将数据存储在 Windows 注册表的 HKEY_CURRENT_USER 中（注册表中保证用户能够写入的唯一位置）。因此，经过一些研究，我们发现 HKEY_CURRENT_USER 是漫游配置文件的一部分，因此 ID 可能会被旧版本覆盖。特别是当用户同时登录网络上的多台计算机时。
因此，我们重写了生成 ID 的部分，以便从用户的“本地设置”目录中读取/写入文件。当然，这不应该被旧版本覆盖。但即使是现在，我仍然偶尔会看到主键违规。在这种情况下，我们唯一能做的就是删除文件中的所有键，将用户踢出应用程序，并且在获得新的 ID 范围之前不要让他们重新进入。
但如果“本地设置”不安全，那什么才是安全的呢？是否有任何地方可以在计算机上存储持久值，并保证不会回滚到旧版本？谁能解释为什么“本地设置”不符合这个标准？
我已经考虑过类似 GUID 的解决方案，但这本身就有问题。
We have an application which stores its data in SQL Server. Each table has a bigint primary key. We used to generate these exclusively on demand, i.e. when you go to insert a new row, you first make a call to generate the next ID, and then you do the insert.
We added support to run in offline mode: if your connection is down (or SQL Server is down), it saves the data to a local file until you go back online, and then syncs everything you've done since then.
This required being able to generate IDs on the client side. Instead of asking SQL for the next 1 ID, it now asks for the next hundred or thousand or 10,000 IDs, and then stores the range locally, so it doesn't have to ask for more until those 10,000 run out. It would actually get them in smaller chunks, so when 5000 run out, it still has a buffer of 5000, and it can ask for 5000 more.
The problem is, as soon as this went live, we started getting reports of primary key violations. We stored the data in the Windows registry, in HKEY_CURRENT_USER (the only place in the registry a user is guaranteed to be able to write to). So after some research, we discovered that HKEY_CURRENT_USER is part of the roaming profile, so it's possible the IDs could get overwritten with an old version. Especially if the user logs into multiple computers on the network simultaneously.
So we re-wrote the part that generates IDs to read/write a file from the user's "Local Settings" directory. Surely that shouldn't get overwritten by an old version. But even now, I still see occasional primary key violations. The only thing we can do in that case is delete any keys in the file, kick the user out of the application, and don't let them back in until they get new ID ranges.
But if "Local Settings" isn't safe, what would be? Is there anywhere you can store a persistent value on a computer which is guaranteed not to be rolled back to an old version? Can anyone explain why "Local Settings" does not meet this criteria?
I've done some consideration of a GUID like solution, but that has problems on its own.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
在您的分布式环境中，最好的选择是使用 GUID
in distributed environment as yours, your best bet is using GUID
在本地保存数据时是否必须使用与数据库同步时使用的相同密钥？
当您在本地保存数据，然后在实际将数据写入数据库时生成真正的密钥时，我会非常想使用 GUID。或者从值 1 开始将数据持久化到本地，然后在实际将数据写入数据库时生成真正的键。
Do you have to use the same key when you persist the data locally that you use when you sync with the database?
I would be sorely tempted to use a GUID when you persist the data locally and then generate the real key when you're actually writing the data to the database. Or persist the data locally starting with a value of 1 and then generate real keys when you actually write the data to the database.
设置 IDENTITY (http://www.simple-talk. com/sql/t-sql-programming/identity-columns/) 在 bigint 主键上，以便 SQL Server 自动生成值。
当您的应用程序离线时，您可以将待处理的更改保留在本地。当它重新上线时，您发送更新（包括新记录），SQL Server 将插入它们并自动分配主键，因为您已经设置了 IDENTITY。
如果您需要知道插入后生成/使用的键值，可以利用 @@IDENTITY 属性 (http://msdn.microsoft.com/en-us/library/aa933167%28v=sql.80%29.aspx)
Setup an IDENTITY (http://www.simple-talk.com/sql/t-sql-programming/identity-columns/) on the bigint primary key so that SQL Server generates the values automatically.
When your application is offline, you keep the pending changes local. When it comes back online, you send your updates (including new records) and SQL Server would INSERT them and automatically assign a primary key since you have the IDENTITY setup.
If you need to know what key value was generated/used after an insert you can utilize the @@IDENTITY property (http://msdn.microsoft.com/en-us/library/aa933167%28v=sql.80%29.aspx)