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

Откуда:
Сообщений: 770
sql2012

Как применяется UNIQUE на таблицу при UPDATE этой таблицы вне явной транзакции?
  • на каждую запись в процессе изменения
  • после изменения всех затронутых записей
  • другое

    Для примера:
    SET IMPLICIT_TRANSACTIONS Off
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    
    create table #t
    (id  char(1) not null
    ,IsCurrent int null
    ,UQorNull as isnull(convert(binary(1),nullif(IsCurrent,0)),convert(binary(1),Id)) persisted not null
    ,unique clustered(Id desc)
    ,unique(UQorNull)) --для задания уникальности IsCurrent при не null значениях
    
    insert #t(Id,IsCurrent)
    select 'a',1
    union all
    select 'b',null
    union all
    select 'c',null
    
    select * from #t
    
    update
    #t
    set
    IsCurrent= case Id
    when 'a' then null
    when 'b' then 1
    when 'c' then null
    end
    
    select * from #t
    
    drop table #t
    


    Если в unique (Id ...) указан desc, то unique(UQorNull) должно бы нарушаться в процессе.

    Кажется, порядок следования записей при update явно задавать нельзя.
    Запрос работает из-за случайного попадания в порядок несмотря на предполагаемое действие кластерного индекса?
    Будет ли этот запрос работать при других условиях и в других версиях sql или он опирается на недокументированное поведение?
  • 9 авг 16, 16:08    [19520814]     Ответить | Цитировать Сообщить модератору
     Re: порядок записей в update или последовательность применения unique  [new]
    Гавриленко Сергей Алексеевич
    Member

    Откуда: Moscow
    Сообщений: 37228
    this: на каждую запись в процессе изменения

    tunknown
    Если в unique (Id ...) указан desc

    Порядок индекса может влиять только неявно, если сервер выберет ordered-скан.
    9 авг 16, 16:29    [19520939]     Ответить | Цитировать Сообщить модератору
     Re: порядок записей в update или последовательность применения unique  [new]
    tunknown
    Member

    Откуда:
    Сообщений: 770
    Гавриленко Сергей Алексеевич
    Порядок индекса может влиять только неявно, если сервер выберет ordered-скан.

    Спасибо.
    При unique clustered(Id аsc) и unique clustered(Id desc) план для update одинаковый.
    И в этом случае нельзя полагаться.
    9 авг 16, 16:45    [19521013]     Ответить | Цитировать Сообщить модератору
     Re: порядок записей в update или последовательность применения unique  [new]
    iap
    Member

    Откуда: Москва
    Сообщений: 47085
    tunknown
    Гавриленко Сергей Алексеевич
    Порядок индекса может влиять только неявно, если сервер выберет ordered-скан.

    Спасибо.
    При unique clustered(Id аsc) и unique clustered(Id desc) план для update одинаковый.
    И в этом случае нельзя полагаться.
    На порядок записей вообще нигде нельзя полагаться, за исключением заданного в ORDER BY.
    9 авг 16, 16:53    [19521052]     Ответить | Цитировать Сообщить модератору
     Re: порядок записей в update или последовательность применения unique  [new]
    daw
    Member

    Откуда: Муром -> Москва
    Сообщений: 7381
    Гавриленко Сергей Алексеевич
    this: на каждую запись в процессе изменения


    эээ. вообще-то, _логически_ констрейнты проверяются по состоянию таблицы после всех изменений, произведенных стейтментом. как это организовать физически - это уже дело сервера.
    9 авг 16, 17:04    [19521112]     Ответить | Цитировать Сообщить модератору
     Re: порядок записей в update или последовательность применения unique  [new]
    daw
    Member

    Откуда: Муром -> Москва
    Сообщений: 7381
    tunknown,

    для размышления:
    create table t (id int identity, c int unique);
    insert into t (c) values (1);
    insert into t (c) values (0);
    
    select * from t
    
    update t set c = case when c = 1 then 0 else 1 end
    
    select * from t
    
    go
    drop table t
    
    9 авг 16, 17:06    [19521132]     Ответить | Цитировать Сообщить модератору
     Re: порядок записей в update или последовательность применения unique  [new]
    tunknown
    Member

    Откуда:
    Сообщений: 770
    daw
    для размышления:

    работает, но как выяснили- на порядок и в этом случае полагаться нельзя
    9 авг 16, 17:19    [19521209]     Ответить | Цитировать Сообщить модератору
     Re: порядок записей в update или последовательность применения unique  [new]
    daw
    Member

    Откуда: Муром -> Москва
    Сообщений: 7381
    tunknown
    daw
    для размышления:

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


    а подумать? если б констрейнт проверялся после изменения каждой записи, этот запрос при любом порядке не работал бы.
    9 авг 16, 17:21    [19521223]     Ответить | Цитировать Сообщить модератору
     Re: порядок записей в update или последовательность применения unique  [new]
    o-o
    Guest
    пусть есть таблица со столбцом натуральных чисел без пропусков и этот столбец ПК.
    теперь что, нельзя проапдэйтить ПК, прибавляя единичку?
    типа на первой же строке получим значение из второй?
    create table dbo.t (id int primary key);
    
    insert into dbo.t(id)
    values (1), (2), (3);
    
    update dbo.t
    set id = id + 1;
    

    плюсую к ответу daw, короче
    9 авг 16, 17:29    [19521270]     Ответить | Цитировать Сообщить модератору
     Re: порядок записей в update или последовательность применения unique  [new]
    Гавриленко Сергей Алексеевич
    Member

    Откуда: Moscow
    Сообщений: 37228
    daw
    Гавриленко Сергей Алексеевич
    this: на каждую запись в процессе изменения


    эээ. вообще-то, _логически_ констрейнты проверяются по состоянию таблицы после всех изменений, произведенных стейтментом. как это организовать физически - это уже дело сервера.
    Да, перепутал.
    9 авг 16, 17:41    [19521335]     Ответить | Цитировать Сообщить модератору
     Re: порядок записей в update или последовательность применения unique  [new]
    tunknown
    Member

    Откуда:
    Сообщений: 770
    o-o
    плюсую к ответу daw, короче

    однако, работает и в более жёстком варианте с foreign key
    use tempdb
    create table t
    ( id int
    ,parent int references t(id)
    ,unique clustered (id asc))
    
    insert t
    select 1,null
    union all
    select 2,1
    union all
    select 3,2
    
    update t set id=id+1,parent=parent+1
    
    select * from t
    
    drop table t
    
    9 авг 16, 17:54    [19521387]     Ответить | Цитировать Сообщить модератору
     Re: порядок записей в update или последовательность применения unique  [new]
    Mind
    Member

    Откуда: Лучший город на Земле
    Сообщений: 2322
    tunknown
    sql2012

    Как применяется UNIQUE на таблицу при UPDATE этой таблицы вне явной транзакции?
    А какая разница, явная/неявная? Транзакция то все равно есть, на все записи.
    9 авг 16, 21:09    [19522180]     Ответить | Цитировать Сообщить модератору
     Re: порядок записей в update или последовательность применения unique  [new]
    alexeyvg
    Member

    Откуда: Moscow
    Сообщений: 31868
    tunknown
    o-o
    плюсую к ответу daw, короче

    однако, работает и в более жёстком варианте с foreign key
    Разумеется. Написали же:
    daw
    _логически_ констрейнты проверяются по состоянию таблицы после всех изменений, произведенных стейтментом.

    Это работает даже в самых сверхжёстких и ультранавороченных вариантах с констрейнами типа foreign key, primary key, check. и даже с unique index. И даже с проверками в триггерах тоже будет работать.
    10 авг 16, 10:13    [19523435]     Ответить | Цитировать Сообщить модератору
     Re: порядок записей в update или последовательность применения unique  [new]
    tunknown
    Member

    Откуда:
    Сообщений: 770
    alexeyvg
    И даже с проверками в триггерах тоже будет работать.

    видимо, не во всех случаях?
    BOL
    An AFTER trigger is executed only after the triggering SQL statement has executed successfully. This successful execution includes all referential cascade actions and constraint checks associated with the object updated or deleted.
    10 авг 16, 10:23    [19523483]     Ответить | Цитировать Сообщить модератору
     Re: порядок записей в update или последовательность применения unique  [new]
    tunknown
    Member

    Откуда:
    Сообщений: 770
    Существует какой-нибудь источник, где бы подробно был описан процесс модификации данных? В BOL разрозненно и подробностей не удалось найти.
    10 авг 16, 10:27    [19523503]     Ответить | Цитировать Сообщить модератору
     Re: порядок записей в update или последовательность применения unique  [new]
    alexeyvg
    Member

    Откуда: Moscow
    Сообщений: 31868
    tunknown
    alexeyvg
    И даже с проверками в триггерах тоже будет работать.

    видимо, не во всех случаях?
    BOL
    An AFTER trigger is executed only after the triggering SQL statement has executed successfully. This successful execution includes all referential cascade actions and constraint checks associated with the object updated or deleted.
    Я имею в виду, что триггер вызывается тоже после всех изменений данных (как, собственно, и написано в этой цитате). Т.е. его работа тоже не будет зависеть от "порядка модификации строк".
    10 авг 16, 10:38    [19523555]     Ответить | Цитировать Сообщить модератору
     Re: порядок записей в update или последовательность применения unique  [new]
    alexeyvg
    Member

    Откуда: Moscow
    Сообщений: 31868
    tunknown
    Существует какой-нибудь источник, где бы подробно был описан процесс модификации данных? В BOL разрозненно и подробностей не удалось найти.
    Дык процесс очень сложный, "в одном месте" описание займёт сотни страниц.
    И люди зарабатывают понимание всего этого ценой нескольких лет плотного изучения.

    Вот, например, почитайте посты знаменитого SomewhereSomehow, пользователя нашего форума, и его блог: http://www.queryprocessor.com/

    Он как раз заплатил за эти знания годами изучения :-)
    10 авг 16, 10:47    [19523593]     Ответить | Цитировать Сообщить модератору
     Re: порядок записей в update или последовательность применения unique  [new]
    Владислав Колосов
    Member

    Откуда:
    Сообщений: 8584
    Логично предположить, что проверки ограничений идут вторым проходом после завершение действия одиночной инструкции и до фиксации транзакции.
    10 авг 16, 12:52    [19524398]     Ответить | Цитировать Сообщить модератору
     Re: порядок записей в update или последовательность применения unique  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9724
    tunknown
    Существует какой-нибудь источник, где бы подробно был описан процесс модификации данных?
    Да. Называется - план выполнения запроса. Там все видно.
    Для обновления уникальных индексов задействуется специальный механизм. Описан тут
    10 авг 16, 13:35    [19524719]     Ответить | Цитировать Сообщить модератору
     Re: порядок записей в update или последовательность применения unique  [new]
    SomewhereSomehow
    Member

    Откуда: Moscow
    Сообщений: 2480
    Блог
    У меня в блоге, к сожалению, ничего нет про обновления, собирался когда-то написать, но так и не собрался, отчасти потому, что очень хороший анализ таких вещей уже есть.

    Советую почитать вот это у Paul White-a:
    http://sqlblog.com/blogs/paul_white/archive/2013/01/26/optimizing-t-sql-queries-that-change-data.aspx
    http://sqlblog.com/blogs/paul_white/archive/2013/02/21/halloween-protection-the-complete-series.aspx

    Скачать и посмотреть доклад Conor Cunningham:
    https://sqlbits.com/Sessions/Event8/Update_Queries-Deep_Dive

    И вот эти посты у Craig Freedman:
    https://blogs.msdn.microsoft.com/craigfr/2007/08/15/optimized-non-clustered-index-maintenance/
    https://blogs.msdn.microsoft.com/craigfr/2007/08/22/optimized-non-clustered-index-maintenance-in-per-index-plans/
    https://blogs.msdn.microsoft.com/craigfr/2007/09/06/maintaining-unique-indexes/
    https://blogs.msdn.microsoft.com/craigfr/2008/01/30/maintaining-unique-indexes-with-ignore_dup_key/
    https://blogs.msdn.microsoft.com/craigfr/2008/02/27/halloween-protection/
    10 авг 16, 13:59    [19524888]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить