sql中的并行插入语句会导致死锁吗? [英] Can parallel insert statements in sql lead to deadlock?

问题描述

我有一个 SP，它正在对许多表进行选择并没有锁定.这将使用许多分组和求和进行大量计算，因此运行大约需要 2 分钟.我需要为大约 500 组不同的数据运行它.所以总共需要大约1000分钟.

为了减少这种情况，我需要并行运行此计算.所以如果我并行运行 4 组数据，我应该在大约 250 分钟内完成.

但问题是，一旦我完成了这个计算，我就会得到一组数据，我需要将这些数据存储到一个表中，该表对其 4 个列有一些主键约束.因此，当并行运行时，我预计会出现一些死锁问题.所以我想将这些数据写入一个临时表，该表将具有相同的列但没有主键或任何约束.

因此，我希望不会有死锁.请在这方面提出建议，并让我知道我的理解是否正确.谢谢.

解决方案

INSERT 不能对同一张表上的另一个 INSERT 造成死锁，因为两个 INSERT 语句将始终以相同的顺序获取锁.话虽如此，我必须评论您的声明:

<块引用><块引用>

如果我并行运行 4 组数据，我应该在大约 250 分钟内完成.

你没有理由期待这一点.首先，此类声明表示您忽略了阿姆达尔定律.其次，SQL 工作负载已经在内部具有并行性，您的过程中的查询尽可能使用并行计划，请参阅并行查询处理，特别是如果它包含许多分组和求和".

最终您面临的是一项优化任务.像任何其他优化任务一样处理它，首先确定瓶颈.Waits and Queues 是您踏上这段旅程的必读之物.>

I am having a SP that is doing a select union of many tables with no lock. This will do lot of calculations with many groupings and sumation and so will take about 2 min for its run. I need to run this for about 500 different set of data. So It totally takes about 1000 min.

To reduce this I need to run this calculation in parallel. So if i run for 4 set of data in parallel I should be done in about 250 min.

But the problem is that once i am done with this calculations I get a set of data that i need to store into a table which has some primary key constraints on 4 of its columns. So when running in parallel I am expecting some dead lock issues. So I was thinking of writing this data to a temporary table that will have the same columns but with no primary key or any constraints.

So with this I am expecting there should be no dead lock. Please advice in this regards and let me know if my understanding is right. Thank you.

解决方案

INSERT cannot deadlock against another INSERT on the same table, because two INSERT statement on the same table will always acquire the locks in the same order. That being said, I must comment on your statement:

if i run for 4 set of data in parallel I should be done in about 250 min.

You have no reason to expect this. First and foremost such a claim denotes you ignore Amdahl's law. Second a SQL workload is already parallelism internally, the queries in your procedure use parallel plans whenever possible, see Parallel Query Processing, especially if it contains many 'groupings and summations'.

Ultimately what you're facing is an optimization task. Approach it like any other optimization task, identify the bottlenecks first. Waits and Queues is a must read as you embark this journey.

这篇关于sql中的并行插入语句会导致死锁吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！