This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What should be our precautions and strategies to avoid a Deadlock in a Database System.
We should use clustered index maximum for all types of transaction. The clustered key stored in the same data page so whenever we are updating or deleting data, it saves the data searching time.
You should always write UPDATE and DELETE statement with the filter of a 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 at a time, do the batchwise insertion.
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 unnecessarily hidden triggers which are manipulating transaction for another table. Many times, I found that Deadlock occurs because of trigger hold a lock on another table.
You should avoid SELECT * and specify the only require column list. You can visit this article for more detail. “SELECT all columns to be good or bad in database system”
