Plagued by deadlocks!
SQL Server 2012
I have a group of 5 tables which are used to create complex reports for a user. The user is given his own report ID (the top table's primary key) and the child tables are filled between a few hundred and a few thousand records based on the user's search criteria and calculations. There are a dozen SQL statements run on the tables all within the main report ID, delete records, insert and then update the records to create the information needed. Everything works well until the tables start getting over 100,000 records or more. At that point, I start getting deadlocks. I should NEVER get a deadlock because user A's report updates never touch any records of User B. So each process is only affecting their own set of records so resources are never attempting to access other records in a circular fashion; never. The process tables about 7 seconds to run when the tables have fewer records and about 30 seconds over 200,000+ records. Then the deadlocks...
How can I see exactly which SQL statement is causing the deadlock (so I can tune that statement)?
How can I stop deadlocks?