Deadlock error on same table with two Update SQL s

2020-07-07 10:48发布

站内问答 / C#
1298 3 6

I have a C# project which writes data to a TSQL database. There are two update statements which run within a loop, eg.:

 for (int i = 0; i < customersProducts.Count; i++) {
     CustomerProducts c = customersProducts[i];

     // Update product dimensions
     for (int j = 0; j < c.Count; j++) {
         Product p = c[j];
         updateProductDimensions(p);
     }

     // ... some processing

     // Update product
     for (int j = 0; j < c.Count; j++) {
         Product p = c[j];
         updateProduct(p);
     }
 }

The updateProductDimensions() and updateProduct() both trigger SQL Update statements. There is some overlap in the columns that are updated:

string updateProductDimensions = "UPDATE products SET width = @width, height = @height, length = @length WHERE id = @id";
string updateProduct = "UPDATE products SET width = @width, height = @height, length = @length, customer_id = @customer_id, weight = @weight .... WHERE id = @id";

Example updateProductDimensions() method - updateProduct() is also similar:

public void updateProductDimensions(Product p) {
     SqlConnection connection = DBFactory.getConnection();
     string updateProductDimensions = "UPDATE products SET width = @width, height = @height, length = @length WHERE id = @id";

     try
     {
         SqlCommand sqlCmd = new sqlCmd(updateProductDimensions, connection);
         sqlCmd.Parameters.AddWithValue("@width", 20);
         sqlCmd.Parameters.AddWithValue("@height", 10);
         sqlCmd.Parameters.AddWithValue("@length", 30);
         sqlCmd.Parameters.AddWithValue("@id", p.id);
         sqlCmd.CommandType = CommandType.Text;

         sqlCmd.ExecuteNonQuery();

     }
     catch (Exception e)
     {
         // Handle exception
     }
     finally
     {
         connection.Close();
     }
}

I have run an SQL Server deadlock trace, and it shows that the updateProduct statement is failing (ie. the victim process) and the surviving process is the one running the updateProductDimensions statement.

A simplified version of the deadlock trace is as follows (with most recent process first):

 - updateProduct2: fail
 - updateProduct2: success
 - updateProduct1: success
 - updateProductDimensions4: success
 - updateProductDimensions3: success
 - updateProductDimensions2: success
 - updateProductDimensions1: success

Each line represents one product per for loop iteration being updated.

And the resource/owner list for updateProduct2:

  - owner: updateProductDimensions1 (mode = U, isolationLevel = read committed (2))
  - waiter: updateProduct2 (mode= U, requestType = wait, isolationLevel = read committed (2))

My question is, why is there a deadlock happening? Even though the two statements update the same row, it is the same table. The server communicates with multiple clients, where the clients can update only their own products - ie. a single product can only be updated by one particular client. In this way multiple DB updates are happening at the same time, but for different rows (products).

How can this be solved without removing the duplicate updated columns?

Create statement for the products table:

    CREATE TABLE Products (
        [id]               VARCHAR (255)    NOT NULL,
        [width]            INT              NOT NULL,
        [length]           INT              NOT NULL,
        [height]           INT              NOT NULL,
        [weight]           INT              NOT NULL,
        // more fields
        [customer_id]      INT                  CONSTRAINT [F_KEY_CUSTOMER] DEFAULT ((0)) NOT NULL,
        CONSTRAINT [P_KEY_PRODUCT] PRIMARY KEY CLUSTERED ([id] ASC),
        CONSTRAINT [F_KEY_CUSTOMER] FOREIGN KEY ([customer_id]) REFERENCES [dbo].[Customer] ([id])
    );

Query plans

Update product dimensions statement:

Update product dimensions statement

Update product statement:

Update product statement

Deadlock trace

    <TextData>
    <deadlock-list>
    <deadlock victim="victimProcess">
    <process-list>
    <process id="victimProcess" taskpriority="0" logused="0" waitresource="PAGE: 15:1:1259" waittime="4594" ownerId="21610772296" transactionname="UPDATE" lasttranstarted="2018-02-21T08:46:44.777" XDES="0x859b9c580" lockMode="U" schedulerid="20" kpid="34240" status="suspended" spid="64" sbid="3" ecid="3" priority="0" trancount="0" lastbatchstarted="2018-02-21T08:46:44.777" lastbatchcompleted="2018-02-21T08:46:44.777" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="636" isolationlevel="read committed (2)" xactid="21610772296" currentdb="15" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
        <executionStack>
        <frame procname="adhoc" line="1" stmtstart="422" sqlhandle="0x02000000696bc4026d3a5eb5fc3835e32324ce9f3e4bdd28">
    UPDATE products SET width = @width, height = @height, length = @length, customer_id = @customer_id, weight = @weight WHERE id = @id     </frame>
        <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
    unknown     </frame>
        </executionStack>
        <inputbuf>
        </inputbuf>
    </process>
    <process id="survivorProcess4" taskpriority="0" logused="0" waitresource="PAGE: 15:1:2795" waittime="4593" ownerId="21610772296" transactionname="UPDATE" lasttranstarted="2018-02-21T08:46:44.777" XDES="0x45ebe3ca0" lockMode="U" schedulerid="18" kpid="254204" status="suspended" spid="64" sbid="3" ecid="6" priority="0" trancount="0" lastbatchstarted="2018-02-21T08:46:44.777" lastbatchcompleted="2018-02-21T08:46:44.777" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="636" isolationlevel="read committed (2)" xactid="21610772296" currentdb="15" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
        <executionStack>
        <frame procname="adhoc" line="1" stmtstart="422" sqlhandle="0x02000000696bc4026d3a5eb5fc3835e32324ce9f3e4bdd28">
    UPDATE products SET width = @width, height = @height, length = @length, customer_id = @customer_id, weight = @weight WHERE id = @id     </frame>
        <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
    unknown     </frame>
        </executionStack>
        <inputbuf>
        </inputbuf>
    </process>
    <process id="survivorProcess3" taskpriority="0" logused="224" waitresource="PAGE: 15:1:2795" waittime="4527" ownerId="21610772095" transactionname="UPDATE" lasttranstarted="2018-02-21T08:46:44.680" XDES="0x859b9c300" lockMode="U" schedulerid="20" kpid="16324" status="suspended" spid="123" sbid="2" ecid="1" priority="0" trancount="0" lastbatchstarted="2018-02-21T08:46:44.680" lastbatchcompleted="2018-02-21T08:46:44.673" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="636" isolationlevel="read committed (2)" xactid="21610772095" currentdb="15" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
        <executionStack>
        <frame procname="adhoc" line="1" stmtstart="102" sqlhandle="0x020000007e9c95155af7dd6044d8697705c48a1d5856dba4">
    UPDATE products SET width = @width, height = @height, length = @length WHERE id = @id     </frame>
        <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
    unknown     </frame>
        </executionStack>
        <inputbuf>
        </inputbuf>
    </process>
    <process id="survivorProcess2" taskpriority="0" logused="224" waitresource="PAGE: 15:1:1259" waittime="4529" ownerId="21610772095" transactionname="UPDATE" lasttranstarted="2018-02-21T08:46:44.680" XDES="0x270bf8b20" lockMode="U" schedulerid="13" kpid="406864" status="suspended" spid="123" sbid="2" ecid="4" priority="0" trancount="0" lastbatchstarted="2018-02-21T08:46:44.680" lastbatchcompleted="2018-02-21T08:46:44.673" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="636" isolationlevel="read committed (2)" xactid="21610772095" currentdb="15" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
        <executionStack>
        <frame procname="adhoc" line="1" stmtstart="102" sqlhandle="0x020000007e9c95155af7dd6044d8697705c48a1d5856dba4">
    UPDATE products SET width = @width, height = @height, length = @length WHERE id = @id     </frame>
        <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
    unknown     </frame>
        </executionStack>
        <inputbuf>
        </inputbuf>
    </process>
    <process id="survivorProcess1" taskpriority="0" logused="10000" waittime="4315" schedulerid="17" kpid="30464" status="suspended" spid="123" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-02-21T08:46:44.680" lastbatchcompleted="2018-02-21T08:46:44.673" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="636" loginname="" isolationlevel="read committed (2)" xactid="21610772095" currentdb="15" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
        <executionStack>
        <frame procname="adhoc" line="1" stmtstart="102" sqlhandle="0x020000007e9c95155af7dd6044d8697705c48a1d5856dba4">
    UPDATE products SET width = @width, height = @height, length = @length WHERE id = @id     </frame>
        <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
    unknown     </frame>
        </executionStack>
        <inputbuf>
    (@width int,@height int,@length int,@id nvarchar(255))UPDATE products SET width = @width, height = @height, length = @length WHERE id = @id    </inputbuf>
    </process>
    </process-list>
    <resource-list>
    <pagelock fileid="1" pageid="1259" dbid="15" objectname="MyDB.dbo.Product" id="lock15a855b00" mode="U" associatedObjectId="72057594038845440">
        <owner-list>
        <owner id="survivorProcess1" mode="U" />
        </owner-list>
        <waiter-list>
        <waiter id="victimProcess" mode="U" requestType="wait" />
        </waiter-list>
    </pagelock>
    <pagelock fileid="1" pageid="2795" dbid="15" objectname="MyDB.dbo.Product" id="lockbb9f0f80" mode="U" associatedObjectId="72057594038845440">
        <owner-list>
        <owner id="survivorProcess1" mode="U" />
        </owner-list>
        <waiter-list>
        <waiter id="survivorProcess4" mode="U" requestType="wait" />
        </waiter-list>
    </pagelock>
    <pagelock fileid="1" pageid="2795" dbid="15" objectname="MyDB.dbo.Product" id="lockbb9f0f80" mode="U" associatedObjectId="72057594038845440">
        <owner-list />
        <waiter-list>
        <waiter id="survivorProcess3" mode="U" requestType="wait" />
        </waiter-list>
    </pagelock>
    <pagelock fileid="1" pageid="1259" dbid="15" objectname="MyDB.dbo.Product" id="lock15a855b00" mode="U" associatedObjectId="72057594038845440">
        <owner-list />
        <waiter-list>
        <waiter id="survivorProcess2" mode="U" requestType="wait" />
        </waiter-list>
    </pagelock>
    <exchangeEvent id="Pipe49e4ca380" WaitType="e_waitPipeGetRow" nodeId="2">
        <owner-list>
        <owner id="survivorProcess3" />
        <owner id="survivorProcess2" />
        </owner-list>
        <waiter-list>
        <waiter id="survivorProcess1" />
        </waiter-list>
    </exchangeEvent>
    </resource-list>
    </deadlock>
    </deadlock-list>
    </TextData>

3条回答
We Are One
2楼-- · 2020-07-07 11:27

Depending on circumstances, MSSQL server locks whole pages (multiple rows) rather than single rows. This is why a deadlock can occour even if every client accesses only it's own rows. Also I have experienced fake-deadlocks that where really timeouts of a very busy server.

1) Tell SQL Server to use row locking (instead of page locking). This might be performance-expensive.

 UPDATE products WITH (ROWLOCK) SET ...

2) Make sure to use the primary key in your where condition.

Unrelated to your deadlock problem:

3) You have nested loops where you fire single statements to MSSQL. Reduce the number of queries by building up one or two big statements. This should boost your runtime performance

4) Dispose your SqlConnection and SqlCommand.

查看更多
0人赞 添加讨论(0) 举报
Viruses.
3楼-- · 2020-07-07 11:33

The question doesn't contain enough of the scenario for me to be able to replicate the example, so I'm going to speculate.

SqlCommand is disposable; but is not in a using block, and is not being disposed, so I would suspect that the previous command is still interfering with the database when the subsequent command takes place.

Put both SqlCommands into "using" blocks; and while you're at it, remove the "finally{connection.Close();}", and also put the SqlConnection into a "using" block as well (the Dispose will do the Close).

查看更多
0人赞 添加讨论(0) 举报
孤傲高冷的网名
4楼-- · 2020-07-07 11:43

As would have determined by now, dead-locks are 'complex beasts' !

Theoretically, since you are updating a single table, there is no 2 tables involved to explain a 'classic dead-lock' scenario. So, you would expect to NOT get a dead-lock, BUT you are getting it ! Welcome to the real world :-)

Based on your deadlock trace xml, you seem to be getting dead-locks due to "Page Lock" i.e., SQL server is locking a page and your process is dead-locking on a Page (i.e., not just your record).

If you look at the resource-list section of your deadlock trace, you can see that your victim process is waiting for a page which is locked by another process.

One simple technique you can try is to use the ROWLOCK hint for your update statement and see if that helps in your scenario.

Related SO post: https://dba.stackexchange.com/questions/121610/how-to-force-sql-server-to-use-row-locking-for-specific-update-delete-statements

UPDATE Table1 WITH (ROWLOCK)
SET FirstName = 'first'
WHERE ID = 1

In the above example WITH (ROWLOCK) is your hint to SQL server to try to use row level locks

Also, some good reading about SQL server dead-locks is in this Simple Talk link

查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(6)