I have a process with a Select which takes a long time to finish, on the order of 5 to 10 minutes.
I am currently not using NOLOCK
as a hint to the MS SQL database engine.
At the same time we have another process doing updates and inserts into the same database and same tables.
The first process has started, recently to end prematurely with a message
SQLEXCEPTION: Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim.
This first process is running at other sites in identical conditions but with smaller databases and thus the select statement in question takes a much shorter period of time (on the order of 30 seconds or so). In these other sites, I don't get the deadlock message in these other sites. I also did not get this message at the site that is having the problem initially, but, I assume, as the database has grown, I believe I must have crossed some threshold. Here are my questions: