Почему вставляют блок операторов TSQL, когда уровень изоляции транзакции для другой транзакции является сериализуемым с неконфликтующим фильтром?

Question

Почему вставляют блок операторов TSQL, когда уровень изоляции транзакции для другой транзакции является сериализуемым с неконфликтующим фильтром?

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

Сценарий таблицы

CREATE TABLE [dbo].[dummy](
    [firstname] [char](20) NULL,
    [lastname] [char](20) NULL
) ON [PRIMARY]

GO

Сессия - 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin tran
select * from dummy where firstname = 'abc'

Сессия - 2

insert into dummy values('lmn', 'lmn') -- Why this blocks?

5

sql-server isolation-level

задан Anand Patel 9 July 2010 в 10:06

2 ответа

From http://msdn.microsoft.com/en-us/library/ms173763.aspx

SERIALIZABLE Указывает следующее:

Транзакции не могут читать данные, которые были изменены, но еще не зафиксированы другими транзакциями.

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

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

0

ответ дан 13 December 2019 в 22:01

Другие вопросы по тегам:

sql-server isolation-level

Похожие вопросы:

score 10 · Accepted Answer

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

Из Блокировка диапазона клавиш в BOL

Прежде, чем может произойти блокировка диапазона клавиш, следующие условия должны быть выполнено:

Уровень изоляции транзакции должен быть установлен на SERIALIZABLE .

Обработчик запросов должен использовать индекс для реализации фильтра диапазона. предикат. Например, ГДЕ предложение в инструкции SELECT может установить условие диапазона с этим предикат: ColumnX МЕЖДУ N'AAA 'И N'CZZ '. Замок с диапазоном ключей может быть приобретается, если ColumnX покрывается индексный ключ.

Не существует подходящего индекса для принятия блокировок RangeS-S , поэтому, чтобы гарантировать сериализуемую семантику, SQL Server необходимо заблокировать всю таблицу.

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

CREATE CLUSTERED INDEX [IX_FirstName] ON [dbo].[dummy] ([firstname] ASC)

... вы обнаружите, что все еще заблокированы!

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

Вы можете понять, почему, запустив следующую команду

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

SELECT *
FROM   dummy
WHERE  firstname = 'abc'

SELECT resource_type,
       resource_description, 
       request_mode
FROM   sys.dm_tran_locks
WHERE  request_session_id = @@SPID

COMMIT

Returns

+---------------+----------------------+--------------+
| resource_type | resource_description | request_mode |
+---------------+----------------------+--------------+
| DATABASE      |                      | S            |
| OBJECT        |                      | IS           |
| PAGE          | 1:198                | IS           |
| KEY           | (ffffffffffff)       | RangeS-S     |
+---------------+----------------------+--------------+

, SQL Server не просто снимает блокировку диапазона именно для того диапазона, который вы указываете в своем запросе.

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

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

Как описано здесь для предиката равенства по уникальному или неуникальному индексу

Если ключ не существует, то блокировка «диапазона» берется на Ключ «следующий» как для уникального, так и для неуникального индекса. Если клавиша «следующий» не существует, то для значения «бесконечность» выполняется блокировка диапазона.

Таким образом, с пустой таблицей SELECT по-прежнему блокирует весь индекс.Вам также необходимо предварительно вставить строку между abc и lmn , и тогда ваша вставка будет успешной.

insert into dummy values('def', 'def')