Блокировка – важное понятие в SQL Server, которое обеспечивает целостность данных и контроль параллелизма. Однако существуют определенные сценарии, когда могут возникать проблемы с блокировкой, особенно при использовании операторов языка определения данных (DDL), таких как SELECT INTO. В этой статье мы рассмотрим побочные эффекты наличия операторов DDL в долгосрочных транзакциях и то, как это может повлиять на системные таблицы и производительность.
DDL и транзакция
Операторы DDL, такие как CREATE TABLE и SELECT INTO, учитывают транзакции. Это означает, что если указано BEGIN TRAN, за которым следует оператор DDL, а затем ROLLBACK, изменения, внесенные оператором DDL, не будут зафиксированы. SELECT INTO работает аналогично CREATE TABLE, поскольку создает новую таблицу с колонками, возвращаемыми оператором SELECT. Под капотом он начинает неявную транзакцию и заполняет связанные системные таблицы необходимыми строками для определения структуры таблицы, как и оператор CREATE TABLE DDL.
Блокировка и системные таблицы
Когда оператор DDL указывается в явной транзакции, соответствующие строки в затронутых системных таблицах блокируются исключительно до явного фиксирования транзакции. Это может привести к блокировкам или превышению времени ожидания при мониторинге запросов, обычных административных задачах и запросах, требующих сканирования всех строк в затронутых системных таблицах.
Тестовая среда
Для демонстрации побочных эффектов мы создадим тестовую базу данных и проанализируем журнал транзакций с помощью недокументированной системной функции fn_dblog. Эта функция позволяет получить информацию о транзакциях и операциях, зарегистрированных в журнале транзакций. Анализируя журнал транзакций, мы можем получить представление о затронутых системных таблицах и поведении блокировки.
Явная транзакция и SELECT INTO
Рассмотрим сценарий, где явная транзакция используется вместе с оператором SELECT INTO для создания временной таблицы. При выполнении оператора SELECT INTO транзакция регистрируется в tempdb, даже если контекст базы данных – пользовательская база данных. Это означает, что при анализе журнала транзакций мы увидим операцию LOP_BEGIN_XACT для SELECT INTO, но без соответствующей операции LOP_COMMIT_XACT, потому что транзакция еще не зафиксирована.
Блокировка и блокировки
При проверке блокировок мы можем обнаружить блокировки намерений на системные таблицы и эксклюзивные блокировки на строки индекса, страницы и области. Эти блокировки относятся к системным таблицам в tempdb, поскольку оператор SELECT INTO автоматически помещается в tempdb в качестве временной таблицы. Это может привести к блокировкам, так как другие запросы, пытающиеся получить доступ к тем же системным таблицам, могут быть заблокированы до фиксации или отката транзакции.
Влияние на производительность
Проблемы с блокировкой, вызванные операторами DDL в долгосрочных транзакциях, могут оказать значительное влияние на производительность. Например, выполнение определенных административных задач, таких как проверка свойств базы данных из SQL Server Management Studio (SSMS), может привести к нереагируемости или превышению времени ожидания SSMS. Кроме того, проверка целостности базы данных в tempdb может завершиться неудачно из-за невозможности получить блокировки на системные таблицы.
Заключение
В заключение, важно избегать использования операторов DDL, включая SELECT INTO, в долгосрочных явных транзакциях. Эти операторы могут создавать эксклюзивные блокировки на строки в системных таблицах, что приводит к блокировкам, превышению времени ожидания и проблемам с производительностью. Понимая потенциальные побочные эффекты и избегая подобных сценариев, разработчики и администраторы баз данных могут обеспечить более гладкую и эффективную работу SQL Server.