Завершающая часть перевода – здесь будут разобраны блокировки, процесс записи изменённых данных, а также дополнительные команды SQL Server.

Предыдущие части перевода:

«латчи» (Latches): защита конкурентного доступа к страницам.

Перед тем, как разбираться с механизмом записи данных, важно изучить механизм, призванный гарантировать, что с диска всегда будут считаны корректные данные. В многопоточной среде всегда важно следить за тем, чтобы «читатели» не могли увидеть данные из незавершённой транзакции записи. Всем программистам знакомы примитивы, реализующие такие проверки: мютексы, семафоры, критические секции. В терминах баз данных устоялся термин «латч» («защёлка», latch). Латчи – это структуры, которые контролируют конкурентный доступ к ресурсам. Например, каждая страница в Buffer pool должна иметь латч для контроля доступа и поддержания консистентности данных.

Латчи поддерживают несколько видов доступа: разделённый (shared), эксклюзивный (exclusive), доступ для обновления (update), удерживающий доступ (keep) и доступ для удаления (destroy). Ниже приведена таблица совместимости видов доступа.

	KP	SH	UP	EX	DT
KP	Y	Y	Y	Y	N
SH	Y	Y	Y	N	N
UP	Y	Y	N	N	N
EX	Y	N	N	N	N
DT	N	N	N	N	N

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

Любой оператор, которому потребуется изменить содержимое страницы, должен получить EX-латч. В каждый момент времени только один оператор может менять содержимое страницы – и ни один оператор не может прочитать её содержимое, пока изменение не будет завершено.

Эти два типа латчей будут отражены в статистике ожиданий как PAGELATCH_SH и PAGELATCH_EX. В статье Diagnosing and Resolving Latch Contention on SQL Server разобрано больше деталей этого механизма, в том числе:

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

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

Блокировки.

В то время, как латчи реализуют физическую защиту конкурентного доступа между потоками, блокировки отвечают за логическую защиту доступа между транзакциями:

Логическая и физическая защита:

Блокировки описывают заблокированную сущность, но сами они не являются сущностью. Это всего лишь 6-байтовая строка, которая описывает, что именно было заблокировано. Латчи – это самые настоящие объекты в памяти, которые могут быть заблокированы. Для того, чтобы получить, «захватить», латч, выполняемый код должен каким-то образом получить ссылку (указатель, адрес) на нужный объект латча. А для того, чтобы выполнить блокировку, коду достаточно сконструировать 6-байтовую строку, описывающую блокируемый объект, и затем запросить блокировку у менеджера блокировок.

Потоки и транзакции:

Латчи захватываются и освобождаются потоками (thread), существующими в рамках процесса. Блокировки захватываются и освобождаются транзакциями. Это важное отличие, поскольку транзакция может состоять из нескольких запросов, кроме того, транзакция может выполняться в контексте разных потоков (thread): либо одновременных, если мы говорим о параллельном выполнении, либо последовательных, если каждый из последовательных запросов транзакции был «подхвачен» разными рабочими потоками (workers)

Блокировки организованы более сложно, чем латчи. Не в последнюю очередь потому, что существует 22 типа блокировок, описанных в статье на Microsoft Docs. Добавьте сюда загадочные названия, наподобие SCH-S, SIX или RX-U – и станет понятно, почему тема блокировок кажется такой сложной. Попробуем немного прояснить, что именно означают эти множества видов блокировок.

Стабильность схемы (Schema Stability)

Во время выполнения плана запроса СУБД ожидает, что объекты, на которые ссылается план (т.е. таблицы, представления и т.п.) не будут менять свою структуру и не будут удалены кем-нибудь. Для того, чтобы гарантировать, что такие изменения не случатся, каждый запрос ещё до начала выполнения накладывает упреждающие блокировки «стабильности схемы» на все объекты, упоминаемые в плане. Это и есть SCH-S- блокировки.

DDL-команды (то есть, команды, изменяющие метаданные) будут накладывать блокировку модификации схемы: SCH-M. Это самая мощная блокировка (т.е. с максимальными ограничениями и с минимальной совместимостью). Эта блокировка гарантирует, что ни одна другая сессия не сможет даже ссылаться на изменяемый объект.

На практике большинство запросов не накладывает SCH-S блокировку, вместо этого используются аналоги – блокировки намерения (см. ниже, например *_IS)

Разделяемые блокировки, блокировки обновления и эксклюзивные блокировки (Shared, Update, Exclusive)

Это базовые режимы блокировок: S (shared, разделяемая), U (update, обновления) и X (eXclusive, исключительная). S- и X-блокировки самоочевидны. U-блокировка накладывается на данные, которые могут быть позже изменены или удалены (соответственно, при этом блокировка будет обновлена до X).

Наложение U-блокировки не блокирует другие чтения (S-блокировки), но не позволяет наложить U-блокировку из другой сессии. Если бы U-блокировок не было, две сессии могли бы попасть в дедлок в тот момент, когда пробовали бы одновременно обновить S-блокировку до X-блокировки. С другой стороны, если бы оператор UPDATE сразу накладывал X-блокировку на все считываемые записи, даже на те, которые потом не придётся изменять – мы бы получили избыточные блокировки других читающих сессий.

Иерархия блокировок и блокировки намерения.

Все блокировки, в названии которых есть буква I – это блокировки намерения (intent). Для того, чтобы понять их назначение, необходимо разобраться в иерархии блокировок.

Предположим, у нас есть запрос, который собирается просканировать всю таблицу, содержащую несколько миллионов строк. Если бы этот запрос накладывал блокировки на каждую из считываемых строк, пришлось бы выполнить несколько миллионов обращений за блокировками – а это довольно расточительно с точки зрения потребляемых ресурсов. Да, наложение блокировки – это быстро, но даже «быстро» суммируется в заметное время, если повторить это миллион раз. Поэтому запрос решает «сэкономить» и вместо блокировки отдельных строк накладывает всего одну S-блокировку, но сразу на объект таблицы.

Другой запрос, работающий параллельно, хочет удалить одну строку из таблицы – поэтому он запрашивает X-блокировку на эту строку. И вот тут возникает проблема: каким образом менеджер блокировок должен понять, что нельзя предоставлять X-блокировку на строку, т.к. другая сессия уже запросила S-блокировку на всю таблицу? Мы ведь помним, что блокировки – это просто строки, которые описывают, что было заблокировано. И по содержимому этих строк менеджер блокировок никак не может узнать, что строка, запрашиваемая для X-блокировки принадлежит таблице, на которую наложена S-блокировка. Строки, описывающие эти блокировки, описывают объекты на разных уровнях иерархии.

Тут и пригождаются блокировки намерения. Начнём с того, что операторы доступа к данным, которые запрашивают блокировки, знают иерархию блокируемых элементов. Тот же оператор изменения знает, что изменяемая строка принадлежит определённой таблице. Поэтому в обязанности оператора доступа к данным входит запрос блокировки намерения к родительскому объекту (или объектам) в иерархии. Получается, оператор изменения сначала запросит IX-блокировку на объект таблицы. Такая блокировка будет означать буквально следующее: «я не буду блокировать всю таблицу, но где-то внутри неё могут быть строки, на которые я наложу X-блокировку». IX-блокировка несовместима с S-блокировкой, которую наложила первая сессия. Проблема решена: «скрытый» конфликт блокировок становится явным, соответственно, одна сессия блокирует другую, пока не выполнит все свои действия. И теперь когда вы увидите, например, «SIX-блокировку», вы поймёте, что это означает: «Я выполнил S-блокировку на объект таблицы, но вместе с тем я могу заблокировать какие-то отдельные строки в X-режиме»

Блокировки диапазонов ключей (Key-range locks)

Режимы блокировок, содержащие в названии R – это блокировки диапазонов (range). Такие блокировки защищают строки, которые ещё не существуют. SQL Server использует блокировки диапазонов чтобы реализовать последовательный (SERIALIZABLE) уровень изоляции транзакций. Дело в том, что уровень SERIALIZABLE требует, чтобы два вызова одного и того же запроса в транзакции возвращали одинаковый результат: никаких новых или потерянных строк. Получается, нужно контролировать не только считанные строки, но и интервалы между ними – чтобы никакой INSERT не смог «протащить» новые строки в диапазон, уже считанный в нашей транзакции. Именно для этого и применяются блокировки диапазона. Больше подробностей о таких блокировках - в статье Key-Range Locking

Запись данных

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

Операторы удаления (delete) и изменения (update) обычно управляются другими операторами чтения, которые находят нужные строки и передают оператору изменения указатели на конкретную строку.

Сами по себе операции вставки, удаления или изменения строк несколько более сложны, чем операции чтения. Это связано с тем, что SQL Server использует упреждающее логирование изменений (Write Ahead Logging). Любая операция модификации данных должна быть в первую очередь записана в лог транзакций.

Грубо говоря, все изменения происходят в следующей последовательности:

Оператор изменения «позиционируется» на странице, которую он должен изменить (то есть, строка должна быть добавлена, удалена или изменена именно на этой странице). Для этого нужная страница должна оказаться в Buffer pool.
Оператор изменения получает эксклюзивный латч на изменяемую страницу. Это означает, что ни один другой оператор не сможет считать изменяемую страницу.
Оператор изменения добавляет в лог транзакций запись, в точности описывающую, что и как оператор собирается изменить на странице.
После этого оператор может изменить страницу в памяти (Buffer pool). Страница на диске в этот момент не меняется!
В момент создания записи в логе транзакций оператор получает LSN (Log sequence number) – номер операции внутри лога транзакций. Этот LSN записывается в заголовок изменяемой страницы в поле «LSN последнего изменения» (last modified LSN)
В момент создания записи в логе транзакций оператор получает LSN (Log sequence number) – номер операции внутри лога транзакций. Этот LSN записывается в заголовок изменяемой страницы в поле «LSN последнего изменения» (last modified LSN)
Снимается эксклюзивный латч на страницу. Теперь страница может быть прочитана другими транзакциями (например, в режиме Read Uncommitted). Важно понимать, что до сих пор ни одного байта не было записано на диск – все изменения происходили в памяти.
Перед тем, как изменяющая транзакция будет завершена, она должна создать новую запись лога, фиксирующую факт, что транзакция зафиксирована (committed). Эта запись добавляется в лог транзакций и после этого она и все предшествующие записи лога (в рамках транзакции) должны быть записаны на диск – в файл лога транзакций. Изменяемая страница в этот момент всё еще не записана на диск, её изменения остаются только в памяти. Но сама транзакция с этого момента становится надёжной (durable): даже если служба SQL Server упадёт, информация об изменениях на странице хранятся в логе транзакций, процесс запуска БД прочитает её и повторит нужные действия
Периодически (но не сразу по окончании транзакции) изменения в страницах записываются на диск, в файл данных. Эта операция называется CHECKPOINT.

Кроме того, есть отдельный вид операции записи, который работает по другому алгоритму: операции с минимальным логированием. Так могут работать только операции, вставляющие новые данные: INSERT или синтаксис .WRITE(@value, NULL) оператора UPDATE (и то, только в случае добавления BLOB-полей). Для того, чтобы транзакция могла использовать минимальное логирование, она должна отвечать дополнительным условиям, см. «Operations that can be minimally logged» и «Data loading performance guide».

Последовательность операций при записи с минимальным логированием будет примерно следующей:

Оператор, выполняющий операцию вставки, выделяет место под новую страницу. Для понимания процесса выделения места под страницы можно изучить статью Managing Extent Allocations and Free Space
Оператор помещает страницу в Buffer pool и получает эксклюзивный латч на неё.
Создаётся запись лога транзакций, фиксирующая, что новая страница используется для операции вставки с минимальным логированием. Эта запись добавляется в лог (в памяти), а LSN записи помещается в заголовок новой страницы как LSN последнего изменения.
Страница добавляется в список страниц с минимальным логированием, связанных с текущей транзакцией.
Теперь оператор может добавить на страницу столько строк, сколько поместится. Ему не требуется создавать записи лога для каждой добавляемой записи. В этот момент изменяется только образ новой страницы в памяти, на диск пока ничего не пишется.
Когда страница заполняется, выделяется новая страница – и описанные выше шаги повторяются.
Перед тем как транзакция с минимальным логированием будет зафиксирована, все страницы, изменённые с минимальным логированием, должны быть записаны на диск. Только после того, как все эти страницы будут записаны, создаётся новая запись лога, подтверждающая, что транзакция зафиксирована. И после этого все записи лога, описывающие транзакцию, должны быть записаны на диск, в файл лога.
Для того, чтобы массовая запись страниц на диск не положила дисковую подсистему специальный фоновый процесс «жадной записи» переносит их на диск ещё до завершения транзакции.

Операции с минимальным легированием остаются полностью транзакционными, целостными и надёжными. В любой момент выполнения такая операция может быть отменена (откачена, rolled back). Сервер СУБД может даже завершиться крэшем во время выполнения такой операции – и всё равно база останется в целостном состоянии (после завершения recovery, восстановления во время запуска)

Точно так же, как и обычные операции, операции с минимальным логированием детально описаны в статье SQL Server 2000 I/O Basics.

DDL

Не все T-SQL-выражения выполняются как последовательный перебор операторов из плана выполнения. Типичный пример такого исключения – DDL-команды (например, CREATE TABLE).

Для понимания того, как работают DDL-команды важно знать, что SQL Server сохраняет все метаданные о существующих объектах в специальных служебных таблицах. Получается, что вставка новой строки в, скажем, таблицу, хранящую список пользовательских таблиц, приведёт к тому, что SQL Server узнает о существовании новой таблицы. Вставка строки в таблицу, хранящую сведения о колонках таблиц, приведёт к появлению новой колонки у нашей новой таблицы. Выполнение команды DROP приведёт к удалению строк из соответствующих системных таблиц. Все объекты, с которыми работает SQL Server, описаны в таких системных таблицах. Более 80 таких таблиц содержат сведения о таблицах, колонках, индексах, схемах, процедурах, представлениях и т.д. – то есть, буквально, о каждом аспекте SQL Server. Получается, что всё, что требуется от DDL-команд – это поддерживать актуальность таких системных таблиц. CREATE TABLE нужно добавить строку в таблицу, описывающую пользовательские объекты (ведь таблица – это не единственный возможный вид объекта) и несколько строк в таблицы, описывающие колонки – вуаля, новая таблица готова! (Помните, что здесь я сильно упрощаю весь процесс).

Несмотря на то, что DDL-команды не используют операторы доступа или изменения данных, которые мы рассмотрели ранее, внутри DDL-команд используется точно такой же код, который находит и изменяет нужные строки в таблицах. Когда DDL-команда выполняется, она не будет вызывать метод next() у оператора поиска - вместо этого она использует точно такой же код, что и у оператора Seek, чтобы найти нужную строку в системной таблице.

Большинство DDL-команд выполняют свою полезную работу просто добавляя, удаляя или изменяя записи в системных таблицах. Но есть отдельные операторы, которые не только работают с системными таблицами, но и, например, обращаются к файловой системе, чтобы выделить место для новой базы данных. Или обращаются к API кластеризации Windows чтобы настроить группу доступности AlwaysOn. И отдельные команды могут обращаться к самим пользовательским таблицам чтобы установить значения по умолчанию или проверить, отвечают ли существующие значения новым ограничениям (check constraint)

BACKUP, RESTORE и DBCC

После того, как мы рассмотрели основные группы операторов – DDL и DML – осталось несколько специальных команд.

BACKUP и RESTORE в своей работе не учитывают практически ничего из того, что мы обсуждали в этой статье. Если смотреть с высоты птичьего полёта, всё, что они делают – просто копируют данные из одних файлов в другие. BACKUP читает данные из файлов данных и логов транзакций и записывает их в файл бэкапа, RESTORE – ровно наоборот. В своей работе эти команды обращаются и к некоторым системным таблицам, но большую часть времени они просто копируют данные из одного файла в другой. Важно, что это копирование происходит без участия Buffer Pool – то есть, бэкап не сможет выдавить из кэша накопленные данные.

(прим. переводчика: на самом деле RESTORE – это не только ~~ценный мех~~ копирование данных; RESTORE запускает процесс восстановления (recovery) базы данных, о котором тоже есть что рассказать. К счастью, всё уже подробно описано в Microsoft Docs – рекомендую изучить эту статью чтобы понимать, из чего складывается время запуска базы данных).

Что же до команд DBCC – каждая из них работает по-своему и выполняет разные задачи. Для примера можно изучить описание работы DBCC CHECKDB в цикле статей CHECKDB from every angle.

Как я могу использовать эту информацию?

У разработчиков, взаимодействующих с базами данных, обычно две главных проблемы: производительность запросов и сохранность данных в случае сбоя. Эта статья никак не поможет со второй проблемой – если у вас нет бэкапа, ничто вас не спасёт. Но, я надеюсь, что понимание того, как работает SQL Server прольёт свет на проблемы производительности.

Как только вы поймёте, что клиент отправляет запрос к СУБД и на каждый запрос создаётся своя задача, весь гордиев узел проблем производительности сильно упростится: в любой момент ваша задача либо выполняется (то есть, использует процессорное время), либо ждёт. И каждый раз, когда задача чего-то ждёт, информация об этом ожидании (чего именно ждали и как долго) попадает во внутреннюю статистику ожиданий, собираемую SQL Server. Есть отличная методичка о том, как использовать эту статистику ожиданий для расследования проблем производительности: Waits and Queries. Если из всех упомянутых ссылок из этой статьи вы откроете только одну – я бы хотел, чтобы это была ссылка на эту методичку.

Перевод: Как MS SQL Server выполняет запросы. Часть 3