访问绑定记录集上的 2000 个超时
过去的爆炸... 负责管理老化的 Access 2000 adp，用于管理驻留在 SQL 2005 服务器上的数据。在其中一个数据表单上，每当我尝试对字段进行更改并将更改保存回数据库时，大约一分钟后就会超时。增加超时限制只会延迟超时错误消息的显示。我完成了所有常见的故障排除以确保兼容性（参见例如这个SO问题）。
设置如下：表单绑定到记录集（作为 SELECT * FROM table_name 查询从数据库读取。结果集的大小根据表单上设置的过滤器而变化，但是结果通常是大约 200 条记录（数据不是很多...）。表单上的某些字段会触发关联事件处理程序中的自动保存，如下所示：
Private Sub EndDate_Exit(Cancel As Integer)
some checking goes here...
...
DoCmd.RunCommand acCmdSaveRecord
End Sub
每当 DoCmd.RunCommand acCmdSaveRecord代码执行后，ADP 冻结，直到出现超时错误。更深入地研究一下，我查看了 SQL Server 上的活动监视器。acCmdSaveRecord 触发了 UPDATE 语句返回到服务器以保存更改的数据。该语句本身是由 MS Access 根据基础表的主键自动构造的，该主键似乎定义正确，但是，更新被正在运行的 SELECT 阻止。语句，对应上面提到的 SELECT * FROM table_name 查询。
这会导致死锁：用户的更改触发 UPDATE，该更新被正在运行的 SELECT 阻止（正如它所显示的那样，它源自正在编辑的表单）。我该如何解决这个问题？
我们尝试过的事情：
- 我们删除了
DoCmd.RunCommand acCmdSaveRecord语句，并将其替换为代码Me.Dirty = False，这会导致与上述完全相同的行为。 - 完整删除了
acCmdSaveRecord，并使用内置的 Access 菜单保存更改。这是与 #1 等效的功能，并且会导致完全相同的行为。 - 将
acCmdSaveRecord调用替换为将更改保存到特定字段的存储过程。效果很好，除了从 GUI 保存整个记录（调用 acCmdSaveRecord 的保存按钮）会导致相同的死锁。 - 将与表单关联的结果集的大小减少到几条记录。奇怪的是，仍然表现出完全相同相同的行为。
Blast from the past... Got tasked to mange an aging Access 2000 adp used to manage data residing on SQL 2005 server. On one of the dataforms, whenever I try to make a change to a field and save the change back to the database, it times out after about a minute. Increasing the time-out limit only delays the display of the time out error message. I went through all the usual troubleshooting to ensure compatibility (see e.g. this SO question).
This is the setup: a Form is bound to a record set (which is read from the database as a SELECT * FROM table_name query. The result set varies in size depending on filters set on the form, but the result is usually around 200 records (not a lot data...). Some of the fields on the form triggers an automatic save in an associated event handler, which looks something like:
Private Sub EndDate_Exit(Cancel As Integer)
some checking goes here...
...
DoCmd.RunCommand acCmdSaveRecord
End Sub
Whenver the DoCmd.RunCommand acCmdSaveRecord code is executed, the ADP freezes up until I get a timeout error. Digging into it a little deeper, I looked at the Activity Monitor on the SQL Server. The acCmdSaveRecord triggers an UPDATE statement back to the server to save the changed data. The statement itself is automatically constructed by MS Access based on the underlying table's primary key, which appears to be defined correctly. However, the update is blocked by a running SELECT statement, which corresponds to the SELECT * FROM table_name query mentioned above.
This leads to a deadlock: The user's change triggers an UPDATE, which is blocked by a running SELECT (which, as it appears, originated from the Form being edited). How can I get around this?
Things we have tried:
- We have removed the
DoCmd.RunCommand acCmdSaveRecordstatement and replaced it with the codeMe.Dirty = False, which leads to the exact same behavior as described above. - Removed the
acCmdSaveRecordcomplete, and used the built-in Access menu to save the change. This is functionality equivalent to #1, and leads to the exact same behavior. - Replace the
acCmdSaveRecordcall with a stored procedure which saves the change to the specific field. Works great, except that saving the entire record from GUI (save button which callsacCmdSaveRecord) leads to the same deadlock. - Reduced the size of the result set associated with the form to a few records. Oddly, still exhibits the EXACT same behavior.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
听起来好像您在某个时刻已将连接的事务隔离级别设置为
SERIALIZABLE。有关详细信息，请参阅设置事务隔离级别 (Transact-SQL)。以下是适用的摘录（已添加重点）：It sounds as though you have set the transaction isolation level to
SERIALIZABLEfor your connection at some point. See SET TRANSACTION ISOLATION LEVEL (Transact-SQL) for more details. Here is the applicable excerpt (emphasis added):