I have a schema-bound view (SSMS 2008 R2) running off of a set of tables maintained and updated by a front-end application. Earlier this week, after a deployment to update the application, the view suddenly deadlock-victims every time its run in Prod despite running successfully in Dev thru Staging.
Running a trace and grabbing the deadlock graph showed the competing DELETE statement came from the application (it doesn't UPDATE records; instead it DELETEs and INSERTs).
Edit1: deadlocks are being caused by competing application-side commands with IX-level locks. VIEW issues S-level locks, but the competing commands continue to deadlock, with the VIEW query consistently being the victim process. Setting isolation to 'read uncommitted' does not resolve the issue.
The VIEW recursively outer-joins on the same tables multiple times to create a linked history of records. I suspect this is the functionality which makes the VIEW too complex to evade the timing of locks. It seems to work half the days and then will consistently deadlock on others.
Is this simply a capacity issue, or is there a better way to build reporting structures that would remedy the deadlocking issues?
if you're getting a lot of deadlocking in the view it may be worthwhile breaking it down into a larger number of simpler views - where a schema bound view has an index drawn from multiple tables it can also be particularly prone to locking issues.