How to Avoid SQL deadlock: 4 Precise Ways

How to prevent dead locks  is really good question asked by many SQL developers.

What is dead lock in database

This is called transaction contingency. Waiting of one transaction on other transaction is called deadlock.

Transaction A might hold a lock on some rows in the Accounts table and needs to update some rows in the Orders table to finish. Transaction B holds locks on those very rows in the Orders table but needs to update the rows in the Accounts table held by Transaction A. Transaction A cannot complete its transaction because of the lock on Orders. Transaction B cannot complete its transaction because of the lock on Accounts. All activity comes to a halt and remains at a standstill forever unless the DBMS detects the deadlock and aborts one of the transactions. The above figure shows this situation.

So, I am giving here the four best methods to avoid it.

1. First you should not leave transactions open longer than necessary. So, you need to commit the transaction as often as possible. You must use appropriately COMMIT and ROLLBACK commands.
2. You should not use higher isolation level than you really need
3. The transactions that take longer time, you should schedule them separately
4. You need to take care while writing SQL query that creates Deadlock

Read More: SQL | DB2 | QUIZ