SQL deadlock four precise ways to avoid it

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.

Deadlock
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 MoreSQL | DB2 | QUIZ

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.