什么 SQL-Server 锁级别适合插入?
我想每秒在表中插入近 1000 次。而且每天我都想一次查询所有插入的行。我想通过多线程和连接池来提高效率。但我想知道哪种级别的并发控制更适合我。 SQL-Server 的选项列表位于 MSDN 站点。
谢谢。
I want to make almost 1000 inserts in a table in each second. And also each day I want to query all inserted rows just once at altogether. And I want to improve efficiency by multi-threading and connection-pooling. But i want to know which level of concurrency control is more suitable for me. The list of options for SQL-Server are in MSDN Site.
Thank you.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您应该可以接受插入的默认隔离级别。你有聚集索引吗?如果是这样,请确保在插入新行时它不会产生碎片。通常 guid 不适合作为聚集索引。此外,如果您有企业版并且能够识别表中的分区,您可以使用此列(例如区域或城市)对表进行分区,并将表的分区存储在不同的文件组上。这样您就可以避免 IO 争用。
如果您每天选择一次所有数据,并且希望在选择期间保持插入速度而不需要太多锁定,则可以考虑创建数据库快照(同样是企业版)并从中进行选择。如果您可以忍受脏读,您可以在选择中添加 with(nolock) 提示。
You should be OK with default isolation level for inserts. Do you have clustered index? If so, ensure that it doesn't fragment as you insert new rows. Typically guid would be a bad candidate for clustered index. Also if you have Enterprise edition and you are able to identify partitions in your table you might to partition the table using this column (for example region or city) and store partitions of the table on different filegroups. This way you might avoid IO contention.
If you select all data once a day and you would like to maintain inserts speed during the select without too much locking, you might consider creating database snapshot (again Enterprise Edition) and select from it. If you can live with dirty reads you might add with(nolock) hint to your select.
你可能找错了树。查看使用行版本控制事务隔离而不是提供锁个别陈述的提示。
我交谈过的很多人都通过使用 READ COMMITTED SNAPSHOT 获得了良好的结果 - 它可以在数据库级别启用并且不需要更改代码。
我可以说 SNAPSHOT 过去对我很有帮助,但它确实需要更改代码。
警告一点,请确保您的 tempdb 吞吐量良好,因为行版本控制会显着增加 tempdb 上的负载。
You might be barking up the wrong tree. Have a look into using row-versioning transaction isolation instead of supplying lock hints for individual statements.
A lot of people I talk to have had good results through the use of READ COMMITTED SNAPSHOT - which can be enabled at the database level and requires no code change.
I can say that SNAPSHOT has served me well in the past, but it does require code change.
And a word of warning, be sure that your tempdb throughput is good, as row-versioning increases the load on tempdb significantly.