同步数据访问
几年前，我为同事构建了一个 Excel 文件，该文件显示来自外部 ODBC 数据源的大量数据。数据被分成不同工作表中的许多数据表。该文件还包含一个允许用户更新数据的按钮。
由于从外部源访问数据非常慢，因此我实现了一些缓存逻辑，将不太可能更改的部分结果存储在 SQL Server 的外部表中，并使用一些魔法来保持数据同步。 Excel文件本身仅访问SQL服务器。每个数据表都使用一个SPROC来获取部分数据。
快进 5 年。 Excel 文件的大小已变大，并且包含大量工作表和数据，以至于我们的 Excel（仍为 2003 版本）出现了问题。所以我的同事将文件分成两半。
现在的问题是，两个 Excel 文件都包含更新数据的逻辑，并且可能会发生用户单击文件号中的更新按钮的情况。 1，而另一个用户已经在更新文件号。 2.
这就是更新逻辑变得疯狂并产生垃圾的地方。
两个 Excel 文件只需要运行一次更新，因为它会更新两个文件中显示的所有数据。它相当昂贵并且持续 5 到 15 分钟。
我也可以将更新运行分成两半，但这不会使它更快，并且更新两个文件将花费两倍的时间。
我想到的是某种互斥体：用户 A 单击更新按钮，更新运行开始。用户 B 也想更新，但 (VBA/SPROC) 逻辑检测到已经有更新正在运行并等待更新完成。
Some years ago I built an Excel file for my colleagues that displays lots of data from an external ODBC data source. The data is partitioned into lots of data tables in different sheets. The file also contains a button that allows the user to update the data.
Since accessing the data from the external source was very slow, I implemented some caching logic that stored parts of the results, that were unlikely to change, in external tables on our SQL server and did some magic to keep the data synchronized. The excel file itself only accesses the SQL server. Every data table uses an SPROC to get part of the data.
Fast forward 5 years. The Excel file has grown in size and contain so many sheets and data that our Excel (still version 2003) got problems with it. So my colleagues split the file into two halfs.
The problem is now, that both excel files contain the logic to update the data and it can happen that a user clicks the update button in file no. 1 while another user is already updating file no. 2.
That's the point where the updating logic goes berserk and produces garbage.
The update run is only required once for both excel files because it updates all the data that's displayed in both files. It's quite expensive and lasts from 5 to 15 minutes.
I could split the update run into two halves as well, but that wouldn't make it any faster and updating the two files would take twice as long.
What I think about is some kind of mutex: User A clicks on the update button and the update run starts. User B wants to update too, but the (VBA/SPROC) logic detects that there's already an update running and waits till it finishes.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可以在具有可序列化隔离级别的事务中执行更新；您的更新代码需要检测并处理 SQL Server 错误 1205（并向用户报告另一个更新正在进行中）。
或者，为每行添加行版本时间戳，并且仅更新自加载以来未更改的行。
You could perform the updates in a Transaction with Serializable isolation level; your update code would need to detect and handle SQL Server error 1205 (and report to user that another update is in process).
Alternatively, add a rowversion timestamp to each row and only update a row if it hasn't been changed since you loaded it.
但当 A 完成后，B 将“免费”运行更新。
相反：当 A 单击更新时，调用异步触发更新的存储过程。
当更新开始时，它会查看上次运行的时间，如果是在 X 分钟之前，则退出。
But when A has finished, B will run the update 'for nothing'.
Instead: When A clicks update, call a stored proc which fires the update asynchronously.
When the update starts, it looks at the last time it ran itself and exits if it was less than X minutes ago.