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.
The query still takes row level locks. The scenario shown by Johnathon
applies when one attempts to insert and attempts to update the same row ( or group of rows ) and the timing for two conflicting operations is perfect up to some milliseconds. ie the update should occur exactly between query engine applies its locks at NC Index and before it obtains lock on clustered Index. This is very unlikely in reality especially if your indexes are correct. It takes a indefinite loop to simulate the example. So, I wont categorize it as a 'Simple/common' scenario.
Btw, at isolation level serializable, the same example it doesn't get into a deadlock as serialzable is lot more pessimistic when it comes to taking Range locks on indexes.
Regards,
Raj