SQL Server Deadlock (page lock) diagnostics

Turning on deadlock monitoring in the database collects the deadlock conditions that occur in the database. There are 2 ways to open it:

1 Turn on 1222 monitoring

Execute SQL statement:

DBCC Traceon (1222,-1);

Then view the deadlock information in the system log.

2 Start SQL Profiler (recommended):

Here is an example diagram of a deadlock occurring:

The following provides an analysis of the deadlock, if there are inappropriate, but also hope that you criticize correct.

There are 3 questions, each one answered below.

First question: What is the resource being locked?

It is very clear that it is a page lock, so what is page lock ?

Usually a deadlock is when you operate a table, and then you operate B, and the other process first operates the B table, and then waits for you to release the deadlock caused by the lock on the table, then the diagram above will explicitly say table lock, not page lock.

To explain page Lock, first explain what the pageis.

In SQL Server, the data is stored in a 8K data unit, called the Page.

When reading data, it is not a stroke of reading, but a page reading of a page, so the SQL Server document will say that some unwanted data may be read.

After you know the page, how can you have a page lock? Table Lock, Row lock is easier to understand, but page lock is not easy to understand, in fact, when SQL Server takes the big table A strategy that it thinks is the best price. If you take a table lock, then once the update data, then others can not select, otherwise there will be inconsistent data (so-called Dirty reading, Phantom reading), but you may say, you modify your, I read not you want to change the pen, you do not lock me, should take row lock, But imagine how expensive it would be to take a row lock for a table with 1 million data. So SQL Server took a mean way to use the 8K page page as the unit of the lock, thus balancing the concurrency and performance issues, the most cost-effective.

Locate page

There are many page in a table, so what is the page locked in this example?

Execute SQL statement Query DB name

Select db_name (5)

Open this database, execute the following SQL statement, you can query who this page belongs to.

     Select object_name (i.object_id), i.name from     sys.partitions as P     Inner joins sys.indexes as I on i.object_id = P.ob ject_id and i.index_id = p.index_id     where p.partition_id = ' 72057594048544768 '  

is a nonclustered index.

What did you put in the page?

Now we know that it locks a data page of an index (if you are interested in the page organization of an index, refer to the index internal structure instance). So what information is stored in the locked 2 page?

Execute the following sql:

The results are as follows:

Execute the following sql:

The results are as follows:

The second question: Who is locking these pages?

After the view found:

1 SQL statements is a SELECT

2 SQL statements are update

Why is the Select statement locking these 2 pages?

In this example, the SELECT statement uses this index, use it to quickly locate, then it is based on the branch node of the index to find, and then to the specified page to read the search, because it is the top 500, so, may find a batch of data, but not enough 500, so then go to the other pages, Until you get 500.

Why did the Update statement lock the 2 pages?

If we find this data, can we just change it? Not, modify the value of an index by at least the following 3 steps:

question three: What does Exchange event mean?

This example analyses and summarizes:

There are multiple data pages in an index, and the UPDATE statement is updating a piece of data in this index, locking 2 pages, and stealing from the 2 pages used in select to form a deadlock.

Solution:

Adjust the statement to adjust the select?

Let it not use this index? The index is created to give select, this select does not, the other select will be used.

Do you want to adjust Update?

Because the updated fields are included in the index, they are always modified synchronously, regardless of how they are adjusted.

Conclusion:

Deadlock is unavoidable, and what we have to do is to reduce the frequency of occurrence, which we often hear, but why? If the order of the update tables is inconsistent, then you should be able to eliminate it by modifying the commit update SQL, but in this case? In what way do you intend to make this situation never exist ?

Reducing the frequency of occurrence is a requirement for us, such as this example, how to reduce?

Reduce the frequency of SQL execution:

Is the SELECT statement and the UPDATE statement issued too frequently, and is the business really so frequent? Are they all set at the same time to execute? For example, update and select are all 5 second rounds?

Improve the efficiency of SQL execution:

How many pens are there in the data returned by the Select statement? Can you reduce some more? How many columns are returned? Can you reduce some?

should the query plan for the Select statement be optimized? up to 3-5 page reads should return results, how many logic read does this select have? Do I need to add an index with an exact location? (Do we add an index to a select?) Of course not, this is used in situations where this deadlock occurs frequently, because since it is frequent, the select executes more frequently, and it is easy to hit with the update. ）

Can select not lock?

Can a small range of dirty reads be allowed on the business?

SQL Server Deadlock (page lock) diagnostics