Menu
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 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>
422
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).
106
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
