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 dbrnd.com, 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 dbrnd.com

Leave a Reply

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

Notify of
avatar
Sort by:   newest | oldest | most voted
Manu
Guest

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

trackback

Very handful website ! Thanks Anvesh for sharing this

trackback

Perfect post sir !

wpDiscuz