эксклюзивная блокировка и общая блокировка для оператора select — SQL Server

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

Сценарий 1 Шаг 1.1

create table Tmp(x int)
insert into Tmp values(1)

Шаг 1.2 – сеанс 1

begin tran
set transaction isolation level serializable 
select * from Tmp

Шаг 1.3 – сеанс 2

select * from Tmp

Даже если первая сессия не завершена, сессия 2 сможет прочитать таблицу tmp. Я думал, что у Tmp будет эксклюзивная блокировка, а общая блокировка не должна выдаваться для выбора запроса в сеансе 2. Но этого не происходит. Я убедился, что уровень изоляции по умолчанию READ COMMITED.

Заранее спасибо за помощь в понимании этого поведения.

РЕДАКТИРОВАТЬ: Почему мне нужно выбрать эксклюзивную блокировку?

У меня есть SP, который фактически генерирует последовательные значения. Итак, поток —

  1. прочитать максимальные значения из таблицы и сохранить значение в переменных
  2. Обновить заданное значение таблицы = значение + 1

Эта SP выполняется параллельно несколькими тысячами экземпляров. Если два экземпляра выполняют SP одновременно, они будут считывать одно и то же значение и обновлять значение +1. Хотя я хотел бы иметь последовательное значение для каждого выполнения. Я думаю, что это возможно только в том случае, если select также является частью монопольной блокировки.


shared sql sql-server locking isolation
person UVData    schedule 08.08.2014    source источник
comment
это связано с некоторой оптимизацией, которую делает sql, обратитесь к этой ссылке. -by-others.aspx" rel="nofollow noreferrer">sqlblog.com/blogs/louis_davidson/archive/2006/12/13/. Если вы хотите, чтобы он работал правильно, используйте 2 вкладки.   -  person Aflred    schedule 03.12.2016

Ответы (1)


arrow_upward
0
arrow_downward

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

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

Если вы хотите, чтобы обычный SELECT получил эксклюзивную блокировку, вам нужно запросить ее:

select * from Tmp with (XLOCK)

или вам нужно выполнить оператор, который на самом деле требует монопольной блокировки:

update Tmp set x = x

Вашему первому сеансу нужна монопольная блокировка, поскольку она не изменяет данные. Если ваш первый (сериализуемый) сеанс завершился и либо откатился, либо был зафиксирован до того, как был запущен второй сеанс, результаты этого сеанса все равно будут такими же, потому что ваш первый сеанс не изменил данные - и поэтому "сериализуемый" характер сделки был правильным.

person Damien_The_Unbeliever    schedule 08.08.2014
comment
У меня есть SP, который фактически генерирует последовательные значения. Таким образом, поток - 1. прочитать максимальные значения из таблицы и сохранить значение в переменных 2. Обновить набор значений таблицы = значение + 1. Этот SP выполняется параллельно несколькими тысячами экземпляров. Если два экземпляра выполняют SP одновременно, они будут считывать одно и то же значение и обновлять значение +1. Хотя я хотел бы иметь последовательное значение для каждого выполнения. Я думаю, что это возможно только в том случае, если select также является частью монопольной блокировки. - person UVData; 08.08.2014
comment
Феноменально дорого с точки зрения использования ресурсов фактически генерировать последовательные числа без пробелов, особенно при наличии нескольких соединений. Вот почему встроенные генераторы (например, столбцов IDENTITY, а с 2012 года и последовательностей) не дают такой гарантии. Вы уверены, что вам нужна такая гарантия и вы не можете просто использовать встроенные функции? - person Damien_The_Unbeliever; 09.08.2014
comment
Я согласен с вами, у нас есть проблемы, но сейчас изменить дизайн невозможно. Но, тем не менее, мне кажется правильным вопрос, почему я не могу иметь эксклюзивную блокировку на операторе select? - person UVData; 11.08.2014
comment
Вы можете - я показываю в своем ответе, как это сделать - вы должны явно попросить об этом, используя подсказку XLOCK. - person Damien_The_Unbeliever; 11.08.2014
comment
Да, я пробовал это, и это работает. Однако я не могу понять, почему сериализуемый уровень изоляции транзакций не работает для оператора select. Есть ли разница между эксклюзивной блокировкой, полученной с использованием сериализуемого уровня изоляции транзакций и XLOCK. Должен ли я узнать, что установленный сериализуемый уровень изоляции транзакций не работает для операторов select, в то время как XLOXK работает? - person UVData; 12.08.2014
comment
Как я указал в первой части своего ответа, вам необходимо установить уровень изоляции до запуска транзакции, чтобы он имел какой-либо эффект, но даже если вы делаете обмен утверждения вокруг, это по-прежнему не имеет никакого эффекта, потому что ему нужна общая блокировка, даже в условиях сериализуемой изоляции, если ваша единственная активность - SELECT. - person Damien_The_Unbeliever; 12.08.2014