I have been chasing this issue for a day now and am stumped, so thought I would put it out to you folks for some inspiration. I'm a bit of a novice when it comes to deadlocks and SQL Server lock modes, I rarely need to delve into this.
The short story:
When a user logs into our application, we want to update a SQL Server view based on the fact that they now have a "session", so that when they subsequently run a SQL Server Reporting Services report based on a report model, it includes security settings for their session.
The regular deadlock I've noticed is occuring between the process that DROPs and reCREATEs the view (which I call the AuthRuleCache), and a Microsoft SQL Server Reporting Services 2008 (SSRS) report that tries to select from the view.
The if I read the SQL Profiler deadlock event properly, the AuthRuleCache has a Sch-M lock, and the report has an IS lock.
The AuthRuleCache code is C# in a DotNet assembly, it's executed when users log into our Classic ASP app.
Obviously I want to avoid the deadlock because it's preventing logins - I don't mind how I achieve this as long as I don't need to compromise any other functionality. I've got full control over the AuthRuleCache and the database, but I would say that we're "light" on enterprise DBA expertise.
Here is an example deadlock event from SQL Profiler:
<deadlock-list>
<deadlock victim="process4785288">
<process-list>
<process id="process4785288" taskpriority="0" logused="0" waitresource="OBJECT: 7:617365564:0 " waittime="13040" ownerId="3133391" transactionname="SELECT" lasttranstarted="2013-01-07T15:16:24.680" XDES="0x8005bd10" lockMode="IS" schedulerid="8" kpid="20580" status="suspended" spid="83" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2013-01-07T15:15:55.780" lastbatchcompleted="2013-01-07T15:15:55.780" clientapp=".Net SqlClient Data Provider" hostname="MYMACHINE" hostpid=