If you work with SQL Server for very long, you will probably find yourself looking into how it locks data. Data locks are one of the biggest causes of slow performing queries. There are things you can do to optimize your database performance if you first understand how longing is performed. Here are some high level features of locking to help guide you in your further research.
First, locking is performed on tables, and the data they contain. The smallest scope for a lock you can hold is a single row. The largest lock is for an entire table. Between those two, there is a page lock, with a variable number of records depending on the size of each record contained in the page. A page holds 8k of data max, regardless of the number of records.
Second, there are different kinds of locks. There are read only locks, which don’t block other read only locks, but can block insert, update or delete locks. There are locks for data modification. This is a big topic, and you should do some research to find out what kinds of locks may be used, and how they interact.
Third, Microsoft SQL Server provides lock management on your behalf. When your query is submitted, it attempts to use the most granular lock available, a row level lock. When the number of locks increase dramatically, then the scope of the lock increases to a page or pages, ultimately scaling to a table lock. Usually it is best to allow SQL Server to manage the lock scope. You may find yourself specifying the kind of lock you wish. Those are done through query hints in your statement. See BOL for examples.
Knowing how locks escalate, it can help you write your different queries so they cooperate. If you have query 1 holding a table lock on Table B, requiring locks on table A, and query 2 holding a table lock on Table A, while requiring locks on Table B, neither query can complete. This is a deadlock condition. Both queries are blocking each other. Eventually SQL Server will terminate one of the queries, and one query will complete. You can reduce this condition by performing locks in the same order on your different queries. You can still have contention…but this reduces the frequency of long term deadlocks or blocking.
Look for locks, lock escalation, and lock scope when you are researching for further understanding on this topic.
Cheers,
Ben