A select in SQL Server causes a deadlock

Project on-line, before the preparation of the acceptance of a serious problem: many SELECT statements as a deadlock of the sacrifice, most of the report cannot be opened. It's a big problem. All reports are inaccessible, and our report is placed on the TV carousel, and the TV is in the factory, so after the problem, the whole factory knows.

To solve this problem is more tortuous, first of all, the colleague who wrote the SAP interface found the problem: SAP has been transmitting error data causing the production table to be locked. This deadlock problem did not occur after modifying the error data transmitted by the SAP. But when I looked at the production environment server log, I found that the problem still exists, because the customer did not mention the problem, I have no reason to ask to spend time to revise, because I have other projects are busy.

The problem always exists and its exposure is only a matter of time. A week later, when we were going to talk about the project acceptance, the problem was revealed. Because the impact is very large, to give customers a bad impression. So modify this problem got the boss's support, to tell the truth, I also encountered this problem for the first time, also can't think of how the SELECT statement is dead locked. I know this problem is very headache, fortunately, with the support of the boss, will give enough time for me to solve the problem, I also have confidence.

On the internet to find a lot of articles, my solution is: through the query to find the deadlock-related SQL statement, only found that the sacrifice of the SQL statement, the other SQL statement is not found, this path does not go through. The next thing is to reproduce the problem and then solve the problem. The road was just beginning to get out of the way, and it did not work, recreating an afternoon with no problem found. I lost my feeling when I was getting off work. The next day and online to find a half-day data, this time think of SQL Server Profiler to listen to the database, the tragedy is the customer over the holiday, not even the problem of the production environment database. Then talk to colleagues about the problem, his advice is still reproduced, and then remember that there is a way to reproduce, this time I suddenly think of this thing.

The repro method is to have the SQL statement loop into the production table to insert the data, since the report mostly reads the production table, then the report is often deadlocked. This time I see hope, just do not understand how the SELECT statement can cause a deadlock, and then read an article online: SQL Server in the SELECT statement caused by the deadlock (http://www.csharpwin.com/csharpspace/ 11505r288.shtml), after reading this article I feel very similar to the situation I encountered.

Through his theory I analyzed the next deadlock process:

• The SELECT statement uses a nonclustered index to query the yield information, which adds a shared lock to the nonclustered index, because there is no full data column for select on the nonclustered index, (so there will be a bookmark lookup), and the production table needs to be queried. When querying the production table, you need to add shared locks to the production table data, and you need to wait for the UPDATE statement to release the exclusive lock after updating the production table. That is, select waits for the update release lock.
• When the Update/insert statement on the production table updates the yield information, it will be positioned on the cluster index, add exclusive locks and modify the information of non-clustered index, the problem is that when modifying the non-clustered index information, it is necessary to add an exclusive lock to the nonclustered family index. At this point, the SELECT statement has added a shared lock on the cluster index and needs to be freed before an exclusive lock can be added. That is, the UPDATE statement waits for the SELECT statement to be locked.
• So the deadlock is formed.

The SQL Server row version level control solves my problem as long as the query statement is shared with the lock to solve the problem.

Use row versioning-based isolation levels: Read operations do not acquire a shared lock on the data being read (S-Lock) when the data is read from a transaction that is running under row versioning-based isolation

Find the quickest way to set the line version level:

It's amazing that after this setup, the deadlock problem doesn't exist.

Whether the query was set successfully:
Select is_read_committed_snapshot_on from sys.databases where name = Db_name ()

A select in SQL Server causes a deadlock