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

Откуда:
Сообщений: 95
Всем привет.
На курсах 1С Эксперт говорят, что если в транзакции запрос выполняется со сканированием, то будет заблокирована вся таблица, даже если в результате запрос ничего не вернет, и в общем-то у меня и в мыслях не было подвергать это сомнению.
Но опытным путем выяснилось, что многое зависит от того какой индекс используется (кластерный или не кластерный), каким образом идет поиск (сканирование или поиск пои индексу), и с каким уровнем изоляции выполняется запрос (SERIALIZABLE или REPEATABLE READ).

Рассмотрим возможные варианты:
Сценарий 1.
Уровень изоляции: SERIALIZABLE
Тип поиска: Clustered Index Scan или Index Scan
Здесь все соответствует теории.
Если запрос выполняется с уровнем изоляции SERIALIZABLE, то вся таблица блокируется до конца транзакции, и неважно был ли это скан кластерного индекса или не кластерного. Если транзакция не закончилась, то нельзя изменить ни одно поле, ни одной строки таблицы.

Сценарий 2.
Уровень изоляции: REPEATABLE READ
Тип поиска: Clustered Index Scan
В этом случае, до конца транзакции будут заблокированы только те записи, которые запрос вернул, а остальные записи будут доступны для редактирования. Это уже противоречит теории, т.к. вроде бы должны быть заблокированы все записи таблицы.

Сценарий 3.
Уровень изоляции: REPEATABLE READ
Тип поиска: Index Scan
В этом случае, до конца транзакции будут заблокированы только те записи, которые были возвращены запросом, остальные будут доступны для редактирования.
При этом даже в тех записях, которые запрос вернул, для редактирования будут доступны те поля, которые не входят в сканируемый индекс. Т.е. получается, что я могу поменять часть данных в строке, которая должна быть заблокирована.

Сценарий 4.
Уровень изоляции: REPEATABLE READ
Тип поиска: Clustered Index Seek
Здесь все так же соответствует теории. Блокируются только те записи, которые были возвращены запросом, при этом ни одно из полей изменить нельзя. Заблокирована вся строка целиком.

Сценарий 5.
Уровень изоляции: REPEATABLE READ
Тип поиска: Index Seek
Блокируются только те записи, которые были возвращены запросом.
Но при этом даже в заблокированной записи, можно изменить поле которое не входит в состав индекса, который использовался для поиска.

Вопрос № 1
Почему в сценарии 2, запрос со сканированием в REPEATABLE READ блокирует только данные, которые запрос возвращает, а если уровень изоляции SERIALIZABLE, тогда запрос блокирует все данные, которые он сканирует?

Вопрос № 2
Почему в сценарии 3, при уровне изоляции REPEATABLE READ не блокируется вся строка, а блокируются только поля, которые входят в индекс, при этом в SERIALIZABLE строка блокируется полностью?

Я могу предположить что т.к. это некластерный индекс, то блокируется именно он, а не строка таблицы, а в кластерном строка и является частью индекса, поэтому она блокируется полностью. В пользу этой теории говорят 4 и 5 сценарий.
Но тогда не понятно, почему нет такого же поведения в SERIALIZABLE?
По возможности прошу дать ссылки на документацию по данным вопросам.
Заранее спасибо за ответ.

P.S. Во вложении находится база 1С для воспроизведения и проверки вышеописанного.
Для воспроизведения разворачиваем базу на платформе 8.2 в автоматическом режиме управления блокировкой данных.
Запускаем одну сессию под отладкой, открываем обработку из архива, ставим точку останова после выполнения запроса, но до завершения транзакции, а во второй сессии пробуем менять различные строки и поля, что бы понять, что именно заблокировано.

К сообщению приложен файл (База и обрабока с запросами.zip - 17Kb) cкачать
28 дек 14, 19:56    [17064492]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка данных запросом в зависимости от уровня изоляции  [new]
aleks2
Guest
Теперь ишо ознакомься с lock escalation.
И наступит просветление.
29 дек 14, 07:20    [17065525]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка данных запросом в зависимости от уровня изоляции  [new]
Андрей_Батькович
Member

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

Эскалация блокировок тут абсолютно не причем.
Эскалация это укрупнение гранулярности, а гранулярность тут везде одинаковая, да и вообще слишком мало записей в таблице.
Прошу отвечать по делу.
29 дек 14, 09:12    [17065711]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка данных запросом в зависимости от уровня изоляции  [new]
хмхмхм
Guest
Андрей_Батькович,

Посмотрите доклад:

Взаимоблокировки, блокировки и уровни изоляции в SQL Server
Докладчик: Резник Денис (Киев)

http://www.sqlpass.org/LearningCenter/SessionRecordings/24HoursRussia2013.aspx

Думаю, многое прояснится
29 дек 14, 10:45    [17066150]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка данных запросом в зависимости от уровня изоляции  [new]
Андрей_Батькович
Member

Откуда:
Сообщений: 95
хмхмхм,

Спасибо за ссылку, но к сожалению ничего нового я там не узнал.
Там нет ответов на мои вопросы.
Чем отличаются уровни изоляции мне известно, но я ведь спрашиваю не об этом.
29 дек 14, 12:56    [17066828]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка данных запросом в зависимости от уровня изоляции  [new]
Glory
Member

Откуда:
Сообщений: 104751
Андрей_Батькович
а во второй сессии пробуем менять различные строки и поля, что бы понять, что именно заблокировано.

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

Андрей_Батькович
Для воспроизведения разворачиваем базу на платформе 8.2 в автоматическом режиме управления блокировкой данных.

Вы серьезно счиатете, что кто-то в этом форуме будет "разворачивать базу на платформе" ?
29 дек 14, 13:02    [17066865]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка данных запросом в зависимости от уровня изоляции  [new]
Андрей_Батькович
Member

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

У меня вопрос не в том что, где и как долго заблокировано, я это прекрасно знаю.
У меня вопрос ПОЧЕМУ в разных случаях разные сценарии блокировки?


Базу я приложил на тот случай, если кто-то захочет убедится и проверить мои слова на практике.
29 дек 14, 13:06    [17066894]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка данных запросом в зависимости от уровня изоляции  [new]
Glory
Member

Откуда:
Сообщений: 104751
Андрей_Батькович
У меня вопрос ПОЧЕМУ в разных случаях разные сценарии блокировки?

А почему они должны быть одинаковыми ?


Андрей_Батькович
Базу я приложил на тот случай, если кто-то захочет убедится и проверить мои слова на практике.

И каким образом я, например, могу что-то увидеть из ваших бинарных файлов ?
29 дек 14, 13:09    [17066912]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка данных запросом в зависимости от уровня изоляции  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8834
SERIALIZABLE блокирует не таблицу, а диапазон ключей, к которым было обращение. Дальше не читал, т.к. первичное утверждение не верно.
29 дек 14, 13:32    [17067052]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка данных запросом в зависимости от уровня изоляции  [new]
MSSQLBug
Guest
Андрей_Батькович
хмхмхм,
Чем отличаются уровни изоляции мне известно, но я ведь спрашиваю не об этом.


По-моему, это Вам только так кажется.

Например:
Андрей_Батькович
В этом случае, до конца транзакции будут заблокированы только те записи, которые запрос вернул, а остальные записи будут доступны для редактирования.


Если Вы считаете, что:

Андрей_Батькович
Это уже противоречит теории, т.к. вроде бы должны быть заблокированы все записи таблицы.


то теорию Вам всё-таки стоит перечитать.
29 дек 14, 13:40    [17067100]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка данных запросом в зависимости от уровня изоляции  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
Андрей_Батькович,

Сценарий 1.
Уровень изоляции: SERIALIZABLE
Тип поиска: Clustered Index Scan или Index Scan
Если запрос выполняется с уровнем изоляции SERIALIZABLE, то вся таблица блокируется до конца транзакции, и неважно был ли
это скан кластерного индекса или не кластерного. Если транзакция не закончилась, то нельзя изменить ни одно поле, ни одной
строки таблицы.


вы хорошо проверяли? если скан идет только некластерного индекса, то блокироваться будет только он - все, что не входит должно быть доступно для изменения.

Сценарий 2.
Уровень изоляции: REPEATABLE READ
Тип поиска: Clustered Index Scan
В этом случае, до конца транзакции будут заблокированы только те записи, которые запрос вернул, а остальные записи будут доступны для редактирования. Это уже противоречит теории, т.к. вроде бы должны быть заблокированы все записи таблицы.


зачем же все? для repeatable read достаточно блокировать только то, что было выбрано.

Почему в сценарии 2, запрос со сканированием в REPEATABLE READ блокирует только данные, которые запрос возвращает, а если уровень изоляции SERIALIZABLE, тогда запрос блокирует все данные, которые он сканирует?


для repeatable read достаточно запретить изменение выбранных данных - поэтому достаточно заблокировать выбранное. для serializable надо еще с фантомами бороться - т.е. надо запретить еще и вставку данных, которые могли бы попасть под условия запроса, поэтому приходится блокировать все вообще, что попало под вычитку (а не только то, что было выдано), да еще и диапазонами.
29 дек 14, 14:21    [17067363]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка данных запросом в зависимости от уровня изоляции  [new]
Андрей_Батькович
Member

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

Большое спасибо за подробный ответ!

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

2. Правильно ли я понимаю, что если запрос сканирует индекс с уровнем изоляции REPEATABLE READ, то в момент выполнения запроса будут заблокированы все записи, которые запрос обрабатывал, но когда запрос завершиться, то будут заблокированы только те записи, которые запрос вернул?

3. По поводу блокировки всей вычитки теперь тоже понятно, я почему – то думал, что защита от фантомов реализована другим способом.

Скиньте пожалуйста ссылки на документацию, где написано, что при REPEATABLE READ блокируется только результат запроса, а при SERIALIZABLE блокируются еще и данные, которые запрос обрабатывал при выполнении.
Почему – то этой информации я нигде не нашел.

Еще раз спасибо.
29 дек 14, 15:06    [17067773]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка данных запросом в зависимости от уровня изоляции  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
Андрей_Батькович
2. Правильно ли я понимаю, что если запрос сканирует индекс с уровнем изоляции REPEATABLE READ, то в момент выполнения запроса будут заблокированы все записи, которые запрос обрабатывал, но когда запрос завершиться, то будут заблокированы только те записи, которые запрос вернул?


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

Андрей_Батькович
3. По поводу блокировки всей вычитки теперь тоже понятно, я почему – то думал, что защита от фантомов реализована другим способом.


хм, а каким?

Андрей_Батькович
Скиньте пожалуйста ссылки на документацию, где написано, что при REPEATABLE READ блокируется только результат запроса, а при SERIALIZABLE блокируются еще и данные, которые запрос обрабатывал при выполнении.
Почему – то этой информации я нигде не нашел.

Еще раз спасибо.


я как-то не уверен, что это подробно документировано. вообще, очень уж полагаться на конкретные механизмы реализации уровней изоляции не рекомендуют, хотя знать их может быть полезно. для знать могу посоветовать вот такую книжку:
http://www.red-gate.com/community/books/sql-server-concurrency
автор более чем авторитетен, хотя, это, конечно, не официальная документация.
29 дек 14, 15:35    [17068039]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка данных запросом в зависимости от уровня изоляции  [new]
MSSQLBug
Guest
daw
я как-то не уверен, что это подробно документировано.

Да нет, подробная документация есть в BOL, но, кажется, не в одном месте.

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


А я бы порекомендовал полагаться именно на конкретные принципы реализации, если Вы используете не SERIALIZABLE. ;) Т.е. почитать, как устроен механизм Strong strict two-phase locking и как именно он ослабляется на каждом уровне изоляции в MS SQL Server-е, а то сюрпризов может быть много.
29 дек 14, 16:32    [17068445]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка данных запросом в зависимости от уровня изоляции  [new]
Андрей_Батькович
Member

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

Еще раз большое спасибо за подробный ответ, книгу обязательно почитаю.
29 дек 14, 16:37    [17068475]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить