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

Откуда:
Сообщений: 4
Доброго времени суток.

MSSQL 2000.Используется одна таблица, в которой хранятся около полутора миллионов записей с восемью строковыми полями и одним инкрементным ключом. Таблица бд используется для сайта, на котором вводишь значение одного поля, и получаешь несколько записей из бд. Для этого используеся хранимая процедура с одним входным параметром(значением поля) и одним командой select. Данные обновляются посредством удаления старых записей по значению одного поля, и вставке новых из программы, написанной на C# с помощью sqlconnection и Datareader. Возникли 2 вопроса по блокировкам.
1. При описанной выше операции удаления могут возникнуть проблемы чтения фантомов. Во избежании этого использую хранимую процедуру:
alter procedure pr_del
@code2='1a'
as
begin
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
delete from prod with (tablock) where code1=@code2
COMMIT TRANSACTION
end

Удаляются порядка 200 тысяч записей. Если не использовать данную хранимую процедуру, в которой есть транзакция, а удалить с помощью Datareader, то займет максимум минуту. Однако при использовании транзакции удаление шло порядка 50 минут, так и не дождался окончания и принудительно выключил. Использовал и с хинтом Tablock, и без него, все равно очень долго.
Вопрос в том, как можно быстрее удалить записи, не отключая сайт от внешнего мира

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

Заранее спасибо за ответы.
28 сен 09, 19:19    [7717217]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки при удалении записей  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
автор
При описанной выше операции удаления могут возникнуть проблемы чтения фантомов. Во избежании этого использую хранимую процедуру:


Гм... Как в одной инструкции могут возникнуть "проблемы чтения фантомов"?! 8-/

автор
Если не использовать данную хранимую процедуру, в которой есть транзакция, а удалить с помощью Datareader, то займет максимум минуту. Однако при использовании транзакции удаление шло порядка 50 минут, так и не дождался окончания и принудительно выключил.


Эээ... А если в хп не будет "явной" транзакции, сколько будет идти удаление? Какой запрос отправляет на сервер адаптер? Какие планы у хп и запроса?
28 сен 09, 19:56    [7717299]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки при удалении записей  [new]
Tako
Member

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

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

автор
Однако при использовании транзакции удаление шло порядка 50 минут, так и не дождался окончания и принудительно выключил.

А посмотреть что на сервере с блокировками при этом? Может Вы не закрыли транзакцию в другом коннекте? (Кстати, почти все операции оборачиваются в транзакцию, даже если вы не задали этого явно. Почитайте здесь)

Какой уровень изоляции используется при чтении и удалении? Какие индексы есть на таблице и какие столбцы выбираются?
28 сен 09, 23:01    [7717683]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки при удалении записей  [new]
Denis Reznik
Member

Откуда: Киев
Сообщений: 156
krasich,

в целях оптимизации, можно удалять данные порциями. Ещё есть интересная техника удаления большого кол-ва записей записей, её я описывал в своём блоге - http://dev.net.ua/blogs/denisreznik/archive/2009/05/28/8394.aspx
28 сен 09, 23:08    [7717699]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки при удалении записей  [new]
krasich
Member

Откуда:
Сообщений: 4
Всем спасибо за ответы!
Перед тем как написать вопрос, я хуже понимал принцип работы транзакций, чем сейчас:) Потому при удалении использовал уровень изоляции serializable, а при чтении явно не задавал уровень, что было ошибкой.
Удаление, а затем вставку новых данных я произвожу раз в сутки ночью, тогда как пользователи читают данные много раз в день. Соответсвенно при чтении данных нет смысла накладывать уровень изоляции serializable. Вообще без изоляции можно обойтись. Тогда как при удалении и вставки достаточно задать read commited.
6 окт 09, 21:04    [7750419]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки при удалении записей  [new]
krasich
Member

Откуда:
Сообщений: 4
pkarklin
Эээ... А если в хп не будет "явной" транзакции, сколько будет идти удаление? Какой запрос отправляет на сервер адаптер? Какие планы у хп и запроса?


Я недавно стал изучать бд. Честно говоря не понял, что значит хп
6 окт 09, 21:04    [7750420]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки при удалении записей  [new]
krasich
Member

Откуда:
Сообщений: 4
To Denis Reznik. Спасибо за ссылку, статья полезная:)
6 окт 09, 21:07    [7750427]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки при удалении записей  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
krasich
Я недавно стал изучать бд. Честно говоря не понял, что значит хп


Хп - это хранимая процедура.
6 окт 09, 21:18    [7750445]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить