在SQL过程和informatica工作流中使用时，Sql目标表锁 [英] Sql target table lock when used in SQL procedure and informatica workflows

问题描述

我的问题是关于并发和锁定机制。



在我的项目中， Informatica和SQL过程必须在单个SQL表上进行处理。

进程1：

Informatica将一组记录转储到SQL中表X.

然后SQL程序需要从X处获取该集合进行处理，然后再将结果转储到X.

处理2：与其他记录集相同。

有这样的14个或更多进程。



在这种情况下，锁定将成为桌面上的问题X.

请建议如何最大程度地降低锁定风险，以免因锁定X而导致任何进程停止工作。



我尝试过：



我想知道可以用来避免锁定目标表的建议和机制。

Hi,

My question is regarding concurrency and locking mechanism.

In my project, Informatica and SQL procedures have to process on a single SQL table.
Process 1:
Informatica dumps a set of records into SQL table X.
Then SQL procedure need to take that set from X to process and then dump the result into X again.
Process 2: same with other set of records.
there are such 14 or more processes.

In such case, locking will be an issue on table X.
Please suggest how to minimize the risk of locking so that no process will stop working due to lock on X.

What I have tried:

I want to know the suggestions and mechanisms which can be used to avoid locking the target table.

推荐答案

您可以解除锁定升级。对于disalbe锁升级，您可以使用ALTER TABLE tablename SET（lock_escalation = DISABLE）在表级指定。

有关详细信息，请参阅https://msdn.microsoft.com/en-us/library /ms190273.aspx



如果禁用它可能会导致更高的内存消耗。因此，在禁用它之前，请仔细检查它。

You can dislable lock escalation. For disalbe lock escalation you can specify at the table level using ALTER TABLE tablename SET(lock_escalation = DISABLE).
For more detail look at https://msdn.microsoft.com/en-us/library/ms190273.aspx

If you disable it can result in higher memory consumption. So before disabling it review it thoroughly.

这篇关于在SQL过程和informatica工作流中使用时，Sql目标表锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！