Sql server - What guidelines should be followed to help minimize deadlocks?

What guidelines should be followed to help minimize deadlocks?

Guidelines to minimize deadlocks:
- Access objects in the same order.
- Avoid user input/interaction during a transaction.
- Keep transactions as short as possible.
- Use a lower isolation level
- Use bound connections.
- Use a row versioning-based isolation level

What guidelines should be followed to help minimize deadlocks?

Guidelines to minimize deadlocks:-
- Avoid user interaction in the transactions. The transaction must not rely on any inputs from the user.
- The concurrent transactions must access data in the same order. There should be consistency in which the operations occur
- Transactions must be short and simple to avoid deadlocks. Long transactions may block other necessary activities
- A lower isolation level like read committed must be used. Using lower isolation levels reduces the possibility of holding shared locks for a longer time
- Bound connections should be used. Here, two or more connections opened by the same application can assist each other.

What guidelines should you follow to help minimize deadlocks?

Prevent deadlocks:
Mutual exclusion: This means that no process will have exclusive rights on a resource. Processes should request all resources they will need before starting. This is to avoid “hold” and “Wait” situation.

No preemption: If a process that is holding a resource requests for more resources that cannot be immediately allocated to it, then all resources currently being held are released

Circular wait: imposes an ordering on the resources. The requests for ordering resources in an increasing order

What is a dead lock?

When two computer programs sharing the same resource prevent each other from accessing the resource results in a deadlock. This deadlock results in finishing the program.

Example:
P1 requests R1 and receives it.
P2 requests R2 and receives it.
P1 requests resource R2 and is queued up, pending the release of R2.
P2 requests resource R1 and is queued up, pending the release of R1

Here, P and R is Program and Resource respectively.