First of all I would like to know what is the actual root cause of deadlocks in SQL Server 2005. Is it because when two processes access the same row in a table?
Anyways, consider two tables _Table_Now_ and _Table_History_ where both having the same structure.
Suppose there is one column called NAME.
So when one process tries to UPDATE a record with NAME='BLUE' in _Table_Now_, first, it need to put the present row with NAME='BLUE' into _Table_History_ then update
_Table_Now_, and also delete previously present row from _Table_History_.
Deadlock occurs while deleting. I do not understand why?
Please guide me!
deadlock basically mean when process A is dependent on process B and process B is dependent on process A, so A will just start\continue when B finishes and B will only start\continue when A finishes
what you may be experiencing are table (or row) lock, so SQL locks the row before updating the table to make sure no other process tries to access that row while it is doing the update.
Can you be more specific on how are you doing the insert\update\delete. You shouldnt have deadlocks in this scenario.
FYI, don't use with (NOLOCK). It will yes prevent from locking but it does so by telling SQL Server to read uncommitted data, and it can end up in data inconsistencies.