如何在多个进程在sql中的同一个表上工作时避免死锁

Can we avoid deadlock by creating different database users for different processes

我们可以通过为不同的进程创建不同的数据库用户来避免死锁

e.g. one user for communicating to API 'ABC' and one user for communicating to API 'PQR' and other user for Processing System data which is brought by API 'ABC' and 'PQR'? And all these user will process same tables.

例如一个用户与API'ABC'进行通信，一个用户与API'PQR'和其他用户进行通信，处理系统数据由API'ABC'和'PQR'提供？所有这些用户都将处理相同的表。

5 个解决方案

#1

Deadlocks happen because of different sessions fighting for the same resources (tables, indexes, rows etc), SQL server doesn't care about who is the owner of the sessions, it can be the same users having multiple sessions or multiple users. So creating multiple users solely to avoid deadlocks isn't going to help.

死锁发生是因为不同的会话争用相同的资源（表，索引，行等），SQL服务器不关心谁是会话的所有者，它可以是具有多个会话或多个用户的相同用户。因此，仅为了避免死锁而创建多个用户并没有帮助。

Things that can help.....

可以帮助的事情......

Access objects in the same order.
以相同的顺序访问对象。
Avoid user interaction in transactions.
避免交易中的用户交互。
Keep transactions short and in one batch.
保持交易简短，一次完成。
Use a lower isolation level(With caution).
使用较低的隔离级别（谨慎）。
Use a row versioning-based isolation level.
使用基于行版本控制的隔离级别。
Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning.
将READ_COMMITTED_SNAPSHOT数据库选项设置为ON以启用读取提交的事务以使用行版本控制。
Use snapshot isolation if possible (be aware it will hammer the hell out of your tempdb).
如果可能的话，使用快照隔离（请注意它会破坏你的tempdb）。

Have a look at this Minimizing Deadlocks

看看这个最小化死锁

#2

I guess that would prevent deadlock because you would have different users accessing different processes but that wouldnt really fix a deadlock problem. Deadlock is more where 2 entities are accessing the same piece of data/ the data gets blocked and then no one can finish the transaction. Its more like a catch 22 situation where they are both waiting for the other to finish but they both cant. Creating different users for different processes would prevent deadlock but its not really practical.

我想这可以防止死锁，因为你会有不同的用户访问不同的进程，但这不会真正解决死锁问题。死锁是更多的地方，2个实体访问同一条数据/数据被阻止，然后没有人可以完成交易。它更像是一个捕获22的情况，他们都在等待对方完成，但他们都不能。为不同的进程创建不同的用户可以防止死锁，但它并不实用。

A normal approach/best practice would simply be to program the system to use locks so that transactions are locked in a certain order when entities are accessing them. This would prevent any transactions from falling into a deadlock scenario and if one transaction is using data, another trying to access the same piece would be forced to wait for the other to finish before it can proceed.

正常的方法/最佳实践只是将系统编程为使用锁，以便在实体访问它们时以特定顺序锁定事务。这将防止任何事务陷入死锁情况，并且如果一个事务正在使用数据，则另一个尝试访问同一块的另一个将*等待另一个完成，然后才能继续。

#3

Personally, you can add a timestamp column to a table to help maintain the integrity of the database when multiple users are updating rows at the same time. You may also want to know how many rows and which rows were updated without re-querying the table.

就个人而言，您可以向表中添加时间戳列，以便在多个用户同时更新行时帮助维护数据库的完整性。您可能还想知道在不重新查询表的情况下更新了多少行和哪些行。

CREATE TABLE MyTest (myKey int PRIMARY KEY, myValue int, RV rowversion);

Then, you can then use the following sample Transact-SQL statements to implement optimistic concurrency control on the [table-name] table during the update.

然后，您可以使用以下示例Transact-SQL语句在更新期间在[table-name]表上实现乐观并发控制。

DECLARE @t TABLE (myKey int);  
UPDATE MyTest  
SET myValue = 2  
    OUTPUT inserted.myKey INTO @t(myKey)   
WHERE myKey = 1   
    AND RV = [row-version-value];  
IF (SELECT COUNT(*) FROM @t) = 0  
    BEGIN  
        RAISERROR ('error changing row with myKey = %d'  
            ,16 -- Severity.  
            ,1 -- State   
            ,1) -- myKey that was changed   
    END;

#4

It may not be suitable in all cases, but we try to handle the processing logic in a stored procedure and use 'sp_getapplock' to prevent the procedure transaction from being used simultaneously.

它可能并不适用于所有情况，但我们尝试在存储过程中处理处理逻辑并使用'sp_getapplock'来防止同时使用过程事务。

#5

No, first find the deadlock victim look at this article. In most cases its lack of index or bad index causes deadlock...

不，首先找到死锁受害者看看这篇文章。在大多数情况下，它缺乏索引或糟糕的索引会导致死锁......

If you can post ur deadlock details we can suggest a best possible solution.

如果您可以发布死锁详细信息，我们可以提出最佳解决方案。

Based on what you have asked its better to set priority to avoid deadlock.

根据你的要求，最好设置优先级以避免死锁。

#1