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

Откуда: Украина, г. Тернополь
Сообщений: 549
Хотелось для себя уяснить следующее:
1. Выборка данных для анализа (можна и "грязные") select * from Table WITH(NOLOCK)
2. Изменение/Вставка/Удаление одной позиции delete from Table WITH(ROWLOCK) where id=@id (поле id первичный ключ)


Тогда не будет блокировок и задержек в работе. Правильно ли я думаю?

3. Когдато читал что блокировка на уровне строки использует больше ресурсов нежели на уровне страницы, и советуют блокировать страницу PAGLOCK.

4. Если я напишу delete from Table where id=@id (поле id первичный ключ),
то сервер заблокирует всю таблицю, пока не удалит данные из неё? Какую блокировку он использует?
11 дек 03, 18:50    [457656]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование Блокировок  [new]
Merle
Guest
Вообщем не правильно...

1. Так лучше не делать, вообще никогда. Потому что сегодня, то что даные грязные вроде как ничего, а завтра они ложатся на стол начальнику у которого отчеты не сходятся. Будет обидно.
NOLOCK можно спокойно использовать только для административных ad hoc запросов, в подавляющем большинстве остальных случаев необходимость использования NOLOCK это следствие кривого дизайна.

2. и 3.
Если не знаешь наверняка, что делает тот или иной хинт, то не пользуй его вообще, сервер лучше разберется, ему виднее что, как и зачем блокировать. Как правило у него просто больше информации. В нагруженных OLTP системах оптимальная гранулярность меняется даже не от запроса к запросу, а прямо в ходе выполнения одного запроса. Так что с хинтами типа ROWLOCK, PAGLOCK к оптимизатору лишний раз лезть не надо, он тоже не дурак.
По этому поводу можно почитать вот здесь: http://www.rsdn.ru/?article/db/LockEscalation.xml
Вообще по блокировкам, для общего развития, можно почитать вот здесь: http://www.rsdn.ru/?article/db/mssqllocks.xml

4. Нет, в подавляющем большинстве случаев будет заблокирована одна запись.
11 дек 03, 20:51    [457730]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование Блокировок  [new]
LSV
Member [заблокирован]

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

При удалении\обновлении если в условии используется поле без индекса, то заблокируется вся таблица.
12 дек 03, 11:10    [457955]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование Блокировок  [new]
Александр Третьяков
Member

Откуда: Украина, г. Тернополь
Сообщений: 549
Спасибо Merle
Вчере полдня разбирались и пробовали.
2. 3. Сервер сам умеет принимать решение насчет блокировок, иногда даже игнорирует наши хинты :)!!! Короче сервер более менее умный.

4. delete from Table where id=@id (поле id первичный ключ) блокируеться только ОДНА запись. В BOL ниписано!!! что при удалении и обновлении данных если в условии where используеться поле с индексом блокируэться только позиция, если индекса нет то вся таблица.



1. думаю ты прав, я так и делал раньше но!!! иногда возникают задержки (не более 1с). с WITH(NOLOCK) задержек нет. Задержка возникает потому что select ожидает когда завершиться транзанкция другого пользователя.
12 дек 03, 11:41    [457985]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование Блокировок  [new]
Merle
Guest
4. Блокируется все равно одна запись. Вне зависимости от того есть там индексы или нет.
Другое дело, что при отсутствии индексов, это гарантированй table scan, для другого запоса, который рано или поздно упрется в эту заблокированную запись. В итоге эффект такой же, как будто бы заблокирована вся таблица.

1. Если эта задержка критична значит надо перепроектировать базу. В подавляющем большинстве случаев можно добиться приемлемой производительности и без NOLOCK.
12 дек 03, 12:01    [458006]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование Блокировок  [new]
Александр Третьяков
Member

Откуда: Украина, г. Тернополь
Сообщений: 549
для Merle
4. Блокируеться ВСЯ таблица, если нет индекса. Это можно проверить поставив на апдейт таблицы тригер с wait for 50c, в одном потоке запустить
update Table where id=3 (индекса нет), а в другом обычний select * from table where id=2!!! (записи разные а ждет 50секунт). Если индекс есть то выполняеться сразуже!
13 дек 03, 15:26    [458748]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование Блокировок  [new]
Merle
Guest
На 100 баксов спорим? Вся таблица блокируется только при уровне изоляции serializable, по другому просто не обеспечить предикатных блокировок, потому как key range lock, ясен пень, здесь не сделаешь.
Во всех остальных случаях блокируется одна запись.
И еще раз прочитай ВНИМАТЕЛЬНО, блин, мое предыдущее сообщение.
Отсутствие нидексов выливается в table scan, при этом любой запрос добирается до уже заблоированной записи и ЭФФЕКТ точно такой же, как буд-то блокируется вся таблица, но на самом деле, заблокирована одна запись.
Чтобы убедиться в этом надо не проводить дурацкие эксперименты с триггерами, а посмотреть в таблицу syslockinfo, после выполнения блокировки в транзакции.
14 дек 03, 00:09    [458870]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование Блокировок  [new]
ua-soft
Member

Откуда: Украина, Киев
Сообщений: 119
Что бы там не говорили, но использовать select ... from table1 with(NOLOCK) приходится. НО ВСЕ ЗАВИСИТ ОТ АРХИТЕКТУРЫ ВАШЕЙ СИСТЕМЫ. Нам просто повезло с архитектурой нашей системы - не бывает такого, что документ печатается, а данные его кто-то изменяет. Если отчет строится по многим документам, то эти документы находятся в "закрытом периоде", т.е. их уже никто не может модифицировать.
Когда раньше пользовались в запросах WITH(ROWLOCK) были постоянные проблемы с блокировками и производительностью.
Еще одно наблюдение. Даже если в SQL-операторах стоит WITH(ROWLOCK), когда блокируется несколько записей на одной странице, MSSQL переходит автоматически на PAGLOCK, при блокировании нескольких страниц в таблице - на TABLOCK. Видимо таким образом он пытается повысит свою производительность :), хотя чаще всего страдают конечные пользователи :( как раз от обратного.
14 дек 03, 16:05    [459006]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование Блокировок  [new]
Merle
Guest
Что бы там не говорили, но использовать select ... from table1 with(NOLOCK) приходится. НО ВСЕ ЗАВИСИТ ОТ АРХИТЕКТУРЫ ВАШЕЙ СИСТЕМЫ.
Бред. Все зависит от рук того, кто проектировал эту систему.
При должном радиусе кривизны оных рук, даже мысль о NOLOCK никому в голову не приходит.

Когда раньше пользовались в запросах WITH(ROWLOCK) были постоянные проблемы с блокировками и производительностью.
Да и не удивительно, нафига серверу мешать, когда он лучше знает?...

Даже если в SQL-операторах стоит WITH(ROWLOCK), когда блокируется несколько записей на одной странице, MSSQL переходит автоматически на PAGLOCK<...>
Двойка, за наблюдательность.
Механизм немного сложнее, чем кажется.

хотя чаще всего страдают конечные пользователи
Конечные пользователи, обычно страдают от избытка ума разарботчиков, в тех случаях, когда они пытаются найти проблемы там, где их нет.
14 дек 03, 23:33    [459135]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование Блокировок  [new]
ua-soft
Member

Откуда: Украина, Киев
Сообщений: 119
2 Merle

Не же работают только с MSSQL, есть еще и другие сервера, с другой архитектурой и особенностями. И вот люди решают перейти от использования сервера X к использованию MSSQL (начальство как правило принимает такое решение). И нужно, чтобы старая архитектура работала в совершенно новых условиях, максимально эффективно, насколько это возможно. И чтобы не переделывать 1000+1 наработанный годами алгоритм, а обеспечить его функционирование в новых условиях. Так что пугать людей не нужно, а если что в моих словах Вам показалось безумием, так аргументами их, аргументами :)...
15 дек 03, 18:29    [460342]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование Блокировок  [new]
Merle
Guest
Не же работают только с MSSQL, есть еще и другие сервера, с другой архитектурой и особенностями
Да кто бы спорил...

И нужно, чтобы старая архитектура работала в совершенно новых условиях, максимально эффективно, насколько это возможно. И чтобы не переделывать 1000+1 наработанный годами алгоритм, а обеспечить его функционирование в новых условиях
База к алгоритмам не имеет никакгого отношения. База - это хранилище, а алгоритмы - это обработка. И если алгоритмы зависят от базы, значит архитектура изначально была не верной.
Базу же перепроектировать надо в любом случае, чудес не бывает. Нельзя перенеси базу с версионника на блокировочник и ожидать, что она будет хоть как-то шевелиться, если база, конечно, не совсем тривиальная.

Так что пугать людей не нужно,
Да я не пугаю, я факты констатирую.

а если что в моих словах Вам показалось безумием, так аргументами их, аргументами
Безумием, мне показалось оправдание промашек допущеных на этапе проектирования, загадочными архитектурными потребностями.
В девяносто девяти, с половиной, случаев из ста - использование dirty read является попыткой заткнуть дыры в кривом дизайне.
16 дек 03, 10:36    [460890]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование Блокировок  [new]
ua-soft
Member

Откуда: Украина, Киев
Сообщений: 119
автор

База к алгоритмам не имеет никакгого отношения. База - это хранилище, а алгоритмы - это обработка.


Под новыми условиями я подразумевал другую СУБД (MSSQL), а не конкретно БД. А вот алгоритмы как раз очень даже и зависят от конкретной СУБД и даже от ее настроек.

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


Как показывает опыт - можно :) Другое дело, что при этом используются приемы, которые другие разработчики никогда не стали бы применять. А советовать переработать архитектуру не так уж и сложно, чем помочь человеку решить задачу так, как ему приходится решать. Когда клиент платит за "MSSQL" и при этом видит, что система у него работает на другой СУБД и говорит "то же, но MSSQL и завтра".

автор

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


На момент проектирования БД для функционирования в СУБД Interbase и Oracle никаких промашек не было :) И опять повторюсь, мы не говорим о проектировании - речь идет о реализации на другой платформе (очень плохо, но другого выхода нет)
16 дек 03, 13:29    [461364]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование Блокировок  [new]
Merle
Guest
А вот алгоритмы как раз очень даже и зависят от конкретной СУБД и даже от ее настроек.
Ну вот это и есть кривой дизайн.

А советовать переработать архитектуру не так уж и сложно, чем помочь человеку решить задачу так, как ему приходится решать.
А что еще советовать, если единственный разумный выход - это редизайн базы?
16 дек 03, 13:36    [461389]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование Блокировок  [new]
ua-soft
Member

Откуда: Украина, Киев
Сообщений: 119
Попробуйте поработать с различными СУБД - реализовать несколько сложных алгоритмов (не select'ы и update'ы с клиента, а набор ХП). А потом 15-20 пользователей, работающих одновременно... Вот тогда Вы и увидите, насколько по разному одни и теже алгоритмы работают в разных СУБД. Они и должны работать по разному, т.к. СУБД РАЗНЫЕ.
16 дек 03, 13:56    [461475]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование Блокировок  [new]
Merle
Guest
Попробуйте поработать с различными СУБД - реализовать несколько сложных алгоритмов (не select'ы и update'ы с клиента, а набор ХП). А потом 15-20 пользователей, работающих одновременно...
;)))))
Ну я как бы тоже не вчера первый раз БД увидел...

Вот тогда Вы и увидите, насколько по разному одни и теже алгоритмы работают в разных СУБД
Алгоритмы чего? У нас тут похоже небольшой мисандестундинг случился.
Слой работы с данными, естественно, будет для каждой базы свой, но это именно дизайн базы.
А алгоритмы - это логика уровнем выше и она никаким боком от способа хранения данных зависеть не должна.
Так вот, мое скромное мнение, заключается в том, что вместо изнурительного выпиливания по вазелину посредством грязного хака, при попытке втиснуть старый дизайн в новую базу без изменений. Надо потратить те же человеко-часы на реализацию нормального слоя данных, заточенного под нужный сервер. Потому как первый способ, в конечном итоге, обходится дороже, иногда на много дороже.

P. S.
Я там в предыдущих сообщениях похоже смайликов понаставить забыл.. ;)
16 дек 03, 14:21    [461587]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование Блокировок  [new]
ua-soft
Member

Откуда: Украина, Киев
Сообщений: 119
автор

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


Что вы понимаете под способом хранения данных? По моему мнению алгоритм оперирует таблицами БД, выполняет чтение, втавку и модификацию данных. А алгоритм уровнем еще выше - ну это ТЗ системы (схемы документооборота и т.п.). Так вот эти самые чтения, вставки и модификации данных, вплетенные в логику алгоритма и будут выполняться в СУБД с разными принципами работы по разному.

автор

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


Смею Вас уверить, что далеко не всегда (в моем случае все обошлось благополучно), хотя это конечно элемент везения.
16 дек 03, 14:58    [461700]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование Блокировок  [new]
Merle
Guest
Что вы понимаете под способом хранения данных?
Архитектурные особенности сервера.

По моему мнению алгоритм оперирует таблицами БД, выполняет чтение, втавку и модификацию данных.
Это называется слой данных. Сюда входят структура базы, запросы к ней и ORM. Грубо говоря этот слой занимается сериализацией бизнес объектов в базу и обратно. Вот он-то действительно зависит от архитектурных особенностей сервера.

А алгоритм уровнем еще выше - ну это ТЗ системы (схемы документооборота и т.п.).
Это называется слой бизнес логики. И он, по идее, должен работать исключительно с объектами и не иметь никакого понятия о БД.

Так вот эти самые чтения, вставки и модификации данных, вплетенные в логику алгоритма и будут выполняться в СУБД с разными принципами работы по разному.
???
Что вплетено и в логику какого алгоритма?
Слой бизнес логики ни как не должен зависеть от слоя данных.

Смею Вас уверить, что далеко не всегда (в моем случае все обошлось благополучно), хотя это конечно элемент везения.
Что именно обошлось блогополучно? И что именно далеко не всегда?
16 дек 03, 15:45    [461867]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование Блокировок  [new]
tygra
Member

Откуда: Тверь (Иркутск, Край)
Сообщений: 9997
Абчем разговор то уже?
То, что в любом случае без указания хинтов не обойтись, это понятно. Какая бы архитектура данных не была.

-- Tygra's --
16 дек 03, 15:50    [461879]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование Блокировок  [new]
Александр Третьяков
Member

Откуда: Украина, г. Тернополь
Сообщений: 549
Re: Merle (зря ты так уверен)
На 100 баксов спорим? Вся таблица блокируется только при уровне изоляции serializable, по другому просто не обеспечить предикатных блокировок, потому как key range lock, ясен пень, здесь не сделаешь.
для Merle
4. Блокируеться ВСЯ таблица, если нет индекса. Это можно проверить поставив на апдейт таблицы тригер с wait for 50c, в одном потоке запустить
update Table where id=3 (индекса нет), а в другом обычний select * from table where id=2!!! (записи разные а ждет 50секунт). Если индекс есть то выполняеться сразуже!


Нет ничего лучше експеримента и читать MSDN!!!

CREATE TABLE test (
[id] [int] NOT NULL ,
[nameid] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO

CREATE TRIGGER Tri_Update_Test ON dbo.test
FOR UPDATE
AS
WAITFOR DELAY '000:00:50'
GO
insert into test (id,nameid) values (1,'ssssss')
insert into test (id,nameid) values (2,'ssssss')
insert into test (id,nameid) values (3,'ssssss')

в одном окне пишеш
update test
set nameid='eee'
where id=2
/*задержка на 50 секунд*/

в другом окне
select * from test where id=3
/*задержка на 50 секунд тоже!!!!!!!! и записи разные!!!*/

если же есть индекс по id то!!! в select * from test where id=1 задержки нет

Это говорит експеримент и MSDN.

Я полность разделяю Ваше мнение насчет того, что надо как можно РЕЖЕ (лучне НИКОГДА) не использовать NOLOCK.
16 дек 03, 16:21    [461958]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование Блокировок  [new]
Merle
Guest
2 Александр Третьяков
Александр, поймите, я не зря писал про дурацкие эксперименты, про триггеры и про внешние эффекты. У меня две бутылки коньяка стоят на этих самых внешних эффектах честно заработанные, так что уверен я не зря.
В MSDN и BOL написано много всего интересного и читать их надо тоже очень внимательно, а не так как хочется. Мне, ей богу, просто лень сейчас полностью расписывать весь механизм работы блокировок при обновлении в блокировочнике. Будет интересно, велкам на rsdn.ru, с подробными вопросами, чего сам не знаю вместе разберемся.
Ваш ловкий эксперимент с триггером способен уловить лишь часть внешних эффектов. Говорю уже в третий и пожалуй последний раз, реально блокируется ровно одна запись, если уровень изоляции ниже serializable, за исключением некоторых, достаточно экзотических случаев.
Отчасти механизм описан вот здесь, при ответе на последний вопрос, в третьем пункте: http://rsdn.ru/?Forum/?mid=423879
Проверять надо не триггерами, а с помощю sp_lock и select * from syslockinfo

2 tygra
Да как сказать. Во первых хинты разные бывают, да и использовать их с умом надо.
А во вторых у меня вот, например, на более чем триста не самых тривиальных хранимок, используются два или три хинта. И то похоже просто потому, что мне разобраться лень.
С хинтами ведь как? Дай серверу хинт и он проживет месяц, дай ему статистику и он продержится всю жизнь.
16 дек 03, 16:58    [462060]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование Блокировок  [new]
tygra
Member

Откуда: Тверь (Иркутск, Край)
Сообщений: 9997
А что за система то эта, с 300 ХП коорая? Чего там делается?
У нас за 2500 перевалило, не все конечно навороченные, но достаточно. И когда 100 юзеров сидят и из них треть просто "издеваются" над сервером, тут никуда не денесся, придется хоть как хинты использовать, вплоть до nolock

-- Tygra's --
16 дек 03, 17:06    [462087]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование Блокировок  [new]
snake
Member

Откуда: Russia, Penza
Сообщений: 2290
OFF
не регистрируетесь принципиально? Картинка с другого сайта.
16 дек 03, 17:14    [462107]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование Блокировок  [new]
Merle
Guest
А что за система то эта, с 300 ХП коорая? Чего там делается?
Как обычно, склад, бухи и прочая лабуда. Около 150-200 клиентов.
В принципе, и при качественно большем количестве процедур можно разрулить все по честному, да и нужно.
Но проблема в том, что сиквел "в лоб" достаточно плохо масштабируется. Иными словами если ожидается, что система будет большой , то закладывать это дело надо сразу, пока проектируется, потом менять будет сложнее.
От этого, обычно, и все проблемы.

не регистрируетесь принципиально?
А смысл?
Я сюда раз в полгода, по обещанию заглядываю.. ;) За сегодня я вообще, похоже, свой трехгодовой лимит сообщений на sql.ru исписал. ;)
16 дек 03, 17:30    [462147]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование Блокировок  [new]
Александр Третьяков
Member

Откуда: Украина, г. Тернополь
Сообщений: 549
to Merle
Спасибо за исчерпывающий ответ. В принципе я понял механизм (точнее причину) почему select * from test where id=3 ждет выполнение тригера. Причина table scan по всез записях, а результат select ждет потомй что гдето нашел заблокрованую запись.
Еще раз спасибо.
17 дек 03, 11:56    [462988]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить