Avoiding a deadlock while the mutiple process runs on the same table in sql

Question

Is it possible to avoid deadlocks by creating different database users for different processes.

eg. one user for communicating with the ABC API and one user for communicating with the PQR API and another user for processing system data, which is represented by the ABC and PQR APIs? And all these users will process the same tables.

Answer 1

Deadlocks occur due to different sessions fighting for the same resources (tables, indexes, rows, etc.), the SQL server does not care about who owns the sessions, it can be the same users who have multiple sessions or multiple users, so creating multiple users solely to prevent deadlocks will not help.

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 insulation level (with caution).
• Use version-based isolation level.
• Set the READ_COMMITTED_SNAPSHOT database parameter to ON to enable fixed transaction transactions to use row versioning.
• , (, tempdb).

Minimizing Deadlocks

Answer 2

timestamp , , . , .

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

Transact-SQL concurrency [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;  

Answer 3

, , , , . , 2 / , . 22, , . , .

/ , , . , , , , , .

Answer 4

, "sp_getapplock", .

Answer 5

No, first find the victim of the dead end, look at this article . In most cases, the lack of an index or a bad index causes a dead end ...

If you can post ur deadlock details, we can offer the best possible solution.

Based on what you asked, it is better to set priority to avoid a deadlock.