设置存储过程中所有语句的锁定粒度 - Microsoft SQL Server 2000
我正在开发一个具有批处理功能的 ETL 应用程序。更新并发性较低（即没有）。我想通过仅锁定整个表来避免粒度锁和锁升级的开销。
我想避免在每个语句中都指定 TABLOCK。有没有什么方法可以在存储过程的顶部设置锁定粒度，以便每个语句自动获取每个使用的表上的表锁？共享或独占并不重要，但共享是首选； ETL 将在没有临时查询负载的情况下运行过夜，并且在 ETL 完成时触发一批报告之前。
谢谢！
I'm developing an ETL application with batch processing. There is low (i.e. no) concurrency for updates. I'd like to avoid the overhead of granular locks and lock escalation by merely locking the entire table.
I'd like to avoid having to specify TABLOCK in every statement. Is there any way to set the locking granularity at the top of a stored procedure such that every statement automatically gets table locks on every table used? Shared or exclusive doesn't matter, though shared is preferred; the ETL will run overnight with no adhoc query load and prior to a batch of reports triggered when the ETL is complete.
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您需要查看事务隔离级别
说实话，我不明白为什么你需要做任何事情。我本以为 SQL Server 本身就能完成足够好的锁定工作。
You will need to take a look at Transaction Isolation Levels
To be honest though, I can't see why you need to be doing anything. I would have thought SQL Server would do a good enough job of locking by itself.