Примечание.
Для доступа к этой странице требуется авторизация. Вы можете попробовать войти или изменить каталоги.
Для доступа к этой странице требуется авторизация. Вы можете попробовать изменить каталоги.
Область применения:
SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure
В этой статье описаны все аспекты транзакций с таблицами, оптимизированными для памяти, и хранимыми процедурами, скомпилированными в коде.
Уровни изоляции транзакций в SQL Server применяются к таблицам, оптимизированным для памяти, и к таблицам на жестком диске по-разному, с использованием разных базовых механизмов. Понимание различий помогает программисту проектировать систему с высокой пропускной способностью. В любом случае необходимо обеспечить целостность транзакций.
Условия ошибок, относящиеся к транзакциям в таблицах, оптимизированных для памяти, см. в разделе "Обнаружение конфликтов и логика повторных попыток ".
Общие сведения см. в разделе SET TRANSACTION ISOLATION LEVEL.
Пессимистичное против оптимистичного
Функциональные различия между оптимизированными для памяти таблицами и таблицами на основе дисков основаны на пессимистичных и оптимистических подходах к целостности транзакций. В таблицах, оптимизированных для памяти, используется оптимистичный подход:
При пессимистичном подходе возможные конфликты блокируются прежде, чем они возникнут. Система блокируется при выполнении инструкции и разблокируется, как только транзакция зафиксирована.
В оптимистичном сценарии система обнаруживает конфликты сразу и проводит проверки во время фиксации.
- Ошибка 1205, взаимоблокировка, не может возникать для оптимизированной для памяти таблицы.
Оптимистичный подход имеет меньше накладных расходов и обычно является более эффективным, отчасти потому, что конфликты транзакций являются редкими в большинстве приложений. Основное функциональное различие между пессимистическим и оптимистическим сценариями заключается в том, что если происходит конфликт, в пессимистическом сценарии нужно ждать, а в оптимистическом сценарии клиент должен повторить попытку выполнить транзакцию, которая завершается сбоем. Функциональные различия больше, когда применяется уровень изоляции REPEATABLE READ, и самые большие для уровня SERIALIZABLE.
Режимы запуска транзакций
SQL Server использует следующие режимы для запуска транзакций:
Автокоммит. Простой запрос или оператор DML неявно открывает транзакцию в начале, а конец инструкции неявно фиксирует транзакцию. Автофиксация используется по умолчанию.
В режиме автоматического подключения обычно не нужно кодировать табличное указание уровня изоляции транзакций в таблице, оптимизированной для памяти, в предложении
FROM.Явный. Ваш Transact-SQL содержит код
BEGIN TRANSACTION, а также возможныйCOMMIT TRANSACTION. Вы можете закрепить два или более инструкций в одной транзакции.В явном режиме необходимо использовать параметр базы данных
MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOTили указать подсказку об уровне изоляции транзакций для таблицы, оптимизированной для памяти, в предложенииFROM.Неявно. Инициируется, когда
SET IMPLICIT_TRANSACTION ONнаходится в силе. Эта опция неявно выполняет эквивалент явногоBEGIN TRANSACTIONвыражения перед каждойUPDATEинструкцией, если@@TRANCOUNTравен0. Поэтому в конечном итоге ваш T-SQL код должен явно выполнитьCOMMIT TRANSACTION.Блок ATOMIC. Все утверждения в
ATOMICблоках всегда выполняются в рамках одной транзакции. При успешном выполнении фиксируются все действия атомарного блока, а при сбое все его действия откатываются. Для каждой нативно скомпилированной хранимой процедуры требуетсяATOMICблок.
Пример кода с явным режимом
В следующем интерпретированном сценарии Transact-SQL используется следующее:
- Явная транзакция.
- Таблица, оптимизированная для памяти, с именем
dbo.Order_mo. -
READ COMMITTEDКонтекст уровня изоляции транзакций.
Поэтому необходимо добавить подсказку при работе с таблицей для таблицы, оптимизированной для работы с памятью. Подсказка должна быть для SNAPSHOT или уровня с еще более высокой степенью изоляции. В примере кода подсказка — это WITH (SNAPSHOT). Если удалить это указание, скрипт сталкивается с ошибкой 41368, для которой не подходит автоматическая повторная попытка:
Ошибка 41368
Доступ к оптимизированным для памяти таблицам с помощью READ COMMITTED уровня изоляции поддерживается только для транзакций автозавершений. Он не поддерживается для явных или неявных транзакций. Укажите поддерживаемый уровень изоляции для оптимизированной для памяти таблицы с помощью указания таблицы, например WITH (SNAPSHOT).
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION; -- Explicit transaction.
-- Order_mo is a memory-optimized table.
SELECT *
FROM dbo.Order_mo AS o WITH (SNAPSHOT) -- Table hint.
INNER JOIN dbo.Customer AS c
ON c.CustomerId = o.CustomerId;
COMMIT TRANSACTION;
Вы можете избежать необходимости WITH (SNAPSHOT) указания с помощью параметра MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT базы данных. При установке этого параметра ON, доступ к оптимизированной для памяти таблице при более низком уровне изоляции автоматически повышается до уровня изоляции SNAPSHOT.
ALTER DATABASE CURRENT
SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
Управление версиями строк
Оптимизированные для памяти таблицы используют высокосложную систему управления версиями строк, которая делает оптимистичный подход эффективным, даже на самом строгом SERIALIZABLEуровне изоляции. Дополнительные сведения см. в разделе Введение в таблицы, оптимизированные для памяти.
Дисковые таблицы косвенно имеют систему управления версиями строк, когда READ_COMMITTED_SNAPSHOT или SNAPSHOT уровень изоляции действует. Система основана на tempdb, тогда как структуры данных, оптимизированные для памяти, имеют встроенное управление версиями строк для максимальной эффективности.
Уровни изоляции
В следующей таблице перечислены возможные уровни изоляции транзакций в порядке увеличения степени изоляции. Сведения о возможных конфликтах и логике повторных попыток, позволяющей разрешить эти конфликты, см. в разделе Обнаружение конфликтов и логика повторных попыток.
| Уровень изоляции | Description |
|---|---|
READ UNCOMMITTED |
Недоступно: невозможно получить доступ к таблицам, оптимизированным для памяти, при уровне изоляции Read Uncommitted. Вы по-прежнему можете получить доступ к таблицам, оптимизированным для памяти, при изоляции SNAPSHOT, если задан уровень сеанса TRANSACTION ISOLATION LEVEL на READ UNCOMMITTED, используя подсказку таблицы WITH (SNAPSHOT) или установив для параметра базы данных значение MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT на ON. |
READ COMMITTED |
Поддерживается для таблиц, оптимизированных для памяти, только если действует режим автоматической фиксации. Вы по-прежнему можете получить доступ к таблицам, оптимизированным для памяти, при SNAPSHOT изоляции, если уровень сеанса установлен на TRANSACTION ISOLATION LEVELREAD COMMITTED, используя подсказку таблицы WITH (SNAPSHOT) или установив для настройки базы данных значение MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOTON.Если задать параметру базы данных READ_COMMITTED_SNAPSHOT значение ON, вы не можете получить доступ как к оптимизированной для памяти таблице, так и к таблице на основе диска при уровне изоляции READ COMMITTED в одной инструкции. |
SNAPSHOT |
Поддерживается для таблиц, оптимизированных для памяти.SNAPSHOT Внутренне является наименее требовательным уровнем изоляции транзакций для оптимизированных для памяти таблиц.SNAPSHOT использует меньше системных ресурсов, чем это делает REPEATABLE READ или SERIALIZABLE. |
REPEATABLE READ |
Поддерживается для таблиц, оптимизированных для памяти. Гарантия, предоставляемая REPEATABLE READ изоляцией, заключается в том, что во время фиксации ни одна из параллельных транзакций не обновила ни одну из строк, прочитанных этой транзакцией.Из-за оптимистичной модели одновременные транзакции не препятствуют обновлению строк, считываемых этой транзакцией. Вместо этого во время фиксации эта транзакция проверила, что REPEATABLE READ изоляция не была нарушена. В противном случае выполняется откат транзакции, ее необходимо повторить. |
SERIALIZABLE |
Поддерживается для таблиц, оптимизированных для памяти. Названный Serializable, так как изоляция настолько строга, что она почти похожа на выполнение транзакций последовательно, а не одновременно. |
Этапы транзакций и время существования
При использовании оптимизированной для памяти таблицы время существования транзакции проходит через этапы, показанные на следующем рисунке:
Описания фаз приводятся ниже.
Этап 1 из 3: обычная обработка
На этом этапе выполняются все запросы и инструкции DML в запросе.
На этом этапе инструкции воспринимают версию таблиц, оптимизированных для памяти, в качестве логического времени начала транзакции.
Этап 2 из 3. Проверка
Этап проверки начинается с назначения времени окончания, которое помечает транзакцию как логическое завершение. Это завершение делает все изменения транзакции видимыми для других транзакций, которые зависят от этой транзакции. Зависимые транзакции не могут быть подтверждены, пока эта транзакция не будет подтверждена успешно. Кроме того, транзакции, которые содержат такие зависимости, не могут возвращать результирующие наборы клиенту, чтобы убедиться, что клиент видит только данные, успешно зафиксированные в базе данных.
Этот этап включает уровни проверку параметров repeatable read и serializable. Для повторяемой проверки чтения она проверяет, обновлена ли транзакция любой из строк, которые она считывает. Для сериализуемой проверки проверяется, вставила ли транзакция строку в любой диапазон данных, который она сканировала. Согласно таблице в разделе уровней изоляции и конфликтов, проверки repeatable read и serializable могут происходить при использовании изоляции snapshot, чтобы подтвердить соответствие ограничений для уникальных и внешних ключей.
Этап 3 из 3: Завершение обработки
На этапе фиксации процесс записывает изменения в устойчивые таблицы в журнал, а процесс записывает журнал на диск. Затем процесс возвращает управление клиенту.
После завершения обработки фиксации процесс уведомляет все зависимые транзакции о возможности фиксации.
Как всегда, сохраняйте свои транзакционные единицы работы как минимальные и краткие, так как это допустимо для ваших потребностей в данных.
Логика обнаружения конфликтов и повторных попыток
Два типа ошибок, связанных с транзакцией, могут привести к сбою транзакции и откату. В большинстве случаев необходимо повторить транзакцию после такого сбоя, как и при возникновении взаимоблокировки.
Конфликты между параллельными транзакциями. Эти конфликты, включая конфликты обновления и сбои проверки, могут произойти из-за нарушений уровня изоляции транзакций или нарушений ограничений.
Сбои зависимостей. Эти сбои происходят из-за неудачной фиксации транзакций, от которых вы зависите, или от слишком большого числа зависимостей.
Следующие условия ошибки могут привести к сбою транзакций при доступе к оптимизированным для памяти таблицам.
| Код ошибки | Description | Причина |
|---|---|---|
41302 |
Предпринята попытка обновить строку, которая была обновлена в другой транзакции после запуска текущей транзакции. | Эта ошибка возникает, если две параллельные транзакции попытаются обновить или удалить одну и ту же строку одновременно. Одна из двух транзакций получает это сообщение об ошибке и должна быть повторно выполнена. |
41305 |
Ошибка проверки операций чтения с возможностью повторения. Строка, считываемая из таблицы, оптимизированной для памяти, эта транзакция была обновлена другой транзакцией, зафиксированной перед фиксацией этой транзакции. | Эта ошибка может возникать при использовании REPEATABLE READ или SERIALIZABLE изоляции, а также, если действия параллельной транзакции вызывают нарушение FOREIGN KEY ограничения.Нарушение параллельными транзакциями ограничений для внешних ключей происходит редко и, как правило, говорит о проблемах с логикой приложения или с вводом данных. Однако ошибка также может возникать, если в столбцах, связанных с FOREIGN KEY ограничением, нет индекса. Поэтому всегда создавайте индекс для столбцов внешнего ключа в таблице, оптимизированной для памяти.Дополнительные сведения о сбоях проверки, вызванных нарушениями внешнего ключа, см. в разделе "Рекомендации по проверке ошибок 41305 и 41325" в оптимизированных для памяти таблицах с внешними ключами в группе консультантов по клиентам SQL Server. |
41325 |
Ошибка сериализуемой проверки. В диапазон, просканированный ранее текущей операцией, вставлена новая строка. Эта строка называется фантомной. | Эта ошибка может возникать при использовании SERIALIZABLE изоляции, а также в том случае, если действия одновременной транзакции вызывают нарушение ограничения PRIMARY KEY, UNIQUE или FOREIGN KEY.Такое нарушение ограничений происходит редко и, как правило, говорит о проблемах с логикой приложения или с вводом данных. Однако, как и повторяющиеся ошибки проверки чтения, эта ошибка также может возникать, если есть FOREIGN KEY ограничение без индекса для столбцов, участвующих. |
41301 |
Сбой зависимости: была сделана зависимость от другой транзакции, которую впоследствии не удалось зафиксировать. | Эта транзакция (Tx1) зависит от другой транзакции (Tx2), в то время как та транзакция (Tx2) находилась на этапе проверки или фиксации, считывая данные, записанные Tx2.
Tx2 Затем не удалось зафиксировать. Наиболее распространенными причинами Tx2 неудачной фиксации являются повторяющиеся считываемые (41305) и сериализуемые (41325) ошибки проверки. Менее распространенной причиной является сбой операций ввода-вывода журнала. |
41823 и 41840. |
Была достигнута квота для пользовательских данных в оптимизированных для памяти таблицах и табличных переменных. | Ошибка 41823 применяется к выпускам SQL Server Express, Web и Standard и отдельным базам данных в Базе данных SQL Azure. Ошибка 41840 применяется к эластичным пулам в База данных SQL Azure. В большинстве случаев эти ошибки указывают на то, что достигнут максимальный размер данных пользователя. Чтобы устранить ошибку, удалите данные из оптимизированных для памяти таблиц. Однако редкие случаи существуют, когда эта ошибка является временной. Повторите попытку при первом обнаружении этих ошибок. Как и другие ошибки в этом списке, ошибки 41823 и 41840 прерывают активную транзакцию. |
41839 |
Транзакция превышает максимальное число зависимостей фиксаций. | Существует ограничение на количество транзакций, от которых может зависеть данная транзакция.Tx1 Такие зависимости называются исходящими. Кроме того, существует ограничение на количество транзакций, которые могут зависеть от данной транзакции (Tx1). Это входящие зависимости. Ограничение в обоих случаях равно 8.Наиболее распространенный случай этого сбоя заключается в том, что большое количество транзакций чтения обращается к данным, записанным одной транзакцией записи. Вероятность достижения этого условия увеличивается, если транзакции чтения выполняют большие проверки одних и тех же данных, и если проверка или фиксация транзакции записи занимает много времени. Например, транзакция записи выполняет крупномасштабное сканирование при сериализуемой изоляции (что увеличивает длину фазы проверки) или журнал транзакций помещается на медленное устройство ввода-вывода для журнала (что увеличивает длину обработки фиксации). Если транзакции чтения выполняют масштабные сканирования и ожидается, что они получат доступ только к нескольким строкам, индекс может отсутствовать. Аналогичным образом, если транзакция записи использует сериализуемую изоляцию и выполняет большие проверки, но ожидается доступ только к нескольким строкам, это условие также указывает на отсутствующий индекс. Ограничение на количество зависимостей коммитов можно снять с помощью флага 9926 трассировки. Используйте этот флаг трассировки, только если вы по-прежнему сталкиваетесь с этой ошибкой после убедительной проверки наличия всех необходимых индексов, так как он может скрыть эти проблемы в упомянутых выше случаях. Еще одна осторожность заключается в том, что сложные графы зависимостей, где каждая транзакция имеет большое количество входящих и исходящих зависимостей, а отдельные транзакции имеют множество уровней зависимостей, могут привести к неэффективности в системе.Область применения: SQL Server 2016 (13.x). В более поздних версиях SQL Server и Azure SQL Database нет ограничения на количество зависимостей при фиксации. |
Логика повторных попыток
Если транзакция завершается ошибкой из-за каких-либо ранее упомянутых условий, повторите операцию.
Вы можете реализовать логику повторных попыток на стороне клиента или сервера. Реализуйте логику повторных попыток на стороне клиента для повышения эффективности. Этот подход также помогает обрабатывать результирующие наборы, возвращаемые транзакцией до сбоя.
Пример кода T-SQL повторного выполнения
Используйте логику повторных попыток на стороне сервера с T-SQL только для транзакций, которые не возвращают результирующие наборы клиенту. В противном случае повторные попытки могут возвращать дополнительные результирующие наборы клиенту.
В следующем интерпретируемом скрипте T-SQL показано, какая логика повторных попыток может выглядеть для ошибок, связанных с конфликтами транзакций, включающими оптимизированные для памяти таблицы.
-- Retry logic, in Transact-SQL.
DROP PROCEDURE If Exists usp_update_salesorder_dates;
GO
CREATE PROCEDURE usp_update_salesorder_dates
AS
BEGIN
DECLARE @retry AS INT = 10;
WHILE (@retry > 0)
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
UPDATE dbo.SalesOrder_mo WITH (SNAPSHOT)
SET OrderDate = GETUTCDATE()
WHERE CustomerId = 42;
UPDATE dbo.SalesOrder_mo WITH (SNAPSHOT)
SET OrderDate = GETUTCDATE()
WHERE CustomerId = 43;
COMMIT TRANSACTION;
SET @retry = 0; -- Stops the loop.
END TRY
BEGIN CATCH
SET @retry - = 1;
IF (@retry > 0
AND ERROR_NUMBER() IN (41302, 41305, 41325, 41301, 41823, 41840, 41839, 1205))
BEGIN
IF XACT_STATE() = -1
ROLLBACK TRANSACTION;
WAITFOR DELAY '00:00:00.001';
END
ELSE
BEGIN
PRINT 'Suffered an error for which Retry is inappropriate.';
THROW;
END
END CATCH
END -- While loop
END
GO
-- EXECUTE usp_update_salesorder_dates;
Транзакция между контейнерами
Транзакция — это межконтейнерная транзакция, если она:
- Обращается к таблице, оптимизированной для памяти, из интерпретированного Transact-SQL.
- Выполняет собственный proc, когда транзакция уже открыта (
XACT_STATE() = 1).
Термин "перекрестный контейнер" исходит от того, что транзакция выполняется между двумя контейнерами управления транзакциями. Один контейнер управляет таблицами на основе дисков, а другой управляет оптимизированными для памяти таблицами.
В рамках одной межконтейнерной транзакции можно использовать различные уровни изоляции для доступа к таблицам на основе дисков и таблицам, оптимизированным для памяти. Вы выражаете это различие с помощью явных подсказок таблицы, таких как WITH (SERIALIZABLE) или с помощью параметра MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOTбазы данных. Этот параметр неявно повышает уровень изоляции для таблицы, оптимизированной для памяти, до уровня моментального снимка, если TRANSACTION ISOLATION LEVEL настроено как READ COMMITTED или READ UNCOMMITTED.
В следующем примере кода Transact-SQL:
- Доступ к таблице
Table_D1на основе диска осуществляется с помощьюREAD COMMITTEDуровня изоляции. - Доступ к оптимизированной для памяти таблице
Table_MO7осуществляется с помощьюSERIALIZABLEуровня изоляции.Table_MO6не имеет специфического уровня изоляции, так как операции вставки всегда согласованы и выполняются, фактически, в условиях сериализуемой изоляции.
-- Different isolation levels for
-- disk-based tables versus memory-optimized tables,
-- within one explicit transaction.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
BEGIN TRANSACTION;
-- Table_D1 is a traditional disk-based table, accessed using READ COMMITTED isolation.
SELECT *
FROM Table_D1;
-- Table_MO6 and Table_MO7 are memory-optimized tables.
-- Table_MO7 is accessed using SERIALIZABLE isolation,
-- while Table_MO6 does not have a specific isolation level.
INSERT INTO Table_MO6
SELECT *
FROM Table_MO7 WITH (SERIALIZABLE);
COMMIT TRANSACTION;
Ограничения
Транзакции между базами данных не поддерживаются для таблиц, оптимизированных для памяти. Если транзакция обращается к таблице, оптимизированной для памяти, транзакция не может получить доступ к другой базе данных, за исключением следующих:
-
tempdbБаза данных. - Доступ к
masterбазе данных только для чтения.
-
Распределенные транзакции не поддерживаются. При использовании
BEGIN DISTRIBUTED TRANSACTIONтранзакция не может получить доступ к оптимизированной для памяти таблице.
Нативно скомпилированные хранимые процедуры
Во встроенной процедуре
ATOMICблок должен объявить уровень изоляции транзакций для всего блока, например:... BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, ...) ...Не удается включить явные инструкции управления транзакциями в текст собственной хранимой процедуры. Утверждения, такие как
BEGIN TRANSACTIONиROLLBACK TRANSACTIONзапрещены.Дополнительные сведения об управлении транзакциями с
ATOMICблоками см. в разделе "Атомарные блоки" в машинных процедурах.