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

Откуда:
Сообщений: 75
Есть таблица с полями name|...|start_date|end_date

Сейчас код добавления новой записи такой:
select .... 'проверка пересечения периодов
insert .... 'если проверка прошла успешно - добавляем новую запись

Проблема в то, что при одновременном добавлении двумя пользователями записей(периоды которых пересекаются) добавляются обе конфликтующие записи. Нужно, чтобы последний пользователь получил ошибку.
Если бы это был, например, mail, то можно было бы просто в insert-e проверять exist-ом, а здесь проверка не на равенство, а на пересечение... В принципе этот вариант более менее подходит.

Как лучше сделать ?
23 май 12, 15:47    [12602452]     Ответить | Цитировать Сообщить модератору
 Re: одновременный insert нескольких пользователей  [new]
VFl
Member

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

проще всего использовать трансакции с блокировкой таблицы. Тогда одновременности не будет. WITH (XLOCK) в проверке перед инсертом.
23 май 12, 15:52    [12602509]     Ответить | Цитировать Сообщить модератору
 Re: одновременный insert нескольких пользователей  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
VFl,

Да, чего уж мелочиться... Всю бд в single user.

tugrik,

А какие проблемы с проверкой пересечения c условием NOT EXISTS в INSERT?
23 май 12, 15:58    [12602558]     Ответить | Цитировать Сообщить модератору
 Re: одновременный insert нескольких пользователей  [new]
VFl
Member

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

всю бд в single user не надо. Провека / инсерт должны занимать доли секунды, второй процесс будет это время просто ждать. А пользователь задержку даже не заметит.
23 май 12, 16:02    [12602602]     Ответить | Цитировать Сообщить модератору
 Re: одновременный insert нескольких пользователей  [new]
tugrik
Member

Откуда:
Сообщений: 75
pkarklin
VFl,

Да, чего уж мелочиться... Всю бд в single user.

tugrik,

А какие проблемы с проверкой пересечения c условием NOT EXISTS в INSERT?


проблем нет, ищу лучший вариант
23 май 12, 16:08    [12602671]     Ответить | Цитировать Сообщить модератору
 Re: одновременный insert нескольких пользователей  [new]
Shakill
Member

Откуда: мск
Сообщений: 1880
tugrik
pkarklin
VFl,

Да, чего уж мелочиться... Всю бд в single user.

tugrik,

А какие проблемы с проверкой пересечения c условием NOT EXISTS в INSERT?


проблем нет, ищу лучший вариант

так это и есть лучший вариант
23 май 12, 16:10    [12602692]     Ответить | Цитировать Сообщить модератору
 Re: одновременный insert нескольких пользователей  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
VFl
Провека / инсерт должны занимать доли секунды, второй процесс будет это время просто ждать. А пользователь задержку даже не заметит.


А если таких запросов на ставку пара тысяч в секунду и нет пересекающихся периодов?

Сообщение было отредактировано: 23 май 12, 16:11
23 май 12, 16:11    [12602695]     Ответить | Цитировать Сообщить модератору
 Re: одновременный insert нескольких пользователей  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
pkarklin
Да, чего уж мелочиться... Всю бд в single user.
Может дабавить иконку в стандарные смайлики - FacePalm.jpg
Мне кажется это отлично отражает наш фоум.
23 май 12, 16:46    [12603033]     Ответить | Цитировать Сообщить модератору
 Re: одновременный insert нескольких пользователей  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
Mnior
pkarklin
Да, чего уж мелочиться... Всю бд в single user.
Может дабавить иконку в стандарные смайлики - FacePalm.jpg
Мне кажется это отлично отражает наш фоум.
Будет отражать еще сильнее, если все остальные стандартные смайлы убрать.
23 май 12, 16:47    [12603052]     Ответить | Цитировать Сообщить модератору
 Re: одновременный insert нескольких пользователей  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
В зависимоти от реализации, но тут может быть Serializable изоляция (Range).
Если так и если в добавок вставка периодов делается в основном последовательно (в конец) и делается проверка не апосля в триггере, а перед, то это тоже что и таблу блокировать. Не?

Range за пределами таблы локирует её конец.
23 май 12, 16:59    [12603166]     Ответить | Цитировать Сообщить модератору
 Re: одновременный insert нескольких пользователей  [new]
VFl
Member

Откуда:
Сообщений: 126
pkarklin
VFl
Провека / инсерт должны занимать доли секунды, второй процесс будет это время просто ждать. А пользователь задержку даже не заметит.


А если таких запросов на ставку пара тысяч в секунду и нет пересекающихся периодов?


Как раз когда пара тысяч в секунду и нужна трансакция. Инсерты происходят параллельно от разных процессов, и проверка EXIST одного процесса будет регулярно попадать между EXIST и INSERT другого. Не надо изобретать велосипед, который к тому же не будет ездить.
23 май 12, 17:52    [12603497]     Ответить | Цитировать Сообщить модератору
 Re: одновременный insert нескольких пользователей  [new]
Shakill
Member

Откуда: мск
Сообщений: 1880
VFl
... и проверка EXIST одного процесса будет регулярно попадать между EXIST и INSERT другого ...

это как? insert ... select.. exists и так является одной транзакцией
23 май 12, 18:06    [12603615]     Ответить | Цитировать Сообщить модератору
 Re: одновременный insert нескольких пользователей  [new]
VFl
Member

Откуда:
Сообщений: 126
Shakill
VFl
... и проверка EXIST одного процесса будет регулярно попадать между EXIST и INSERT другого ...

это как? insert ... select.. exists и так является одной транзакцией


У трансакции есть 2 функции. Первая - гарантировать, что будут выполнены все команды между BEGIN und COMMIT, или ни одна из них. И вторая - ВОЗМОЖНОСТЬ блокировать таблицы на время трансакции, так, что они будут изменены только процессом, начавшим трансакцию (SET TRANSACTION ISOLATION LEVEL). Здесь имелась в виду именно это.
23 май 12, 18:23    [12603736]     Ответить | Цитировать Сообщить модератору
 Re: одновременный insert нескольких пользователей  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
VFl
Shakill
пропущено...

это как? insert ... select.. exists и так является одной транзакцией


У трансакции есть 2 функции. Первая - гарантировать, что будут выполнены все команды между BEGIN und COMMIT, или ни одна из них. И вторая - ВОЗМОЖНОСТЬ блокировать таблицы на время трансакции, так, что они будут изменены только процессом, начавшим трансакцию (SET TRANSACTION ISOLATION LEVEL). Здесь имелась в виду именно это.
Какая-то каша у вас. Не функции, а свойства. И не два, а четыре. Блокировки - это всего-лишь механизм для обеспечения некоторых этих свойств. Причем, важно помнить, что у читающих транзакций есть аж четыре (а то и пять) уровней изоляции.
23 май 12, 18:26    [12603760]     Ответить | Цитировать Сообщить модератору
 Re: одновременный insert нескольких пользователей  [new]
VFl
Member

Откуда:
Сообщений: 126
Гавриленко Сергей Алексеевич
VFl
пропущено...


У трансакции есть 2 функции. Первая - гарантировать, что будут выполнены все команды между BEGIN und COMMIT, или ни одна из них. И вторая - ВОЗМОЖНОСТЬ блокировать таблицы на время трансакции, так, что они будут изменены только процессом, начавшим трансакцию (SET TRANSACTION ISOLATION LEVEL). Здесь имелась в виду именно это.
Какая-то каша у вас. Не функции, а свойства. И не два, а четыре. Блокировки - это всего-лишь механизм для обеспечения некоторых этих свойств. Причем, важно помнить, что у читающих транзакций есть аж четыре (а то и пять) уровней изоляции.


Я рад, что по теме вы со мной согласны. Насчет функций или свойств можно поспорить в другой ветке :) Уровни изоляции можно менять, особо критичные места (вроде описанного) надо оформлять как SET TRANSACTION ISOLATION LEVEL READ COMMITTED и SELECT в трансакции делать с WITH (XLOCK)
23 май 12, 18:34    [12603807]     Ответить | Цитировать Сообщить модератору
 Re: одновременный insert нескольких пользователей  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Shakill
это как? insert ... select.. exists и так является одной транзакцией
SPIDTypeModeStatusResource
ATABIXGRANT
APAGIXGRANT1:546
AKEYXGRANT(f601b18dbee2)
BTABIXGRANT
BPAGISGRANT1:546
BKEYSWAIT(f601b18dbee2)

+ тест
use tempdb
GO
CREATE TABLE dbo.Test ([From] Date,[To] Date, CONSTRAINT [PK_Test] PRIMARY KEY ([From],[To]))
GO
-- TRUNCATE TABLE dbo.Test
INSERT dbo.Test
SELECT	 DateAdd(Month,V.number,'20010101')
	,DateAdd(Month,V.number,'20010107')
FROM	master.dbo.spt_values V
WHERE	V.type = 'P'
-- SELECT * FROM dbo.Test
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN XTest

INSERT	dbo.Test
SELECT	[From],[To]
FROM	(VALUES
	('30010103','30010107')	-- A
--	('30010101','30010105')	-- B1
--	('30010112','30010120')	-- B2 неважно
	)X([From],[To])
WHERE	NOT Exists(SELECT * FROM dbo.Test T WHERE T.[From] < X.[To] AND T.[To] > X.[From])
-- ROLLBACK
-- COMMIT TRAN XTest
-- sp_lock
Выстраиватеся очередь. Терпимо у TC.
23 май 12, 18:36    [12603819]     Ответить | Цитировать Сообщить модератору
 Re: одновременный insert нескольких пользователей  [new]
Shakill
Member

Откуда: мск
Сообщений: 1880
VFl
Shakill
пропущено...

это как? insert ... select.. exists и так является одной транзакцией


У трансакции есть 2 функции. Первая - гарантировать, что будут выполнены все команды между BEGIN und COMMIT, или ни одна из них. И вторая - ВОЗМОЖНОСТЬ блокировать таблицы на время трансакции, так, что они будут изменены только процессом, начавшим трансакцию (SET TRANSACTION ISOLATION LEVEL). Здесь имелась в виду именно это.


так зачем with (xlock)? read committed, пришел запрос вида insert..select.. not exists. остальные такие же, пришедшие во время его работы, неважно на каком этапе, либо отсеются сразу (если в таблице до начала работы первого запроса уже были записи, попадающие в exists), либо будут ждать завершения работы первого. и никакой вставки конфликтующих записей.
23 май 12, 18:39    [12603831]     Ответить | Цитировать Сообщить модератору
 Re: одновременный insert нескольких пользователей  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
VFl
WITH (XLOCK)
Чтобы доказать нуна сделать чтоб между EXIST и INSERT в плане стояла очень долгая операция, блокировка, для первого процесса и нулевая для второго. Слабо?
23 май 12, 18:40    [12603837]     Ответить | Цитировать Сообщить модератору
 Re: одновременный insert нескольких пользователей  [new]
Shakill
Member

Откуда: мск
Сообщений: 1880
Mnior, то есть насчёт одной транзакции я был не прав и существует вероятность вклиниться между exists и insert? печаль
23 май 12, 18:50    [12603889]     Ответить | Цитировать Сообщить модератору
 Re: одновременный insert нескольких пользователей  [new]
VFl
Member

Откуда:
Сообщений: 126
Shakill
VFl
пропущено...


У трансакции есть 2 функции. Первая - гарантировать, что будут выполнены все команды между BEGIN und COMMIT, или ни одна из них. И вторая - ВОЗМОЖНОСТЬ блокировать таблицы на время трансакции, так, что они будут изменены только процессом, начавшим трансакцию (SET TRANSACTION ISOLATION LEVEL). Здесь имелась в виду именно это.


так зачем with (xlock)? read committed, пришел запрос вида insert..select.. not exists. остальные такие же, пришедшие во время его работы, неважно на каком этапе, либо отсеются сразу (если в таблице до начала работы первого запроса уже были записи, попадающие в exists), либо будут ждать завершения работы первого. и никакой вставки конфликтующих записей.



без select ... with (xlock) блокируются только измененные записи. С помощью with (xlock) можно заблокировать и те записи, которые не меняются.
23 май 12, 18:52    [12603896]     Ответить | Цитировать Сообщить модератору
 Re: одновременный insert нескольких пользователей  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Shakill, Нет не так.
Нужно проверять! Всегда!, незваисимо от того кто(!) это пишет.

VFl, аргументирует правильно. Но без лишних подробностей.

И главное. Вариант без очереди не представлен.
23 май 12, 19:02    [12603935]     Ответить | Цитировать Сообщить модератору
 Re: одновременный insert нескольких пользователей  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
VFl
без select ... with (xlock) блокируются только измененные записи. С помощью with (xlock) можно заблокировать и те записи, которые не меняются.
Специально для любителей писать хинт xlock: https://www.sql.ru/forum/actualthread.aspx?tid=926475
23 май 12, 19:02    [12603938]     Ответить | Цитировать Сообщить модератору
 Re: одновременный insert нескольких пользователей  [new]
VFl
Member

Откуда:
Сообщений: 126
Mnior
VFl
WITH (XLOCK)
Чтобы доказать нуна сделать чтоб между EXIST и INSERT в плане стояла очень долгая операция, блокировка, для первого процесса и нулевая для второго. Слабо?



Запросто

Создает таблицу

USE [DiamondLight]
GO
/****** Objekt:  Table [dbo].[Table_Test2]    Skriptdatum: 05/23/2012 17:31:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_Test2](
	[ID] [int] NOT NULL,
 CONSTRAINT [PK_Table_Test2] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]



Открываем 2 коннекта к базе (у меня 2 окна в Enterprice Manager)

В одном такой скрипт:

DECLARE 
@newid int,
@counter int

TRUNCATE TABLE Table_Test2

SET @counter = 1

WHILE @counter < 50000
BEGIN

	BEGIN TRANSACTION

	SET @newid = IsNull((SELECT Max(ID) FROM dbo.Table_Test2), 0) + 1 --WITH (XLOCK)

	INSERT INTO Table_Test2 (ID)
	VALUES (@newid)

	SET @counter = @counter + 1	

	COMMIT TRANSACTION

END


Время исполнения у меня около 15 сек. У кого меньше, увеличиваем 50000

В другом окне похожий скрипт:

DECLARE 
@newid int,
@counter int

SET @counter = 1

WHILE @counter < 50000
BEGIN

	BEGIN TRANSACTION

	SET @newid = IsNull((SELECT Max(ID) FROM dbo.Table_Test2), 0) + 1 --WITH (XLOCK)

	INSERT INTO Table_Test2 (ID)
	VALUES (@newid)

	SET @counter = @counter + 1	

	COMMIT TRANSACTION

END


Запускаем первый скрипт, меняеемсябыстро ко второму и запускаем его тожею Т.е. они исполняются параллельно. Без WITH (XLOCK) происходит ошибка. Если WITH (XLOCK) присутствует в обоих скриптах - ошибок нет.
23 май 12, 19:40    [12604079]     Ответить | Цитировать Сообщить модератору
 Re: одновременный insert нескольких пользователей  [new]
serg99
Member

Откуда:
Сообщений: 422
А в SQL Server сериализуемость транзакций поддерживается?
23 май 12, 19:59    [12604194]     Ответить | Цитировать Сообщить модератору
 Re: одновременный insert нескольких пользователей  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Кстати я немного неправильно сказал. Не в конец, а если банально рядом, то будет очередь.
VFl
with (xlock)
Монописуально.
+ Repro
use tempdb
GO
CREATE TABLE dbo.Test ([From] Date,[To] Date, CONSTRAINT [PK_Test] PRIMARY KEY ([From],[To]))
CREATE TABLE dbo.SPID (SPID Int PRIMARY KEY)
GO
INSERT dbo.Test
SELECT	 DateAdd(Month,V.number,'20010101')
	,DateAdd(Month,V.number,'20010107')
FROM	master.dbo.spt_values V
WHERE	V.type = 'P'

INSERT dbo.SPID VALUES
 (<@@SPID_A>)
,(BEETWEEN <@@SPID_A> AND <@@SPID_B>) -- Контрольный в голову
,(<@@SPID_B>)
GO
-----------------------------------------------
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN XTest
INSERT	dbo.Test
SELECT	[From],[To]
FROM	(VALUES
	('20020111','20020122')	-- A
--	('20020110','20020112')	-- B
	)X([From],[To])
WHERE	NOT Exists(SELECT * FROM dbo.Test T WITH(XLock) WHERE T.[From] < X.[To] AND T.[To] > X.[From])
	AND Exists(SELECT * FROM dbo.SPID WHERE SPID = @@SPID)
-- ROLLBACK
-- COMMIT TRAN XTest
GO
-----------------------------------------------
-- C
BEGIN TRAN XSPID
DELETE dbo.SPID WHERE SPID = <@@SPID_A>
-- ROLLBACK
Порядок запуска C->A->B->C->...

FromTo
2002-01-102002-01-12
2002-01-112002-01-22
При логичном Serializable / HoldLock вывалился DeadLock при Rollback "C" сессии.

По мне, так в консерватории что-то не то. Хрен с ним, с триггерами, запросы на изменение это не хухры-мухры.
23 май 12, 20:05    [12604239]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить