Рекомендации по уровням изоляции транзакций с таблицами Memory-Optimized

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

Требования к указанию уровня изоляции транзакций:

  • УРОВЕНЬ ИЗОЛЯЦИИ ТРАНЗАКЦИй — это обязательный параметр для блока ATOMIC, содержащего содержимое скомпилированной хранимой процедуры в собственном коде.

  • Из-за ограничений на использование уровня изоляции в транзакциях между контейнерами использование оптимизированных для памяти таблиц в интерпретируемых Transact-SQL часто сопровождается указанием табличного уровня, указывающего уровень изоляции, используемый для доступа к таблице. Дополнительные сведения о указаниях уровня изоляции и межконтейнерных транзакциях см. в разделе "Уровни изоляции транзакций".

  • Требуемый уровень изоляции транзакций должен быть явно объявлен. Невозможно использовать указания блокировки (например, XLOCK), чтобы гарантировать изоляцию определенных строк или таблиц в транзакции.

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

  • Длительные транзакции следует избегать с таблицами, оптимизированными для памяти. Такие транзакции повышают вероятность конфликтов и последующего прекращения транзакций. Длительная транзакция также откладывает сборку мусора. Чем дольше выполняется транзакция, тем больше In-Memory OLTP сохраняет недавно удаленные версии строк, что может снизить производительность поиска для новых транзакций.

Таблицы, основанные на дисках, обычно используют блокировку и блокирование для изоляции транзакций. Оптимизированные для памяти таблицы используют многоверсионность и обнаружение конфликтов, чтобы гарантировать изоляцию. Дополнительные сведения см. в разделе об обнаружении конфликтов, проверке и фиксации проверок зависимостей в транзакциях в таблицах Memory-Optimized.

Таблицы на диске позволяют многоверсионность с уровнями изоляции SNAPSHOT и READ_COMMITTED_SNAPSHOT. Для таблиц, оптимизированных для памяти, все уровни изоляции основаны на нескольких версиях, включая REPEATABLE READ и SERIALIZABLE.

Типы транзакций

Каждый запрос в SQL Server выполняется в контексте транзакции.

В SQL Server существует три типа транзакций:

  • Операции автокоммитирования. Если в этом сеансе нет активного контекста транзакций и неявные транзакции не установлены в положение ON, каждый запрос имеет собственный контекст транзакции. Транзакция начинается при запуске инструкции и завершается после завершения инструкции.

  • Явные транзакции. Пользователь начинает транзакцию, используя явные инструкции BEGIN TRAN или BEGIN ATOMIC. Транзакция завершается после соответствующих команд COMMIT и ROLLBACK или END (в случае атомарного блока).

  • Неявные транзакции. Если параметр IMPLICIT_TRANSACTIONS имеет значение ON, транзакция запускается неявно, когда пользователь выполняет инструкцию и не имеет активного контекста транзакции. Транзакция завершается с помощью явных операций COMMIT и ROLLBACK.

Базовая изоляция "READ COMMITTED" (уровень изоляции SQL)

READ COMMITTED — это уровень изоляции по умолчанию в SQL Server.

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

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

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

Руководство. Если требуется только гарантия изоляции READ COMMITTED, используйте изоляцию SNAPSHOT с скомпилированных хранимых процедур и для доступа к оптимизированным для памяти таблицам с помощью интерпретированного Transact-SQL.

Для транзакций автоматической фиксации уровень изоляции READ COMMITTED неявно сопоставляется с SNAPSHOT для таблиц, оптимизированных для памяти. Таким образом, если для параметра сеанса TRANSACTION ISOLATION LEVEL задано значение READ COMMITTED, необязательно указывать уровень изоляции с помощью указания таблицы при доступе к оптимизированным для памяти таблицам.

В следующем примере автономной транзакции показано соединение между оптимизированной для памяти таблицей Customers и обычной таблицей [Журнал заказов], в рамках произвольного пакета:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  
GO  
SELECT *   
FROM dbo.Customers AS c   
LEFT JOIN dbo.[Order History] AS oh   
    ON c.customer_id = oh.customer_id;

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

SET TRANSACTION ISOLATION LEVEL READ COMMITTED  
GO  
BEGIN TRAN  
SELECT * FROM dbo.Customers c with (SNAPSHOT)   
LEFT JOIN dbo.[Order History] oh   
    ON c.customer_id=oh.customer_id  
...  
COMMIT

Операционные различия

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

  • Реализация уровня изоляции READ COMMITTED для таблиц на основе дисков (при условии, что READ_COMMITTED_SNAPSHOT имеет значение OFF), использует блокировки для предотвращения конфликтов между средствами чтения и записи. Когда модуль записи начинает обновлять строку, он принимает блокировку и не освобождает блокировку, пока транзакция не будет зафиксирована. Все операции чтения блокируются и ожидают подтверждения транзакции записи.

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

    Директива: Приложения не могут полагаться на поведение блокировки. Если приложению требуется синхронизация между параллельными транзакциями, такая логика может быть реализована на уровне приложения или на уровне базы данных через sp_getapplock (Transact-SQL).

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

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

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

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

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

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

-- poll table  
WHILE NOT EXISTS (SELECT 1 FROM dbo.t1)  
BEGIN   
  -- if empty, wait and poll again  
  WAITFOR DELAY '00:00:01'  
END  
  
BEGIN TRANSACTION  
  DECLARE @id int  
  SELECT TOP 1 @id=id FROM dbo.t1 WITH (SNAPSHOT)  
  DELETE FROM dbo.t1 WITH (SNAPSHOT) WHERE id=@id  
  
  -- insert processing based on @id  
COMMIT

Указания по блокировке таблицы

Подсказки блокировки (подсказки таблиц (Transact-SQL)), такие как HOLDLOCK и XLOCK, можно использовать с таблицами на основе дисков, чтобы sql Server использовал больше блокировок, чем требуется для указанного уровня изоляции.

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

Подсказки блокировки не поддерживаются. Вместо этого объявите необходимые гарантии с помощью уровней изоляции транзакций. (NOLOCK поддерживается, так как SQL Server не принимает блокировки для оптимизированных для памяти таблиц. Обратите внимание, что, в отличие от таблиц на основе дисков, NOLOCK не подразумевает поведение READ UNCOMMITTED для таблиц, оптимизированных для памяти.)

См. также

Общие сведения о транзакциях в таблицах Memory-Optimized
Рекомендации по логике повторных попыток для транзакций в таблицах Memory-Optimized
Уровни изоляции транзакций

  • Last updated on