This article is related to a post written a while ago about NOLOCK hint. Basically what I want to say is that waiting for a lock is not actually a deadlock. The process of acquiring locks is actually pretty normal and is indeed required by most systems - a reason for using a RDBMS instead of an excel file to store our data.

Simple explanation for a deadlock

A classic deadlock example is when user1 has a lock on table1 and wants to acquire a lock on table2; user2 has a lock on table2 and wants to acquire a lock on table1. In such a situation, SQL Server will choose a deadlock victim, one transaction continue and the other one is rollback.

To reduce the chance of a deadlock, you should minimize the duration of transactions!

Shared lock

When a SELECT statement is executed, its transaction obtains a shared lock on the data. Another SELECT that tries to read the same piece of data is permitted to read; but a transaction that tries to update the data will be prevented from doing so until the shared lock is released. How long a shared lock is maintained?

• when transaction isolation level is READ_COMMITTED lock against a row/page is releases when stepping to the next row/page
• when transaction isolation level is SERIALIZABLE or REPEATABLE_READ lock is maintained for the duration of the query.
• READ_UNCOMMITTED is not requesting any locks