SQL Server - путаница с сериализуемым уровнем изоляции

Я прочитал статью (https://www.simple-talk.com/sql/t-sql-programming/questions-about-t-sql-transaction-isolation-levels-you-were-too-shy-to-ask/), и у меня есть вопрос в соответствии с:

«SERIALIZABLE: запрос в текущей транзакции не может считать данные, измененные другой транзакцией, которая еще не зафиксирована. Никакая другая транзакция не может изменять данные, считываемые текущей транзакцией, до ее завершения, и никакая другая транзакция не может вставлять новые строки, которые соответствовали бы условия поиска в текущей транзакции до ее завершения. В результате уровень изоляции Serializable предотвращает грязные чтения, неповторяемые чтения и фантомные чтения. Однако он может оказать наибольшее влияние на производительность по сравнению с другими уровнями изоляции».

Меня смущает вставка новых строк, которые не соответствуют условию поиска из 1 сеанса/запроса. Пример ниже:

Предположим, у меня есть таблица

EmpID   FirstName
1       john
2       new employee
3       A new employee

И запросы в отдельных вкладках:

--session 1----------------------------------
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

    SELECT FirstName FROM EmployeeInfo
    WHERE FirstName like 'new empl%'

    WAITFOR DELAY '00:00:10'  

    SELECT FirstName FROM EmployeeInfo
    WHERE FirstName like 'new empl%'

ROLLBACK TRANSACTION;

---------session 2---------------------------
begin transaction;

    UPDATE EmployeeInfo
    SET FirstName = 'frank'
    WHERE EmpID = 1;

commit transaction;

-----session 3----
insert into EmployeeInfo values('A new employe 2')

Я выполняю запросы один за другим: сеанс 1, сеанс 2, сеанс 3. Я ожидал, что сеанс 1 не остановит выполнение сеанса 2 и сеанса 3, потому что обновления и вставки из этого сеанса не выполняют условие поиска, которое используется в первом запросе. Однако в результатах я вижу, что сеанс 1 должен быть завершен (откат) до выполнения сеанса 2 и сеанса 3.

Однако, пока я использую другое условие поиска в сеансе 1, как показано ниже:

--session 1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

SELECT EmpID, FirstName FROM EmployeeInfo
WHERE EmpID = 2

WAITFOR DELAY '00:00:10'  

SELECT FirstName FROM EmployeeInfo

ROLLBACK TRANSACTION;

затем сеанс 2 и сеанс 3 завершаются независимо от сеанса 1. Почему? Почему нравится вставка блока условий, а "=" нет?

Отредактировано: 1. На EmpID есть только первичный ключ.


person user3883406    schedule 20.05.2017    source источник
comment
Каково определение таблицы, включая индексы?   -  person Martin Smith    schedule 20.05.2017


Ответы (1)


Это связано с тем, что «никакая другая транзакция не может вставлять новые строки, соответствующие условию поиска в текущей транзакции, пока она не завершится». И SQL Server обеспечивает это, устанавливая блокировки диапазона, которые предотвращают конфликтную вставку.

Если у вас есть индекс для EmployeeInfo.FirstName, SQL может использовать узкую блокировку, чтобы обеспечить это. Но без индекса SQL блокирует любые вставки. Кроме того, если бы предикат запроса SELECT не поддерживался с индексом, он блокировал бы все вставки.

Вы можете проверить текущее состояние замков с помощью:

select @@spid this_session, *
from sys.dm_tran_locks

. И обратите внимание, что такое поведение делает SERIALIZABLE не очень полезным уровнем изоляции. И вам действительно следует использовать только READ COMMITTED и SNAPSHOT, возможно, добавляя подсказки блокировки для определенных транзакций.

person David Browne - Microsoft    schedule 20.05.2017
comment
Не могли бы вы расширить свое утверждение о том, что такое поведение делает serializable не очень полезным? Я считаю, что это очень полезный уровень изоляции, и я хотел бы узнать больше о том, что привело вас к противоположному выводу. - person SqlZim; 20.05.2017
comment
Этот случай действительно показательный. Простой запрос SELECT заблокировал вставки во всю таблицу по не столь очевидной причине. Таким образом, это значительно снижает параллелизм в вашем приложении. И неприятный побочный эффект этого заключается в том, что он создает множество возможностей для взаимоблокировок. И в этом нет большого преимущества, так как поведение изоляции, которое оно гарантирует, не очень полезно. - person David Browne - Microsoft; 20.05.2017
comment
Это больше похоже на то, что вы говорите, что он не очень полезен в качестве уровня изоляции по умолчанию. Это то, что вы имели в виду? - person SqlZim; 20.05.2017
comment
Мне действительно не нравится изменять уровень изоляции вообще. Я думаю, что проще и эффективнее использовать подсказки блокировки для отдельных запросов по мере необходимости внутри транзакции READ COMMITTED. - person David Browne - Microsoft; 20.05.2017
comment
Кажется, теперь я понимаю, что вы имели в виду. Таким образом, вы говорите, что serializable - это не очень полезный уровень изоляции при использовании set transaction isolation level serializable в качестве настройки уровня соединения или в процедуре, а не просто при использовании таких подсказок, как with (serializable). Это правильно? - person SqlZim; 20.05.2017
comment
Правильно. И я использую сериализуемую подсказку только с одновременными операторами MERGE. Использование UPDLOCK встречается гораздо чаще, так как предотвращает одновременные чтения с UPDLOCK. - person David Browne - Microsoft; 20.05.2017