Автор: Минюров Сергей
Соблюдение классических правил проектирования и разработки баз данных для бизнес-приложений (OLTP) позволяет нам получить быструю и надежную базу данных в режиме блокировки данных, который установлен для Microsoft SQL Server по умолчанию.
В случае сложной программной логики на стороне базы данных или совмещения нагрузки по обработке транзакций и аналитических запросов можно перевести конкретную базу данных в режим изоляции с поддержкой версий строк данных.
Само по себе это не гарантирует повышения производительности и может потребовать усиления аппаратной конфигурации (из-за дополнительной нагрузки на tempdb), существенных доработок серверного и клиентского кода, тщательного тестирования для оценки надежности и производительности работы приложений в данном режиме.
Если мы используем продуктовое решение, то необходимо, чтобы версионность поддерживалась данным программным продуктом.
В общем случае с помощью версионности мы ускоряем чтение данных и замедляем их модификацию, поскольку имеются дополнительные операции на поддержку копий строк данных и индексов.
Как работает версионность
При выполнении транзакции в режиме изоляции SNAPSHOT (версионность строк данных), при изменении строки в во временной базе данных сохраняется до завершения транзакции ее начальная версия. При этом увеличивается размер данных (соответственно, нагрузка на ввод-вывод), поскольку для каждой строки добавляется 14 байт служебной информации. Другие транзакции, которые выполняются одновременно и читают эту строку данных, не будут заблокированы, т.е. ждать подтверждения транзакции на изменение, а получат без задержки начальную версию строки.
В определенном смысле, мы получаем эффект «грязного чтения» наоборот — т.е. читаем устаревшие данные, которые логически не существуют в случае успешного завершения транзакции на обновление данных. Что не для всех задач является допустимым, с точки зрения функциональной логики. Если нам требуется более строгая работа с данным, то для конкретной транзакции или запроса мы можем указывать режим изоляции с блокировкой данных.
При повторном изменении строки в транзакции накапливается стек версий. Изменения индексов также работают в режиме версионности.
Просмотреть наличие версий строк данных можно с помощью системного представления sys.dm_tran_version_store. Транзакции, работающие в режиме версионности доступы с помощью системных представлений sys.dm_tran_transactions_snapshot и sys.dm_tran_active_snapshot_database_transactions.
Очевидно, что при версионности у нас значительно возрастает нагрузка на временную базу данных, и для получения реального выигрыша по эффективности нужно обеспечить ее максимальную производительность (поместить, например, на быстрый RAID, SSD или виртуальный диск в оперативной памяти).
Мы можем перевести временную базу данных в режим версионности и протестировать, какой эффект при этом получим.
Настройка версионности
Для каждой базы данных, которую мы хотим перевести в режим версионности, необходимо выполнить следующую команду:
ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON;
Команда не блокируется подключением других пользователей и выполняется в отложенном режиме до завершения всех текущих транзакций. Новые транзакции после данной команды начнут создавать версии строк при модификации данных.
До завершения перехода базы данных в режим версионности попытка выполнения команд с уровнем изоляции SNAPSHOT_ISOLATION будет приводить к программной ошибке 3956.
Проверить состояние базы данных можно следующим запросом:
select snapshot_isolation_state, snapshot_isolation_state_desc from sys.databases where name = 'AdventureWorks'
Если вернутся значения snapshot_isolation_state = 1 или snapshot_isolation_state_desc = ON, то это значит что база данных перешла в режим версионности и можно запускать сессии с этим уровнем изоляции.
Для запуска сессии в режиме версионности требуется выполнить следующую команду:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
Не всегда мы можем исправить существующий код чтобы явно установить уровень изоляции с версионностью. В этом случае требуется выполнить команду, которая уровень по умолчанию READ COMITTED (блокировка чтения при записи) заменит на версионность:
ALTER DATABASE AdventureWorks SET READ_COMMITTED_SNAPSHOT ON
Команда блокируется подключением других пользователей. Для ее выполнения требуется монопольный доступ к базе данных.
Если в коде используется «грязное чтение» (уровень изоляции READ UNCOMITTED, подсказка NOLOCK), то мы не получим преимуществ по производительности от версионности.
Но данный режим работы базы данных нам потребуется для поддержки распределенных транзакций, снижения нагрузки на временную базу данных и предотвращения конфликтов при обновлении данных. Если имеются распределенные транзакции, то для поддержки версионности требуется перевести в этот режим все участвующие базы данных.
Формула для оценки дополнительного пространства в tempdb: 2 * [объем версий данных за минуту] * [длительность самой долгой транзакции в минутах].
Разработка в режиме версионности
Уровень изоляции SNAPSHOT рекомендуется, если отмена транзакций является редким событием. Также она полезна для выполнения аналитических запросов для отчетов. Но для программистов это увеличивает сложность разработки, поскольку нужно учитывать особенности, связанные с версионностью данных и их обновлением.
При одновременном обновлении одной и той же записи в разных транзакциях, в сессии в которой обновление было выполнено последним, при завершении первой транзакции возникнет ошибка 3960. Это спасает нас от проблемы потерянных обновлений, но требует обрабатывать данную ошибку в приложении (например, повторить транзакцию).
Если это приводит к постоянным ошибкам, то рекомендуется в данном случае использовать уровень изоляции READ COMMITTED, который гарантирует нам чтение и обработку актуальных данных. Но при этом также возникают ожидания блокировок. Либо можно для конкретного запроса использовать подсказку (hint) UPDLOCK.
