Simultaneous select and UPDATE statement deadlock issues in SQL Server with high concurrency (i)



The recent use of SQL Server in project-on-line usage has found that frequent updates and frequent queries cause deadlocks in high concurrency situations. Usually we know that if two transactions are inserting or modifying data on a table at the same time, it will occur when the X lock on the table is requested and has been held by the other party. Because the lock is not available, subsequent commits cannot be executed so that both parties begin to deadlock. But when the SELECT statement and the UPDATE statement are executed simultaneously, how can a deadlock occur? After reading the following analysis, you will understand ...

First see the method of the query used in the code select

<span style= "FONT-SIZE:18PX;" >//<summary>///query teacher information based on student ID. For front desk Student ratings Main Page display///</summary>///<param name= "enteachercoursestudent" > Teacher Course Student Relationship entity: Studentid</param >public DataTable queryteacherbystudent (teachercoursestudentlinkentity enteachercoursestudent) {//TODO:Query Teacherbystudent string strSQL = "Select ID, Collegeteacherid,collegeteachername,teacherid,teachercode," +//"T Eachername,courseid,coursename,coursetypeid,coursetypename, "+//" Studentid,studentname,isevluation from TA_Tea            Chercoursestudentlink with (NOLOCK) "+//" WHERE StudentID = @StudentID "; The SQL statement that queries the student about which teachers scored by the student ID String strSQL = "Select ID, Collegeteacherid,collegeteachername,teacherid,teachercode," + "TEAC Hername,courseid,coursename,coursetypeid,coursetypename, "+" studentid,studentname,isevluation From Ta_teachercoursestudentlink with (NOLOCK) "+" WHERE StudentID = @StudentID "; Parameter sqlparameter[] para = new sqlparameter[] {new SqlParameter ("@StudentID", enteachercoursest Udent.            StudentID)//student ID};            Execute a SQL query statement or stored procedure with parameters DataTable Dtstuteacher = Sqlhelper.executequery (Strsql,para,commandtype.text);            Return query result return dtstuteacher; }</span>

Update method

<span style= "FONT-SIZE:18PX;" >//<summary>///Student scores on teachers, whether to evaluate the change from N to y///</summary>///<param name= "Enteachercoursestudent" >        Teacher Course student Relationship entity: StudentID, Teacherid, courseid</param>//<return> whether modified successfully, True succeeded, false failed </return> Public Boolean editisevaluation (teachercoursestudentlinkentity enteachercoursestudent, SqlConnection Sqlcon, SqlTransaction Sqltran) {//change whether the SQL statement with the field "Y" is evaluated as String strSQL = "UPDATE Ta_teachercoursestuden Tlink with (UPDLOCK) SET isevluation= ' Y ' WHERE [email protected] and [email protected] and [email             Protected] "; Parameter sqlparameter[] Paras = new sqlparameter[]{new SqlParameter ("@TeacherID", Enteachercoursestu Dent.                Teacherid),//teacher ID new SqlParameter ("@StudentID", Enteachercoursestudent.studentid),//student ID            New SqlParameter ("@CourseID", Enteachercoursestudent.courseid)//course ID}; Li Shi added December 29, 2014           Boolean flagmodify = false; try {//execute with parameter additions and deletions to SQL statement or stored procedure flagmodify = Sqlhelper.execnoselect (strSQL, paras, Co            Mmandtype.text, Sqlcon, Sqltran);            } catch (Exception e) {throw e; }//Returns the result of the modification return flagmodify;} </span>

Now analysis, in the database system, the deadlock refers to a number of users (processes) to lock a resource, and then try to request to lock the other's locked resources, which creates a lock request ring, causing multiple users (processes) are waiting for the other party to release the state of the locked resources. There is also a typical deadlock situation when in a database, there are several long-running transactions to perform parallel operations, when the Query Analyzer processing a very complex query such as a connection query, then because the processing order can not be controlled, there is a possibility of deadlock phenomenon.

So, what caused the deadlock?

Phenomenon Diagram

By querying the transaction log view of SQL Server, the error log view that occurs is known to be the deadlock that occurs when the update and select are raised in high concurrency

, let's look at an example;

<span style= "FONT-SIZE:18PX;" >create PROC p1 @p1 int      as SELECT C2, C3 from T1 WHERE C2 between @p1 and @p1 +1   GO   CREATE PROC p2 @p1 int A S         Update t1 Set c2 = c2+1 where C1 = @p1         UPDATE T1 SET c2 = c2-1 WHERE C1 = @p1   go</span>

P1 no insert, no delete, no update, just a SELECT,P2 is update.
So, what caused the deadlock?

<span style= "FONT-SIZE:18PX;" > need to see the SQL deadlock information from the event log:   Spid X is running this query (line 2 of proc [P1], InputBuffer "... EXEC p1 4 ... "):   Select C2, c3 from T1 WHERE C2 between @p1 and @p1 +1   Spid Y are running this query (line 2 of proc [ P2], inputbuffer "EXEC P2 4"):   UPDATE t1 SET c2 = c2+1 WHERE C1 = @p1 the                  SELECT is waiting for a Shared KEY lock On index T1.CIDX. The UPDATE holds a conflicting X lock.   The UPDATE is waiting a eXclusive KEY lock on index t1.idx1. The SELECT holds a conflicting S lock.</span>

First, let's look at P1 's execution plan. What do you think? The SET STATISTICS profile can be executed on, which can be done. Here's the P1 execution plan.

<span style= "FONT-SIZE:18PX;" >select C2, C3 from T1 WHERE C2 between @p1 and @p1 +1        |--nested Loops (Inner Join, OUTER REFERENCES: ([Uniq1002], [t1 ]. [C1])               | --index Seek (OBJECT: ([t1].[ IDX1]), SEEK: ([T1]. [C2] >= [@p1] and [T1]. [C2] <= [@p1]+ (1)) ORDERED FORWARD)                     |--clustered Index Seek (OBJECT: ([t1].[ CIDX]), SEEK: ([T1]. [C1]=[t1]. [C1] and [uniq1002]=[uniq1002]) LOOKUP ORDERED FORWARD) </span>

We see a nested loops, the first line, the ROWID that seek,seek out using the index T1.C2, in the second row, to find the entire row of data through a clustered index. What is it? Is the bookmark lookup Ah! Why? Because we need C2, C3 can not be fully indexed t1.c1 out, so we need to bookmark the search.

OK, we'll go on to see P2 's execution plan.

<span style= "FONT-SIZE:18PX;" >update T1 SET c2 = c2+1 WHERE C1 = @p1         |--clustered Index UPDATE (OBJECT: ([t1].[ CIDX]), OBJECT: ([T1]. [idx1]), SET: ([T1]. [C2] = [Expr1004]))               |--compute scalar (DEFINE: ([expr1013]=[expr1013]))                     |--compute scalar (DEFINE: ([Expr1004] =[T1]. [C2]+ (1), [expr1013]=case when case] ...                           | --top (ROWCOUNT est 0)                                 |--clustered Index Seek (OBJECT: ([t1].[ CIDX]), SEEK: ([T1]. [c1]=[@p1]) ORDERED FORWARD) </span>

Seek by the clustered index finds a row and then begins the update. Note here that when update is applied, it will apply for an X lock for clustered index.

In fact, here we understand why update creates a deadlock on select. Update, will apply for a clustered index x lock, so blocked (note, not a deadlock!) The last clustered in the Select Index Seek. Where is the other half of the deadlock? Note Our SELECT statement, C2 exists in index IDX1, C1 is a clustered index CIDX. Here's the problem! We have updated C2 this value in P2, so SQL Server automatically updates the nonclustered index that contains the C2 column: Idx1. And where is IDX1? Just in the SELECT statement we just made. The change to this index column means that a row or rows of the index collection need to be rearranged and rearranged, requiring an X lock.

So ..., the problem has been found out.

Summarize

That is, if a query uses a nonclustered index to select data, it holds an S lock on the nonclustered index. When there are some columns of select that are not on the index, it needs to find the row of the corresponding clustered index according to rowID, and then find the other data. At this point, in the second query, update is busy on the clustered index: Locate, locking, modify, and so on. But because a column that is being modified is a column of another nonclustered index, at this point it needs to change the information for that nonclustered index at the same time, which requires a second x lock on that nonclustered index. Select starts waiting for the x lock of the update, and update begins to wait for the select's S lock, deadlock, and so on the bird.

Added a nonclustered index to the same table for select and update to solve the problem

So why do we add a nonclustered index, and the deadlock disappears the bird? Let's take a look at the execution plan following the automatically added index above:

<span style= "FONT-SIZE:18PX;" >  SELECT C2, c3 from T1 WHERE C2 between @p1 and @p1 +1      |--index Seek (OBJECT: ([deadlocktest].[ DBO]. [T1]. [_dta_index_t1_7_2073058421__k2_k1_3]), SEEK: ([Deadlocktest]. [dbo]. [T1]. [C2] >= [@p1] and [deadlocktest]. [dbo]. [T1]. [C2] <= [@p1]+ (1)) ORDERED FORWARD) </span>

Well, there's no need for clustered index, because the increased coverage index is enough to select all of the information. It's so simple.

In fact, in SQL Server 2005, if you use Profiler to catch eventid:1222, then there will be a deadlock diagram, it is very intuitive to say.

The following methods help minimize deadlocks (for details, see SQL Server online Help, search: Minimize deadlocks.)

· Access the object in the same order.

· Avoid user interaction in a transaction.

· Keep the transaction short and in a batch.

· Use a lower isolation level.

· Use the isolation level based on row versioning.

-Set the READ_COMMITTED_SNAPSHOT database option to on so that committed read transactions use row versioning.

-Use snapshot Isolation.

· Use a bound connection.

 

Simultaneous select and UPDATE statement deadlock issues in SQL Server with high concurrency (i)