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

Как сделать чтобы нельзя была добавить запись с пересекающимисся датами с констрейнтом?
23 ноя 12, 16:39    [13520628]     Ответить | Цитировать Сообщить модератору
 Re: Constraint на пересечение дат  [new]
Glory
Member

Откуда:
Сообщений: 104760
Написать функцию по проверке таких диапазонов.
Поставить ее в констрейнт.
23 ноя 12, 16:46    [13520667]     Ответить | Цитировать Сообщить модератору
 Re: Constraint на пересечение дат  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
Триггер
23 ноя 12, 19:10    [13521515]     Ответить | Цитировать Сообщить модератору
 Re: Constraint на пересечение дат  [new]
не писатель
Guest
iap
Триггер
и только after...а то косяк у меня вышел тут :)
23 ноя 12, 22:15    [13522008]     Ответить | Цитировать Сообщить модератору
 Re: Constraint на пересечение дат  [new]
Sergei.Agalakov
Member

Откуда:
Сообщений: 575
Задача весьма нетривиальная. Решения Glory и iap не будут правильно работать в многопользовательской среде без дополнительных блокировок табличного или глобального уровня. Проблема в том что правильно проверить такой констрейнт можно только в момент коммита, где он проверит все закоммиченные данные со всех сессий. Это можно сделать в Оракле, где есть deferrable constraints и MV refresh on commit. В Оракловской ветки можно найти корректные решения этой проблемы для Оракла. В сущности это применения оптимистичной блокировки. В MS SQL Server так сделать не получится. Можно попробовать применить пессимистичную блокировку, т.е. констрайнт должен сработать для потенциальных нарушений. Подозреваю, что это можно достичь с READ UNCOMMITED.
Ну а попроще конечно можно сделать по совету Glory и iap с прибавлением блокировки уровня таблицы со всеми вытекающими последствиями.
23 ноя 12, 22:29    [13522027]     Ответить | Цитировать Сообщить модератору
 Re: Constraint на пересечение дат  [new]
dalex1973
Member

Откуда: Польша
Сообщений: 287
Sergei.Agalakov
Задача весьма нетривиальная. Решения Glory и iap не будут правильно работать в многопользовательской среде без дополнительных блокировок табличного или глобального уровня. Проблема в том что правильно проверить такой констрейнт можно только в момент коммита, где он проверит все закоммиченные данные со всех сессий. Это можно сделать в Оракле, где есть deferrable constraints и MV refresh on commit. В Оракловской ветки можно найти корректные решения этой проблемы для Оракла. В сущности это применения оптимистичной блокировки. В MS SQL Server так сделать не получится. Можно попробовать применить пессимистичную блокировку, т.е. констрайнт должен сработать для потенциальных нарушений. Подозреваю, что это можно достичь с READ UNCOMMITED.
Ну а попроще конечно можно сделать по совету Glory и iap с прибавлением блокировки уровня таблицы со всеми вытекающими последствиями.

Плюс за глубину понимания.

Однако READ UNCOMITTED(NOLOCK) не даёт гарантии. Ситуация -
1. транзакция 1 надписывает А значение Б
2. транзакция 2 видит Б значение, запись А позволена
3. транзакция 1 откатывается,Б становится А
Имеем 2 А.

Считаю поможет только HOLDLOCK. Ваше мнение?
23 ноя 12, 23:57    [13522246]     Ответить | Цитировать Сообщить модератору
 Re: Constraint на пересечение дат  [new]
invm
Member

Откуда: Москва
Сообщений: 9405
12607335
24 ноя 12, 00:36    [13522340]     Ответить | Цитировать Сообщить модератору
 Re: Constraint на пересечение дат  [new]
aleks2
Guest
dalex1973
Считаю поможет только HOLDLOCK. Ваше мнение?

От чего поможет? "Но от глупости да-да, и от тупости..."
Несете наукообразную чушь. Constraint обязан обеспечить свое выполнение.
Да, один/несколько из "многопользовательской среды" обломаются.
Но в таблице пересекающихся интервалов не будет.
24 ноя 12, 08:26    [13522919]     Ответить | Цитировать Сообщить модератору
 Re: Constraint на пересечение дат  [new]
Glory
Member

Откуда:
Сообщений: 104760
Sergei.Agalakov
Решения Glory и iap не будут правильно работать в многопользовательской среде без дополнительных блокировок табличного или глобального уровня.

"будут неправильно работать" означает, что в таблице появятся записи, нарушающие констрайнт ?
24 ноя 12, 16:02    [13523622]     Ответить | Цитировать Сообщить модератору
 Re: Constraint на пересечение дат  [new]
dalex1973
Member

Откуда: Польша
Сообщений: 287
Glory
Sergei.Agalakov
Решения Glory и iap не будут правильно работать в многопользовательской среде без дополнительных блокировок табличного или глобального уровня.

"будут неправильно работать" означает, что в таблице появятся записи, нарушающие констрайнт ?

По умолчанию имеем пессимистическую блокировку, т.е. заблокируется всё что связано.
Отвечая на Ваш вопрос - нет, констрейнт сработает. Но за это Вы заплатите блокировкой всей таблицы.
Поскольку неизвестно какая у Вас таблица дальнейшие разьяснения не имеют смысла; на Ваш вопрос Вы ответ получили и тему можно закрывать.
26 ноя 12, 10:55    [13527947]     Ответить | Цитировать Сообщить модератору
 Re: Constraint на пересечение дат  [new]
Glory
Member

Откуда:
Сообщений: 104760
dalex1973
По умолчанию имеем пессимистическую блокировку, т.е. заблокируется всё что связано.
Отвечая на Ваш вопрос - нет, констрейнт сработает. Но за это Вы заплатите блокировкой всей таблицы.
Поскольку неизвестно какая у Вас таблица дальнейшие разьяснения не имеют смысла; на Ваш вопрос Вы ответ получили и тему можно закрывать.

Что тогда означает "не будут правильно работать в многопользовательской среде" ?
26 ноя 12, 11:05    [13528019]     Ответить | Цитировать Сообщить модератору
 Re: Constraint на пересечение дат  [new]
invm
Member

Откуда: Москва
Сообщений: 9405
dalex1973
Плюс за глубину понимания.
Глубины-то как раз и нету.
dalex1973
Однако READ UNCOMITTED(NOLOCK) не даёт гарантии. Ситуация -
1. транзакция 1 надписывает А значение Б
2. транзакция 2 видит Б значение, запись А позволена
3. транзакция 1 откатывается,Б становится А
Имеем 2 А.

Считаю поможет только HOLDLOCK. Ваше мнение?
В MS далеко не дураки работают.
На момент запуска чека, запись в таблице/индексе уже есть, да еще и с наложенной X-блокировкой. Так что никаких дополнительных приседаний с блокировками уровня таблицы не требуется -- конкурирующие соединения сами между собой разберутся.
26 ноя 12, 12:48    [13528781]     Ответить | Цитировать Сообщить модератору
 Re: Constraint на пересечение дат  [new]
Sergei.Agalakov
Member

Откуда:
Сообщений: 575
Glory
"будут неправильно работать" означает, что в таблице появятся записи, нарушающие констрайнт ?

Да. Констрайнт может быть не нарушен в момент модификации данных, но в момент коммита он уже будет нарушен, потому что вторая сессия успеет закоммитить свои изменения за время между модификацией данных, и их коммитом в первой сессии.
invm
В MS далеко не дураки работают.
На момент запуска чека, запись в таблице/индексе уже есть, да еще и с наложенной X-блокировкой. Так что никаких дополнительных приседаний с блокировками уровня таблицы не требуется -- конкурирующие соединения сами между собой разберутся.

Каким образом можно предсказать конфликт на уровне строк, если он возможен в любом месте таблицы? Вторая-то сессия не видит новой/обновленной строки, пока она не закоммичена. Единственный выход - блокировка табличного уровня. Врочем, покажите пример рабочего кода на тестовой таблице с 3-4 записями, может быть я что-то не понимаю.
dalex1973
Однако READ UNCOMITTED(NOLOCK) не даёт гарантии. Ситуация -
1. транзакция 1 надписывает А значение Б
2. транзакция 2 видит Б значение, запись А позволена
3. транзакция 1 откатывается,Б становится А
Имеем 2 А.
Считаю поможет только HOLDLOCK. Ваше мнение?

Да, без блокировки таблицы, по-видимому, не обойтись.
26 ноя 12, 20:19    [13532420]     Ответить | Цитировать Сообщить модератору
 Re: Constraint на пересечение дат  [new]
Crimean
Member

Откуда:
Сообщений: 13148
эээ... совсем "на всю таблицу", вроде как, не нужно. достаточно только половины, судя по всему. ибо если добавляем запись с диапазоном @D1 - @D2, то нас не интересуют существующие записи, для которых D1 > @D2 и D2 < @D1. а вот для остальных - да - будет блокировка. ну и в зависимости по какому полю у нас будет индекс - та часть и заблочица ("слева" или "справа")
но "в глубокой теории" если "развернуть" хранение таким образом, чтобы диапазоны привести к 1 столбцу, что не есть совсем нереально, то можно попробовать и диапазон заблочить.. скажем, сделать вторую, "техническую" табличку "с голыми датами" и, скажем, с [каскадными] форинами на нее. и, допустим, в триггере ее заполнять. ну, в целом, как-то так (пример умышленно упрощен, уш звиняйте):

-- drop table dbo.d1
-- create table dbo.d1 ( date int primary key )

-- drop table dbo.a1
-- create table dbo.a1 ( id int identity primary key , d1 int not null references dbo.d1( date ) , d2 int not null references dbo.d1( date ) )
-- create index ia1_1 on dbo.a1( d1 )
-- create index ia1_2 on dbo.a1( d2 )

-- drop trigger dbo.ta1
/*
create trigger dbo.ta1
on dbo.a1
instead of insert
as
set nocount on

declare @d1 int
declare @d2 int
declare @Count int

select top 2 @d1 = d1, @d2 = d2 from inserted

set @Count = @@rowcount

if @Count = 0 return

if @Count > 1
begin
	raiserror( 'Pls, insert only 1 row', 16, 1 )
	rollback
	return
end

insert into dbo.d1( date )
select d.date from ( select @d1 as date union select @d2 ) as d
where d.date not in ( select date from dbo.d1 )

-- А вот тут-то диапазон и лочится
select @Count = count(*) from dbo.d1 with ( xlock, holdlock ) where date between @d1 and @d2

if exists ( select 1 from dbo.a1 where not ( d1 > @d2 or d2 < @d1 ) )
begin
	raiserror( 'Error dates', 16, 1 )
	rollback
	return
end

insert into dbo.a1 ( d1, d2 ) values( @d1, @d2 )

if @@error <> 0
begin
	rollback
	return
end

return
*/


в результате, если не ошибаюсь, первый коннект делаем:

insert into dbo.a1 ( d1, d2 ) select 10, 10
insert into dbo.a1 ( d1, d2 ) select 20, 20
insert into dbo.a1 ( d1, d2 ) select 30, 30


после

begin tran
insert into dbo.a1 ( d1, d2 ) select 11, 19


второй коннект можно сделать:

insert into dbo.a1 ( d1, d2 ) select 1, 9
insert into dbo.a1 ( d1, d2 ) select 21, 29


но нельзя сделать:

insert into dbo.a1 ( d1, d2 ) select 15, 15


после того, как в первом делаем commit - второй отваливается по ошибке диапазонов
26 ноя 12, 21:17    [13532705]     Ответить | Цитировать Сообщить модератору
 Re: Constraint на пересечение дат  [new]
invm
Member

Откуда: Москва
Сообщений: 9405
Sergei.Agalakov
Вторая-то сессия не видит новой/обновленной строки, пока она не закоммичена. Единственный выход - блокировка табличного уровня. Врочем, покажите пример рабочего кода на тестовой таблице с 3-4 записями, может быть я что-то не понимаю.
Это не Oracle. Вторая сессия будет ожидать снятия X-блокировки с этой строки.
А вот на снепшотных TIL озвученная проблема будет иметь место.
26 ноя 12, 22:51    [13533056]     Ответить | Цитировать Сообщить модератору
 Re: Constraint на пересечение дат  [new]
Sergei.Agalakov
Member

Откуда:
Сообщений: 575
Короче, пессимистичное блокирование всего, что потенциально может вызвать конфликт либо явно, либо используюя блокировочный TIL. Сделать можно, но на таблице со многими одновременными модификациями будет серьезная проблема с блокировками.
27 ноя 12, 02:27    [13533624]     Ответить | Цитировать Сообщить модератору
 Re: Constraint на пересечение дат  [new]
alels2
Guest
Sergei.Agalakov
Короче, пессимистичное блокирование всего, что потенциально может вызвать конфликт либо явно, либо используюя блокировочный TIL. Сделать можно, но на таблице со многими одновременными модификациями будет серьезная проблема с блокировками.

Правельные перцы хранять интервалы в виде "НачалоИнтервала" и фсе. Считая начало следующего концом предыдущего.
Никаких констрейнтов, кроме констрейнта уникальности и никаких "из пальца высосанных проблем".

Зато проблемы с выборками.
27 ноя 12, 05:27    [13533668]     Ответить | Цитировать Сообщить модератору
 Re: Constraint на пересечение дат  [new]
Crimean
Member

Откуда:
Сообщений: 13148
alels2
Правельные перцы хранять интервалы в виде "НачалоИнтервала" и фсе. Считая начало следующего концом предыдущего


а как же быть если НЕ нужны непрерывные интервалы? когда значение может действовать с 1 по 5 и с 15 по 20?
ну вопрос (пока) не в эффективности выборок а в обеспечении "проверки на непересечение", не залочив всю таблицу
27 ноя 12, 11:40    [13534868]     Ответить | Цитировать Сообщить модератору
 Re: Constraint на пересечение дат  [new]
aleks2
Guest
Crimean
alels2
Правельные перцы хранять интервалы в виде "НачалоИнтервала" и фсе. Считая начало следующего концом предыдущего


а как же быть если НЕ нужны непрерывные интервалы? когда значение может действовать с 1 по 5 и с 15 по 20?
ну вопрос (пока) не в эффективности выборок а в обеспечении "проверки на непересечение", не залочив всю таблицу


1. Запомни - время непрерывно. Поэтому между интервалами "действия" неизбежно ё интервалы "бездействия".
Фсе просто.

2. Вот те на вопрос и ответили. Не?
27 ноя 12, 11:47    [13534919]     Ответить | Цитировать Сообщить модератору
 Re: Constraint на пересечение дат  [new]
Crimean
Member

Откуда:
Сообщений: 13148
aleks2
Вот те на вопрос и ответили. Не?


а. понял. вам оракл обсудить. извините, не заметил сразу
27 ноя 12, 12:05    [13535113]     Ответить | Цитировать Сообщить модератору
 Re: Constraint на пересечение дат  [new]
aleks2
Guest
Crimean
aleks2
Вот те на вопрос и ответили. Не?


а. понял. вам оракл обсудить. извините, не заметил сразу


Казалось бы причем тута собакен путена (Оракл)?
27 ноя 12, 12:21    [13535275]     Ответить | Цитировать Сообщить модератору
 Re: Constraint на пересечение дат  [new]
Crimean
Member

Откуда:
Сообщений: 13148
aleks2
Казалось бы причем тута


да не при чем, особо. с одной таблицей все равно будет блокироваться пол-таблицы. с двумя - только интервал.
27 ноя 12, 12:30    [13535351]     Ответить | Цитировать Сообщить модератору
 Re: Constraint на пересечение дат  [new]
aleks2
Guest
Crimean
aleks2
Казалось бы причем тута


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


Вот с хрена ли оно заблокирует таблицу при вставке ОДНОЙ даты в уникальный индекс по ЭТОЙ дате?
27 ноя 12, 12:32    [13535368]     Ответить | Цитировать Сообщить модератору
 Re: Constraint на пересечение дат  [new]
Crimean
Member

Откуда:
Сообщений: 13148
aleks2
Вот с хрена ли оно заблокирует таблицу при вставке ОДНОЙ даты в уникальный индекс по ЭТОЙ дате?


задача была не "обеспечить вставки". а "обеспечить непересечение". снизойдите до простых смертных с высот вашего опыта - нарисуйте примерчик
27 ноя 12, 12:44    [13535471]     Ответить | Цитировать Сообщить модератору
 Re: Constraint на пересечение дат  [new]
dalex1973
Member

Откуда: Польша
Сообщений: 287
Crimean,

Crimean
ибо если добавляем запись с диапазоном @D1 - @D2, то нас не интересуют существующие записи, для которых D1 > @D2 и D2 < @D1

Нас всё интересует,ибоm последняя вставка("нельзя допустить") может быть:
27 ноя 12, 13:42    [13535942]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить