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”