Ошибки при входе в приложение ASP, вызванные сбросом/созданием представлений SQL Server

Я преследовал эту проблему уже целый день, и я был в тупике, поэтому подумал, что я расскажу вам о людях для какого-то вдохновения. Я немного новичок, когда дело доходит до тупиков и режимов блокировки SQL Server, мне редко приходится вникать в это.

Рассказ:

Когда пользователь регистрируется в нашем приложении, мы хотим обновить представление SQL Server на основании того факта, что теперь у них есть "сеанс", поэтому, когда они впоследствии запускают отчет служб отчетов SQL Server на основе модели отчета, он включает параметры безопасности для своего сеанса.

Регулярный тупик, который я заметил, происходит между процессом, который DROPs и reCREATEs вид (который я вызываю AuthRuleCache), и отчет Microsoft SQL Server Reporting Services 2008 (SSRS), который пытается выбрать из представления.

Если я правильно прочитал событие тупика SQL Profiler, в AuthRuleCache есть блокировка Sch-M, и отчет имеет блокировку IS.

Код AuthRuleCache - это С# в сборке DotNet, он выполняется, когда пользователи входят в наше приложение Classic ASP.

Очевидно, я хочу избежать тупика, потому что он предотвращает вход в систему - я не возражаю, как я это достигаю, пока мне не нужно компрометировать какие-либо другие функции. Я получил полный контроль над AuthRuleCache и базой данных, но я бы сказал, что мы "легки" на экспертизе бизнес-приложений.

Вот пример события блокировки 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>

ДЛИННАЯ история:

Я решил сделать это как Q & A.

В: Почему вам приходится часто менять схемы, чтобы обеспечить безопасность отчетов?

A: Я пришел к этому только потому, что наш механизм отчетности SSRS полностью основан на моделях отчетов, и наше приложение поддерживает безопасность на уровне строк, применяя правила. Сами правила определены в базе данных как небольшие фрагменты SQL. Эти фрагменты повторно собираются во время выполнения и применяются на основе a), кто пользователь, b) что они пытаются сделать, и c) что они пытаются сделать. Таким образом, каждый пользователь может иметь уникальный вид данных на основе правил, которые применяются к ним. У нас есть пользователи, которые создают и сохраняют свои собственные отчеты, поэтому я хотел, чтобы эта система безопасности использовалась в модели, чтобы они не наткнулись на данные, к которым у них не должно быть доступа.

Задача, с которой мы столкнулись с моделями отчетов, состоит в том, что они основаны на представлении источника данных (DSV), который может состоять только из статических источников, например. таблицы, named-queries, views. Вы не можете вводить некоторый код С# в DSV, чтобы заставить его динамически реагировать на конкретного пользователя, выполняющего отчет. Вы получаете UserID на модели (SMDL), чтобы использовать ее для фильтрации. Наше решение состоит в том, чтобы заставить DSV отображать представление со всеми данными для ВСЕХ уникальных наборов правил пользователей (а именно AuthRuleCache), а затем SMDL будет отфильтровывать это обратно до уникального набора правил запрашивающего пользователя. Hey-presto, у вас есть динамическая система на уровне строк, основанная на правилах в модели отчетов SSRS!

Правила изменяются нечасто, так что это нормально, если они будут вести себя одинаково в течение всего сеанса пользователя. Поскольку у нас есть десятки тысяч пользователей, но только несколько сот или около того могут регистрироваться в течение 24-часового периода, я решил обновить AuthRuleCache в любое время, когда пользователь войдет в систему и истечет через 24 часа, поэтому он содержит только информацию о безопасности для пользователей с текущими сеансами.

Q: Какую форму принимает AuthRuleCache?

A: Это представление, объединяющее несколько других представлений. Каждый пользователь имеет свой собственный вид, например. widgets_authorized_123, где widgets - это таблица, содержащая защищенные данные, а 123 - идентификатор пользователя. Затем открывается главный вид (например, widgets_authorized), в котором UNION объединяют все пользовательские представления

В: Это звучит ужасно неэффективно, вы идиот?

A: Возможно, однако, благодаря удивительности SQL Query Processor, все это, похоже, работает хорошо и быстро для отчетов в реальном времени. Я экспериментировал с использованием таблицы кэша для фактического хранения идентификаторов записей для использования с безопасностью приложений и обнаружил, что это привело к раздутым таблицам и задержкам обновления и чтения из кеша.

Q: Хорошо, вы все равно можете быть идиоткой, но пусть изучит другой вариант. Можете ли вы перестроить AuthRuleCache асинхронно, а не ждать пользователя при входе в систему?

A: Ну, первое, что пользователь делает после входа в систему, попадает на панель мониторинга, содержащую отчеты на основе модели, поэтому нам необходимо, чтобы правила безопасности работали сразу после входа в систему.

Q: Вы изучили различные режимы блокировки и уровни изоляции?

A: Сортировка - я попытался включить изменение базы данных read_committed_snapshot ON, но это, казалось, не имело значения. В ретроспективе я думаю, что тот факт, что я пытаюсь сделать DROP/CREATE VIEW и требующий блокировки Sch-M, означает, что Read Committed Snapshot Isolation (RCSI) не помогло бы, потому что это касалось обработки concurrency операторов DML, и я делаю DDL.

В: Вы изучили моментальные снимки базы данных базы данных или зеркалирование для целей отчетности?

A: Я бы не стал это делать, но я надеялся, что вы скорее найдете решение, ориентированное на приложения, а не сделайте инфраструктурные изменения. Это будет переход на использование ресурсов и накладные расходы на техническое обслуживание, которые мне нужно будет эскалации для других людей.

Q: Что-нибудь еще мы должны знать?

A: Да, процесс обновления AuthRuleCache завернут в транзакцию, потому что я хотел сделать сир, чтобы никто не смог увидеть неполный/недействительный кеш, например. widget_authorized view, ссылаясь на widget_authorized_123, когда widget_authorized_123 был удален, поскольку пользовательский сеанс истек. Я тестировал без транзакции, и тупики остановились, но я начал получать блокированные отчеты о процессах от SQL Profiler. Я видел ~ 15 секунд задержки при входе в систему, а иногда и таймауты - так что верните транзакцию.

Q: Как часто это происходит?

A: AuthRuleCache отключается в рабочей среде в настоящий момент, поэтому он не влияет на пользователей. Мое локальное тестирование 100 последовательных входов в систему показывает, что, возможно, 10% тупик или сбой. Я подозреваю, что это хуже для пользователей, у которых на панели управления есть отчет, основанный на модели отчетов.

В: Как получить моментальные снимки отчета?

A: Может быть, возможность - не знаю, насколько хорошо это работает с параметризованными отчетами. Меня беспокоит то, что у нас есть некоторые пользователи, которые будут встревожены, если они вставляют запись, но не видят ее на панели инструментов до получаса. Кроме того, я не всегда могу гарантировать, что все будут использовать снимки отчетов правильно все время, поэтому не хочу оставлять дверь открытой для тупиков, чтобы снова проникнуть на более позднюю дату.

В: Могу ли я увидеть полный T-SQL транзакции обновления AuthRuleCache?

A: Вот инструкции, выпущенные внутри одной транзакции, захваченной из SQL Profiler, для входа одного пользователя:

Ищите истекшие сеансы - мы удалим связанный с ним просмотр, если нашли

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
    )

Отбросить любое ранее существующее представление для пользователя 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]

Создать представление для пользователя 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
    )

Получить список всех пользовательских представлений для сущности действий

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

Отбросить существующее представление основных действий

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

Создайте новое представление основных действий, и мы закончили

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
-- ...

Ответы

CREATE VIEW widgets
AS
SELECT AW.*
FROM AllWidgets AW
INNER JOIN widgets_authorized WA ON WA.Widget_ID = AW.Widget_ID
INNER JOIN Users U ON WA.User_ID = U.User_ID
WHERE U.Login = SYSTEM_USER

CREATE VIEW widgets
AS
SELECT AW.*
FROM AllWidgets AW
INNER JOIN widgets_authorized WA ON WA.Widget_ID = AW.Widget_ID
WHERE WA.User_ID IN (SELECT U.User_ID FROM Users U WHERE U.Login = SYSTEM_USER)