---
title: "How to Avoid SQL deadlock: 4 Precise Ways"
description: "The popular ways to prevent deadlock in database while dealing with SQL queries written clearly such that even layman can understand quickly."
url: https://srinimf.com/2017/10/27/sql-deadlock-four-precise-ways-to-avoid-it/
date: 2017-10-27
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2017/10/sql-deadlock.png?fit=440%2C385&ssl=1
categories: ["SQL"]
tags: ["deadlock prevention", "prevent deadlock sql", "SQL tips and tricks"]
type: post
lang: en
---

# How to Avoid SQL deadlock: 4 Precise Ways

[How to](https://srinimf.com/2016/12/13/the-best-solution-for-deadlock-in-ims-db-database/) prevent dead locks  is really good question asked by many [SQL developers](https://community.oracle.com/community/database/developer-tools/sql_developer).

## **What** **is** **dead** **lock** **in** **database**

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

[![Deadlock](https://i0.wp.com/db.apache.org/derby/docs/10.0/images/dead.gif?ssl=1)](http://searchdatamanagement.techtarget.com/answer/Why-deadlocks-occur-and-what-to-do-about-them)*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](https://en.wikipedia.org/wiki/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](https://srinimf.com/category/sql/) | [DB2](https://srinimf.com/tag/db2-tips/) | [QUIZ](https://srinimf.com/category/quiz/)

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2017%2F10%2F27%2Fsql-deadlock-four-precise-ways-to-avoid-it%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2017%2F10%2Fsql-deadlock.png%3Ffit%3D440%252C385%26ssl%3D1&description=How%20to%20Avoid%20SQL%20deadlock%3A%204%20Precise%20Ways)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2017/10/27/sql-deadlock-four-precise-ways-to-avoid-it/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2017/10/27/sql-deadlock-four-precise-ways-to-avoid-it/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2017/10/27/sql-deadlock-four-precise-ways-to-avoid-it/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2017/10/27/sql-deadlock-four-precise-ways-to-avoid-it/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20How%20to%20Avoid%20SQL%20deadlock%3A%204%20Precise%20Ways&body=https%3A%2F%2Fsrinimf.com%2F2017%2F10%2F27%2Fsql-deadlock-four-precise-ways-to-avoid-it%2F&share=email)
-

### Like this:

Like Loading…