MS SQL Server - безопасное одновременное использование глобальной временной таблицы?

В MS SQL Server я использую глобальную временную таблицу для хранения информации о сеансе, передаваемой клиентом, а затем использую эту информацию внутри триггеров.

Поскольку одну и ту же глобальную временную таблицу можно использовать в разных сеансах, и она может существовать или не существовать, когда я хочу записать в нее (в зависимости от того, закрыты ли все предыдущие сеансы, в которых она использовалась ранее), я проверяю существование глобальной временной таблицы, на основе которой я создаю, прежде чем писать в нее.

IF OBJECT_ID('tempdb..##VTT_CONTEXT_INFO_USER_TASK') IS NULL
  CREATE TABLE ##VTT_CONTEXT_INFO_USER_TASK (
    session_id    smallint,
    login_time    datetime,
    HstryUserName VDT_USERNAME,
    HstryTaskName VDT_TASKNAME,
  )

MERGE ##VTT_CONTEXT_INFO_USER_TASK As target
USING (SELECT @@SPID, @HstryUserName, @HstryTaskName) as source (session_id, HstryUserName, HstryTaskName)
ON (target.session_id = source.session_id)
WHEN MATCHED THEN
  UPDATE SET HstryUserName = source.HstryUserName, HstryTaskName = source.HstryTaskName
WHEN NOT MATCHED THEN
  INSERT VALUES (@@SPID, @LoginTime, source.HstryUserName, source.HstryTaskName);

Проблема в том, что между моей проверкой существования таблицы и MERGE оператор SQL Server может отбросить временную таблицу, если все сеансы, которые ранее ее использовали, закрывались именно в этом конкретном случае (это действительно происходило в моих тестах).

Существует ли передовая практика, как избежать проблем параллелизма такого рода, чтобы таблица не отбрасывалась между проверкой на наличие и последующим использованием?

Источник

2 ответа

Решение

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

Но поскольку сейчас это было невозможно из-за нехватки времени (для получения необходимых формальных утверждений для новой нормальной таблицы требуется несколько недель), я предложил решение, позволяющее SQL Server не сбрасывать глобальную временную таблицу между проверками существование и MERGE заявление.

Существует некоторая информация о том, когда SQL Server удаляет глобальную временную таблицу; Мои личные тесты показали, что SQL Server удаляет глобальную временную таблицу в тот момент, когда завершается сеанс, который ее создал, и завершаются любые другие транзакции, запущенные в других сеансах, которые изменили данные в этой таблице.

Моим решением было подделать изменения данных в глобальной временной таблице еще до того, как я проверил ее существование. Если таблица существует в этот момент, SQL Server будет знать, что ему нужно сохранить ее до завершения текущей транзакции, и она не может быть больше удалена после проверки ее существования. Код теперь выглядит так (правильно прокомментирован, так как это своего рода хак):

-- Faking a delete on the table ensures that SQL Server will keep the table until the end of the transaction
-- Since ##VTT_CONTEXT_INFO_USER_TASK may actually not exist, we need to fake the delete inside TRY .. CATCH
-- FUTURE 2016, Feb 03: A cleaner solution would use a real table instead of a global temp table. 
BEGIN TRY
  -- Because schema errors are checked during compile, they cannot be caught using TRY, this can be done by wrapping the query in sp_executesql
  DECLARE @QueryText NVARCHAR(100) = 'DELETE ##VTT_CONTEXT_INFO_USER_TASK WHERE 0 = 1'
  EXEC sp_executesql @QueryText
END TRY
BEGIN CATCH
-- nothing to do here (see comment above)
END CATCH

IF OBJECT_ID('tempdb..##VTT_CONTEXT_INFO_USER_TASK') IS NULL
  CREATE TABLE ##VTT_CONTEXT_INFO_USER_TASK (
    session_id    smallint,
    login_time    datetime,
    HstryUserName VDT_USERNAME,
    HstryTaskName VDT_TASKNAME,
  )

MERGE ##VTT_CONTEXT_INFO_USER_TASK As target
USING (SELECT @@SPID, @HstryUserName, @HstryTaskName) as source (session_id, HstryUserName, HstryTaskName)
ON (target.session_id = source.session_id)
WHEN MATCHED THEN
  UPDATE SET HstryUserName = source.HstryUserName, HstryTaskName = source.HstryTaskName
WHEN NOT MATCHED THEN
  INSERT VALUES (@@SPID, @LoginTime, source.HstryUserName, source.HstryTaskName);

Хотя я бы назвал это решение "используйте его на свой страх и риск", оно предотвращает влияние использования глобальной временной таблицы в других сеансах на ее использование в текущем сеансе, что побудило меня запустить этот поток.

Спасибо всем за ваше время! (от редактирования текста до ответов)

Источник

Понятия "глобальная временная таблица" и "триггер" просто не нажимают. Таблицы являются постоянными хранилищами данных, как и их атрибуты, включая триггеры. Временные таблицы удаляются при перезапуске сервера. Зачем кому-то разрабатывать систему, в которой постоянный блок кода (триггер) зависит от механизма временного общего хранилища? Это похоже на рецепт неудачи.

Вместо глобальной временной таблицы используйте реальную таблицу. Если хотите, поставьте полезный префикс, такой как temp_ перед именем. Если таблица совместно используется базами данных, поместите ее в базу данных, к которой имеет доступ весь код.

Создайте таблицу один раз и оставьте ее там (удаление строк - это нормально), чтобы код триггера мог получить к ней доступ.

Источник
Другие вопросы по тегам