A SQL Server deadlock is a special concurrency problem in which two transactions block the progress of each other. The first transaction has a lock on some database object that the other transaction wants to access, and vice versa. (In general, several transactions can cause a deadlock by building a circle of dependencies.)
Example 1 below shows the deadlock situation between two transactions.
NOTE
The parallelism of processes cannot be achieved naturally using the smallsampledatabase, because every transaction in it is executed very quickly. Therefore, Example 1 below uses the WAITFOR statement to pause both transactions for ten seconds to simulate the deadlock.
Example 1
If both transactions in Example 13.5 are executed at the same time, the deadlock appears and the system returns the following output:
As the output of Example 13.5 shows, the database system handles a deadlock by choosing one of the transactions as a “victim” (actually, the one that closed the loop in lock requests) and rolling it back. (The other transaction is executed after that.) A programmer can handle a deadlock by implementing the conditional statement that tests for the returned error number (1205) and then executes the rolled-back transaction again.
You can affect which transaction the system chooses as the “victim” by using the DEADLOCK_PRIORITY option of the SET statement. There are 21 different priority levels, from ?10 to 10. The value LOW corresponds to ?5, NORMAL (the default value) corresponds to 0, and HIGH corresponds to 5. The “victim” session is chosen according to the session’s deadlock priority.
I don’t have much experience of deadlock, but searching for the resource for deadlock i have found this article very useful and this one is also good to know how to handle deadlock in SQL Server http://www.sqlservercentral.com/blogs/zoras-sql-tips/2016/07/04/know-how-to-handle-deadlock-in-sql-server/