Browse by Tags

• Concurrency
• Defensive programming
• indexed view
• lock contention
• query performance
• SQL Deadlocks
• SQL Server
• Transact SQL

• Running OLTPish system without deadlocks, and loving it.

  Our OLTPish mixed load system has not had a single deadlock since last April, and we just love it. I would not make any blanket statements, but I think in our case being deadlock-free just makes a lot of practical sense. Of course, in other cases in might Read More... Posted Wednesday, January 04, 2012 5:31 PM by Alexander Kuznetsov | 9 Comments Filed under: Transact SQL, Deadlocks, Concurrency

• Reproducing a Conversion Deadlock

  Even if two processes compete on only one resource, they still can embrace in a deadlock. The following scripts reproduce such a scenario. In one tab, run this: CREATE TABLE dbo.Test ( i INT ) ; GO INSERT INTO dbo.Test ( i ) VALUES ( 1 ) ; GO SET TRANSACTION Read More... Posted Wednesday, June 02, 2010 9:39 AM by Alexander Kuznetsov | 1 Comments Filed under: Transact SQL, Deadlocks

• When acquiring locks in the same order is not possible or not feasible.

  To avoid deadlocks, one of the most common recommendations is "to acquire locks in the same order" or "access objects in the same order". Clearly this makes perfect sense, but is it always feasible? Is it always possible? I keep encountering cases when Read More... Posted Friday, January 15, 2010 4:18 PM by Alexander Kuznetsov | 6 Comments Filed under: Transact SQL, Defensive programming, Deadlocks

• Be ready to drop your indexed view.

  In all-too-many cases an indexed view may solve your short term performance goals but at some later time become counterproductive. So if you choose to use an indexed view, you may need an exit strategy. Let me describe a few common problems with indexed Read More... Posted Tuesday, June 02, 2009 5:10 PM by Alexander Kuznetsov | 10 Comments Filed under: Transact SQL, Deadlocks, query performance, lock contention, indexed view

• Reproducing one more intermittent deadlock on only one table

  I have already described several deadlock scenarios that involve only one table in another post. This time I will describe an intermittent deadlock which occurs in high concurrency environments. Before providing a complex repro script for it, I will provide Read More... Posted Thursday, March 26, 2009 11:46 AM by Alexander Kuznetsov | 2 Comments Filed under: SQL Server, SQL Deadlocks, Deadlocks