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

Post New Web Links


Posted By:      Posted Date: September 28, 2010    Points: 0   Category :ASP.Net

i'm new to sql, can someone teach me how to prevent deadlock? read from other forum knowing that even a simple select statement will cause deadlock, but i confuse on how can i prevent it?? anyone please teach me.. thanks 

View Complete Post

More Related Resource Links

.NET Matters: Deadlock monitor


This month Stephen Toub discusses deadlocks that can occur when synchronizing threads.

Stephen Toub

MSDN Magazine October 2007

Error in Replication: connection was chosen as the victim in a deadlock

I'm using a merge replication with SQL 2008 at server and SQL Express 2008 on subscribers. We are using around 100 subscribers. I'm getting the following error once two subscribers replicate at the same time. The final number of transaction uploaded to the server is always arong 100 and a similar number in the downloads, so the amount of transaction is not an issue. I'he been playing with the merge profile with no luck. The merge process could not replicate one or more INSERT statements to the 'Publisher'. A stored procedure failed to execute. Troubleshoot by using SQL Profiler. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200990) Get help: http://help/MSSQL_REPL-2147200990 A query executing on Publisher 'xxxxx' failed because the connection was chosen as the victim in a deadlock. Please rerun the merge process if you still see this error after internal retries by the merge process. (Source: MSSQLServer, Error number: 1205) Get help: http://help/1205 Any idea? This is getting to a critical point where transactions are not being uploaded to the server.  Additonally, there are a lot of blocks between the subscribers, and they are mainly associated with table MSmerge_partition_groups  

Deadlock during CUBE Synchronization

When OLAP database synchornizes with other OLAP database, we get error saying ""Transaction errors: Aborting transaction on session %". what might be reason for this error when CUBE/database syncs with other database. As per understanding, sync should not hold any locks> any help here will be greatly appreciated.SugeshKumar

Deadlock issue in SQL Server 2008 R2 (.Net 2.0 Application)

The Sql Server 2008 R2 instance in question is a heavy load OLTP production server. The deadlock issue came up a few days back and is still unresolved. We received the Xml deadlock report that listed the stored procedures involved in the deadlock and some other details. I'll try to list down the facts from this xml first: Two stored procedures are involved in the deadlock, say SP1 and SP2. According to the report SP1 was running in Isolation level "Serializable" and SP2 was running in "ReadCommitted" . We have investigated the following: Are we setting IsolationLevel of SP1 to "Serializable" inside SP or in Code? - No. Is any other SP whose IsolationLevel is "Serializable" calling SP1? - No. Are the table used by SP1 called by any other SP that has Isolation Level as "Serializable"? - Yes. There are SPs that have Isolation Level set to "Serializable" and access the same tables as SP1, but we don't know whether they were running at the time of deadlock or not as the deadlock report only showed SP1 and SP2. Lines of thought: We have considered the following possible causes: Deadlock is occurring because SP1 is running as "Serializable". - Why is this SP running in Serializable when I haven't set it? Is the Isolation level escalating (like locks do)? If we figure this out and make it run as ReadCom

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 
    DELETE FooTable WHERE  Param1 = @Param1    
    INSERT INTO FooTable   

Deadlock - i guess i have an idea why, but i'd like to make sure.



What happens if form A is waiting on a lock(object) while another thread, which has the lock for this object, performs an A.Invoke(...)?

I have a deadlock and not finding any other explanation i assume this may cause it.

Finding details of a page deadlock in SQL 2008

We have a java application which runs 5 threads simultaneously (firing few update & select queries against one table). This sometimes causes page deadlock (object id 0). Is there a way to find which queries are causing the deadlock?

SQL 2005 OpenXML causing deadlock

In our project we have used extensively OpenXML  statements in Stored Procedures inside SQL Transaction. Oflate, we started seeing lot of deadlocks happenning in our environment. On further investigation we found thatmoving openXML statments outside transaction block and populating the data in table variable and use that inside transaction helped us ot resolve issues. Is there anything connected with OpenXML and Deadlock. We tested with around 6-8 concurrent requestsfrom fronend app (ASP.NET)

Deadlock SQL Server 2008


Our application has a job which spawns multiple threads. Each thread does insert, select and delete records on the same table. When this is tested in DEVELOPMENT environment, there is a deadlock issue. On analysis it is found that this is due to page lock by DELETE operation.

But the strange thing is that this is not happening in UAT or PRODUCTION environment.

1. When the database performance itself (due to hardware configuration or network related issues) is slow, Can possibility of deadlocks increase?

Is there any other possible reasons that could cause this? Please share.


Need to debug IIS deadlock on Sharepoint server


We have a problem with a sporadic deadlock that occurs on our primary production Sharepoint 2007 farm. When it occurs we have to manually reset IIS as all sites stop responding. When it occurs it is usually around 5:00AM when the UK would be coming on line and working

1 x WFE, 1 Search, 1 SQL server
Sharepoint 2007 SP2 with Dec cumulative updates
2 primary web apps

I have installed debugdiag and created a crash rule to capture any faults in IIS process, but that is not capturing the data when the deadlock occurs.

When I setup a hang rule it fails on the verification of URL with a 401 Unauthorized error. I have turned off loopback checking on our test server and it fails with this same error. I am able to access the site via IE on the server. This hang rule is the only thing i see that would trigger a dump to be captured when the site did not respond.

Is there  a better way to approach this? The goal is to have something configured to trigger a dump when the deadlock event occurs



Why deadlock occurs using INSERT/UPDATE in a table with 2 sessions



I am working on a problem in my odbc application, which i have isolated to the MS SQL Server query analyzer.

The problem is related to the deadlock faced by one of the session, when 2 transactions each running from 2 sessions are trying to INSERT a record from their respective session with the duplicate values for the columns icol1 and cCol2 and then one of the session UPDATE the value of column icol1 in the previously inserted record. After the completion of the test, I see 1 record created in the table from one of the session, but other session becomes deadlock victim.

Following is the table structure:

CREATE TABLE [sgarg].[test_buf](

[icol1] [int] NULL DEFAULT ((0)),
[ccol2] [varchar](30) NULL,
[ccol3] [varchar](30) NULL,
[RECID] [bigint] NULL

Following are the index definitions

CREATE UNIQUE NONCLUSTERED INDEX [test_buf##ixprim] ON [sgarg].[test_buf]
 [icol1] ASC,
 [ccol2] ASC

CREATE NONCLUSTERED INDEX [test_buf#_#recid] ON [sgarg].[test_buf]

Deadlock error with services



I got the below error from application server.May know what is the cause?is this with SQL Server databse or application server.I am really worried about this error.

Here is the log :

2010-09-28 12:26:55,118 pool-2-thread-8 DEBUG [org.springframework.transaction.interceptor.TransactionInterceptor] - <Getting transaction for [com.maximus.sdc.process.ProcessManagerService.getStepInstanceForReference]>
2010-09-28 12:26:55,118 pool-2-thread-8 DEBUG [org.springframework.orm.ibatis.SqlMapClientTemplate] - <Opened SqlMapSession [com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl@1d982af8] for iBATIS operation>
2010-09-28 12:26:55,118 pool-2-thread-8 DEBUG [java.sql.Connection] - <{conn-108743} Connection>
2010-09-28 12:26:55,118 pool-2-thread-8 DEBUG [org.springframework.transaction.support.TransactionSynchronizationManager] - <Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@6b7547a3] for key [org.apache.commons.dbcp.BasicDataSource@394300c8] bound to thread [pool-2-thread-8]>
2010-09-28 12:26:55,118 pool-2-thread-7 DEBUG [java.sql.PreparedStatement] - <{pstm-108741} Executing Statement:    SELECT         PROCESS_INSTANCE.PROCESS_INSTANCE_ID AS "PI.PROCESS_INSTANCE_ID",      PROCESS_INSTAN

Sql server 2000 deadlock prevention.

we have an application developed in sql server 2000 and asp.net. From couple of weeks we are getting lots of deadlock in our system.The reason is our users have to complete some task on monday morning. its a huge stress on our server. we are looking for some prventions or avoidence for these kind of deadlocks. 

Sql server 2000 deadlock prevention.

we have an application developed in sql server 2000 and asp.net. From couple of weeks we are getting lots of deadlock in our system.The reason is our users have to complete some task on monday morning. its a huge stress on our server. we are looking for some prventions or avoidence for these kind of deadlocks. 

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?



context switch deadlock


I moved a windows forms app from an xp machine to win7/64

In vs I have the build set to x86

I get this error now.

contextswitch deadlock was detected

The CLR has been unable to transition from COM context 0x525b118 to COM context 0x525b288 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations.


This error occurs while trying to read/write to an access database.

OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\ServerName\AccessDBS\ERTS\erdata.mdb");

I don't create any threads.



How to set up Deadlock events xml to be saved separately?

There are couple useful options that SQL Server Profiler UI provides for Deadlock tracing.

They allow to chose to save Deadlock events xml separately from the trace file, even saving each deadlock in separate file is possible.

I'm wondering if there is any way to do such setup with sp_trace_xxx procedures or somehow else using TSQL?

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