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

1. Необходимо сделать 2 большие выборки (милионы записей) из таблицы с кластерным индексом.
2. Нужно чтобы в это время таблица не менялась (по крайней мере выбираемые данные, чтобы выборки получали согласованные данные).
3. Теоритически эти выборки выбирают данных из разных диапазонов индекса.
4. Выборки выполняю в одной транзакции.
5. Уровень изоляции транзации SERIALIZABLE

Вопросы:

А. Правильно я понимаю, что при таких условиях (кластерный индекс, SERIALIZABLE) сначала блокируется весь требуемый диапазон индекса, а потом уже выполняется выборка (для какой нибудь одной выборки)?
Б. Может ли случится ситуация, что пока выполняется первая выборка, кто-нибудь изменит данные из которых будет выбирать вторая? Если может, то как это предотвратить ?
16 янв 14, 13:31    [15424496]     Ответить | Цитировать Сообщить модератору
 Re: Как заранее заблокировать 2 диапазона ключа  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
Score
А. Правильно я понимаю, что при таких условиях (кластерный индекс, SERIALIZABLE) сначала блокируется весь требуемый диапазон индекса, а потом уже выполняется выборка (для какой нибудь одной выборки)?
Нет.
Score
Б. Может ли случится ситуация, что пока выполняется первая выборка, кто-нибудь изменит данные из которых будет выбирать вторая?
Может.
Score
Если может, то как это предотвратить ?
а) Предварительно монопольно заблокировать таблицу.
б) Выполнять транзакцию на TIL Snapshot - получите согласованные данные на момент начала транзакции.
16 янв 14, 13:58    [15424697]     Ответить | Цитировать Сообщить модератору
 Re: Как заранее заблокировать 2 диапазона ключа  [new]
Score
Guest
invm,
а нас не спасают в этой ситуации блокировки намерения?
Чем они нам помогают вообще?

SELECT * FROM Table WITH(SERIALIZABLE)

К сообщению приложен файл. Размер - 45Kb
16 янв 14, 14:32    [15424894]     Ответить | Цитировать Сообщить модератору
 Re: Как заранее заблокировать 2 диапазона ключа  [new]
Score
Guest
Или вот так

SELECT * FROM Table WITH(SERIALIZABLE, UPDLOCK)

К сообщению приложен файл. Размер - 47Kb
16 янв 14, 14:33    [15424898]     Ответить | Цитировать Сообщить модератору
 Re: Как заранее заблокировать 2 диапазона ключа  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Score,

Что-то я не понял invm совсем. Какой-то агрессивный у него ответ.
У вас всё правильно написано.
Да, будет заблокирован диапазон. S или U - не важно, оно не даст менять данные на момент выборки.

Но то что транзакция и то что две выборки - это отдельно. Т.к. локировка диапазонов не может быть одновременной, то тут может быть проблема дедлоков.
Гарантированные способы убрать дедлоки, это завязаться на одном объекте. Или явно или косвенно (порядок действия).
Порядок действия - К примеру блокировать всегда сначала младший из 2х диапазонов (как по данным, так и по объектам).

Если у вас Snapshot - то тут дело идеологическое. Что значит не менялось?
Нужно согласованность читаемых данных или реально запретить писать новые данные, которые никак на ваши не повлияют?
Если согласованность, то фиолетово - Snapshot или не-Snapshot - дело в стратегии выполнении параллельных процессов.
Если запретить, тогда явно хинтуете локировку.
16 янв 14, 15:16    [15425197]     Ответить | Цитировать Сообщить модератору
 Re: Как заранее заблокировать 2 диапазона ключа  [new]
Score
Guest
Mnior
Да, будет заблокирован диапазон. S или U - не важно, оно не даст менять данные на момент выборки.
т.е. диапазон будет заблокирован заранее? до выполнения выборки?

Mnior
Если у вас Snapshot - то тут дело идеологическое. Что значит не менялось?
Нужно согласованность читаемых данных или реально запретить писать новые данные, которые никак на ваши не повлияют?
Если согласованность, то фиолетово - Snapshot или не-Snapshot - дело в стратегии выполнении параллельных процессов.
Если запретить, тогда явно хинтуете локировку.
да, нужна согласованность данных. в идеале Snapshot подошел бы, но есть причины по которым Snapshot поставить не получится
16 янв 14, 16:11    [15425530]     Ответить | Цитировать Сообщить модератору
 Re: Как заранее заблокировать 2 диапазона ключа  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
Score
а нас не спасают в этой ситуации блокировки намерения?
Чем они нам помогают вообще?
Во-первых, они друг с другом совместимы. Во-вторых, у блокировок намерения другая цель - они позволяют определить наличие блокировок более низкого уровня.
Mnior
Что-то я не понял invm совсем. Какой-то агрессивный у него ответ.
У вас всё правильно написано.
Да, будет заблокирован диапазон. S или U - не важно, оно не даст менять данные на момент выборки.
Не будет одной блокировки диапазона. Вернее будет, но только при пустом диапазоне. Range lock будет накладываться на каждое значение ключа в диапазоне плюс на следующее значение вне его. И накладываться они будут в процессе прохода по таблице/индексу. Следовательно, в процессе выполнения инструкции, вполне возможны изменения в данных, которые еще не были прочитаны. Serializable всего лишь обеспечивает отсутствие каких-либо фантомов, а не атомарность инструкции. Вот простой пример:
+ Подготовка
use tempdb;
go

if object_id('dbo.tt', 'U') is not null
 drop table dbo.tt;

create table dbo.tt (id int not null primary key, v int);

insert into dbo.tt
values
 (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7);
+ Первый сеанс
use tempdb;
go

begin tran;

select
 *
from
 dbo.tt with (xlock, serializable)
where
 id between 1 and 4 and
 tools.clrfnWT_Delay(case when id = 2 then 10000 else 0 end) = 1/*Задержка 10 сек.*/;

exec sp_lock @@spid;

--commit;
+ Второй сеанс
use tempdb;
go

select
 *
from
 dbo.tt with (xlock, serializable)
where
 id between 4 and 6;
Как будет видно, код во втором сеансе можно многократно выполнять в течение примерно 10 сек.
16 янв 14, 16:16    [15425557]     Ответить | Цитировать Сообщить модератору
 Re: Как заранее заблокировать 2 диапазона ключа  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
Исходник функции:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Threading;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlBoolean Delay(SqlInt32 AMilliseconds)
    {
        if (AMilliseconds.IsNull || AMilliseconds.Value < 0)
            throw new ArgumentOutOfRangeException("AMilliseconds");

        Thread.Sleep(AMilliseconds.Value);

        return new SqlBoolean(true);
    }
};
16 янв 14, 16:41    [15425691]     Ответить | Цитировать Сообщить модератору
 Re: Как заранее заблокировать 2 диапазона ключа  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Score
т.е. диапазон будет заблокирован заранее? до выполнения выборки?
Все локировки накладываются до действия и снимаются после.
invm
Во-первых, они друг с другом совместимы.
Какой ещё "друг с другом"? Вы о чём.
Совместимость блокировок
invm
Во-вторых, у блокировок намерения другая цель - они позволяют определить наличие блокировок более низкого уровня.
Вообще не понял о чём вы. Что за ракурс видения?
Зачем что либо определять, зачем вообще позиционировать что целью является определить? Целью является предотвратить.

ТС говорит о том что в одном сеансе надо заблокировать два диапазона (в одной таблице или в 2х неважно). А не о двух процессах которые лочат каждый свой диапазон.

invm
Range lock будет накладываться на каждое значение ключа в диапазоне плюс на следующее значение вне его. И накладываться они будут в процессе прохода по таблице/индексу.
Спасибо. Я что-то это не так воспринимал интерпретатор. Хотя я пока не могу это увидеть явно.
Но.
invm
Не будет одной блокировки диапазона. Вернее будет, но только при пустом диапазоне. Следовательно, в процессе выполнения инструкции, вполне возможны изменения в данных, которые еще не были прочитаны. Serializable всего лишь обеспечивает отсутствие каких-либо фантомов, а не атомарность инструкции.
Тут вы не правны или не точны в высказывании. Атомарность и целостность данных никак не связана с понятием времени - совершенно.
Это как не понимать теорию относительности эйнштейна о причинно следственных связей.
Два события могут иметь разный порядок наблюдения в зависимости от точки наблюдения - ACID есть ровно следствие из этого.

Диапазон будет один, целостный, который будет расширятся по мере чтения данных. А пройдёт ли этот процесс удачно, покажет только коммит. Но в процессе чтения, оно хоть и читает очередную строку но под одним спином, который лочит указанную строку, а потом читает данные из неё. Не во время, не до, а после.

Если в режиме RC целостность распространяется на одну логическую строку - когда лочятся все соединяемые строки. То для SERIALIZE целостность распространяется на все строки и на все данные целиком по всем командам. (две крайности)

И совершенно неважно и фиолетово:
- накладываешь локировки как черепаха или резво как понос
- считываешь данные после накладывания самой последней локировки в этой транзакции или сразу после накладывания для неё самой

Ещё раз сенкс invm за видение реализации механизма от MS. Всё логично.
16 янв 14, 20:10    [15426716]     Ответить | Цитировать Сообщить модератору
 Re: Как заранее заблокировать 2 диапазона ключа  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Score
Б. Может ли случится ситуация, что пока выполняется первая выборка, кто-нибудь изменит данные из которых будет выбирать вторая? Если может, то как это предотвратить ?
1. Поэтому опять вопрос. Что значит менять?
Если надо сделать целостность между данными этих двух диапазонов данных - то меняться данные могут, ибо это не значит ещё нарушение целостности.
Ибо механизм (написаный код) который будет их менять опять таки согласован со всем остальным кодом.
Если есть возможность несанкционированного доступа и несанкционированных процессов - то такой гарантии нет.

2. Гарантия целостности может гарантированно определятся только после произведённых изменений и накладывания всех необходимых локов.

Т.е. вообще невозможно запретить изменения, но при этом гарантировать целостность.

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

Иногда это очень тяжело реализовать, и тяжело контролировать, и возникают дедлоки.

invm
Вернее будет, но только при пустом диапазоне.
Это очень верный ракурс.
Вот рассмотрим некую реализацию в вакууме:
AND NOT Exists(SELECT * FROM dbo.Table2 T2 WITH(HoldLock) WHERE T2.Key BETWEEN 1 AND 100500 AND T2.Lock = 1)
Как оно будет работать?
Если там дофига данных, то получается что будет сканиться все строки, локируя диапазон. В итоге диапазон "2" будет залочен и основная часть запрос может "запустится", но при этом процессе это не запрещает при этом менять данные из этого основного запроса.

Но я не об этом. Если повесить фильтрованный индекс Lock = 1, то соответственно "пустой диапазон" даст быстрый результат.

3. Если у вас целостность данных завязана между этими двумя диапазонами как 1 к 1-му. То изначальный вопрос был не верен.
Вам не нужно проверять одновременную целостность между всеми данными сразу.

4. Если надо соблюдать целостность огромного числа строк, то из теории порядка локировок идёт очевидное следствие - любое изменение этих строк желательно направить через предварительное чтение/изменение одной строки-состояния.
Изменяя значение этой строки вы тем самым прекращаете эти нескончаемые потоки параллельных процессов.
16 янв 14, 21:10    [15426916]     Ответить | Цитировать Сообщить модератору
 Re: Как заранее заблокировать 2 диапазона ключа  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Mnior
И совершенно неважно и фиолетово:
- накладываешь локировки как черепаха или резво как понос
- считываешь данные после накладывания самой последней локировки в этой транзакции или сразу после накладывания для неё самой
Уточню.
- Совершенно неважно накладываете ли вы локи по мере считывания или нет (предварительно).
К примеру, можно для диапазона придумать механизм беганья по самим локам, а не данным.

И в ракурсе того, что у MS есть варианты (15406005) заблокировать несуществующие строки, читая данные из иной таблицы.
16 янв 14, 21:28    [15426982]     Ответить | Цитировать Сообщить модератору
 Re: Как заранее заблокировать 2 диапазона ключа  [new]
Scrore
Guest
дяденька, вы сейчас с кем разговаривали?
16 янв 14, 21:41    [15427015]     Ответить | Цитировать Сообщить модератору
 Re: Как заранее заблокировать 2 диапазона ключа  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Scrore
дяденька, вы сейчас с кем разговаривали?
Кто здесь?

Вообще-то это в первую очередь вам, Scrore, капитан очевидность всё пережёвывал, но на китайском.
Да, такая у меня роль.

Если что-то непонятно, спрашивайте. Или если есть что сказать - пишите.
Я настаиваю.
17 янв 14, 00:09    [15427547]     Ответить | Цитировать Сообщить модератору
 Re: Как заранее заблокировать 2 диапазона ключа  [new]
Score
Guest
Mnior
Ещё раз сенкс invm за видение реализации механизма от MS. Всё логично.
т.е. вы тоже считаете, что записи будут блокироваться не заранее, а при считывании?

Mnior
4. Если надо соблюдать целостность огромного числа строк, то из теории порядка локировок идёт очевидное следствие - любое изменение этих строк желательно направить через предварительное чтение/изменение одной строки-состояния.
Изменяя значение этой строки вы тем самым прекращаете эти нескончаемые потоки параллельных процессов.
вы предлагаете сделать внешний флаг - признак блокированности таблицы, и устанавливать его вручную?
26 янв 14, 14:13    [15470628]     Ответить | Цитировать Сообщить модератору
 Re: Как заранее заблокировать 2 диапазона ключа  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Score
т.е. вы тоже считаете, что записи будут блокироваться не заранее, а при считывании?
А вы что не верите invm ?!
Судя по поведению (ещё и в другой ветке обсуждалось), так оно и есть.

И это, всё относительно. Т.е. если не считать эскалации локировки (не забывайте об этом, раз) и явного указания хинтов, то да - по мере считывания.
Но даже если и считать, то тоже по мере считывания.
Вы не можете одновременно заблокировать два разных объекта за раз, а только один: одну строку, одну страницу, один сегмент, одну таблицу, одну базу, одну локировку приложения (именованную) ...

Логика целостности строится на локировании одних и тех же объектов, и на правильном порядке локирования, независимо от того что эти процессы разные. (это два)

Score
вы предлагаете сделать внешний флаг - признак блокированности таблицы, и устанавливать его вручную?
Не признак блокированности таблицы (для этого и TablLock бы подошёл бы), а у объекта бизнес процесса.
Я не знаю что у вас за процесс, чтобы сказать как он называется.
Если у вам нужно блокировать всю таблицу - блокируйте.

Слишком "крупный" объект заблокируете, будут ненужные задержки других процессов, слишком мало - нарушите целостность, не в том порядке - будут дедлоки.
27 янв 14, 17:29    [15477114]     Ответить | Цитировать Сообщить модератору
 Re: Как заранее заблокировать 2 диапазона ключа  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
Score
чтобы выборки получали согласованные данные
Каков критерий согласованности?
27 янв 14, 18:18    [15477478]     Ответить | Цитировать Сообщить модератору
 Re: Как заранее заблокировать 2 диапазона ключа  [new]
Score
Guest
invm
Score
чтобы выборки получали согласованные данные
Каков критерий согласованности?
Таблица представляет из себя некий набор данных записанный однажды единомоментно. Если выполняем две последовательные выборки из разных диапазонов этой таблицы, то согласованностью считаем то, что эти выборки из одного набора.
27 янв 14, 20:42    [15478247]     Ответить | Цитировать Сообщить модератору
 Re: Как заранее заблокировать 2 диапазона ключа  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
+
Score
Б. Может ли случится ситуация, что пока выполняется первая выборка, кто-нибудь изменит данные из которых будет выбирать вторая? Если может, то как это предотвратить ?

Score
Если выполняем две последовательные выборки из разных диапазонов этой таблицы, то согласованностью считаем то, что эти выборки из одного набора.
+ ?
1. Вопрос в другом. Как вы отделяете эти выборки как одну?
Случайные строки подойдут или есть критерий отбора?
Что вы с ними делаете?

2. В чём проблема?
У вас возникают проблемы дедлоков?
Или вы проектируете систему и думаете как предотвратить возможные ошибки/проблемы?
Или теоретический вопрос?
Если эти данные (два диапазона) не должны быть согласованны с другими данными, и дедлок приемлем - уровень Serialize, или любой снэпшот (тогда и без дедов).
28 янв 14, 01:40    [15479236]     Ответить | Цитировать Сообщить модератору
 Re: Как заранее заблокировать 2 диапазона ключа  [new]
Score
Guest
Mnior
1. Вопрос в другом. Как вы отделяете эти выборки как одну?
Случайные строки подойдут или есть критерий отбора?
Что вы с ними делаете?

2. В чём проблема?
У вас возникают проблемы дедлоков?
Или вы проектируете систему и думаете как предотвратить возможные ошибки/проблемы?
Или теоретический вопрос?
1. выборки могут пересекаться, но могут и нет
2. проектируем систему и думает, как предотвратить возможные ошибки
10 фев 14, 08:21    [15544453]     Ответить | Цитировать Сообщить модератору
 Re: Как заранее заблокировать 2 диапазона ключа  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Score,

1м пунктом вы ответили на совершенно другой вопрос.

Как логически связаны эти два диапазона? Каков критерий отбора строк.
Что вы с ними делаете?

Там не даром было написано "Случайные строки подойдут".


Вяло как-то проектируете систему.
11 фев 14, 04:13    [15549110]     Ответить | Цитировать Сообщить модератору
 Re: Как заранее заблокировать 2 диапазона ключа  [new]
Score
Guest
Mnior
Score,

1м пунктом вы ответили на совершенно другой вопрос.

Как логически связаны эти два диапазона? Каков критерий отбора строк.
Что вы с ними делаете?

Там не даром было написано "Случайные строки подойдут".


Вяло как-то проектируете систему.
Исходя из этого обсуждения и экспериментов я решил использовать другой способ изоляции данных (не используя транзации/блокировки sql). Поэтому вопрос из практического стал теоритическим и на ваши точные вопросы уже не могу ответить. Спасибо вам за помощь.
11 фев 14, 13:25    [15550916]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить