New answers tagged

0

What's a "double friend"? (OK, I made that up.) Your schema allows a user to have the same friend twice. Let's get rid of that, and hope that it helps your real problem. That table should be (ignoring the FKs -- which may be contributing to the problem): CREATE TABLE `friend` ( -- leave this out: `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` ...


1

there are lots of different ways to monitor for deadlocks in SQL Server. You can use Extended Events, Performance Monitor (to measure rate of deadlocks), Profiler (as a legacy alternative to XE) or even deadlocks to the SQL Error Log through enabling Trace Flag 1204 (details of nodes involved in the deadlock) and Trace Flag 1222 (deadlock information in ...


2

As Aaron has already said, this is due to a bug in SQL Server 2008 / 2008 R2. The bug has been marked as fixed in a later version. It will not be fixed in SQL 2008 / 2008 R2. I have a PowerShell script that I wrote to pull deadlocks from the event buffer, add the missing information if possible, and write the deadlock graphs out as .xdl files. The .xdl ...


1

Drastically reducing the number of rows will reduce the likelihood of getting the deadlock, but it won't go away completely. In simple terms the select is first using the index to determine the rows to select, then fetching the rows, while the insert is inserting a row, then trying to update the (XLOCKED) index. Application developers tend to use XLOCK ...


12

As far I understand this, I am looking at a KEYLOCK deadlock basically caused by an uncovered index query that uses a nonclustered and a clustered index in order to collect the required values, right? Essentially, yes. The read operation (select) accesses the nonclustered index first, then the clustered index (lookup). The write operation (insert) ...



Top 50 recent answers are included