具有两个Update SQL语句的同一表上的死锁错误 [英] Deadlock error on same table with two Update SQL statements
问题描述
我有一个C＃项目，该项目将数据写入TSQL数据库。有两个更新语句在循环中运行，例如：
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);
}
}
updateProductDimensions（）和 updateProduct（）都触发SQL Update语句。在更新的列中有一些重叠：
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";
示例 updateProductDimensions（）方法- updateProduct（）也类似：
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();
}
}
我已经运行了SQL Server死锁跟踪，显示 updateProduct 语句失败（即受害进程），而尚存的进程是运行 updateProductDimensions 的进程
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
每行代表每<$ c $个产品c> for循环迭代正在更新。
Each line represents one product per for loop iteration being updated.
以及 updateProduct2 ：
- owner: updateProductDimensions1 (mode = U, isolationLevel = read committed (2))
- waiter: updateProduct2 (mode= U, requestType = wait, isolationLevel = read committed (2))
My问题是，为什么会发生僵局？即使两个语句更新同一行，它也是同一表。服务器与多个客户端通信，客户端只能在其中更新自己的产品。单个产品只能由一个特定的客户进行更新。这样，可以同时发生多个数据库更新，但是针对不同的行（产品）。
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?
为 products 表创建语句：
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])
);
查询计划
更新产品尺寸说明：
更新产品说明：
死锁跟踪
<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>
推荐答案
问题所涉及的场景不足我可以复制示例，所以我要推测。
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是可抛弃的。但是它不在using块中，也没有被处置，因此我怀疑在执行后续命令时，先前的命令仍会干扰数据库。
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.
将两个SqlCommands放入 using块中；并且在使用它时，请删除 finally {connection.Close（）;}，并将SqlConnection也放入 using块中（Dispose将执行Close）。
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).
这篇关于具有两个Update SQL语句的同一表上的死锁错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
