обновить с помощью rowlock на сервере MSSQL

Я пытался понять ROWLOCK в SQL-сервере, чтобы обновить запись после ее блокировки. Вот мое наблюдение и я хотел бы получить подтверждение, похож ли ROWLOCK на блокировку таблицы или страницы, или я не пробовал это правильно. ROWLOCK должен быть блокировкой только для строки, а не для таблицы или страницы.

Вот что я пробовал:

Я создал простую таблицу: row_lock_temp_test с двумя столбцами ID и Имя без PK или индекса. Теперь я открываю SQL Server, два разных клиента, но с одинаковыми учетными данными, и пробую выполнить набор следующих запросов:

Клиент 1:

1: BEGIN TRANSACTION;
2: update row_lock_temp_test set name = 'CC' where id = 2
3: COMMIT

Клиент 2:

1: BEGIN TRANSACTION;
2: update row_lock_temp_test set name= 'CC' where id = 2
3: COMMIT

Я выполнил запросы 1, 2 на C-1 и перешел к C-2 и выполнил те же запросы, оба клиента выполнили запросы, а затем я зафиксировал транзакцию, все в порядке.

Затем я добавил RowLock для обновления запроса,

C-1

  1: BEGIN TRANSACTION;
  2: update row_lock_temp_test WITH(rowlock) set name = 'CC' where id = 2
  3: COMMIT

C-2

1: BEGIN TRANSACTION;
2: update row_lock_temp_test WITH(rowlock) set name = 'CC' where id = 2
3: COMMIT

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

здесь я попробовал другой сценарий для выполнения того же набора запросов с идентификатором строки = 3

C-2

 1: BEGIN TRANSACTION;
 2: update row_lock_temp_test WITH(rowlock) set name = 'CC' where id = 3
 3: COMMIT

Я выполнил первые два запроса в C-1, а затем перешел к выполнению первых двух запросов из C-2, идентификатор строки отличается у обоих клиентов, но все же запрос в C-2 застрял. Это означает, что при обновлении запроса с id = 2 он заблокировал страницу или таблицу, я ожидал блокировки строки, но это похоже на блокировку страницы или таблицы.

Я также пробовал использовать xlock, HOLDLOCK и UPDLOCK с разными комбинациями, но он всегда блокирует таблицу. есть ли возможность заблокировать только строку.

Выбор и вставка работают должным образом.

Заранее спасибо.


sql-server rowlocking
person NoNaMe    schedule 11.07.2020    source источник
comment
Вы можете проверить удерживаемые блокировки с помощью sys.dm_tran_locks, и вы должны добавить первичные ключи в таблицу. В противном случае каждый запрос должен будет читать (и, возможно, блокировать) каждую строку.   -  person David Browne - Microsoft    schedule 11.07.2020

Ответы (1)


arrow_upward
3
arrow_downward

Подсказки блокировки - это всего лишь подсказки. Вы не можете заставить SQL взять определенный тип блокировки.

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

select      tl.request_session_id,
            tl.resource_type,
            tl.request_mode,
            tl.resource_description,
            tl.request_status
from        sys.dm_tran_locks   tl
join        sys.partitions      pt  on  pt.hobt_id = tl.resource_associated_entity_id
join        sys.objects         ob  on  ob.object_id = pt.object_id
where       tl.resource_database_id = db_id()
order by    tl.request_session_id

Хорошо, давайте запустим некоторый код в окне запроса SSMS:

create table t(i int, j int);
insert t values (1, 1), (2, 2);

begin tran;
update t with(rowlock) set j = 2 where i = 1;

Откройте второе окно SSMS и запустите это:

begin tran;
update t with(rowlock) set j = 2 where i = 2;

Вторая казнь будет заблокирована. Почему?

Запустите запрос блокировки в третьем окне и обратите внимание, что есть две строки с resource_type из RID, одна с status предоставления, а другая с status ожидания. Мы перейдем к биту RID через секунду. Также посмотрите на столбец resource_description для этих строк. То же значение.

Хорошо, а что такое resource_description? Это зависит от theresource_type. Но для нашего RID он представляет: идентификатор файла, затем идентификатор страницы, затем идентификатор строки (также известный как слот). Но почему оба исполнения блокируют слот строки 0? Разве они не должны пытаться заблокировать разные строки? Ведь мы обновляем разные строки.

Дэвид Браун дал ответ: чтобы найти правильную строку для обновления, SQL должен просканировать всю таблицу, потому что нет индекса, указывающего, сколько строк там, где i = 1. При сканировании каждой строки потребуется блокировка обновления. Почему требуется блокировка обновления для каждой строки? Ну это не для обновления, так сказать. Для этого потребуется эксклюзивная блокировка. Блокировки обновлений почти всегда используются для предотвращения взаимоблокировок.

Итак, первый запрос просканировал строки, взяв U блокировку для каждой строки. Конечно, он нашел строку, которую хотел обновить сразу, в слоте 0, и взял X блокировку. И у него все еще есть эта X блокировка, потому что мы еще не зафиксировали.

Затем мы запустили второй запрос, который также должен просканировать все строки, чтобы найти нужную. Он начался с попытки снять U блокировку в первой строке и был заблокирован. Блокировка X нашего первого запроса блокирует его.

Итак, вы видите, что даже при блокировке строк ваш второй запрос все еще заблокирован.

Хорошо, давайте откатим запросы и посмотрим, что произойдет, если первый запрос обновит вторую строку, а второй запрос обновит первую строку? Это работает? Неа! Потому что в SQL до сих пор нет возможности узнать, сколько строк соответствует предикату. Таким образом, первый запрос берет свою блокировку обновления в слоте 0, видит, что его не нужно обновлять, берет свою блокировку обновления в слоте 1, видит правильное значение для i, берет свою эксклюзивную блокировку и ждет, пока мы зафиксируем.

Запрос 2 приходит, берет блокировку обновления в слоте 0, видит желаемое значение, берет свою исключительную блокировку, обновляет значение , а затем пытается принять блокировку обновления в слоте 1, потому что это также может иметь желаемую ценность.

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

Решение в этом случае? Добавьте индекс в столбец i. В данном случае это, вероятно, первичный ключ. Затем вы можете выполнять обновления в любом порядке. Запрос на блокировку строк в этом случае не имеет значения, потому что SQL не знает, сколько строк соответствует предикату. Но даже если вы попытаетесь принудительно заблокировать строку в какой-то ситуации, и даже с первичным ключом или соответствующим индексом, SQL все равно может выбрать эскалацию типа блокировки, потому что блокировка может быть более эффективной. целую страницу или всю таблицу, чем для блокировки и разблокировки отдельных строк.

person allmhuran    schedule 12.07.2020