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


Top 5 Contributors of the Month
satyapriyanayak
SP
Sambanthamoorthy
Ashutosh Jha
Imran Ghani
Post New Web Links

Can I prevent deadlock during concurrent delete

Posted By:      Posted Date: November 29, 2010    Points: 0   Category :Sql Server
 

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


View Complete Post


More Related Resource Links

Prevent Concurrent Logins ? [ Already logged in or currest session is still active implementation] ?

  

Hi Experts ,

I searched over the web but no luck so far and thought of getting some help form gurus .

Did any out really implemented a way to handle concurrent logins ?

Please not that i'm no using either forms authentication or Roles/Memberships , I do have custom authentication for my website .

I'm sure it is someway related  with global.asax file handling something in application events and session events but unable to figure this one out .

Any pointers will highly be valued and appreciated .

Thanks a tonne in advance .


Why single SQL delete statement will cause deadlock?

  

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 
    )   
    AS   
       
    DELETE FooTable WHERE  Param1 = @Param1    
        
    INSERT INTO FooTable   
     ( 
     Param1 
     ,Param2 
     ,Param3 

User-friendly way to prevent concurrent form editing (browser-enabled IPath forms)

  
Hi,

I have a single InfoPath form with a lot of information that multiple users will need to access to read and some of them will also need to update. I'm struggling to find a user-friendly way to avoid concurrent editing of the form. I find that the "require check-out" option is very badly integrated with Form Services.

Indeed, if the user forgets to check out, he can open the form and make all his changes and it's only when he clicks on "save" that the form tells him that he needs to check-out before editing (in my case, the user shouldn't save as another file). On the other hand, if a user checks out the form and save his changes, no prompt suggests him to check in his form so there is a big risk that the user may prevent for a long time the others to update the form.

It's strange that InfoPath 2007 doesn't have the ease of use that Word 2007 has with checking out, namely the prompt to check out a document once clicked and the prompt to check in the document once closed.

Any idea for this problem?

Adding Dynamic Rows in ASP.NET GridView Control with TextBoxes and with Delete functionality

  
In my previous examples, I have demonstrated on how to add dynamic rows in GridView control with TextBoxes and how to save the values into the database. Now, seems that most of the developers are asking if how to add a delete functionality with it. So in this example, I'm going to show on how to delete a certain row in the dynamic GridView with TextBoxes.

SqlCommand.ExecuteNonQuery() returns -1 when doing Insert / Update / Delete

  
Sometimes you end up with a return value of -1 when using the SqlClient.SqlCommand.ExecuteNonQuery method.

Why is that?



Well, the ExecuteNonQuery method is there for statements for changing data, ie. DELETE / UPDATE /INSERT, and the returned value are the number of rows affected by that statement.

When checking the documentation we can see that there are some conditions that return -1.



For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.

When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of

rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

update/delete not working on server only

  

my aspx page works in VWD, and everything works on the server EXCEPT update and delete sql functions. any ideas?

thanks 


How to use TOP in DELETE

  

Hi,

I have a large database in MSSQL 2005 (rows around 3,788,299 : size 4GB)

Two questions

1. SQL query ~ select top 100 * from [tablename], how do capture the next 100 record?

2. SQL query ~ delete top 100 from [tablename] --- MSSQL show me syntax error? How can I only delete the top 100 record?

 

thk.


delete record from excel sheet

  



Hello,

           I have write the code for deleting the data in excel sheet. but we have got the error" Deleting data in a linked table is not supported by this ISAM."

     below I paste some code snippest.

string strDelete = "Delete  from [" + strSheetName + "$]";

            cmdExcel.Connection = con;
            cmdExcel.CommandType = CommandType.Text;
            cmdExcel.CommandText = strDelete;

            cmdExcel.ExecuteNonQuery();


Please can anybody this answer.It is urgent


delete data in excel sheet

  

Hello,

           I have write the code for deleting the data in excel sheet. but we have got the error" Deleting data in a linked table is not supported by this ISAM."

     below I paste some code snippest.

string strDelete = "Delete  from [" + strSheetName + "$]";

            cmdExcel.Connection = con;
            cmdExcel.CommandType = CommandType.Text;
            cmdExcel.CommandText = strDelete;

            cmdExcel.ExecuteNonQuery();


Please can anybody this answer.It is urgent




Concurrent Affairs: Data-Parallel Patterns and PLINQ

  

Multicore processors are now ubiquitous on mainstream desktop computers, but applications that use their full potential are still difficult to write. Version 4 of the .NET Framework will deliver several tools that programmers can employ to make this task easier. This article explores the use of one of those tools: Parallel LINQ (PLINQ).

Igor Ostrovsky

MSDN Magazine December 2009


Concurrent Affairs: Four Ways to Use the Concurrency Runtime in Your C++ Projects

  

This article describes how to integrate the new parallel computing libraries in the Visual Studio 2010 Beta into existing C++ projects. Based on four common scenarios, the column explains a few of the ways developers can use the APIs and classes that are part of the Parallel Pattern Library (PPL), Asynchronous Agents Library, and Concurrency Runtime.

Rick Molloy

MSDN Magazine October 2009


Concurrent Affairs: Solving The Dining Philosophers Problem With Asynchronous Agents

  

We use the new Asynchronous Agents Library in Visual C++ 2010 to solve the classic Dining Philosophers concurrency problem.

Rick Molloy

MSDN Magazine June 2009


Easy Async: Build Concurrent Apps From Simple F# Expressions

  

In this article, the author explores how the F# language helps you create asynchronous function libraries that can be called seamlessly from any other .NET-compliant language.

Chance Coble

MSDN Magazine October 2008


Concurrent Affairs: More AsyncEnumerator Features

  

Jeffrey Richter shows you some additional cool features of his AsyncEnumerator class.

Jeffrey Richter

MSDN Magazine August 2008


Categories: 
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