我可以限制对 SQL 游标内的单个循环的阻塞吗
我在一个较大的数据库上要做一些工作，这基本上需要为表中的每个值（大约 20k 条目）调用具有不同参数的存储过程。我希望在整个过程运行期间不阻塞该表或每个循环涉及的其他表。是否可以从 SQL 内部将事务限制为游标上的单个循环？
对我来说，手动检查流程完成后循环的表是否有更改并为新条目再次运行它非常简单，因此我不关心跟踪对此的更改。
I've some work to do on a largish database which basically requires calling a stored proc with different parameters for every value in a table (approx. 20k entries). I want to do this without blocking on that table or the other tables involved for each loop for the time it takes the whole process to run. Is is possible from within SQL to limit the transactions to individual loops over the cursor?
It'll be pretty simple for me to check manually if there were changes to the table I'm looping over after the process has completed and run it again for the new entries so I'm not concerned about tracking changes to that.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可以将事务隔离级别设置为未提交读取。
但请确保这就是您真正想要的，因为您可能会遇到脏读、丢失更新、幻像更新等......
You can set the transaction isolation level to read uncommited.
but be sure that's what you really want, since you can get dirty reads, lost updates, phantom updates, etc....
我建议循环 20000 条记录是一个糟糕的选择。最好用基于集合的解决方案替换存储过程。您应该避免使用游标。
I would suggest that looping through 20000 records is a bad choice. Better to replace the stored proc with a set-based solution. YOu should avoid using cursors.