Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Почему возникает блокировка  [new]
Юстас
Member

Откуда:
Сообщений: 130
Написал пример с возникновением взаимоблокировки. Теперь сам не могу разобраться. :-)
В Management Studio в первом окне выполняю скрипт, который создает достаточно большую таблицу (с маленькой блокировок не возникает). Жду пока заполнится и заблокируется таблица
---------------------------------------------------------
create table t(rec int, primary key(rec))
declare @i int set @i=1 while @i<=250000
begin
insert into t (rec) select @i; set @i=@i+1;
end
go
select * from t
go
begin tran
update t set rec=rec from t (tablockx) where 1=0
--commit tran
---------------------------------------------------------
Затем во втором и третьем окнах запускаю один и тот же скрипт, который не стартует из-за того, что в первом окне таблица t заблокирована:
---------------------------------------------------------
begin tran
delete from t where rec=1
select * from t
commit tran
---------------------------------------------------------
После этого в первом окне завершаю транзакцию, чтобы завершить блокировку и чтобы заработали скрипты во втором и третьем окнах. Один и них отрабатывает нормально, а второй становится жертвой блокировки.

Бужу благодарен тому, кто разъяснит почему.
15 мар 12, 18:40    [12256181]     Ответить | Цитировать Сообщить модератору
 Re: Почему возникает блокировка  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Юстас,

select @@VERSION

У меня все нормально отрабатывает, один из скриптов удаляет 1 запись, выбирает остальные, второй - удаляет ноль, выбирает остальные.
15 мар 12, 19:02    [12256295]     Ответить | Цитировать Сообщить модератору
 Re: Почему возникает блокировка  [new]
_Алекс__2u
Guest
Юстас,

1: row update lock
2: row update lock
1: tab sh lock (blocked by 2 upd)
2: tab sh lock (blocked by 1 upd)
15 мар 12, 19:06    [12256325]     Ответить | Цитировать Сообщить модератору
 Re: Почему возникает блокировка  [new]
AndyD
Member

Откуда:
Сообщений: 30
Если rec одинаковое, то наврядли получится получить дидлок.
Просто, кто-то один из них успеет наложить блокировку, а второй будет ждать освобождения ресурса

Вот если разнести их на достаточно большой промежуток (поставить rec=1 и rec=10000, к примеру), то дидлок появится
15 мар 12, 19:09    [12256336]     Ответить | Цитировать Сообщить модератору
 Re: Почему возникает блокировка  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
AndyD
Если rec одинаковое, то наврядли получится получить дидлок.
Просто, кто-то один из них успеет наложить блокировку, а второй будет ждать освобождения ресурса

Вот если разнести их на достаточно большой промежуток (поставить rec=1 и rec=10000, к примеру), то дидлок появится
Дедлок здесь в принципе нереален, потому что обращение идет к одной таблице. дедлок - это когда одна транзакция залочила одну таблицу и хочет получить доступ ко второй, а вторая транцакция залочила вторую таблицу, и хочет получить доступ к первой. Такая ситуация разрешиться сама собой не может и решается SQL Server-ом.
15 мар 12, 19:13    [12256355]     Ответить | Цитировать Сообщить модератору
 Re: Почему возникает блокировка  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
_Алекс__2u
Юстас,

1: row update lock
2: row update lock
1: tab sh lock (blocked by 2 upd)
2: tab sh lock (blocked by 1 upd)
Если моя память не спит с другим, то вторая сессия не сможет наложить row update lock на строки, которые уже заблокированы update lock'ом.
15 мар 12, 19:14    [12256358]     Ответить | Цитировать Сообщить модератору
 Re: Почему возникает блокировка  [new]
AndyD
Member

Откуда:
Сообщений: 30
Minamoto
Дедлок здесь в принципе нереален, потому что обращение идет к одной таблице. дедлок - это когда одна транзакция залочила одну таблицу и хочет получить доступ ко второй, а вторая транцакция залочила вторую таблицу, и хочет получить доступ к первой. Такая ситуация разрешиться сама собой не может и решается SQL Server-ом.


К сожалению, вы не правы:)
Дидлоки на записях одной таблице - это как раз иллюстритуется этим примером.
Вот то, что разрешает их скуль - это да.

Разумеется, RCSI в данном случае не используется
15 мар 12, 19:18    [12256374]     Ответить | Цитировать Сообщить модератору
 Re: Почему возникает блокировка  [new]
_Алекс__2u
Guest
Minamoto,

та да. id один, еще проще. и возможно не tab, а pk для начала.
15 мар 12, 19:25    [12256398]     Ответить | Цитировать Сообщить модератору
 Re: Почему возникает блокировка  [new]
Ctrl-C
Guest
профайлер Вам в помощь:
Картинка с другого сайта.
15 мар 12, 20:01    [12256526]     Ответить | Цитировать Сообщить модератору
 Re: Почему возникает блокировка  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
AndyD
Minamoto
Дедлок здесь в принципе нереален, потому что обращение идет к одной таблице. дедлок - это когда одна транзакция залочила одну таблицу и хочет получить доступ ко второй, а вторая транцакция залочила вторую таблицу, и хочет получить доступ к первой. Такая ситуация разрешиться сама собой не может и решается SQL Server-ом.


К сожалению, вы не правы:)
Дидлоки на записях одной таблице - это как раз иллюстритуется этим примером.
Вот то, что разрешает их скуль - это да.

Разумеется, RCSI в данном случае не используется

Кому иллюстрируется? У меня этот пример исполняется корректно, без блокировок.
15 мар 12, 20:22    [12256586]     Ответить | Цитировать Сообщить модератору
 Re: Почему возникает блокировка  [new]
iljy
Member

Откуда:
Сообщений: 8711
Minamoto,

вы серьезно считаете, что для дедлока нужно 2 таблицы? Счастливый человек!
1я сессия:
use tempdb
create table t1 (id int primary key, val int)

insert t1
select number, number from master..spt_values
where type ='P' and number between 1 and 1000
go
begin tran

update t1 set val = val+1 where id = 1

waitfor delay '0:0:10'

update t1 set val = val+1 where id = 10
commit

2я сессия
begin tran

update t1 set val = val+1 where id = 10

update t1 set val = val+1 where id = 1
commit
15 мар 12, 20:32    [12256645]     Ответить | Цитировать Сообщить модератору
 Re: Почему возникает блокировка  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Minamoto
дедлок - это когда одна транзакция залочила одну таблицу и хочет получить доступ ко второй, а вторая транцакция залочила вторую таблицу, и хочет получить доступ к первой.
Т.е. когда одна транзакция залочила одну запись и хочет получить доступ ко второй, а вторая транцакция залочила вторую запись, и хочет получить доступ к первой, это называется как-то по-другому?
15 мар 12, 20:33    [12256654]     Ответить | Цитировать Сообщить модератору
 Re: Почему возникает блокировка  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Хорошо, хорошо :)

Но в приведенном в первом посте примере удаляется одна и та же запись, поэтому дедлока быть не может. Или я не прав? :)
15 мар 12, 20:44    [12256697]     Ответить | Цитировать Сообщить модератору
 Re: Почему возникает блокировка  [new]
Юстас
Member

Откуда:
Сообщений: 130
Изначально я делал пример немного другой. Одновременно запускались немного разные скрипты.
Сессия 1:
begin tran
delete from t where rec=1
select * from t
commit tran

Сессия 2:
begin tran
delete from t where rec=2
select * from t
commit tran

В результате получалось что в певой сессии блокировалась (для удаления) запись rec=1, а во второй сессии запись rec=2.
Затем в первой сессии производилась попытка выборки всех записей, но поскольку запись с rec=2 была заблокирована, то первый скрипт вставал в режим ожидания разблокировки. В это время второй скрипт пытался также сделать выборку всех запией , но натыкался на блокировку записи rec=1 первым скриптом и тоже вставал в режим ожидания. Во всяком случае я так понимал механизм возникновения взаимоблокировки в случае с разными скриптами и здесь у меня вопросов не возникало. Блокировка возникала всегда и было понятно почему.

Затем, в качестве эксперимента, я запустил в разных сессиях одинаковый скрипт, который удалял rec=1 (как описано в первом посте). Каково же было моё удивление, когда также возникла взаимоблокировка. Причем она возникает нерегулярно. Т.е. с увеличением числа запусков для возникновения взаимоблокировки требуется все большая таблица. Те, у кого взаимоблокировки не возникали - попробуйте увеличить размер таблицы t.

В случае из первого поста в профайлере, у меня такая же картинка как у Ctrl-C
15 мар 12, 23:05    [12257300]     Ответить | Цитировать Сообщить модератору
 Re: Почему возникает блокировка  [new]
AndyD
Member

Откуда:
Сообщений: 30
Точно, у меня тоже получилось воспроизвести

Вот что происходит, по-моему


1 процесс блокирует всю таблицу, но изменения не вносит. Страницы находится в памяти, но не помечены как измененные.
2 и 3-й процессы при выполнении скрипта на уделение запрашивают блокировку IX на таблицу и ожидают снятия блокировки первым процессом

После снятия блокировки 2 и 3-й процессы одновременно накладывают блокировку IX на таблицу, затем блокировку IX на страницу.
Тот кто шустрее (или более везучий:), пусть это будет 2-й) усперавает первым наложит X блокировку на запись. Другой (3-й) так же пытается наложит X-блокировку, но уходит в таймаут.

Второй процесс, после удачного наложения блокировки, удаляет запись и снимает блокировку со страницы и таблицы (IX)

На этот момент имеем.
2-й процесс удалил запись и снял все блокировки.
3-й процесс стартовал удаление, держит блокировки IX на таблице и странице и висит в ожидании блокировки X на записи


Второй процесс начинает выборку из таблицы.
Так как запись изменилась, то страница помечена как изменная.
Теперь он пытается наложить S блокировку на страницу
Но так как 3-й процесс продолжает удерживать IX блокировку этой же страцы, то 2-й процесс отправляется в таймаут

Бинго! Дидлок.
3-й процесс отваливается.

Все это - с отключенным RCSI

К сообщению приложен файл. Размер - 75Kb
16 мар 12, 10:15    [12258318]     Ответить | Цитировать Сообщить модератору
 Re: Почему возникает блокировка  [new]
iljy
Member

Откуда:
Сообщений: 8711
На самом деле, чтобы получить 100% дедлок, достаточно модифицировать скрипт ТС вот так:
begin tran
delete from t where rec=1
waitfor delay '0:0:1'
select * from t
commit tran

Иначе во многих случях одна из транзакций успевает наложить SIX на страницу, после чего вторая вполне закономерно ждет ее снятия для наложения IX.
16 мар 12, 11:12    [12258601]     Ответить | Цитировать Сообщить модератору
 Re: Почему возникает блокировка  [new]
Юстас
Member

Откуда:
Сообщений: 130
iljy, попробовал ваш скрипт. Если таблица t - маленькая (10 записей), то взаимоблокировки не происходит.

AndyD, Правильно ли я понял их Вашего объяснения, что взаимоблокировка возникает когда второй процесс пытается наложить S-блокировку и ему это не удается, из-за того, что третьим процессом уже наложена IX-блокировка, которая не может разрешиться до тех пор пока второй процесс не завершит транзакцию? И иногда, когда таблица маленькая, то второй процесс успевает все сделать до наложения третьим процессом IX-блокировки?
16 мар 12, 11:46    [12258939]     Ответить | Цитировать Сообщить модератору
 Re: Почему возникает блокировка  [new]
iljy
Member

Откуда:
Сообщений: 8711
Юстас,

а вы на блокировки смотрели? Естественно не происходит, потому что в этом случае сразу накладывается X на таблицу. Вы подсказку TABLOCK если поставите - дедлока тоже не будет.
16 мар 12, 12:05    [12259160]     Ответить | Цитировать Сообщить модератору
 Re: Почему возникает блокировка  [new]
AndyD
Member

Откуда:
Сообщений: 30
Юстас
iljy, попробовал ваш скрипт. Если таблица t - маленькая (10 записей), то взаимоблокировки не происходит.

AndyD, Правильно ли я понял их Вашего объяснения, что взаимоблокировка возникает когда второй процесс пытается наложить S-блокировку и ему это не удается, из-за того, что третьим процессом уже наложена IX-блокировка, которая не может разрешиться до тех пор пока второй процесс не завершит транзакцию? И иногда, когда таблица маленькая, то второй процесс успевает все сделать до наложения третьим процессом IX-блокировки?


По поводу большой таблицы - привильно

По поводу маленькой - там не происходит эскалации блокировки до уровня страницы
Записей немного, поэтому S-блокировки накладываются на них, а не на страницу.
На страницу накладнываетс IS блокировка, которая совместима с IX

Т.е. до момента выборки из таблицы сценарий одинаковый с тем, что я привел выше.
Но затем, процесс 2 накладывает IS блокировку на страницу
После успешного завершения этой операции он начинает последовательно накладнывать/снимать S-блокировки на записи.
После завершения этих операций транзакция завершается, процесс 3 снимается с таймаута и начинает работать, аналогичного процессу 2
16 мар 12, 12:35    [12259404]     Ответить | Цитировать Сообщить модератору
 Re: Почему возникает блокировка  [new]
iljy
Member

Откуда:
Сообщений: 8711
AndyD,

вы неправы. Для маленькой таблицы сразу (при удалении) накладывается X на таблицу, поэтому в дальнейшем никаких проблем не воникает.
16 мар 12, 12:56    [12259572]     Ответить | Цитировать Сообщить модератору
 Re: Почему возникает блокировка  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
iljy
Юстас,

а вы на блокировки смотрели? Естественно не происходит, потому что в этом случае сразу накладывается X на таблицу. Вы подсказку TABLOCK если поставите - дедлока тоже не будет.

Сумел воспроизвести ошибку на таблице 25000 записей, для ухода от дедлока помогает хинт на пейджлок при удалении.

Почему то одна транзакция накладывает блокировку на строку, а вторая - на страницу, и где-то после этого происходит дедлок.
16 мар 12, 12:58    [12259588]     Ответить | Цитировать Сообщить модератору
 Re: Почему возникает блокировка  [new]
AndyD
Member

Откуда:
Сообщений: 30
iljy,

Тогда уж сиквел неправ

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
Copyright (c) Microsoft Corporation
Developer Edition (64-bit)

К сообщению приложен файл. Размер - 148Kb
16 мар 12, 13:04    [12259634]     Ответить | Цитировать Сообщить модератору
 Re: Почему возникает блокировка  [new]
Юстас
Member

Откуда:
Сообщений: 130
Вроде бы разобрался в том, что пишут AndyD и iljy. Отдельно спасибо AndyD за обстоятельность и подробность ответов.

Но как же все происходит на самом деле, ведь иногда блокировки не возникает и при t, содержащей 1'000'000 записей? Если вставить оператор waitfor delay '0:0:1' то блокировка возникает и при 100'000 записях, но опять пропадает при 10'000.
16 мар 12, 13:59    [12260196]     Ответить | Цитировать Сообщить модератору
 Re: Почему возникает блокировка  [new]
AndyD
Member

Откуда:
Сообщений: 30
Юстас,

Думаю, если так модифицировать запрос, то дидлоки и при 10 записях появятся
begin tran
delete from t where rec=1
waitfor delay '0:0:1'
select * from t with (paglock)
commit tran
16 мар 12, 15:26    [12261068]     Ответить | Цитировать Сообщить модератору
 Re: Почему возникает блокировка  [new]
Юстас
Member

Откуда:
Сообщений: 130
AndyD, проверил Ваш вариант на маленькой таблице. Взаимоблокировка происходит. Принимаю Ваше объяснение происходящего.
Дополнительно хочу спросить, поможет ли подсказка pagelock, установленная при операторе delete избежать взаимоблокировки? Она не даст третьему процессу возможность наложить на таблицу блокировку ix? Почему?
16 мар 12, 20:02    [12263316]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить