Avoid deadlock in database transaction in database

Deadlock is occurred when two process want to access the resources of each other which they already locked by them at same time.

Example Session A help lock on employee table and looking to acquire lock on other table department. At the same time Session B held the lock on department table and looking to acquire lock on employee table. Then both sessions are waiting for each other and that cause deadlock.

There are the following ways to avoid the Deadlock:

1. Avoid locking if not required in unnecessary cases or in case of long batches operation.

2. Make sure that you required the locking in the same order Example: In above example of dead lock if both the transaction acquire lock in same sequence then the deadlock situation is avoided. Session A lock first table Employee then second table Department. If Session B use the same sequence First table Employee instead of Department then we can avoid the situation of deadlock occurred.

3. Increase the speed or performance of transaction. In that way, we can avoid the locking. If transaction held locking for lock time then it cause the situations of Dead lock so always use indexes, parallel query to finish the SQL Queries