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

Откуда: Харьков
Сообщений: 2947
День добрый.

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

В общем написал я тут процедуру:

CREATE PROCEDURE dbo.GetIncrementedValue (
  @Numerator LongName,
  @Increment INT)
AS
BEGIN
  CREATE TABLE [#Value] ([Value] BIGINT);
    
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  
  BEGIN TRANSACTION;
       
  BEGIN TRY    
    MERGE Numerators AS T
	  USING (SELECT @Numerator, @Increment) AS S (Numerator, Increment) ON T.[Name] = S.Numerator
      WHEN MATCHED THEN UPDATE SET [Value] = [Value] + S.Increment
      WHEN NOT MATCHED THEN INSERT ([Name], [Value]) VALUES (S.Numerator, S.Increment)
      OUTPUT INSERTED.[Value] INTO [#Value];
      
    COMMIT
  END TRY
  BEGIN CATCH    
    ROLLBACK            
    
    RAISERROR('Can not get incremented generator value', 16, 1);    
  END CATCH
  
  SELECT * FROM [#Value];
END


В общем в результате возникла каша в голове с блокировками, уровнями изоляции и всей этой мутатой.

Это процедура должна работать аналогично вызову NEXT VALUE FOR: http://msdn.microsoft.com/en-us/library/ff878370.aspx

Т.е. не должна генерировать и гарантировать уникальные значения не зависимо от настройки и взаимодействия транзакций.

Тут у нас всплыло непонимание работы транзакций и команды MERGE в частности. Вот такие вот есть вопросы:

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

2) Для варианта, когда нумератора не существует, обе транзакции попытаются выполнить инсёрт и одна из них упадёт, потому что имя нумератора в таблице уникально (первичный ключ). Нам это не прокатывает. Вопрос чё делать? Ну кроме нескольких попыток.

Я программирую на C# и там у нас есть механизм блокировок. Типа пишешь lock (synchronizationObject), а дальше всё работает по принципу кто первый - того и тапки, а остальным ждать. Т.е. кто первый получил лок, тот входит внутрь блока, а остальные просто ждут. Нам, в идеале, нужно что-то аналогичное замутить в хранимой процедуре. Другими словами чтобы лочить выполнение SQL-сценария. Объектом синхронизации было бы имя нумератора и тогда для одного нумератора всегда бы выполнялась только одна процедура. Это возможно вообще как-то?
9 янв 13, 17:01    [13742791]     Ответить | Цитировать Сообщить модератору
 Re: Команда MERGE для эмуляции последовательностей  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Gold,

копайте в сторону [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] >>> <table_hint_limited>
9 янв 13, 18:46    [13743356]     Ответить | Цитировать Сообщить модератору
 Re: Команда MERGE для эмуляции последовательностей  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
WarAnt,

serializable - это самый высокий уровень изоляции транзакций или говоря на вашем языке это:
WarAnt
кто первый - того и тапки, а остальным ждать. Т.е. кто первый получил лок, тот входит внутрь блока, а остальные просто ждут.

Ответы для ниже приведеной процедуры и уровня изоляции serializable:
WarAnt
1) Представим что имеется две конкурирующих транзакции. Для варианта, когда нумератор уже существует, если обе транзакции попытаются одновременно проапдейтить значение, то первая зафиксируется, а вторая будет ждать выполнения первой и потом тоже зафиксируется или возникнет исключение?

Инструкция merge состоит из двух частей, считиывания данных таблицы и ее обновления. Этап считывания нужен для того, чтобы определить есть запись в таблице или нет, т.е. нам надо определить будем мы ее обновлять или вставлять. (не уверен в этом на 100%, но по крайней мере вижу это в плане :) ). Уровень изоляции serializable запрещает считывание данных которые вставляются/обновляются другими транзакциями. Т.е. для вашего случая будем иметь ждущую 2-ю транзакцию, которая успешно выполнится после завершения первой, получив следующий пакет новых номеров.

WarAnt
2) Для варианта, когда нумератора не существует, обе транзакции попытаются выполнить инсёрт и одна из них упадёт, потому что имя нумератора в таблице уникально (первичный ключ). Нам это не прокатывает. Вопрос чё делать? Ну кроме нескольких попыток.

Вышесказаное относится и для вставки. Вторая транзакция будет ждать когда завершиться первая еще на этапе зачитки данных. После того как первая транзакция завершиться, вторая зачитает данные и выполнит обновление таблицы (update - если первая транзакция была успешной, insert - если первая откатилась).

create table dbo.Numerators ( 
    Numerator varchar(32) primary key clustered
  , Value int
)
go

CREATE PROCEDURE dbo.GetIncrementedValue (
  @Numerator varchar(32),
  @Increment INT
)
AS
BEGIN
       
  BEGIN TRY    
  
    begin transaction
    
      MERGE Numerators with ( rowlock, serializable ) AS T
	  USING (SELECT @Numerator, @Increment) AS S (Numerator, Increment) ON T.Numerator = S.Numerator
      WHEN MATCHED THEN UPDATE SET [Value] = [Value] + S.Increment
      WHEN NOT MATCHED THEN INSERT (Numerator, [Value]) VALUES (S.Numerator, S.Increment)
      OUTPUT INSERTED.[Value];
	
    commit transaction
	    
  END TRY
  BEGIN CATCH  
  
    rollback transaction
    
    RAISERROR('Can not get incremented generator value', 16, 1);   
	 
  END CATCH
  
END
10 янв 13, 00:10    [13744325]     Ответить | Цитировать Сообщить модератору
 Re: Команда MERGE для эмуляции последовательностей  [new]
Gold
Member

Откуда: Харьков
Сообщений: 2947
Блин, так а мне не надо лочить всю таблицу. Если я хочу параллельно два диапазона для разных нумераторов получить, то они должны параллельно выполниться.

PS а так спасибо за разъяснение.
10 янв 13, 18:49    [13749432]     Ответить | Цитировать Сообщить модератору
 Re: Команда MERGE для эмуляции последовательностей  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
Gold
Если я хочу параллельно два диапазона для разных нумераторов получить, то они должны параллельно выполниться.
CREATE PROCEDURE dbo.GetIncrementedValue (
  @Numerator varchar(32),
  @Increment INT
)
AS
BEGIN
       
  BEGIN TRY    
  
    begin transaction
    
      declare @result int;
 
       exec @result = sp_getapplock @Numerator, 'Exclusive', 'Transaction', 0;
       if @result <> 0
        raiserror('Cannot obtain application lock on "%s"', 16, 1, @Numerator);
      
      MERGE Numerators with (rowlock) AS T
	  USING (SELECT @Numerator, @Increment) AS S (Numerator, Increment) ON T.Numerator = S.Numerator
      WHEN MATCHED THEN UPDATE SET [Value] = [Value] + S.Increment
      WHEN NOT MATCHED THEN INSERT (Numerator, [Value]) VALUES (S.Numerator, S.Increment)
      OUTPUT INSERTED.[Value];
	
    commit transaction
	    
  END TRY
  BEGIN CATCH  
  
    rollback transaction
    
    RAISERROR('Can not get incremented generator value', 16, 1);   
	 
  END CATCH
  
END
10 янв 13, 19:57    [13749781]     Ответить | Цитировать Сообщить модератору
 Re: Команда MERGE для эмуляции последовательностей  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
Gold
Блин, так а мне не надо лочить всю таблицу. Если я хочу параллельно два диапазона для разных нумераторов получить, то они должны параллельно выполниться.

Нет лочиться будет только тот нумератор, с которым в данный момент работает процедура.
На этапе зачитки будет зачитываться не вся таблица, а только одна строка соответствующая переданному нумератору, она же и будет заблокирована.
Паралельно другие нумераторы будут доступны.
10 янв 13, 21:39    [13750139]     Ответить | Цитировать Сообщить модератору
 Re: Команда MERGE для эмуляции последовательностей  [new]
Gold
Member

Откуда: Харьков
Сообщений: 2947
автор
Вышесказаное относится и для вставки. Вторая транзакция будет ждать когда завершиться первая еще на этапе зачитки данных. После того как первая транзакция завершиться, вторая зачитает данные и выполнит обновление таблицы (update - если первая транзакция была успешной, insert - если первая откатилась).


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


Такс, что-то не то тут. Представим что одновременно запускаются две транзакции для генерации номера с использованием одногои того-же не существующео генератора. Что залочит первая транзакция? Ничего! Что залочит вторая транзакция? Тоже ничего. Потом одна из них закомитится, а вторая обломится на ограничении уникальности (на первичном ключе). Т.е. для инсёрта будет таки проблема, так?


Второе, что я бы хотел прояснить, это механизм. Почему я в своём варианте использовал READ COMMITED. Потому что я думал что считывание подразумевает просто то, что сервер нашёл строку в базе данных. Я думал что после этого на строку поставится лок, начнётся апдейт, считается значение из ячейки value, прибавится инкремент, запишется новое значение и снимется блокировка. Я где-то ошибаюсь? Если нет, то тогда READ COMMITED достаточно. Или может считывание подразумевает таки сначала получение значения, которые будет потом апдейтиться?
11 янв 13, 13:27    [13753549]     Ответить | Цитировать Сообщить модератору
 Re: Команда MERGE для эмуляции последовательностей  [new]
Gold
Member

Откуда: Харьков
Сообщений: 2947
invm, спасибо за sp_getapplock. Только мне нужно в случае, если блокировка установлена, ждать, а не сразу возвращать результат. Есть такая возможность?
11 янв 13, 13:32    [13753592]     Ответить | Цитировать Сообщить модератору
 Re: Команда MERGE для эмуляции последовательностей  [new]
Gold
Member

Откуда: Харьков
Сообщений: 2947
Ой, не досомтрел, sp_getapplock - это, похоже, то что надо!!!
11 янв 13, 13:33    [13753601]     Ответить | Цитировать Сообщить модератору
 Re: Команда MERGE для эмуляции последовательностей  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
Gold
Такс, что-то не то тут. Представим что одновременно запускаются две транзакции для генерации номера с использованием одногои того-же не существующео генератора. Что залочит первая транзакция? Ничего! Что залочит вторая транзакция? Тоже ничего. Потом одна из них закомитится, а вторая обломится на ограничении уникальности (на первичном ключе). Т.е. для инсёрта будет таки проблема, так?

Первая транзакция залочит диапазон значений указаных в условии. Для нас условие это имя нового нумератора.

BOL
SERIALIZABLE

Specifies the following:
* Statements cannot read data that has been modified but not yet committed by other transactions.
* No other transactions can modify data that has been read by the current transaction until the current transaction completes.
* Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction.This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction. This means that if any of the statements in a transaction are executed a second time, they will read the same set of rows.The range locks are held until the transaction completes.This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction completes.Because concurrency is lower, use this option only when necessary.This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.


Gold
Второе, что я бы хотел прояснить, это механизм. Почему я в своём варианте использовал READ COMMITED. Потому что я думал что считывание подразумевает просто то, что сервер нашёл строку в базе данных. Я думал что после этого на строку поставится лок, начнётся апдейт, считается значение из ячейки value, прибавится инкремент, запишется новое значение и снимется блокировка. Я где-то ошибаюсь? Если нет, то тогда READ COMMITED достаточно. Или может считывание подразумевает таки сначала получение значения, которые будет потом апдейтиться?

READ COMMITED достаточно если нет инсерта новых нумераторов. И все что вы написали правильно и будет работать, но только для апдейтов.
11 янв 13, 19:53    [13756504]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить