Вчера-сегодня пришлось испытать на собственной шкуре, что такое версионность в СУБД, как работает READ_COMMITTED_SNAPSHOT в SQL Server и когда его включать не следует.

Что делает READ_COMMITTED_SNAPSHOT?

Для транзакций, выполняющихся на уровне изоляции READ COMMITTED, инструкция ALTER DATABASE [...] SET READ_COMMITTED_SNAPSHOT ON изменяет стратегию избегания "грязного чтения". "Грязное чтение" это плохо — это когда читающая транзакция прочитывает данные незавершенной пишущей транзакции, то есть данные, которые в итоге могут быть отменены. Все промышленные СУБД (по умолчанию) запускают транзакции в режимах изоляции, исключающих "грязное чтение".

Microsoft SQL Server на уровне изоляции READ COMMITTED (уровень изоляции по умолчанию) может использовать две стратегии:

  1. При выключенной опции READ_COMMITTED_SNAPSHOT, SQL Server прибегает к разделяемой (shared) блокировке объектов, чтение которых производится транзакцией.
  2. При включенной опции READ_COMMITTED_SNAPSHOT, SQL Server для каждого изменения создает новую версию строки, возвращая читающим транзакциям ранее принятую (committed) версию, а изменяющей транзакции предоставляя собственную рабочую версию.

Есть направление священных войн (holywars) - "СУБД версионная, против СУБД с блокировками", где SQL Server противопоставляется Oracle или Firebird именно как СУБД, в которой целостность данных обеспечивается только блокировками. 10 лет назад это было так.

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

А минус? За версионность приходится платить ресурсами — в первую очередь ОЗУ (поэтому Oracle так любит память). Выключенная по умолчанию версионность, видимо, одна из причин, почему SQL Server может работать на очень ограниченных ресурсах.

Теперь подтвержу практикой.

Описание кейса

Исходная ситуация

Старый сервер с 2 CPU Intel Xeon, 8 ГБ ОЗУ и несколькими RAID массивами. На нем работает SQL Server, где база Terrasoft CRM (сильно нагружена, в том числе job-ами синхронизации) и другие базы.

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

В данном случае, базы находятся на G:, журнал транзакций на T:, файл подкачки на S: и это разные RAID массивы.

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

На сервере постоянно собираются (некоторые) данные со счетчиков производительности при помощи системного монитора.

Ситуация вчера

Из-за роста нагрузки на основной инстанс SQL Server, стал вылетать Terrasoft CRM с сообщением "Превышено время ожидания блокировки".  SQL Profiler показал, что происходят Lock Timeout-ы, а так же, что при установлении сеанса приложение устанавливает ограничение на ожидание блокировки в 2 секунды (SET LOCK TIMEOUT 2000) — привет от разработчиков, из-за чего вся последующая котовасия.

Поскольку поправить время ожидания возможности нет, я установил READ_COMMITTED_SNAPSHOT для БД Terrasoft CRM в значение ON, включив таким образом версионность. Проблемы "Превышено время ожидания блокировки" исчезли.

Ситуация сегодня

Проиллюстрирую картинкой системного монитора:

Голубой (частый вначале) сплошной график - Transactions/sec (масштаб 0,1).
Светло-голубой сплошной график - Lock waits/sec (масштаб 100,0).
Красный пунктирный график - Средняя длинна очереди записи на массив с базами (масштаб 10,0).
Красный сплошной график - Средняя длинна очереди чтения с массива с базами (масштаб 0,1).
Розовый сплошной график (с двумя заметными пиками) - Кол-во обращений к диску, где лежит файл подкачки (масштаб 10,0).
Синий пунктирный график (горизонтальный с единственным перепадом) - Total Server Memory KB (масштаб 0,00001).

Первое на что хочется обратить внимание - голубой график (количество транзакций), показывает сколько транзакций сервер смог провести, а не сколько требовалось. Если ресурсов не хватает, количество транзакций будет снижаться из-за ожиданий. Этот график удобно сравнивать с количеством компиляций SQL запросов, например, что бы понять, что не нагрузка падает, а мы проседаем, но тут и так всё понятно.

Первый пик светло-голубого графика (блокировки) приходится на 9 утра. Это можно объяснить тем, что с 9 утра начинают работать некоторые job-ы, которые ночью спят. Но я предполагаю, что блокировки, которые ими вызываются, относятся к другим базам, ведь наша основная база находится в режиме READ_COMMITTED_SNAPSHOT. Хотя возможно, что это блокировки операций записи между собой (из разных job-ов).

Где-то с 9:40, когда люди реально начинают работать, наблюдается снижение количества блокировок и снижение количества транзакций (специально подчеркиваю), зато растет очередь чтения с массива с базами (красный график).

В 10:22 произошел сильный обмен с файлом подкачки (розовый график) и очередь чтения с массива с базами вырасла ещё более заметно. Случился серьезный дефицит памяти и SQL Server стал сокращать время жизни страниц в буфере, чаще (по новой) забирая их с диска. Но блокировок мало — приложение хоть и тормозит, но работает без ошибок "Превышено время ожидания....", и тем не менее, работать в нём невозможно.

Я предполагаю, что с 10 утра до 10:45 происходил пик получения отчетов, обычно у нас с утра есть такой, приблизительно в это время. Так или иначе, около 10:45 очередь чтения с массива с базами снизилась и количество проводимых транзакций подросло, однако, субъективно, было плохо — на сервере тормозило даже RDP.

В 11:16 я выключил READ_COMMITTED_SNAPSHOT — это видно по пику светло-голубого графика. Дальше пошли блокировки, они стали несколько плотнее чем до 9:40. Сервер субъективно растормозился (практически мгновенно), но вернулись вчерашние проблемы - иногда возникающее "Превышено время ожидания блокировки".

В 12:50, я выключил инстанс Developer Edition и удвоил память основному инстансу SQL Server, что показывает синий график (Total Server Memory). В результате, плотность и высота пиков светло-голубого графика (Lock waits/sec) упала, а голубой график (Transactions/sec) вырос.

Выводы

Механизм версионности при одинаковых нагрузках требует больше памяти. Если нет памяти, лучше решить вопрос установкой большего LOCK TIMEOUT (если приложение его выставляет).

Вопрос "что лучше: версионность или блокировки?", не решается, очевидно, в пользу версионности, вот почему:

Если памяти для СУБД мало — версионность работать будет, но пользоваться приложением будет нельзя (будет работать слишком медленно).

Если памяти для СУБД много — время блокировок само по себе сократится (из-за ускорения записи и сокращения очередей на дисках) и преимущество неблокирующего чтения сойдет на нет.

В итоге, версионная у вас СУБД или блокирующая неважно, требуется адекватный объем памяти. Единственный плюс блокирующей СУБД, во всяком случае SQL Server перед Oracle в том, что при маленькой нагрузке можно работать на небольшом объеме памяти.