How to avoid deadlock while mutiple process working on same table in sql

Question

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.

Answer 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.

Things that can help.....

1. Access objects in the same order.
2. Avoid user interaction in transactions.
3. Keep transactions short and in one batch.
4. Use a lower isolation level(With caution).
5. Use a row versioning-based isolation level.
6. Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning.
7. Use snapshot isolation if possible (be aware it will hammer the hell out of your tempdb).

Have a look at this Minimizing Deadlocks

Answer 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.

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.