Posted on 2015-01-13
I'm having problems with deadlocks and I don't think I should.
I fully understand how a deadlock is caused having a circle of resource requests, so no need to explain that. Here is my situation..
I have a few parent-child tables that are used for reporting. A user enters some criteria and a few hundred records are populated into the report tables based on the information entered, the user can then browse the report records created. All reporting records have a unique report ID for a reporting session. So each user is only touching those records for that session of reporting
Since every user is only touching records with his own unique report ID, there is no way that someone else's reporting form will access those same records. They will insert/update records in the same table, but not those records. So there should never be a dead lock.
So, why am I getting deadlocks and how do I prevent them?
The basic action of the reporting form is to (1) insert records using an insert-select statement (2) update those entered records and (3) delete those records if the user chooses to re-run the report
I was using SQL Server 2005 and now I have gone to 2012 and the problem is worse!