none
MS SQL 2008 R2. T-SQL. Многопоточность или обеспечение реентерабельности процедуры RRS feed

  • Вопрос

  • Доброго времени суток!

     

    Вопрос наверное больше к программерам по MS SQL 2008 R2.

    Есть таблица

     

    tblCity

    iCity_id int (PK,IDENTITY(1,1))

    sCity nvarchar(50)

     

    Есть хранимая процедура, на вход которой поступает в том числе название города (@sCity)

    Необходимо получить iCity_id в переменную @iCity_id. При этом, если такого города в таблице ещё нет, добавить его и опять же получить сгенерённый id в переменную.

    Собственно первое что приходит в голову:

     

    ...

    SELECT @iCity_id=[iCity_id]

    FROM [dbo].[tblCity]

    WHERE [sCity]=@sCity

     

    IF @@ROWCOUNT = 0

      BEGIN

        INSERT INTO [dbo].[tblCity]([sCity])

        VALUES (@sCity)

        SET @iCity_id=@@IDENTITY

      END

    ...

     

    В однопользовательской однопоточной среде все хорошо.

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

     

    Собственно вопрос: как такую задачу решать?

    28 марта 2012 г. 4:42

Ответы

  • А не соблаговарит уважаемый джинн меня носом ткнуть конкретно что читать?

    Нда уж...

    create table tbl_20120328_01 ( id int identity, city varchar(50) primary key )
    go
    insert into tbl_20120328_01
    values 
      ('Екатеринбург'), ('Киев'), ('Минск')
    go
    
    
    --1 транзакция
    begin tran
    if not exists( select * from tbl_20120328_01 with (xlock, holdlock) 
                         where city = 'Москва' 
                    ) 
    print 'ok'
    
    
    --2 транзакция
    if not exists( select * from tbl_20120328_01 with (xlock, holdlock) 
                         where city = 'Москва' 
                    ) 
    print 'ok'


    http://www.t-sql.ru

    • Помечено в качестве ответа cryman 28 марта 2012 г. 12:17
    28 марта 2012 г. 11:57
    Отвечающий

Все ответы

  • Доброго времени суток!

    Вопрос наверное больше к программерам по MS SQL 2008 R2.

    Есть таблица

    tblCity

    iCity_id int (PK,IDENTITY(1,1))

    sCity nvarchar(50)

    Есть хранимая процедура, на вход которой поступает в том числе название города (@sCity)

    Необходимо получить iCity_id в переменную @iCity_id. При этом, если такого города в таблице ещё нет, добавить его и опять же получить сгенерённый id в переменную.

    Собственно первое что приходит в голову:

    ...

    SELECT @iCity_id=[iCity_id]

    FROM [dbo].[tblCity]

    WHERE [sCity]=@sCity

    IF @@ROWCOUNT = 0

      BEGIN

        INSERT INTO [dbo].[tblCity]([sCity])

        VALUES (@sCity)

        SET @iCity_id=@@IDENTITY

      END

    ...

    В однопользовательской однопоточной среде все хорошо.

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

    Собственно вопрос: как такую задачу решать?

    1) Не использовать @@IDENTITY (причины описаны в бол)

    2) Накладывать блокировку, чтобы гарантировать, что другой поток не вставит дубль

    3) посмотреть в сторону операторов OUTPUT и возможно MERGE


    http://www.t-sql.ru

    28 марта 2012 г. 5:35
    Отвечающий
  • Очень просто. С некоторых пор у INSERT-а появилась клаузула OUTPUT, сообщающая вам значения строки затронутые ЭТИМ (и никаким иным!) инсертом.

    Вот почти ваш пример. Ну и BOL, конечно.


    www.sqlCMD.ru - all around MS SQL Server

    28 марта 2012 г. 5:38
  • Если под блокировкой понимается BEGIN TRANSACTION, то не поможет, ибо транзакция фактически начинается на инсерте. Проверялось.

    28 марта 2012 г. 7:36
  • Про OUTPUT я помню, спасибо. Однако, чем мне это поможет в случае, когда инсерт делать не нужно? То есть когда первый селект мне что-то возвращает.

    28 марта 2012 г. 7:37
  • Если под блокировкой понимается BEGIN TRANSACTION, то не поможет, ибо транзакция фактически начинается на инсерте. Проверялось.


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

    http://www.t-sql.ru

    28 марта 2012 г. 9:02
    Отвечающий
  • Про OUTPUT я помню, спасибо. Однако, чем мне это поможет в случае, когда инсерт делать не нужно? То есть когда первый селект мне что-то возвращает.


    Так когда вам инсерт делать не нужно, то вы явным селектом извлекаете данные. Либо, как я уже писал выше можно задействовать MERGE который будет добавлять запись, если её нет и обновлять значение само на себя если есть...и опять же через OUTPUT вытаскивать идентификатор для обеих операций

    http://www.t-sql.ru

    28 марта 2012 г. 9:03
    Отвечающий
  • 1. По блокировкам. Может быть я что-то не понимаю, но даже установка SET TRANSACTION ISOLATION LEVEL SERIALIZABLE не приводит к блокировке таблицы на селект другой операцией селекта. То есть потенциально есть вероятность того, что  два потока в разных сеансах одновременно запросят таблицу tblCity на предмет наличия в ней города "Москва". И если его там нет, то в обоих потоках я получу @@ROWCOUNT = 0 со всеми вытекающими.

    2. MERGE либо апдейтит, либо вставляет, либо удаляет данные. Мне не надо ни апдейтить, ни удалять. Мне либо получать ID, который там уже есть, либо вставлять новое значение с получением добавленного идентификатора. А вот синтаксис MERGE для найденного предполагает либо удаление, либо апдейт

    <merge_matched>::=
        { UPDATE SET <set_clause> | DELETE }

    Как получать идентификатор, либо через OUTPUT, либо через @@IDENTITY на данном этапе не принципиально, ибо потенциально конкурирующие операции будут осуществляться в разных коннектах.

    28 марта 2012 г. 9:44
  • >>MERGE либо апдейтит, либо вставляет, либо удаляет данные.

    Это потому, что вы смотрите "не свою" ветку. А вот - ваша:

    <merge_not_matched>::=
    {
        INSERT [ ( column_list ) ] 
            { VALUES ( values_list )
            | DEFAULT VALUES }
    }

    >>1. По блокировкам. Может быть я что-то не понимаю, но даже установка SET TRANSACTION ISOLATION LEVEL SERIALIZABLE не приводит к блокировке таблицы на селект другой операцией селекта. То есть потенциально есть вероятность того, что  два потока в разных сеансах одновременно запросят таблицу tblCity на предмет наличия в ней города "Москва". И если его там нет, то в обоих потоках я получу @@ROWCOUNT = 0 со всеми вытекающими.

    Это потому, что в описанном у вас SELECT+INSERT. А мерж - одна команда. Улавливаете?

    www.sqlCMD.ru - all around MS SQL Server

    28 марта 2012 г. 10:13
  • Стоп. С merge_not_matched как раз все понятно и просто. Мне нужно именно merge_matched, то есть когда такое значение уже есть получить его ID. Alexey Knyazev предлагает в этом случае делать апдейт на такое же значение. Безусловно, тогда я получу все что мне надо одной инструкцией. И тогда уже можно говорить про уровне изоляции. Однако MERGE само на себя как-то совсем не нравится. Потому как на каждый чих скул будет делать апдейт с переколбашиванием всяких индексов и статистик.

    Неужели нет какого-нибудь красивого и простого способа? Просто некрасиво и сложно сделать проблем нет. Сразу аж два воркараунда в голову приходит, не считая предложенного мержа. Но сдаётся мне, что задача уже вдоль и поперёк решённая.

    28 марта 2012 г. 10:21
  • 1. По блокировкам. Может быть я что-то не понимаю, но даже установка SET TRANSACTION ISOLATION LEVEL SERIALIZABLE не приводит к блокировке таблицы на селект другой операцией селекта. То есть потенциально есть вероятность того, что  два потока в разных сеансах одновременно запросят таблицу tblCity на предмет наличия в ней города "Москва". И если его там нет, то в обоих потоках я получу @@ROWCOUNT = 0 со всеми вытекающими.

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

    ЗЫ: и все-таки начните с чтения теории по уровням изоляции


    http://www.t-sql.ru

    28 марта 2012 г. 10:50
    Отвечающий
  • Это потому, что в описанном у вас SELECT+INSERT. А мерж - одна команда.

    При этом она так же требует наложения блокировки, чтобы не получить дублей

    http://www.t-sql.ru

    28 марта 2012 г. 10:51
    Отвечающий
  • А не соблаговарит уважаемый джинн меня носом ткнуть конкретно что читать?

    Ибо проводим экскримент:

    Первый коннект:

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE (Выдержка из MSDN "Этот параметр действует так же, как и настройка HOLDLOCK всех таблиц во всех инструкциях SELECT в транзакции")
    BEGIN TRANSACTION
    SELECT * FROM [dbo].[tblCity]

    Получаю какой-то результат

    Не закрывая первого коннекта, во втором коннекте делаю тоже самое
    И тоже получаю результат
    Замечу, без COMMIT в первом
    Вот если бы в первом было бы что-то типа

    INSERT INTO [dbo].[tblCity]
    VALUES бла-бла-бла

    То второй ничего не выдаёт до COMMIT/ROLLBACK в первом

    И таким образом к моменту INSERT у меня оба коннекта думают, что "Москвы" в таблице нет. И оба радостно её вставляют. Ну либо одна не вставляет, если я определю уникальный индекс. 

    28 марта 2012 г. 11:09
  • А не соблаговарит уважаемый джинн меня носом ткнуть конкретно что читать?

    Нда уж...

    create table tbl_20120328_01 ( id int identity, city varchar(50) primary key )
    go
    insert into tbl_20120328_01
    values 
      ('Екатеринбург'), ('Киев'), ('Минск')
    go
    
    
    --1 транзакция
    begin tran
    if not exists( select * from tbl_20120328_01 with (xlock, holdlock) 
                         where city = 'Москва' 
                    ) 
    print 'ok'
    
    
    --2 транзакция
    if not exists( select * from tbl_20120328_01 with (xlock, holdlock) 
                         where city = 'Москва' 
                    ) 
    print 'ok'


    http://www.t-sql.ru

    • Помечено в качестве ответа cryman 28 марта 2012 г. 12:17
    28 марта 2012 г. 11:57
    Отвечающий
  • Да, мне тоже зачастую проще самому сделать чем обьяснять

    Но полюбому, спасибо. Есть пища к размышлению

    28 марта 2012 г. 12:17
  • Это потому, что в описанном у вас SELECT+INSERT. А мерж - одна команда.


    При этом она так же требует наложения блокировки, чтобы не получить дублей

    http://www.t-sql.ru

    Отдельной и специальной - нет. Достаточно выставить требуемый уровень изоляции, тот же сериализейбл, для примера.

    www.sqlCMD.ru - all around MS SQL Server

    28 марта 2012 г. 13:28
  • Это потому, что в описанном у вас SELECT+INSERT. А мерж - одна команда.


    При этом она так же требует наложения блокировки, чтобы не получить дублей

    http://www.t-sql.ru

    Отдельной и специальной - нет. Достаточно выставить требуемый уровень изоляции, тот же сериализейбл, для примера.

    www.sqlCMD.ru - all around MS SQL Server

    Про это и разговор, что в данном случаи повышать уровень изоляции все равно придётся, т.к. иначе, при ридкоммиттед, мы можем получить дубли т.к. при "уровне изоляции по-умолчанию" мы не удерживаем блокировку в момент самого чтения

    http://www.t-sql.ru

    28 марта 2012 г. 13:36
    Отвечающий