Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Check constraint and udf-function - нарушение целостности  [new]
ппп-пп
Guest
Добрый день,

1. Заинтересовался вот этим вот обсуждением темы на stackoverflow
Обсуждение темы

Пытаюсь понять причины почему у человека получается обойти check constraint.

2. В ответе от Vladimir Baranov на сайте указано:

Vladimir Baranov
If two threads run the fnCheckUserConcurrentAssignment function at the same time, they will get the same count from Tasks. Then each thread will proceed with inserting the row and the final state of the database would violate your constraint.

If you want to keep your approach with the function in the CHECK constraint, or the trigger, you should make sure that your transaction isolation level is set to SERIALIZABLE. Or use query hints to lock the table. Or use sp_getapplock to serialise calls to your function/trigger.


Но мне в этом ответе не понятно вот что. Как я знаю check constraint срабатывает уже после того, как строчка вставлена в таблицу.
Как будет реагировать функция вызванная из check constraint на строку, которая вставляется в другой сессии (с учетом, что уровень read committed).
Помогите мне, пжл, понять, правильно ли приведен ответ и почему.
27 дек 16, 13:14    [20052521]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint and udf-function - нарушение целостности  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
ппп-пп,

при READ COMMITED прочитает то что есть, и всё консистентно. Вывод известен - не используйте функции в констрейне
27 дек 16, 13:52    [20052745]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint and udf-function - нарушение целостности  [new]
ппп-пп
Guest
TaPaK,

Так вроде бы read committed еще и подождать должен, если есть not committed данные.
27 дек 16, 13:53    [20052752]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint and udf-function - нарушение целостности  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
ппп-пп,

на момент фиксации
27 дек 16, 13:55    [20052766]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint and udf-function - нарушение целостности  [new]
ппп-пп
Guest
TaPaK
ппп-пп,

на момент фиксации


Не совсем понял, что это означает. Что за термин в БД "фиксация".
27 дек 16, 13:59    [20052796]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint and udf-function - нарушение целостности  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
ппп-пп,

сдаётся гоню, жизнь на rsci даёт свои минусы :) у него на азуре дефолт rsci вот всё и живёт, а не то что он думает
27 дек 16, 14:14    [20052865]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint and udf-function - нарушение целостности  [new]
ппп-пп
Guest
TaPaK
ппп-пп,

сдаётся гоню, жизнь на rsci даёт свои минусы :) у него на азуре дефолт rsci вот всё и живёт, а не то что он думает


А вот теперь мне ваш ответ очень даже понятен. СПАСИБО.

http://borishristov.com/blog/surprises-and-a-bug-with-sql-azure-db/
27 дек 16, 14:31    [20052937]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint and udf-function - нарушение целостности  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8353
ппп-пп,

после завершения выполнения команды на изменение/вставку, между "тик" и "так".
Т.е. внутри update/insert после обработки полного объема данных.

Имеется в виду, что не всегда сразу на прочитанные данные накладывает несовместимая для операций блокировка, такое гарантировать может только SERIALIZABLE.
27 дек 16, 14:37    [20052957]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint and udf-function - нарушение целостности  [new]
ппп-пп
Guest
Владислав Колосов
ппп-пп,

после завершения выполнения команды на изменение/вставку, между "тик" и "так".
Т.е. внутри update/insert после обработки полного объема данных.

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



Что-то написанное ниже немного противоречит Вам. Ну или я не совсем понимаю, что Вы хотели сказать.

Shared (S) locks allow concurrent transactions to read (SELECT) a resource under pessimistic concurrency control. For more information, see Types of Concurrency Control. No other transactions can modify the data while shared (S) locks exist on the resource. Shared (S) locks on a resource are released as soon as the read operation completes, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction.
27 дек 16, 14:45    [20052991]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint and udf-function - нарушение целостности  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
ппп-пп
Ну или я не совсем понимаю, что Вы хотели сказать.
Просто Владислав Колосов придерживается ошибочного мнения, что ограничения проверяются после всех модификаций данных, выполненных инструкцией, а не в процессе оных.
29 дек 16, 11:44    [20059632]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint and udf-function - нарушение целостности  [new]
Владислав Колосов
Member

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

а как применяются, после изменения каждой записи?
29 дек 16, 12:29    [20059879]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint and udf-function - нарушение целостности  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
Владислав Колосов
а как применяются, после изменения каждой записи?
Конечно.

Иначе после модификации, например 100500 строк, первая из которых нарушает какое-то ограничение, придется отменить модификацию всех 100500 строк, записав в ЖТ еще 100500 записей в дополнение к уже записанным 100500 записям при самой модификации.

И все это вместо отката модификации одной единственной строки, при построчной проверке ограничений.
29 дек 16, 12:47    [20059976]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint and udf-function - нарушение целостности  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8353
Провел несложный эксперимент - проверяется ограничение для каждой строки. Меня сбила с толку недавно прочитанная статья, не могу ее отыскать, к сожалению.
29 дек 16, 13:13    [20060152]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint and udf-function - нарушение целостности  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
эээ. вы о чем?
логически, целостность контролируется по состоянию таблицы именно после всех изменений, произведенных стейтментом.
ну, хотя, я могу допустить, что в некоторых случаях _физически_ проверка может быть и построчной.
create table #t (id int identity(1, 1), c int unique clustered);
go
insert into #t (c) values (1), (0);
go
select * from #t order by id;
go
-- при построчной проверке этот апдейт бы не выполнился. изменение любой из двух строк в отдельности приведет к нарушению уникальности
update #t set c = case when c = 1 then 0 else 1 end;
go
select * from #t order by id;
go
drop table #t;

или я не понял, о чем речь в дискуссии?
29 дек 16, 15:47    [20061065]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint and udf-function - нарушение целостности  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
daw
ну, хотя, я могу допустить, что в некоторых случаях _физически_ проверка может быть и построчной.

и не всегда это оказывается реально обоснованным. ну, вот как с функциями типа описанной по ссылке, в check constraint-е.
29 дек 16, 16:03    [20061149]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint and udf-function - нарушение целостности  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
daw
логически, целостность контролируется по состоянию таблицы именно после всех изменений, произведенных стейтментом.
Логически да, физически совсем не обязательно.
В вашем примере все равно проверка уникальности физически осуществляется построчно в Clustered Index Update.
29 дек 16, 16:19    [20061220]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint and udf-function - нарушение целостности  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8353
Да, речь там шла о проверке уникальности при обновлении, теперь я вспомнил. В связи с этим используется механизм для исключения парадокса, показанного в примере.
29 дек 16, 18:46    [20061725]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint and udf-function - нарушение целостности  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
Владислав Колосов
используется механизм для исключения парадокса
Да. Но только этот механизм (split - sort - collapse) не проверяет уникальность.
29 дек 16, 19:01    [20061767]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint and udf-function - нарушение целостности  [new]
Владислав Колосов
Member

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

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

К примеру, при построчной проверке уникальности можно подготовленные к сохранению записи читать не с диска, а из временного хранилища. Или это не так работает?
29 дек 16, 19:18    [20061802]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint and udf-function - нарушение целостности  [new]
o-o
Guest
Диск тут вообще при чем?
29 дек 16, 19:36    [20061840]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint and udf-function - нарушение целостности  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
Владислав Колосов,

В примере от daw возникает так называемое "фантомное" нарушение уникальности. Для предотвращения этого существует специальный механизм - https://www.simple-talk.com/sql/learn-sql-server/showplan-operator-of-the-week-split-sort-collapse/

Уникальность же всегда проверяется в итераторах (Clustered) Index Insert (Update). Для каждой обрабатываемой ими строки.
29 дек 16, 20:12    [20061887]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint and udf-function - нарушение целостности  [new]
o-o
Guest
daw
эээ. вы о чем?
логически, целостность контролируется по состоянию таблицы именно после всех изменений, произведенных стейтментом.
ну, хотя, я могу допустить, что в некоторых случаях _физически_ проверка может быть и построчной.

конкретно так построчно:
create table checks (
    flag char(1) not null
)
 
insert checks values( 'A' )
insert checks values( 'A' )
insert checks values( 'A' )
insert checks values( 'A' )
insert checks values( 'A' )
 
go
 
create function dbo.fn_check ( @flag char(1) )
    returns varchar(3)
as
begin
    declare @status varchar(3)
 
    if exists ( select * from checks where flag <> @flag )
        set @status = 'BAD'
    else
        set @status = 'OK'
 
    return @status
 
end
go
 
alter table checks add constraint checks_udf_lookup check ( dbo.fn_check( flag ) = 'OK' )
go


update checks
    set flag = 'B'

Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "checks_udf_lookup". The conflict occurred in database "db1", table "dbo.checks", column 'flag'.
The statement has been terminated.
----
a ведь мы типа апдэйтим все строки разом,
так что не может быть "разных" значений,
по крайней мере,
"по состоянию таблицы именно после всех изменений, произведенных стейтментом"
----
вот отсюда взято
автор
Be warned that the UDF is executed row by row (for each row inserted or updated as they get updated/inserted)
so an inconsistency may occur if you are aggregating rather than just doing existance checking
29 дек 16, 23:28    [20062366]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint and udf-function - нарушение целостности  [new]
aleks2
Guest
Миф "ограничения проверяются сразу на всех строках" - происходит, видимо, от работы триггеров.

Которые, действительно, срабатывают один раз на все.
30 дек 16, 05:57    [20062683]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint and udf-function - нарушение целостности  [new]
Гулин Федор
Member

Откуда: МИНСК
Сообщений: 1298
У меня возник попутный вопрос
использование UDF в constraint плохая практика ?
30 дек 16, 18:23    [20064931]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint and udf-function - нарушение целостности  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
Гулин Федор,

Использование скалярных юдф вообще плохая практика, и не только в ограничениях
30 дек 16, 19:08    [20065007]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить