Sql Server - выбор при вставке в другую транзакцию дает неожиданные результаты

Я наткнулся на ситуацию, которая меняет мои знания о транзакциях и блокировке в принципе (я не очень-то знаю), и мне нужна помощь, чтобы понять это.

Скажем, у меня есть таблица вроде этого:

CREATE TABLE [dbo].[SomeTable](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[SomeData] [varchar](200) NOT NULL,
[Moment] [datetime] NOT NULL,
[SomeInt] [bigint] NOT NULL
) ON [PRIMARY]

и я запустил этот запрос «Вставить 1000 строк в транзакцию»:

BEGIN TRAN t1

DECLARE @i INT = 0

WHILE @i < 1000
BEGIN
    SET @i = @i + 1

    INSERT INTO [SomeTable] ([SomeData] ,Moment, SomeInt)
    VALUES (CONVERT(VARCHAR(255), NEWID()), getdate(), @i)

    WAITFOR DELAY '00:00:00:010'
END

COMMIT TRAN t1

Пока эта транзакция выполняется, я выполняю простой выбор:

SELECT Id, Moment, SomeData, SomeInt FROM [SomeTable]

Не всегда возможно воспроизвести его (по-видимому, зависит от таймингов), но иногда выбор запроса, после завершения транзакции вставки, возвращает менее 1000 строк. По моему невежеству я верил, что select всегда будет возвращать 1000 строк (учитывая, что уровень изоляции - Read Committed), но, очевидно, я неправильно понял, как работают транзакции и блокировка.

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

Итак, у меня есть следующие вопросы:

  1. Почему выбор не всегда возвращает все строки, совершенные транзакцией?
  2. Если это ожидаемое поведение, то лучший способ заставить его работать так, как я ожидал? В принципе, я хочу, чтобы выбор возвращал состояние таблицы после (или до) транзакции, а не некоторые полуфабрикаты. Изоляция снимка в настоящее время не является опцией. Полагаю, что TABLOCK делает работу, но есть ли лучшее решение? В реальной жизни у меня есть таблицы, которые я бы не хотел блокировать на этом уровне, если это не совсем необходимо.
  3. Почему включение индекса приводит к изменению этого поведения?

Спасибо заранее.

6 голосов | спросил poke 5 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowFri, 05 Sep 2014 22:29:43 +0400 2014, 22:29:43

1 ответ


12

Мне не удалось воспроизвести это после запуска кода несколько раз.

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

Таким образом, порядок операций (например)

  • Строки, вставленные в кучу на страницах 200, 207, 223
  • Оператор Select запускается и выполняет упорядоченное сканирование распределения. Находит, что первая страница 200 и заблокирована в ожидании блокировки строки.
  • Другие строки вставляются первой транзакцией. Некоторые из них выделяются на странице до 200. Вставьте транзакцию.
  • Блокировка строк отключена и продолжает распределенное упорядоченное сканирование. Строки ранее в файле пропущены.

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

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

SELECT [SomeData],
       Moment,
       SomeInt,
       file_id,
       page_id,
       slot_id
FROM   [SomeTable] 
/*Undocumented - Use at own risk*/
CROSS APPLY sys.fn_PhysLocCracker(%% physloc %%)
ORDER BY page_id, SomeInt

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

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

Поскольку прочитанное сообщение, как правило, освобождает блокировки, как только данные считываются, возможно при сканировании по индексу для чтения строк дважды или вообще нет (если индексный ключ обновляется параллельной транзакцией, вызывающей перемещение строки вперед или назад) См. Уровень готовности к чтению для более подробного обсуждения этого типа проблем.


Кстати, я изначально предполагал для индексированного случая, что индекс находился на одном из столбцов, который увеличивается относительно порядка вставки (любой из Id, Moment, SomeInt). Однако даже если кластеризованный индекс находится на случайном SomeData, проблема все равно не возникает.

Я пробовал

DBCC TRACEON(3604, 1200, -1) /*Caution. Global trace flag. Outputs lock info
                               on every connection*/

SELECT TOP 2 *,
             %%LOCKRES%%
FROM   [SomeTable] WITH(nolock)
ORDER BY [SomeData];

SELECT *,
       %%LOCKRES%%
FROM   [SomeTable]
ORDER BY [SomeData];

/*Turn off trace flags. Doesn't check whether or not they were on already 
  before we started, with TRACEOFF*/
DBCC TRACEOFF(3604, 1200, -1)

Результаты были ниже

введите описание изображения здесь>> </p>

<p> Второй набор результатов включает в себя все 1000 строк. Информация о блокировке показывает, что даже если она заблокирована, ожидая ресурса блокировки <code>---- +: = 4 =: + ----</code>, когда блокировка была выпущена,  не  просто продолжите сканирование с этой точки. Вместо этого он немедленно освобождает эту блокировку и получает блокировку строк в новой первой строке. </p>

<p> <img src =

ответил Martin Smith 6 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowSat, 06 Sep 2014 19:24:53 +0400 2014, 19:24:53

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

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132