This is my first time reading the article and although you do state,

The example you give is very simple and I think a very common scenario. Unfortunately I can't agree with the solution. No I'm not talking about the application handling the deadlock exception and retrying. That's been argued enough already and I actually agree that the application should handled the exception (what it wants to do with it is another story).

I'm questioning the covered index solution. I know with most things computers, the answer is "it depends". So I'd be curious to get some feedback on the following scenario.

We don't have a very busy database in my opinion, but we do experience a lot of deadlocks. Unfortunately the client application uses NHibernate, which I feel is a major part of the problem. But all that aside, let's say we have the following table and requirements:

CREATE TABLE Trip(

id int IDENTITY(1,1) NOT NULL,

userID int NOT NULL,

name varchar(200) NOT NULL CONSTRAINT DF_Trip_name DEFAULT(''),

descriptionText varchar(max) NOT NULL CONSTRAINT DF_Trip_summary DEFAULT(''),

ulLat float NULL,

ulLon float NULL,

brLat float NULL,

brLon float NULL,

created datetime NOT NULL CONSTRAINT DF_Trip_created DEFAULT(getdate()),

lastUpdated datetime NOT NULL,

startDate datetime NOT NULL,

viewCount int NOT NULL CONSTRAINT DF_Trip_viewCount DEFAULT((0)),

sourceDevice varchar(50) NOT NULL CONSTRAINT DF_Trip_sourceDevice DEFAULT(''),

sourceCarrier varchar(50) NOT NULL CONSTRAINT DF_Trip_sourceCarrier DEFAULT (''),

sourceManufacturer varchar(50) NOT NULL CONSTRAINT DF_Trip_sourceManufacturer DEFAULT(''),

poiCount int NOT NULL CONSTRAINT DF_Trip_poiCount DEFAULT((0)),

mediaCount int NOT NULL CONSTRAINT DF_Trip_mediaCount DEFAULT((0)),

startLocationLatitude float NULL,

startLocationLongitude float NULL,

startLocationQuadKey varchar(50) NOT NULL,

CONSTRAINT PK_Trip PRIMARY KEY CLUSTERED

(

id ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY

) ON PRIMARY

GO

CREATE NONCLUSTERED INDEX IX_Trip_startDate_userID ON Trip

(

startDate ASC,

userID ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY

Many inserts happen on this table and although the 'descriptionText' field is defined as varchar(max), the application limits it to 64KB.

Now, let's say that the only SELECT statement that goes against this table is to return a particular user's most recent trips.

SELECT * FROM Trip WHERE startDate > getdate() - 1 AND userID = @userid

The execution plan for that query is, I think, the best it can be. It properly does an Index Seek on the IX_Trip_startDate_userID index to satisfy the WHERE clause, and then does a Key Lookup using the clustered primary key index PK_Trip to get all the data.

So given your example, all that has to happen for a deadlock is the SELECT starts and grabs a lock on IX_Trip_startDate_userID, but before it can get a lock for the key lookup, an INSERT starts and grabs the lock on PK_Trip.

It seems to me that the database should be smart enough to analyze the SELECT query and recognize that it cannot acquire all the necessary data using the IX_Trip_startDate_userID index alone and should know and therefore acquire a lock on PK_Trip at the same time.

I think the solution of creating a covering index in this scenario isn't a very good one since you'd be duplicating all that data, particularly the 'descriptionText' field.

Does anyone have any suggestions or comments? Either on my example scenario or why the database isn't smart enough to acquire the locks that it needs all at the same time. I understand that in order to have a highly performant, highly concurrent system, you need to minimize the use of locks, but your article just surprised me on how seemingly simple it is to generate deadlock in SQL Server by doing something so common and what the database is supposed to be designed to handle, that is, concurrent INSERTs and SELECTs.