1
vote
0answers
45 views

Multi-victim deadlock in SQL Server

I have been seeing a lot of deadlocks occurring on a system with deadlock graphs similar to this one: I have been advised that it is a multi-victim deadlock but I cannot re-create a deadlock graph ...
1
vote
2answers
51 views

Changing two publications at the same time causes deadlock in distribution database

I have transactional replication setup. Let's say three databases on server A replicate to corresponding three databases on Server B. The databases are identical (same structure for different ...
1
vote
0answers
49 views

Side effects of using updlock lock hint?

I was just exploring UPDLOCK hint in SQL-Server 2008 and I found it is very useful in avoiding certain deadlock scenarios. There are always some side effects when using these types of lock hints but I ...
1
vote
1answer
76 views

Deadlock occurring on database, SQL Server 2008R2

Last few days I had major problems with my production site, turns out deadlock occurred, every half an hour maybe 50-60 of them. To do a quick fix in order to keep the site running I set : ...
6
votes
0answers
178 views

Is this a race condition or bug in sql server using serializable TIL with updlock? [closed]

I've been staring at this problem for almost a week now. I'm getting a deadlock in some query, and I've reduced the problem to these reproducible steps: Create a database with: create table offering ...
4
votes
1answer
53 views

How can a connection for one DB block a connection to another DB in SQL Server?

We have an interesting scenario. For weeks, we have been experiencing 'outages' on SQL Server 2008 -- where our websites have slowed to a crawl and mass timeouts for all customers who have DB's ...
5
votes
2answers
149 views

SQL Server 2000 - 'Performance: Deadlock'

We had to restart our SQL Server today, we had made no changes to it. When it came back up we immediately started getting this error from the server DATE/TIME: 2/27/2014 3:09:31 PM ...
2
votes
1answer
78 views

Avoiding Deadlocks

We currently have a trigger-based system in place that does the following: When records in certain tables are modified, a trigger fires that makes a field NULL in a varying number of records in ...
0
votes
1answer
71 views

SQL Server 2008R2 - Stored Procedure with 2 updates causing deadlocks

I have a web api web service that calls an authentication stored procedure. This stored procedure calls a different stored procedure that does 1 update: UPDATE ...
2
votes
1answer
85 views

How to use proper hints for Upsert involving a top 1 subquery

For this specific case (tracking Load Balancer switches) we wish to optimize an Upsert so that it does not exhibit any race conditions, cause any PK violation, or acquire any over sized locks. ...
2
votes
2answers
100 views

Reducing deadlocks with update query hint or other method

I'm getting 20-30 deadlocks per day on the same table. (2 mil rows) Profiler DeadlockGraphs show KEY LOCK and PAGE LOCK (See attched jpg) Total datatype size per row = 114 bytes We have multiple ...
0
votes
1answer
581 views

Why are these two INSERTs deadlocking? Is it the trigger? What does this deadlock trace 1222 log tell me?

We are seeing intermittent deadlocks in production when receiving multiple simultaneous API requests. Each request basically culminates in an INSERT statement into the same table, which is where we ...
3
votes
1answer
545 views

PAGELOCK Conversion Deadlock From Update to Intent Exclusive

A customer of ours is experiencing frequent deadlocks. The deadlocks are mostly on the same UPDATE statement. The deadlocks follow a pattern in which both SPIDs have acquired an update (U) lock on a ...
3
votes
1answer
583 views

Understanding SIX lock in Microsoft SQL-Server

Can somebody explain me how a process can acquire SIX lock on a page? I my deadlock-graph xml file I see that a process running under RC isolation level (executing a select statement at the moment of ...
5
votes
2answers
152 views

Given two known statements, how can I replicate deadlock?

I have two statements (an update against every row in one table (call it table A) and a delete on another table that looks up rows in table A) that I know are causing occasional deadlocks. It seems ...
2
votes
2answers
293 views

How to rollback the identity seed after deadlock

Now that's an approximate sequence of operations Im performing: SET IDENTITY_INSERT <table-name> ON; INSERT SOMETHING to <table-name> with explicitly specifyed id DECLARE @oldID bigint ...
1
vote
0answers
104 views

Concurrent Access causing deadlock on the same table [closed]

Transaction 1 : SELECT distinct column_id from grid where row_id = @P0 Transaction 2 : INSERT INTO grid (grid_id, row_id, column_id) In this table grid grid_id is primary key clustered. ...
4
votes
2answers
483 views

Lock escalation problem on a trigger

I've inherited a SQL Server 2005 database that is getting 2-3 deadlocks a day. I've tracked it down to a scheduled job that runs during the day and inserts into a table with a trigger. The trigger ...
1
vote
1answer
448 views

sp_getapplock doubt

We are using sp_getapplock as a transaction level in a procedure which in turn calls another procedure where again we use sp_getapplock with the same resource names. I wanted to know whether this ...
1
vote
0answers
164 views

Why Am I Getting Deadlock - Please Read Trace

can anyone help me? i'm getting deadlock on this trace, i'm using symmetricds and i can't understand why i'm getting a deadlock on a certain table. I'm using sql server 2008 r2, ...
3
votes
1answer
569 views

Deadlocks on same wait resource

I am experiencing a deadlock in SQL Server, but I do not understand why this is a deadlock. It looks like the two resources are actually the same. Meaning that they each have the lock and are asking ...
13
votes
5answers
2k views

Handling concurrent access to a key table without deadlocks in SQL Server

I have a table that is used by a legacy application as a substitute for IDENTITY fields in various other tables. Each row in the table stores the last used ID LastID for the field named in IDName. ...
1
vote
2answers
521 views

SQL Server deadlock too long [closed]

I have a really weird problem with a deadlock between a business process and a monitoring job. The both were involved on a deadlock that lasted around 5.5 hours. I don't know why SQL Server took so ...
6
votes
1answer
1k views

SQL Server Deadlock on two updates due to index lock order

I have two UPDATEs - one locks the CI first and then the NCI (on status) because the status column is also being updated. The other already owns a U lock on the NCI because it knows it is changing ...
3
votes
2answers
246 views

Can 2 tables lock each other if concurrent read/update do not affect same rows? pagelocks off

Can 2 tables cause a deadlock, if the pagelocks are off, and the rows are not related. E.g. Query 1 runs for a few seconds Select * from Orders where CustomerID = 1 and DateOrdered between @D1 and ...
6
votes
1answer
372 views

Deadlock graph with a lock on an index on a seemingly unrelated table

I have a deadlock graph from a deadlock where one process is doing a SELECT and one is doing an UPDATE. This seems like the classic case where the SELECT gets a NCI lock to perform a join and then a ...
-3
votes
2answers
3k views

SQL Server - How many type of timeout can happen, and how?

When working with SQL Server, there can be multiple application host accessing it, and each application can have one or many connection. Each connection can potentially have multiple transaction ...
2
votes
0answers
292 views

SQL Server 2008 R2 - Deadlock Trace, Need Help Understanding It

I really need help on understanding this deadlock, i've already replaced the values, mind if anyone helps me interpreting where my deadlocks are coming from? What does e_waitpipegetrow mean? btw, i'm ...
6
votes
2answers
268 views

SQL Server Select Count READ_COMMITTED_SNAPSHOT QUESTION

I seem to be getting a lot of deadlocks when doing select count(*) on a particular table. I have already changed all the required parameters and made them into row only locking. I've also changed the ...
11
votes
2answers
2k views

Deadlocks From Locks on Same Temporary Tables In Different Processes

I have found a deadlock that appears to show something I thought was impossible. There are two processes involved in the deadlock: 1. process8cf948 SPID 63 Performing an ALTER TABLE on temporary ...
4
votes
2answers
1k views

SELECT/INSERT Deadlock

This instance hosts the SharePoint 2007 databases (SP). We have been experiencing numerous SELECT/INSERT deadlocks against one heavily utilized table within SP content database. I have narrowed down ...
3
votes
1answer
921 views

How to force the use of row locks?

I am trying to get my table to use row locks. I have already disabled lock escalation. The isolation level is READ_COMMITTED_SNAPSHOT alter table <TABLE_NAME> SET (LOCK_ESCALATION=DISABLE) go ...
4
votes
2answers
1k views

Deadlock on insert for a table with an insert trigger that updates column in same triggered table

By specification each table uses a guid as the primary key. Another requirement is that each resource requires a 6 number identifier (100001, 100002, ...) as a public id that is unique within the ...
6
votes
1answer
445 views

Deadlock on Merge replication

I'm having a really confusing problem on the msmerge_contents table on some of my merge replication databases. It seems I'm receiving a dead lock on the exact same row on the same index on the table ...
2
votes
1answer
305 views

Intent Exclusive (IX) Lock questions [closed]

I was experiencing a deadlock so I set up a trace flags as well as profile trace to capture a deadlock. During this exercise I found something interesting. In the profile trace I noticed that "sa" ...
2
votes
1answer
216 views

Why my application is blocked infinitely and SQL Server never timeout and kill the deadlock

I have a multi-thread application that can opened more than one database connection to perform database operation. These connections are using the same credential. Here is a problem, the application ...
7
votes
3answers
3k views

SQL Server 2008 merge statement deadlocking itself

I have the following procedure (SQL Server 2008r2): create procedure usp_SaveCompanyUserData @companyId bigint, @userId bigint, @dataTable tt_CoUserdata readonly as begin set ...
2
votes
1answer
154 views

SSMS Object Explorer Details causes deadlock while job is running

I have a problem with one of my SQL Server jobs. I've found that while the job is running, SQL Server Management Studio locks up. It always locks up while I'm looking at tables and sprocs within the ...
8
votes
1answer
599 views

Why are there Victimless Entries on the Deadlock Graph?

I am trying to learn how to analyze SQL Server 2008's deadlock graph, and I'm finding alot of entries with an empty <victim-list> node. I don't understand what these entries represent: if there ...
3
votes
1answer
230 views

All-or-none exclusive lock on 2 SQL tables

My simplistic testing suggested that I can acquire an exclusive lock on 2 tables or postpone acquiring the lock on either until both can be acquired at once by using a query like this: SELECT ...
1
vote
1answer
1k views

Cannot fetch the rowset from OLE DB provider “SQLNCLI” for linked server X

I am getting this error while doing concurrent read from same table through linked server. Time out setting is alright. When this error happens, I also start getting the following error for the same ...
3
votes
3answers
494 views

Why am i getting deadlocks with 1 read and 1 write thread in SQL

if anyone could please help me understand why the following transactions deadlock? I am providing the 2 transactions and the indexes for each table: Transaction 1: SELECT blockId, blockData, ...
1
vote
2answers
827 views

Is problem with parallelism more common today than before? And what issues can arise from paralellism?

This is a very general question so I don't give much specific information. I am more interested in parallelism and its possible drawbacks. Generally I am all for parallelism as something useful and ...
4
votes
3answers
347 views

Does Snapshot Isolation only give you what has been commited? (And General Snapshot Isolation questions.)

So, I have some deadlocking issues. I have seen two options to resolve it. Add read uncommitted to my db or do Snapshot Isolation and add read committed snapshot. After doing some research into ...
1
vote
3answers
112 views

Keeping an eye on contention

In SQL Server, I am using the following command to see blocked sessions: select * from sys.sysprocesses where blocked>0 This is super. The problem is I need to see what block sessions there ...
6
votes
3answers
4k views

How to make sure that simultaneous INSERT and SELECT on one table won't make a deadlock?

I have the following table in my SQL Server 2008 database: CREATE TABLE [dbo].[SomeTable] ( [Id] [bigint] IDENTITY(1,1) NOT NULL, [Column1] [varchar](30) NOT NULL, [Column2] [varchar](30) ...
1
vote
2answers
2k views

How do I determine why my stored procedure is getting deadlocked?

We have a stored procedure that our application uses to query data (no write operations are performed). I have the complete staging database copied to my local version of Sql Server developer edition. ...
2
votes
2answers
2k views

Deadlock on Insert/Update

Using SQL Server 2008 R2: I'm currently attempting to track down the cause of a deadlock issue we have. Not sure where to turn. Here's the setup. TABLE Scores: id INT IDENTITY(1,1), first_name ...
3
votes
1answer
110 views

Deadlock between two databases

I have my code running online shared hosting provider. Recently I am seeing too many deadlock exception is being thrown. As you know that shared hosting uses the same database server to host all the ...
5
votes
1answer
484 views

Deadlocks in SQL Server

We're currently encountering a problem with out ASP.NET Application that results in deadlocks on the SQL Server database. A bit of background information I can provide is that we rely heavy on AJAX ...