A deadlock occurs when one process locks a page or a table while another process needs it, and the second process has a lock that the first process needs. A deadlock is also known as a deadly embrace. Microsoft® SQL Server™ detects and resolves deadlocks automatically. If a deadlock is found, the server terminates the user process that has completed the deadly embrace.
After every data modification, your program code should check for error message number 1205, which indicates a deadlock situation. If this error message number is returned, a deadlock has occurred and the transaction was rolled back. In this situation, your application must restart the transaction.