Database Design: The Ultimate Strategies to avoid Deadlock

What should be our precautions and strategies to avoid a Deadlock in a Database System.

We should use clustered index maximum with the all types of transaction. The clustered key stored in the same data page so whenever we are updating or deleting a data, it saves the data searching time.
You should always write UPDATE and DELETE statement with the filter of clustered key.

The RDBMS systems provide different types of isolation level, you should write your transactions under the proper isolation level.
If your system is ok with dirty read, you can SELECT uncommitted data which never block another transaction. You can also use MVCC architecture for this.

You should avoid high row count SQL statements that can cause a table lock. For example, instead of inserting all rows from one table to another all at once, put a single INSERT statement in a loop and insert one row at a time.

You should break the long transaction into small parts and arrange the proper sequence for that sorter transaction and also write properly COMMIT and ROLLBACK statement.

You should try to avoid unnecessary hidden triggers which are manipulating transaction for another table. Many times, I have found that Deadlock occurs because trigger creates a lock on another table.

You should avoid SELECT * and specify the require column list. You can visit this article for more detail. “SELECT all columns to be good or bad in database system”

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from

Leave a Reply

2 Comments on "Database Design: The Ultimate Strategies to avoid Deadlock"

Notify of

Sort by:   newest | oldest | most voted
5 months 21 days ago

Yeah bookmaking this wasn’t a risky decision outstanding post! .

1 month 19 days ago

Very handful website ! Thanks Anvesh for sharing this