Deadlocks during logon to ASP app caused by dropping/creating SQL Server views

Question

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="1176" loginname="MYMACHINE\MyUser" isolationlevel="read committed (2)" xactid="3133391" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="2" stmtstart="34" sqlhandle="0x02000000bd919913e43fd778cd5913aabd70d423cb30904a">
SELECT
    CAST(1 AS BIT) [c0_is_agg],
    1 [agg_row_count],
    COALESCE([dbo_actions2].[ActionOverdue30days], 0) [ActionOverdue30days],
    COALESCE([dbo_actions3].[ActionOverdueTotal], 0) [ActionOverdueTotal],
    COALESCE([dbo_actions4].[ActionOverdue90daysPLUS], 0) [ActionOverdue90daysPLUS],
    COALESCE([dbo_actions5].[ActionOverdue60days], 0) [ActionOverdue60days],
    COALESCE([dbo_actions6].[ActionOverdue90days], 0) [ActionOverdue90days],
    COALESCE([dbo_actions7].[ActionPlanned30days], 0) [ActionPlanned30days],
    COALESCE([dbo_actions8].[ActionPlanned60days], 0) [ActionPlanned60days],
    COALESCE([dbo_actions9].[ActionPlanned90days], 0) [ActionPlanned90days],
    COALESCE([dbo_actions10].[ActionPlanned90daysPLUS], 0) [ActionPlanned90daysPLUS],
    COALESCE([dbo_actions11].[ActionPlannedTotal], 0) [ActionPlannedTotal],
    CASE WHEN [dbo_actions12].[CountOfFilter] > 0 THEN 'Overdue0-30days' WHEN [dbo_actions13].[CountOfFilter] > 0 THEN 'Overdue90daysPlus' WHEN [dbo_actions5].[Count     </frame>
    </executionStack>
    <inputbuf>
  SET DATEFIRST 7
  SELECT
    CAST(1 AS BIT) [c0_is_agg],
    1 [agg_row_count],
    COALESCE([dbo_actions2].[ActionOverdue30days], 0) [ActionOverdue30days],
    COALESCE([dbo_actions3].[ActionOverdueTotal], 0) [ActionOverdueTotal],
    COALESCE([dbo_actions4].[ActionOverdue90daysPLUS], 0) [ActionOverdue90daysPLUS],
    COALESCE([dbo_actions5].[ActionOverdue60days], 0) [ActionOverdue60days],
    COALESCE([dbo_actions6].[ActionOverdue90days], 0) [ActionOverdue90days],
    COALESCE([dbo_actions7].[ActionPlanned30days], 0) [ActionPlanned30days],
    COALESCE([dbo_actions8].[ActionPlanned60days], 0) [ActionPlanned60days],
    COALESCE([dbo_actions9].[ActionPlanned90days], 0) [ActionPlanned90days],
    COALESCE([dbo_actions10].[ActionPlanned90daysPLUS], 0) [ActionPlanned90daysPLUS],
    COALESCE([dbo_actions11].[ActionPlannedTotal], 0) [ActionPlannedTotal],
    CASE WHEN [dbo_actions12].[CountOfFilter] > 0 THEN 'Overdue0-30days' WHEN [dbo_actions13].[CountOfFilter] > 0 THEN 'Overdue90daysPlus' WHEN [db    </inputbuf>
   </process>
   <process id="process476ae08" taskpriority="0" logused="16056" waitresource="OBJECT: 7:1854941980:0 " waittime="4539" ownerId="3132267" transactionname="user_transaction" lasttranstarted="2013-01-07T15:16:18.373" XDES="0x9a7f3970" lockMode="Sch-M" schedulerid="7" kpid="1940" status="suspended" spid="63" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-01-07T15:16:33.183" lastbatchcompleted="2013-01-07T15:16:33.183" clientapp=".Net SqlClient Data Provider" hostname="MYMACHINE" hostpid="14788" loginname="MYMACHINE\MyUser" isolationlevel="read committed (2)" xactid="3132267" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="3" stmtstart="202" stmtend="278" sqlhandle="0x02000000cf24d22c6cc84dbf398267db80eb194e79f91543">
  DROP VIEW [sec].[actions_authorized]     </frame>
    </executionStack>
    <inputbuf>

  IF EXISTS ( SELECT * FROM sys.VIEWS WHERE object_id = OBJECT_ID(N'[sec].[actions_authorized]'))
  DROP VIEW [sec].[actions_authorized]
      </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <objectlock lockPartition="0" objid="617365564" subresource="FULL" dbid="7" objectname="617365564" id="lock932d2f00" mode="Sch-M" associatedObjectId="617365564">
    <owner-list>
     <owner id="process476ae08" mode="Sch-M"/>
    </owner-list>
    <waiter-list>
     <waiter id="process4785288" mode="IS" requestType="wait"/>
    </waiter-list>
   </objectlock>
   <objectlock lockPartition="0" objid="1854941980" subresource="FULL" dbid="7" objectname="1854941980" id="locke6f0b580" mode="IS" associatedObjectId="1854941980">
    <owner-list>
     <owner id="process4785288" mode="IS"/>
    </owner-list>
    <waiter-list>
     <waiter id="process476ae08" mode="Sch-M" requestType="convert"/>
    </waiter-list>
   </objectlock>
  </resource-list>
 </deadlock>
</deadlock-list>

The LONG story:

I've decided to do this as a Q&A.

Q: Why do you have to make frequent schema changes just to enforce security on reports?

A: Well, I only arrived that this approach because our SSRS reporting mechanism is totally based on report models, and our application supports row-level security by applying rules. The rules themselves are defined in the database as little SQL fragments. These fragments are re-assembled at run-time and applied based on a) who the user is, b) what they are trying to do, and c) what they are trying to do it to. So, each user may have a unique view of the data based on the rules that apply to them. We have users authoring and saving their own reports, so I wanted this security enforced at the model to prevent them from stumbling upon data they should not have access to.

The challenge we faced with report models is that they are based on a data source view (DSV) that can only be made up of static sources, e.g. tables, named-queries, views. You cannot inject some C# code into the DSV to get it to dynamically respond to the particular user running the report. You do get the UserID at the model (SMDL) so you can use this for filtering. Our solution is to get the DSV to expose a view with ALL of the data for ALL of the currently logged in users' unique rulesets (namely, the AuthRuleCache), then the SMDL will filter this back to the unique ruleset of the requesting user. Hey-presto, you've got dynamic row-level, rule-based security in an SSRS report model!

The rules change infrequently, so it's OK for these to behave the same way for the duration of a user's session. Because we have tens of thousnds of users, but only a few hundred or so may log in during a 24 hour period, I decided to refresh the AuthRuleCache any time a user logs in and expire it after 24 hours so it contains only security info for users with current sessions.

Q: What form does the AuthRuleCache take?

A: It's a view UNIONing a buch of other views. Each user has their own view e.g. widgets_authorized_123 where widgets is the table containing data being secured, and 123 is the user id. Then, there's a master view (e.g. widgets_authorized) that UNIONs together all the user views

Q: That sounds hideously inefficient, are you a moron?

A: Possibly - however thanks to the awesomeness of the SQL Query Processor, it all seems to run nice and fast for live user reports. I experimented with using a cache table to actually hold record-ids for use with the application security and found this led to bloated-tables and delays refreshing and reading from the cache.

Q: Okay, you may still be a moron, but let's explore another option. Can you rebuild the AuthRuleCache asynchronously instead of having the user wait at logon?

A: Well, the first thing the user does after logon is hit a dashboard containing reports based on the model - so we need the security rules up and running immediately after logon.

Q: Have you explored different locking modes and isolation levels?

A: Sort of - I tried enabling altering the database read_committed_snapshot ON but that seemed to make no difference. In retrospect, I think the fact that I'm trying to do a DROP/CREATE VIEW and requiring a Sch-M lock means that Read Committed Snapshot Isolation (RCSI) wouldn't help because it's about handling concurrency of DML statements, and I'm doing DDL.

Q: Have you explored whole-database database snapshots or mirroring for reporting purposes?

A: I wouldn't rule this out, but I was hoping for more of an application-centric solution rather than making infrastructural changes. This would be a jump in resources utilization and maintenance overhead which I'd need to escalate to other people.

Q: Is there anything else we should know?

A: Yes, the AuthRuleCache refresh process is wrapped in a transaction because I wanted to make sire that nobody gets to see an incomplete/invalid cache, e.g. widget_authorized view referring to widget_authorized_123 when widget_authorized_123 has been dropped because the user's session has expired. I tested without the transaction, and the deadlocks stopped, but I started getting blocked process reports from SQL Profiler instead. I saw ~15 second delays at login, and sometimes timeouts - so put the transaction back in.

Q: How often is it happening?

A: The AuthRuleCache is switched off in the production environment at the moment so it's not affecting users. My local testing of 100 sequential logons shows that maybe 10% deadlock or fail. I suspect it is worse for users that have a long-running report model based report on their dashboard.

Q: How about report snapshots?

A: Maybe a possibility - not sure how well this works with parametized reports. My concern is that we do have some users who will be alarmed if they insert a record but don't see it on the dashboard until half an hour later. Also, I can't always guarantee everyone will use report snapshots correctly all the time, so don't want to leave the door open for deadlocks to sneak back in at a later date.

Q: Can I see the full T-SQL of the AuthRuleCache refresh transaction?

A: Here are the statements issued inside one transaction captured from SQL Profiler for one user logging on:

Look for expired sessions - we'd delete the associated view if found

SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA + '.' + TABLE_NAME LIKE 'sec.actions_authorized_%'
  AND RIGHT(TABLE_NAME, NULLIF(CHARINDEX('_', REVERSE(TABLE_NAME)), 0) - 1) NOT IN (
    SELECT DISTINCT CAST(empid AS NVARCHAR(20))
    FROM session
    )

Drop any pre-existing view for user 'myuser', id 298

IF EXISTS (
    SELECT *
    FROM sys.VIEWS
    WHERE object_id = OBJECT_ID(N'[sec].[actions_authorized_298]')
    )
  DROP VIEW [sec].[actions_authorized_298]

Create a view for user id 298

CREATE VIEW [sec].[actions_authorized_298]
AS
SELECT actid
  ,'myuser' AS username
FROM actions
WHERE actid IN (
    SELECT actid
    FROM actions
    WHERE (
        --A bunch of custom where statements generated from security rules in the system prior to this transaction starting
    )

Get a list of ALL user specific views for the actions entity

SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA + '.' + TABLE_NAME LIKE 'sec.actions_authorized_%'

Drop the existing master actions view

IF EXISTS (
    SELECT *
    FROM sys.VIEWS
    WHERE object_id = OBJECT_ID(N'[sec].[actions_authorized]')
    )
  DROP VIEW [sec].[actions_authorized]

Create a new master actions view and we're done

CREATE VIEW [sec].[actions_authorized]
AS
SELECT actid
  ,username
FROM sec.actions_authorized_182    
UNION
SELECT actid
  ,username
FROM sec.actions_authorized_298
UNION
-- Repeat for a bunch of other per-user custom views, generated from the prior select
-- ...

Answer 1

Thanks for all who offered suggestions. I've settled on a solution that I think will work for us. It may be a while before I get the final code together, but I've done some tests and it's looking positive - I wanted to close this question off with my planned approach.

Firstly, the deadlocks are a totally appropriate consequence of what I was trying to do from the outset. As I understand, recreating a view requires a schema modification lock - and any process in the middle of reading from that view requires a schema stability lock. Dependent on timing, these competing locks resulted in a deadlock in about 10% of logon attempts during busy periods.

When I changed the code to do a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE before running the view drop/recreate, the deadlocks went away because it is much more restrictive about what can happen concurrently, sacrificing response speed for stability.

Unfortunately, instead of deadlocking, I was seeing blocked process reports where processes were waiting upwards of 10 seconds to obtain the necessary locks. Still not really solving my problem.

I had a rethink about my "weird solution" of using a big UNIONed view to combine multiple views. Let me be clear that I didn't arrive at this approach by choice, I am simply trying to work around a limitation in SSRS Report Models whereby you can't implement parameters in the tables/named queries underlying the model.

I found in MS documentation that Partitioned Views can use a similar structure when merging together rows from multiple tables into a single view, example here: http://msdn.microsoft.com/en-us/library/ms190019(v=sql.105).aspx

So I'm not alone in using views in this way. I need this UNIONed view, but dropping and recreating views is going to be a performance problem. So, I did some testing using Service Broker and found I could queue up the view drop/recreate operation, allowing users to log in rapidly without waiting around for the for the DDL to complete. I'm going to follow @usr's suggestions and get the transaction as lean as possible, moving stuff not critical to completing a logon (such as expiring old sessions) out of the transaction.