我有一个存储过程正在更新一个非常大的表（包含超过 1 亿条记录）。存储过程正在更新该表中的记录。
步骤如下：
- 将要更新的记录 ID 存储在记录集中（并非所有记录都会更新 - 仅大约 20000 条）
- 循环遍历记录集，并为记录集中的每个记录 ID 调用存储过程
- 每次存储过程完成时（对于第 1 部分中提到的记录集中的每条记录），更新表中的标志以表示更新已完成。
我发现一些奇怪的行为。看来存储过程在完成更新之前将控制权传递回 VB6，并继续处理下一条记录。存储过程稍后会超时（在另一个记录 ID 上）。因此，即使存储过程尚未运行（因为超时），也会有一些标志表示已更新（步骤 3）。这是正常行为吗？即存储过程在完成工作之前将控制权传递回 VB6？
我用 Google 搜索了一下，发现这可能是由于 SQL Server 优化存储过程的方式造成的。我希望控制权仅在更新完成后传递回 VB6。难道不是这样吗？
请注意，我意识到可能有更好的方法来解决这个问题。我的问题具体涉及 SQL Server 在完成工作（更新）之前将控制权传递回 VB6。
I have a stored procedure that is updating a very large table (with over 100 million records). The stored procedure is updating records in this table.
The steps are as follows:
- Store record IDs to be updated in a recordset (not all records will be updated - only about 20000)
- Loop through the recordset and call the stored procedure for each record ID in the recordset
- Each time the stored procedure has finished (for each record in the recordset mentioned in part 1), update a flag in a table to say that the update completed.
I am finding some strange behaviour. It appears that the stored procedure is passing control back to VB6 before it has completed its updates and is continuing processing the next record. The stored procedure is then timing out later on (on another record ID). Therefore there are flags that say updated (step 3), even though the stored procedure has not run (because it timed out). Is this normal behaviour i.e. for the stored procedure to pass control back to VB6 before it has finished the work?
I have Googled this and I have discovered that it could be because of the way the stored procedure is optimised by SQL Server. I would expect control only to be passed back to VB6 after the updates have completed. Is this not the case?
Please note that I realise there may be better ways of approaching this. My question specifically relates to SQL Server passing control back to VB6 before it has finished the work (update).
发布评论
评论（2）
以下文章被证明是此问题的解决方案： http://weblogs.sqlteam.com/dang/archive/2007/10/20/Use-Caution-with-Explicit-Transactions-in-Stored-Procedures.aspx。似乎发生了以下行为：
1) 记录 1. 运行存储过程并创建事务。 SQL Command 对象发生超时。
2）记录2.存储过程运行成功。将控制权返回给 VB6 以更新数据库中的标志。
3）记录3.存储过程运行成功。将控制权返回给 VB6 以更新数据库中的标志。
4）记录4.存储过程运行成功。将控制权返回给 VB6 以更新数据库中的标志。
5) 程序结束。存储过程回滚事务（事务现在包含记录 1-4）。因此记录1-4不会被删除。
The following article proved to be the solution to this problem: http://weblogs.sqlteam.com/dang/archive/2007/10/20/Use-Caution-with-Explicit-Transactions-in-Stored-Procedures.aspx. It appears that the following behaviour was happening:
1) Record 1. Run stored procedure and create transaction. Timeout on SQL Command object occurrs.
2) Record 2. Run stored procedure successfully. Return constrol to VB6 to update flag in database.
3) Record 3. Run stored procedure successfully. Return constrol to VB6 to update flag in database.
4) Record 4. Run stored procedure successfully. Return constrol to VB6 to update flag in database.
5) Program ends. Stored procedure rolls back transaction (transaction now encompasses records 1-4). Therefore records 1-4 are not deleted.
你能...
其他需要调查的事情，考虑到我们不知道您测试过哪些情况...
另外，还有一些其他的想法...
Can you...
other things to investigate, given we dont not what cases you have tested for...
Also, some other thoughts...