Отправляет email-рассылки с помощью сервиса Sendsay

Вопросы и ответы по MS SQL Server

Подписаться Бесплатная новостная рассылка Подписчиков 17.253 RSS
←   Май 2003   →
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31


За последние 60 дней 1 выпусков (1 раз в 2 месяца)


Сайт рассылки: http://www.itnews.ru
Открыта: 04-03-2003

Автор
Сергей

Статистика

17.253 подписчиков
-3 за неделю
←   Все выпуски   →

Вопросы и ответы по MS SQL Server


Информационный Канал Subscribe.Ru

Вопросы и ответы по MS SQL Server

Выпуск No. 11 от 2003-05-02
Вопрос : Для чего нужны опции блокировки (Locking Hints) в операторах SELECT, INSERT, UPDATE и DELETE?
Ответ:
Использование этих опций изменяет уровень блокировки данных в таблице для текущей сессии.

Оптимизатор SQL Server'а автоматически определяет необходимый вид блокировки, но иногда появляется необходимость использовать эти опции для улучшения работы приложения.
Описание опций блокировки для операторов можно прочитать в Books Online в разделе "Locking Hints".

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

1. NOLOCK
В MS SQL Server нет механизма версионности записей, чтобы осуществлять возможность чтения данных, в состоянии на момент начала транзакции. Поэтому, чтобы другая транзакция не прочитала данные, которые изменены, но еще не известно - будут ли они записаны или нет, эти данные блокируются от чтения.
С помощью опции NOLOCK можно читать данные незаконченных транзакций. Использование этой опции эквивалентно установке уровня изоляции транзакций READ UNCOMMITTED для конкретной таблицы.
Для того чтобы посмотреть, как работает эта опция, необходимо создать два соединения с базой данной и выполнять показанный ниже код в разных соединениях. 
-- В 1-м соединении:

use tempdb

if OBJECT_ID('Table1') is not null drop table Table1

create table Table1 (ID int identity, Name varchar(50))



insert into Table1 values('Строка 1')

insert into Table1 values('Строка 2')



begin tran

update Table1 set Name='Обновленная строка' where ID=1



-- Во 2-м соединении:

use tempdb

-- читаются "грязные данные"

select * from Table1 with (nolock)

-- читаются данные после снятия блокировки

select * from Table1



-- В 1-м соединении:

rollback

Во втором соединении будет следующий результат: 
ID          Name

----------- --------------------------------------------------

1           Обновленная строка

2           Строка 2



(2 row(s) affected)



ID          Name

----------- --------------------------------------------------

1           Строка 1

2           Строка 2



(2 row(s) affected)

При этом второй набор данных появится только после завершения транзакции командой rollback.
Как видно из примера, эту опцию нужно использовать, только если Вам не нужны актуальные данные. Например, если Вы выбираете данные за прошедший период для аналитических отчетов.
При составлении сложных запросов опцию надо указывать для каждой таблицы. Например, в показанном выше коде во втором соединении можно было бы написать следующее: 
select * from Table1 a with (nolock)

cross join Table1 b with (nolock)


2. ROWLOCK
Эта опция используется в сочетании с командами INSERT, UPDATE и DELETE для блокировки изменяемых данных построчно.
Лучше не указывать эту опцию, потому что оптимизатор сам хорошо определяет, где необходимо блокировать данные построчно, а где блокировать всю таблицу или страницы данных (по крайней мере в MSSQL2000+SP3).
Используйте эту опцию, только если Вам необходимо быть уверенным в том, чтобы данные блокировались построчно. Однако следует помнить, что данную опцию можно использовать только в том случае, когда вы уверены, что эти операции будут выполняться над небольшим числом записей. При массовых операциях модификации записей с указанной опцией, на каждую модифицируемую запись будет занят отдельный ресурс, называемый "блокировка". Количество таких объектов при достижении критически больших величин может привести к исчерпанию ресурсов памяти SQL-сервера.
Следующий ниже код показывает, что блокировка записей построчно производится автоматически: 
use tempdb

if OBJECT_ID('Table1') is not null drop table Table1

create table Table1 (ID int identity primary key, Name varchar(50))



insert into Table1 values('Строка 1')

insert into Table1 values('Строка 2')



begin tran

update Table1 set Name='Обновленная строка'



exec sp_lock @@SPID



rollback

Результат: 
spid   dbid   ObjId       IndId  Type Resource         Mode     Status

------ ------ ----------- ------ ---- ---------------- -------- ------

56     2      400836265   1      PAG  1:30             IX       GRANT

56     2      400836265   0      TAB                   IX       GRANT

56     1      85575343    0      TAB                   IS       GRANT

56     2      224835638   0      TAB                   IX       GRANT

56     2      400836265   1      KEY  (020068e8b274)   X        GRANT

56     2      400836265   1      KEY  (010086470766)   X        GRANT


3. UPDLOCK
Опция используется для блокировки считанных строк из таблицы в текущей транзакции, чтобы в период между считыванием данных из таблицы и окончанием действия транзакции данные не изменялись.
В следующем примере показано для чего это нужно: 
use tempdb

if OBJECT_ID('Table1') is not null drop table Table1

create table Table1 (ID int identity primary key, Name varchar(50))



insert into Table1 values('Строка 1')

insert into Table1 values('Строка 2')



begin tran

declare @Name varchar(50)



select @Name=Name

from Table1 with (updlock)

where ID=1



-- проводим какие-то преобразования

set @Name=@Name+@Name



update Table1 set Name=@Name where ID=1



exec sp_lock @@SPID

commit tran

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

4. TABLOCKX
Опция используется для блокировки всей таблицы. 
use tempdb

if OBJECT_ID('Table1') is not null drop table Table1

create table Table1 (ID int identity primary key, Name varchar(50))



insert into Table1 values('Строка 1')

insert into Table1 values('Строка 2')



begin tran

declare @i int, @Name varchar(50)



-- блокировка всей таблицы до окончания транзакции

select @i=1 from Table1 (tablockx)



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

select @Name=Name from Table1 where ID=1

update Table1 set Name=@Name where ID=2



exec sp_lock @@SPID

commit tran


Показанные четыре опции используются наиболее часто.
Следует учесть, что указание опций блокировки в разделе FROM имеет смысл только для тех таблиц, которые существуют на момент создания скрипта. Опции по временным таблицам и таблицам Inserted и Deleted в триггерах игнорируются.

Некоторые программисты используют опции UPDLOCK и TABLOCKX для блокирования таблиц и записей на все время работы пользователей с данными. Например, чтобы два пользователя не могли одновременно работать с одними и теми же данными. В результате блокировки длятся очень длительное время. Это неправильный подход (и это не только мое мнение).
Более правильный подход заключается в записи данных в процессе их ввода. Например, при заведении прихода товара записывать данные не по окончании ввода прихода, а каждую позицию товара в отдельности. Тогда можно проверять на момент записи данных в базу, были ли внесены изменения в процессе работы с данными, и уже на основе этого предпринимать соответствующие действия.
Если же такой вариант Вам не подходит, то лучше сделать специальную таблицу, содержащую информацию о блокировках. При этом надо следить за освобождением заблокированных ресурсов при некорректном закрытии соединения с базой данных.

Дальнейшие рассуждения уводят в сложную философскую тему блокировок. На этом закончим.
Надеюсь, Вы нашли эту информацию полезной. Вопросы, предложения и пожелания шлите на адрес sql@likor.ru
С уважением, Сергей Кошкин.
Адрес сайта Рассылки - http://sql.softmatics.ru/

Архив на Subscribe.Ru
Поиск по архиву рассылки
"Вопросы и ответы по MS SQL Server"

Рейтинг@Mail.ru
http://subscribe.ru/
E-mail: ask@subscribe.ru 		Отписаться
Убрать рекламу
В избранное