在编写存储过程时需要遵循步骤以避免导致死锁 [英] Steps need to be followed while writing a stored procedure to avoid causing the deadlocks

问题描述

目前我们在sql server 2014上面临死锁问题。

Currently we are facing deadlock issue on sql server 2014.

 我们有一个存储过程，涉及表上的多次INSERTS（大约20个）以及执行时间这个存储过程  1:30秒我们已经通过死锁图和每个死锁报告声明这个存储的
过程导致死锁

 we have one stored procedure which involves multiples INSERTS on tables(around 20) and time taking to execute this stored procedure  s 1:30 secs . we have gone through deadlock graph and each and every dead lock report stating that this stored procedure causing the deadlock

我们如何重构这个存储过程而不会导致死锁。

how can we refactored this stored procedure without causing deadlock.

请建议在编写存储过程时遵循步骤以避免导致死锁。

please suggest the Steps need to be followed while writing a stored procedure to avoid causing the deadlocks.

推荐答案

您插入了多少数据？你有触发器吗？你看到table\DML参与死锁了吗？如果你有许多非聚簇索引（除了聚集索引），那么这两个原因的可能性都会增加。



如果内存不足和/或更新大范围或行，锁升级的变化增加

How much data do you insert ? Do you have triggers ? Do you see what tables\DML participate in the deadlock. If you have many nonclustered indexes (in addition to a clustered index), then the chances of both these reasons increase.

If you are low on memory and/or updating large ranges or rows, the change of lock escalation increases

我认为 您需要在插入一组数据之前专门锁定表格

I think  you need to exclusively lock the table before insert a set of data 

这篇关于在编写存储过程时需要遵循步骤以避免导致死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！