.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Why single SQL delete statement will cause deadlock?

Posted By:      Posted Date: September 20, 2010    Points: 0   Category :Sql Server

Hello everyone,

I am using SQL Server 2008 Enterprise. I am wondering why even a single delete statement of this stored procedure will cause deadlock if executed by multiple threads at the same time? Any tools or sample codes which could be used to reproduce this scenario?

For the delete statement, Param1 is a column of table FooTable, Param1 is a foreign key of another table (refers to another primary key clustered index column of the other table). There is no index on Param1 itself for table FooTable. FooTable has another column which is used as clustered primary key, but not Param1 column.

    create PROCEDURE [dbo].[FooProc]   
     @Param1 int
     ,@Param2 int 
     ,@Param3 int 
    DELETE FooTable WHERE  Param1 = @Param1    
    INSERT INTO FooTable   

View Complete Post

More Related Resource Links

The DELETE statement conflicted with the REFERENCE constraint


I have created ASP.Net Dynamic data web site using scaffolding in Visual Studio 2010. When I click on delete in data grid for customer table I got below error message.

The DELETE statement conflicted with the REFERENCE constraint "FK_Order_Details_Products". The conflict occured in database "../NORTHWIND.MDF", table "dbo.order details", column 'ProductId'

The statement has been terminated


Is this a bug in MERGE statement with DELETE/INSERT?

This looks to me like a bug with MERGE statement. This does not work on SQL 2008 and 2008 R2. Note that the first commneted statement works properly but the second does not. Is there any explanation why not, other than a bug? Duplication script below. use tempdb go /* IF object_id('dbo.Test', 'U') IS NOT NULL DROP TABLE dbo.Test go IF object_id('dbo.Src', 'U') IS NOT NULL DROP TABLE dbo.Src go */ IF object_id('dbo.Test', 'U') IS NULL BEGIN     CREATE TABLE dbo.Test     (         intID int NOT NULL IDENTITY PRIMARY KEY         ,sysID int NOT NULL         ,ioID int NOT NULL         ,Code nvarchar(10) NOT NULL     )     INSERT dbo.test (sysID, ioID, Code) VALUES (1, 1, 'A')       CREATE UNIQUE NONCLUSTERED INDEX [UIXF_Test] ON [dbo].[Test]     (           [sysID] ASC,           [ioID] ASC     )     WHERE ([ioID] IN ((1)))     WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

It is possible to alter multiple columns within a single alter table statement?


It is possible to alter multiple columns within a single alter table statement?

I tried & searched not getting it.

Alter table au_de alter column m_user char(9),c_user char(9)

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ','.


Alter table au_de alter column m_user char(9),alter column c_user char(9)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

The DELETE statement conflicted with the REFERENCE constraint "FK_tblAdvanceLink_tblLink


I"m using table adapters here. I tried to use the following in my table adapter:

ALTER TABLE tblAdvanceLInk

DROP FOREIGN KEY FK_tblAdvanceLink_tblLink

DELETE FROM [tblAdvanceLink] WHERE WebsiteID=@WebsiteID 


The DELETE statement conflicted with the REFERENCE constraint "FK_tblAdvanceLink_tblLink". The conflict occurred in database "LinkExchanger", table "dbo.tblAdvanceLink", column 'LinkID'.
The statement has been terminated

I get the same problem every time. So I read you could do this in a sql query. Why doesn't this work in my tableadapter? Do I have this in the right place?  Do I also put this into the other table? (tblLInk)

Isn't there a way to just relax ALL constraints so I can delete some rows? I'm deleting rows from both tables which have the corresponding websiteID

Deadlock: Store Procedure that only contains select statement owns an update key lock


I have a seemingly simple deadlock graph that contains 2 SP's. One SP is updating a table, while another SP (the victim) is selecting from it. The interesting thing about the graph is that the SP that contains the select (and only a select) is shown to own an UPDATE lock on the table that SP2 wants to update. How is this possible?



Insert multiple rows with a single INSERT statement


With SQL Server 2005 Express coming out, I have switched from MySql.  I'm having trouble with a very simple INSERT statement that has previously worked in both MySql and Oracle.  The statement is as follows:

INSERT INTO pantscolor_t (procode,color,pic) VALUES
 ('74251', 'Black', '511black.jpg'),
 ('74251', 'Charcoal', '511charcoal.jpg'),
 ('74251', 'Khaki', '511khaki.jpg'),
 ('74251', 'Navy', '511navy.jpg'),
 ('74251', 'OD Green', '511odgreen.jpg');

However, when I attempt to execute this statement with Management Studio Express I get the following error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.

Any ideas?

The DELETE statement conflicted with the REFERENCE constraint. The conflict occurred in database.



I am getting the following error 

The DELETE statement conflicted with the REFERENCE constraint "FK_Person_Country_CountryID_CountryID".
The statement has been terminated.

I know what the problem is, but can anyone help me in catching this exception in the best possible way?

I do not want the user to delete the country if there are any FK contrains. Or a better way will be to ask the user if they want to delete the records referencing this "Country".

int countryID = Convert.ToInt32(gvCountry.DataKeys[e.RowIndex].Value);
        db.DeleteCountry(1, 1, countryID);
        gvCountry.EditIndex = -1;

**i am using linq to sql

Thank you.


Delete statement running slow


Dear Friends

I've got a delete statement which is taking 40 secs to run. There's only a row to delete but it's taking 40 secs . The table has got foreign keys which references other tables. I have checked indexes exist on these foreign key columns.

I have already updated statistics with fullscan on the this table.


Any ideas

Many thanks.


Delete Statement with nested select in where clause deletes all entries in a table


I have fallen into a trap unadvertedly causing all records in a table being deleted. Can anybody tell me, if this a the intended behaviour of SQL Server 2008 R2 or does anybody know a method how to safely discover those kinds of typos in advance?

Regards, Markus

Here follows a script reproducing the behaviour:









Delete Statement Code for C# ASP.NET SQL


I'm having problems with creating detele functionality to my asp.net page.  I have found lots of bits of code indicating parts of how to do it, but not in its entirety.  Could someone display it in a more complete version so I could have something to compare and figure out what i am doing wrong?


Delete or Truncate statement for data purging



I am writting a stored procedure to purge data from table every month. There is no constraint on table column except primary key.

I am using 'delete' statement as it records an entry in the transaction log for each deleted row. But it's a slow process compared to 'Truncate' statement.

Can you please suggest what should I choose (Delete or truncate) as per best practice.








Single insert/delete takes more than 5 sec on merge replicated table.


Dear experts,

Environment: MSSQL standard edition – 2008 / Windows server 2008 (64 bit)

I have lots of tables with merge publication (filtered) on windows 2008 server and it takes few seconds to insert/ delete single row on that database.


This is what execution plan shows.

Delete single row: 7 sec. It shows 47 queries.

Insert single row (2 columns): 4 sec. 35 queries.


What I can see it MSmerge_past_partition_mappings and MSmerge_current_partition_mappings table take 15%-30% of cost.


Please help to to resole this issue. I can send execution plan if anyone interested. 

Record Count on MERGE Statement on Insert,Update,Delete


HI All,

How can I get the numbers of records affected in the Merge statement, INSERT,UPDATE,DELETE  separately and store it in a variable so I can get it in the application side? 



khrizz tell

Can I prevent deadlock during concurrent delete


Hi All,

  For some irrelevant reasons I need to delete a bunch of records by column value without saving these columns to a database. The list is huge so I run several delete operators concurrently. Recently I experienced a deadlock and I wonder what are the possible reasons and a reliable way to prevent it? If I drop clustered index first (I drop it anyway to speed Bulk Insert up) will it help?

   I have only seen this once so there're no reliable reproduction conditions to capture additional info.

   So to summarize, I'm using SQL Server 2005. I have a table with clustered index and I concurrently issue a bunch of DELETE operators in form

DELETE from MyTable

where MyColumn in (Val1, Val2, ... ValN)

and the exception was

System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObjec

Display data from two lists in a single Data View - how to edit/delete/view items?


I have an aggregate dataview from two lists. (something like this http://office.microsoft.com/en-us/sharepoint-designer-help/display-data-from-multiple-sources-in-a-single-data-view-HA010099144.aspx#BM3)

The problem is, that when I click on "Data view properties", the options in Editing tab (edit link, delete link and view link) are greyed out.

 I'd like to create custom delete/edit/view link for rows in that dataview with linked source. Any idea?

Multiple calculations in a single Statement in SQL Ce


Hi guys

I have a table that has a date, amount of litres, price per litre. The table is updated every month, what I have been trying to do is display the total revenue for each year ( amount of litres * price per litre this will give me the revenue for each month and then add each month together to return that years revenue) I can't seem to get this statement right, any tips?



how to merge two columns value into single column in sql select statement?


i have a query like this

SELECT     tblClientDocument_Base.Document_name, tblJobDocument_Base.Document_Name AS Document_name
FROM         tblClientDocument_Base JOIN
                      tblJobDocument_Base ON tblClientDocument_Base.Tenant_Id = tblJobDocument_Base.Tenant_Id

it returns

Document_name        Document_name

a                                   d

b                                   e

c                                    f

now i want to merge like this









plz help?

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend