Transactions with Memory-Optimized Tables

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

В этой статье описаны все аспекты транзакций с таблицами, оптимизированными для памяти, и хранимыми процедурами, скомпилированными в коде.

Уровни изоляции транзакций в SQL Server применяются к таблицам, оптимизированным для памяти, и к таблицам на жестком диске по-разному, с использованием разных базовых механизмов. Понимание различий помогает программисту проектировать систему с высокой пропускной способностью. В любом случае необходимо обеспечить целостность транзакций.

Описание условий ошибок, относящихся к транзакциям в таблицах, оптимизированных для памяти, см. в разделе Обнаружение конфликтов и логика повторных попыток.

Общие сведения см. в разделе SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Различия между пессимистичным и оптимистичным подходами

Функциональные различия зависят от пессимистичного или оптимистичного подхода к целостности транзакций. В таблицах, оптимизированных для памяти, используется оптимистичный подход:

• При пессимистичном подходе возможные конфликты блокируются прежде, чем они возникнут. Система блокируется при выполнении инструкции и разблокируется, как только транзакция зафиксирована.

• В оптимистичном сценарии система обнаруживает конфликты сразу и проводит проверки во время фиксации.

  • В таблицах, оптимизированных для памяти, может возникать ошибка 1205, взаимоблокировка.

Оптимистичный подход требует меньше ресурсов и обычно более эффективен. Отчасти это связано с тем, что в большинстве приложений конфликты транзакций встречаются редко. Основное функциональное различие между пессимистическим и оптимистическим сценариями заключается в том, что если происходит конфликт, в пессимистическом сценарии нужно ждать, а в оптимистическом сценарии клиент должен повторить попытку выполнить транзакцию, которая завершается сбоем. Функциональные различия увеличиваются, если действует уровень изоляции REPEATABLE READ, и достигают максимума на уровне SERIALIZABLE.

Режимы инициации транзакций

SQL Server имеет следующие режимы запуска транзакций.

• Автоматическая фиксация . Начало простого запроса или инструкция DML неявно открывает транзакцию, а окончание инструкции неявно фиксирует эту транзакцию. Автофиксация используется по умолчанию.

  • В режиме автоматической фиксации обычно не требуется добавлять в условие FROM код табличного указания об уровне изоляции транзакции для таблицы, оптимизированной для памяти.

• Явный . Transact-SQL содержит код BEGIN TRANSACTION и может содержать код COMMIT TRANSACTION. В одной и той же транзакции могут одновременно выполняться сразу несколько инструкций.

  • В явном режиме необходимо использовать параметр базы данных MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT или создать код табличного указания об уровне изоляции транзакции для таблицы, оптимизированной для памяти, в предложении FROM.

• Неявный . Активен, пока действует код SET IMPLICIT_TRANSACTION ON. Возможно, лучше подошло бы имя IMPLICIT_BEGIN_TRANSACTION, поскольку единственная задача этого параметра — явное выполнение неявного кода BEGIN TRANSACTION перед каждой инструкцией UPDATE, если 0 = @@trancount. Таким образом, в конечном итоге именно ваш код T-SQL выдает явный код COMMIT TRANSACTION.

• Блок ATOMIC — все инструкции в блоках ATOMIC (атомарных блоках) всегда выполняются в рамках одной транзакции. При успешном выполнении фиксируются все действия атомарного блока, а при сбое все его действия откатываются. Использовать блок ATOMIC обязательно для всех хранимых процедур, скомпилированных в собственном коде.

Пример кода в режиме Explicit

В следующем интерпретированном сценарии Transact-SQL используется следующее:

• Явная транзакция.
• Таблица, оптимизированная для памяти, с именем dbo.Order_mo.
• Контекст для уровня изоляции транзакции READ COMMITTED.

В связи с этим в таблице, оптимизированной для памяти, должно присутствовать табличное указание. Оно должно вводить уровень изоляции SNAPSHOT или более высокий. В примера кода используется указание WITH (SNAPSHOT). Если оно удаляется, сценарий выдает ошибку 41368, при которой автоматическая повторная попытка недопустима:

Ошибка 41368

Доступ к оптимизированным для памяти таблицам с уровнем изоляции READ COMMITTED поддерживается только для транзакций с автоматической фиксацией. Он не поддерживается для явных или неявных транзакций. Обеспечьте поддерживаемый уровень изоляции для оптимизированной для памяти таблицы с помощью табличного указания, например WITH (SNAPSHOT).

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  
GO  

BEGIN TRANSACTION;  -- Explicit transaction.  

-- Order_mo  is a memory-optimized table.  
SELECT * FROM  
           dbo.Order_mo  as o  WITH (SNAPSHOT)  -- Table hint.  
      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. А в структурах данных, оптимизированных для памяти, для большей эффективности используются встроенные механизмы.

Уровни изоляции

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

Фазы и время существования транзакций

При работе с таблицей, оптимизированной для памяти, время существования транзакции можно разделить на несколько этапов, как показано на следующей схеме.

Описания фаз приводятся ниже.

Обычная обработка: фаза 1 (из 3)

• Этот этап включает выполнение всех запросов и инструкций DML в запросе.
• На этом этапе инструкции воспринимают версию таблиц, оптимизированных для памяти, в качестве логического времени начала транзакции.

Проверка: фаза 2 (из 3)

• Фаза проверки начинается с назначения времени окончания (тем самым транзакция помечается как логически завершенная). При таком завершении все изменения транзакции становятся видимы другим транзакциям, которые от нее зависят. Пока транзакция не зафиксирована, фиксация зависимых транзакций невозможна. Кроме того, транзакции с такими зависимостями не могут возвращать результирующие наборы клиенту. Клиент будет видеть только данные, успешно зафиксированные в базе.
• Этот этап включает уровни проверку параметров repeatable read и serializable. С уровнем изоляции repeatable read система проверяет наличие изменений в прочитанных транзакцией строках с момента прочтения. При проверке параметра serializable система проверяет, были ли какие-либо строки вставлены в диапазон данных, сканированный этой транзакцией. Согласно таблице в разделе уровней изоляции и конфликтов, проверки repeatable read и serializable могут происходить при использовании изоляции snapshot, чтобы подтвердить соответствие ограничений для уникальных и внешних ключей.

Обработка фиксации: фаза 3 (из 3)

• Во время фиксации изменения в надежных таблицах заносятся в журнал, а журнал записывается на диск. Затем управление возвращается клиенту.
• По окончании фиксации все зависимые транзакции уведомляются о том, что их можно фиксировать.

Как всегда, единицы работы в транзакции должны быть настолько минимальными и краткими, насколько это соответствует требованиям ваших данных.

Обнаружение конфликтов и логика повторных попыток

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

• Конфликты между параллельными транзакциями. Это конфликты обновления и сбои проверок. Они могут быть вызваны нарушением уровня изоляции транзакций или нарушением ограничений.
• Сбои зависимости. Они возникают, если транзакция, от которой зависят другие действия, не фиксируется, либо если количество зависимостей становится слишком большим.

Ниже перечислены условия ошибок, которые могут не позволить транзакциям получить доступ к таблицам, оптимизированным для памяти.

Логика повторных попыток

Если транзакция дает сбой из-за одного из вышеупомянутых условий, следует повторить транзакцию.

Логика повторных попыток может быть реализована как на стороне клиента, так и на стороне сервера. Общая рекомендация заключается в том, что нужно реализовать логику повторных попыток на стороне клиента, это более эффективно и позволяет работать с наборами результатов, возвращаемыми транзакцией, прежде чем произойдет сбой.

Пример кода повторной попытки 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 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; или
• выполняет хранимую процедуру, скомпилированную в собственном коде, если транзакция уже открыта (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 не имеет определенного уровня изоляции, поскольку вставляемые элементы отличаются единообразием и выполняются в основном с изоляцией serializable.

-- 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 Table_MO6
        SELECT * FROM Table_MO7 WITH (SERIALIZABLE);

COMMIT TRANSACTION;
go

Ограничения

• Транзакции между базами данных не поддерживаются с таблицами, оптимизированными для памяти. Если транзакция обращается к таблице, оптимизированной для памяти, она не может получить доступ ни к одной базе данных, кроме следующих:

  • база данных tempdb;
  • база данных master, доступная только для чтения.

• Распределенные транзакции не поддерживаются: если используется команда BEGIN DISTRIBUTED TRANSACTION, транзакции не удается осуществить доступ к оптимизированной для памяти таблице.

Скомпилированные в собственном коде хранимые процедуры

• В хранимой процедуре, скомпилированной в собственном коде, атомарный блок должен объявить уровень изоляции транзакций для всего блока, например:

  • ... BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, ...) ...

• В хранимой процедуре, скомпилированной в собственном коде, нельзя использовать явные инструкции управления транзакциями. BEGIN TRANSACTION и ROLLBACK TRANSACTION и т. д. запрещены.

• Дополнительные сведения о контроле над транзакциями с блоками ATOMIC см. в разделе Блоки ATOMIC

Другие ссылки по транзакциям

• SET IMPLICIT_TRANSACTIONS

• sp_getapplock (Transact-SQL)

• Уровни изоляции, основанные на управлении версиями строк, в компоненте Database Engine

• Управление устойчивостью транзакций