Table locking must be carefully planned and anticipated if a multiuser system is to function correctly.
Automatic Deadlock Detection
If different transactions require write access to several tables at once, you must be careful to avoid a deadlock situation. A deadlock occurs when one transaction locks some of the tables it needs and another transaction has locked some other tables and neither transaction can continue before the other has finished and releases the tables that it has locked.
When this situation, sometimes known as a "Deadly Embrace," occurs, SQL Server chooses one of the participants as the deadlock victim, and informs the unlucky user in an error message.