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

Откуда:
Сообщений: 1197
Блокируется ли таблица при простом update для всех записей или select into ?

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

спасибо
23 дек 11, 09:05    [11812716]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка таблицы  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
Посмотрите сами:

BEGIN TRAN...

UPDATE T1 SET F1 = <Value>

exec sp_lock @@spid

ROLLBACK
23 дек 11, 09:09    [11812727]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка таблицы  [new]
relief
Member

Откуда:
Сообщений: 1197
pkarklin
Посмотрите сами:

BEGIN TRAN...

UPDATE T1 SET F1 = <Value>

exec sp_lock @@spid

ROLLBACK


2 вопроса.

1. как залочить конкретную запись при апдейте, чтобы следующий селект не мог прочитать ее?
2. вы привели пример с BEGIN TRAN, а я спрашивал как раз если будет просто update, будет ли блокировка?
23 дек 11, 09:26    [11812815]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка таблицы  [new]
SanyL
Member

Откуда: Москва
Сообщений: 4540
relief
pkarklin
Посмотрите сами:

BEGIN TRAN...

UPDATE T1 SET F1 = <Value>

exec sp_lock @@spid

ROLLBACK


2 вопроса.

1. как залочить конкретную запись при апдейте, чтобы следующий селект не мог прочитать ее?
2. вы привели пример с BEGIN TRAN, а я спрашивал как раз если будет просто update, будет ли блокировка?


Будет!

pkarklin заключил в BEGIN TRAN только для того чтобы Вы увидели эту блокировку... иначе после апдейта она бы снялась и Вы ее не увиделибы
23 дек 11, 09:58    [11812917]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка таблицы  [new]
relief
Member

Откуда:
Сообщений: 1197
SanyL
relief
пропущено...


2 вопроса.

1. как залочить конкретную запись при апдейте, чтобы следующий селект не мог прочитать ее?
2. вы привели пример с BEGIN TRAN, а я спрашивал как раз если будет просто update, будет ли блокировка?


Будет!

pkarklin заключил в BEGIN TRAN только для того чтобы Вы увидели эту блокировку... иначе после апдейта она бы снялась и Вы ее не увиделибы


но получается при апдейте блокировка идет на запись, а не на таблицу
а как сэмулировать блокировку одной записи при апдейте, чтобы следующий запрос завис при ее чтении?
23 дек 11, 10:10    [11812982]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка таблицы  [new]
SanyL
Member

Откуда: Москва
Сообщений: 4540
relief
SanyL
пропущено...


Будет!

pkarklin заключил в BEGIN TRAN только для того чтобы Вы увидели эту блокировку... иначе после апдейта она бы снялась и Вы ее не увиделибы


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


ну так Вы почитайте про блокировки и их совместимость. При апдейте будет заблокирована строка. Но ток еще почтитайте про уровни изоляции транзакций.
23 дек 11, 10:15    [11813000]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка таблицы  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
relief
интересует, могут ли данные операции залочить таблицы намертво, при каких либо условиях?
если могут, то при каких?

спасибо


Вот вам пример
-- первый запрос (отдельное окно)

CREATE TABLE dbo.Tbl1
    (
      id INT NOT NULL
    , PlaceHolder CHAR(100) NOT NULL DEFAULT ( 'abc' )
    )
go

;
WITH cte AS ( SELECT   1 AS id 
               UNION ALL
               SELECT   id + 1
               FROM     cte
               WHERE    id < 1000
             )
    INSERT  INTO dbo.Tbl1 ( id )
    SELECT  id
    FROM    cte
    OPTION  ( MAXRECURSION 0 )

GO

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION
        -- посмотрите на план выполнения, имеется TableScan
	UPDATE dbo.Tbl1 
	SET PlaceHolder = 'xxx'
	WHERE id = 500	

-- пока не выполняем комит, а переходим в другое окно и выполняем селект (приведен ниже)
COMMIT TRANSACTION


-- второй запрос (отдельное окно)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT  *
FROM    dbo.Tbl1


Селект не выдаст ни одной строки до тех пор пока транзакция в первом окне не завершится.
Это происходит из-за того, что при апдейте происходит скан таблицы, а значит на время апдейта она блокируется полностью.
Решением в данной ситуации могло бы быть создание кластерного индекса в таблице, тогда при апдейте заблокирована бы была только одна строка.
ALTER TABLE dbo.Tbl1
ADD CONSTRAINT PK_Tbl1 PRIMARY KEY CLUSTERED ( id )
GO


Вобщем надо избегать Сканов (IndexScan) и стремится к поиску в индексе IndexSeek.

Еще один вариант хоть и не лучший, но если он вас устраивает можете понизить уровень транзакции до грязного чтения.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT  *
FROM    dbo.Tbl1
23 дек 11, 11:57    [11813788]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка таблицы  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
Владимир Затуливетер,

Кластерный индекс безусловно наклыдвает X-блокировку на строку, но так же и накладывает IX блокировку страницу. А при условии если ваш id (либо другое поле) будет неуникален, то может произойти эскалация блокировки до страницы, а то и до таблицы. Конечно можно ограничиться только блокировкой строки или блокировкой страницы, через хины with(rowlock) или with(paglock).
23 дек 11, 12:19    [11814042]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка таблицы  [new]
SanyL
Member

Откуда: Москва
Сообщений: 4540
Владимир Затуливетер
Селект не выдаст ни одной строки до тех пор пока транзакция в первом окне не завершится.
Это происходит из-за того, что при апдейте происходит скан таблицы, а значит на время апдейта она блокируется полностью.
Решением в данной ситуации могло бы быть создание кластерного индекса в таблице, тогда при апдейте заблокирована бы была только одна строка.



А если включить версионность? Надо рассматривать случаи отдельно по ситуациям...
23 дек 11, 13:29    [11814695]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка таблицы  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
вы ребята хотите чтобы в одном посте я все учел? ;)
на полноту не претендую, но основная идея вроде ясна.
а дальше ужо пущай сам разбирается...
23 дек 11, 14:25    [11815299]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка таблицы  [new]
relief
Member

Откуда:
Сообщений: 1197
Владимир Затуливетер
Еще один вариант хоть и не лучший, но если он вас устраивает можете понизить уровень транзакции до грязного чтения.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT  *
FROM    dbo.Tbl1


т.е. если будет такая конструкция

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN

  SELECT * from tbl1

  UPDATE tbl 2
COMMIT TRAN 


то при вызове в другом месте я получу результат, даже если не закончится вызов 1?
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * from tbl1
23 янв 12, 17:45    [11954656]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка таблицы  [new]
invm
Member

Откуда: Москва
Сообщений: 9824
relief, ознакомьтесь -- Эффекты параллелизма
23 янв 12, 18:58    [11955415]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить