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

t(id int, d varchar(10));

нужно написать процедуру, которая принимает на вход параметры @p_id, @p_d и если строка с id = @p_id существует, то сделать update, иначе insert. процедура будет вызываться очень часто (с этим ничего не поделаешь).

Как сделать красиво и правильно? merge?

Ведь если сделать:

if(exists(select 1 from t where id = @p_id)) then
begin

update...

end
else if

insert...

end;

То может быть ситуация, когда между select и insert уже кто-то сделал insert...
11 янв 14, 22:08    [15402879]     Ответить | Цитировать Сообщить модератору
 Re: Вставка строки  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32178
Михаил1989
То может быть ситуация, когда между select и insert уже кто-то сделал insert...
Нужно делать в транзакции, и желательно наоборот:
begin transaction
update ...
if @@rocount = 0
insert...
commit transaction


И кроме того, вместо insert...update можно использовать собственно инструкцию merge
11 янв 14, 22:15    [15402909]     Ответить | Цитировать Сообщить модератору
 Re: Вставка строки  [new]
Михаил1989
Guest
alexeyvg
Нужно делать в транзакции, и желательно наоборот:

begin transaction
update ...
if @@rocount = 0
insert...
commit transaction




Спасибо за ответ. А разве между if и insert, нельзя будет сделать insert из другой сессии? Ведь update не встретив строк не наложит никаких блокировок... еще забыл сказать уровень изоляции read committed.
11 янв 14, 22:20    [15402932]     Ответить | Цитировать Сообщить модератору
 Re: Вставка строки  [new]
iap
Member

Откуда: Москва
Сообщений: 47198
alexeyvg
Михаил1989
То может быть ситуация, когда между select и insert уже кто-то сделал insert...
Нужно делать в транзакции, и желательно наоборот:
begin transaction
update ...
if @@rocount = 0
insert...
commit transaction


И кроме того, вместо insert...update можно использовать собственно инструкцию merge
... который тоже отдельно делает SELECT и INSERT
Между которыми тоже кто-то может сделать INSERT

Только эксклюзивная блокировка на чтение спасёт.
Но разве это будет жизнь?
11 янв 14, 22:21    [15402940]     Ответить | Цитировать Сообщить модератору
 Re: Вставка строки  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
Интересно, а зачем читать, если все равно надо писАть?!
11 янв 14, 22:23    [15402948]     Ответить | Цитировать Сообщить модератору
 Re: Вставка строки  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32178
Михаил1989
А разве между if и insert, нельзя будет сделать insert из другой сессии? Ведь update не встретив строк не наложит никаких блокировок... еще забыл сказать уровень изоляции read committed.
А, точно...

Нужно видимо так:
if(exists(select * from T with(rowlock, SERIALIZABLE ) where id = @id))

или соответственно так:
update T with(rowlock, SERIALIZABLE )
...
11 янв 14, 22:35    [15402998]     Ответить | Цитировать Сообщить модератору
 Re: Вставка строки  [new]
Михаил1989
Guest
create table rrr1(id int, data varchar(10));
go

create unique clustered index uidx_rrr1 on rrr1(id);
go

set showplan_text on;
go

begin transaction;

merge
	into rrr1 as trgt
	using (select 1, '12345') as src(id, data)
	on
		trgt.id = src.id
	when matched then
		update
		set
			trgt.data = src.data
	when not matched then
		insert(id, data)
		values(src.id, src.data);

rollback transaction;
go

set showplan_text off;
go


а если так, только вместо констант передавать параметры процедуры?


|--Clustered Index Merge(OBJECT:([db1].[dbo].[rrr1].[uidx_rrr1] AS [trgt]), SET:(Insert, [db1].[dbo].[rrr1].[data] as [trgt].[data] = [Expr1007],[db1].[dbo].[rrr1].[id] as [trgt].[id] = [Expr1008]), SET:(Update, [db1].[dbo].[rrr1].[data] as [trgt].[data] = [Expr1007]) ACTION:([Action1006]))
|--Compute Scalar(DEFINE:([Expr1007]=CONVERT_IMPLICIT(varchar(10),[Expr1001],0), [Expr1008]=CASE WHEN [Action1006]=(4) THEN [Expr1000] ELSE [db1].[dbo].[rrr1].[id] as [trgt].[id] END))
|--Compute Scalar(DEFINE:([Action1006]=ForceOrder(CASE WHEN [TrgPrb1004] IS NOT NULL THEN (1) ELSE (4) END)))
|--Compute Scalar(DEFINE:([Expr1000]=(1), [Expr1001]='12345'))
|--Nested Loops(Left Outer Join)
|--Constant Scan
|--Compute Scalar(DEFINE:([TrgPrb1004]=(1)))
|--Clustered Index Seek(OBJECT:([db1].[dbo].[rrr1].[uidx_rrr1] AS [trgt]), SEEK:([trgt].[id]=(1)) ORDERED FORWARD)
11 янв 14, 22:41    [15403037]     Ответить | Цитировать Сообщить модератору
 Re: Вставка строки  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
Интересный способ отслеживания накладываемых блокировок планом выполнения.
11 янв 14, 22:47    [15403069]     Ответить | Цитировать Сообщить модератору
 Re: Вставка строки  [new]
iap
Member

Откуда: Москва
Сообщений: 47198
pkarklin
Интересно, а зачем читать, если все равно надо писАть?!
Чтобы узнать, есть уже в таблице такое значение или ещё нет.
Если двое одновременно узнают, что такой записи нет, то оба будут пытаться вставить.
Но повезёт только первому!
11 янв 14, 22:51    [15403084]     Ответить | Цитировать Сообщить модератору
 Re: Вставка строки  [new]
Михаил1989
Guest
pkarklin
Интересный способ отслеживания накладываемых блокировок планом выполнения.


+

EventModeObjectIdObjectId2TextDataType
Trace Start
Lock:Acquired3 - S00[PLANGUIDE]2 - DATABASE
Lock:Acquired1 - Sch-S3415762553415762555 - OBJECT
Lock:Acquired1 - Sch-S00object_id = 341576255, index_id or stats_id = 111 - METADATA
Lock:Acquired1 - Sch-S00object_id = 341576255, index_id or stats_id = 111 - METADATA
Lock:Released0 - NULL09574652809936699392object_id = 341576255, index_id or stats_id = 111 - METADATA
Lock:Acquired1 - Sch-S00object_id = 341576255, index_id or stats_id = 111 - METADATA
Lock:Released0 - NULL00object_id = 341576255, index_id or stats_id = 111 - METADATA
Lock:Acquired1 - Sch-S00object_id = 341576255, index_id or stats_id = 111 - METADATA
Lock:Acquired1 - Sch-S00object_id = 341576255, stats_id = 111 - METADATA
Lock:Released0 - NULL016158915465152364544object_id = 341576255, index_id or stats_id = 111 - METADATA
Lock:Released1 - Sch-S016158915465152364544object_id = 341576255, stats_id = 111 - METADATA
Lock:Acquired1 - Sch-S00object_id = 341576255, index_id or stats_id = 211 - METADATA
Lock:Acquired1 - Sch-S00object_id = 341576255, stats_id = 211 - METADATA
Lock:Released1 - Sch-S016158915465152364544object_id = 341576255, index_id or stats_id = 211 - METADATA
Lock:Released1 - Sch-S016158915465152364544object_id = 341576255, stats_id = 211 - METADATA
Lock:Acquired1 - Sch-S00object_id = 341576255, index_id or stats_id = 111 - METADATA
Lock:Acquired1 - Sch-S00object_id = 341576255, stats_id = 111 - METADATA
Lock:Released0 - NULL016158915465152364544object_id = 341576255, index_id or stats_id = 111 - METADATA
Lock:Released1 - Sch-S016158915465152364544object_id = 341576255, stats_id = 111 - METADATA
Lock:Acquired1 - Sch-S00object_id = 341576255, index_id or stats_id = 211 - METADATA
Lock:Acquired1 - Sch-S00object_id = 341576255, stats_id = 211 - METADATA
Lock:Released1 - Sch-S016158915465152364544object_id = 341576255, index_id or stats_id = 211 - METADATA
Lock:Released1 - Sch-S016158915465152364544object_id = 341576255, stats_id = 211 - METADATA
Lock:Acquired1 - Sch-S00object_id = 341576255, index_id or stats_id = 111 - METADATA
Lock:Acquired1 - Sch-S00object_id = 341576255, stats_id = 111 - METADATA
Lock:Acquired1 - Sch-S3415762553415762555 - OBJECT
Lock:Released1 - Sch-S3415762553415762555 - OBJECT
Lock:Acquired1 - Sch-S3415762553415762555 - OBJECT
Lock:Released1 - Sch-S3415762553415762555 - OBJECT
Lock:Acquired1 - Sch-S3415762553415762555 - OBJECT
Lock:Released1 - Sch-S3415762553415762555 - OBJECT
Lock:Acquired1 - Sch-S3415762553415762555 - OBJECT
Lock:Released1 - Sch-S3415762553415762555 - OBJECT
Lock:Released0 - NULL00object_id = 341576255, index_id or stats_id = 111 - METADATA
Lock:Released1 - Sch-S00object_id = 341576255, stats_id = 111 - METADATA
Lock:Released1 - Sch-S00object_id = 341576255, index_id or stats_id = 111 - METADATA
Lock:Released1 - Sch-S3415762553415762555 - OBJECT
Lock:Released3 - S08984681258751164416[PLANGUIDE]2 - DATABASE
Lock:Acquired8 - IX3415762553415762555 - OBJECT
Lock:Acquired4 - U001:1688 - EXTENT
Lock:Acquired5 - X0720575940394352641:1696 - PAGE
Lock:Released4 - U022653106125673594881:1688 - EXTENT
Lock:Acquired4 - U001:1688 - EXTENT
Lock:Acquired5 - X0720575940394352641:1716 - PAGE
Lock:Released4 - U019770802364156477441:1688 - EXTENT
Lock:Acquired5 - X064880641:171:09 - RID
Lock:Acquired5 - X001:1696 - PAGE
Lock:Released5 - X064880641:171:09 - RID
Lock:Released5 - X001:1696 - PAGE
Lock:Acquired8 - IX775 - OBJECT
Lock:Acquired5 - X0458752(9f63180b996f)7 - KEY
Lock:Released0 - NULL0458752(9f63180b996f)7 - KEY
Lock:Acquired5 - X0458752(9f63180b996f)7 - KEY
Lock:Released0 - NULL0458752(9f63180b996f)7 - KEY
Lock:Acquired5 - X0458752(9f63180b996f)7 - KEY
Lock:Released0 - NULL0458752(9f63180b996f)7 - KEY
Lock:Released5 - X0458752(9f63180b996f)7 - KEY
Lock:Released8 - IX775 - OBJECT
Lock:Released5 - X001:1696 - PAGE
Lock:Released5 - X0720575940394352641:1716 - PAGE
Lock:Acquired8 - IX0720575940394352641:1696 - PAGE
Lock:Acquired15 - RangeI-N072057594039435264(ffffffffffff)7 - KEY
Lock:Acquired5 - X072057594039435264(8194443284a0)7 - KEY
Trace Pause

11 янв 14, 23:04    [15403154]     Ответить | Цитировать Сообщить модератору
 Re: Вставка строки  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
iap
Чтобы узнать, есть уже в таблице такое значение или ещё нет.
Если двое одновременно узнают, что такой записи нет, то оба будут пытаться вставить.
Но повезёт только первому!


Спасибо! Вопрос был риторическим, и был обращён к автору топика с целью навести на мысли.
11 янв 14, 23:05    [15403158]     Ответить | Цитировать Сообщить модератору
 Re: Вставка строки  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
Михаил1989,

Вы умеете пользоваться профайлером. Похвально!
11 янв 14, 23:06    [15403164]     Ответить | Цитировать Сообщить модератору
 Re: Вставка строки  [new]
Михаил1989
Guest
pkarklin,

может мысли на которые я должен был натолкнуться озвучите?
11 янв 14, 23:14    [15403211]     Ответить | Цитировать Сообщить модератору
 Re: Вставка строки  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
Михаил1989,

Мысли у Вас были правильные в самом начале:

автор
merge?

Проверять их правоту следует запустив несколько (пару сотен) вызовов одновременно, а не использовать профайлер.
11 янв 14, 23:19    [15403227]     Ответить | Цитировать Сообщить модератору
 Re: Вставка строки  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 637
iap
alexeyvg
пропущено...
Нужно делать в транзакции, и желательно наоборот:
begin transaction
update ...
if @@rocount = 0
insert...
commit transaction


И кроме того, вместо insert...update можно использовать собственно инструкцию merge
... который тоже отдельно делает SELECT и INSERT
Между которыми тоже кто-то может сделать INSERT

Только эксклюзивная блокировка на чтение спасёт.
Но разве это будет жизнь?


А разве весь MERGE выполняется не в одной транзакции? Или наложение блокировки на вставку производится только перед выполнением INSERT, а не перед селкетом внутри MERGE?
Пробовал выяснить в доке, но не нашел, если есть где-то описание наложения блокировок во время MERGE - киньтесь пожалуйста.
12 янв 14, 01:31    [15403667]     Ответить | Цитировать Сообщить модератору
 Re: Вставка строки  [new]
leov
Member

Откуда: С-Петербург
Сообщений: 616
Михаил1989,

на мой взгляд вот так это пишется
insert t(id,d)
select @p_id, @p_d
where not exists(select 1 from t where id = @p_id)
update t set d=@p_d
where id = @p_id
12 янв 14, 02:15    [15403751]     Ответить | Цитировать Сообщить модератору
 Re: Вставка строки  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32178
leov
на мой взгляд вот так это пишется
Тут ведь тоже возможна вставка между INSERT и UPDATE, ничего не поменялось по сравнению с исходным запросом ТС.
Без блокировки не обойтись...
12 янв 14, 12:35    [15404236]     Ответить | Цитировать Сообщить модератору
 Re: Вставка строки  [new]
Михаил1989
Guest
alexeyvg,

merge работает как надо
12 янв 14, 13:25    [15404360]     Ответить | Цитировать Сообщить модератору
 Re: Вставка строки  [new]
invm
Member

Откуда: Москва
Сообщений: 9915
Михаил1989
merge работает как надо
Merge без serializable не работает как надо. Это легко проверяется.
12 янв 14, 13:36    [15404387]     Ответить | Цитировать Сообщить модератору
 Re: Вставка строки  [new]
leov
Member

Откуда: С-Петербург
Сообщений: 616
alexeyvg
leov
на мой взгляд вот так это пишется
Тут ведь тоже возможна вставка между INSERT и UPDATE, ничего не поменялось по сравнению с исходным запросом ТС.
Без блокировки не обойтись...
это как она может быь возможна?
если моя вставка прошла то никакая другая аналогичная уж точно не пройдет
12 янв 14, 14:13    [15404468]     Ответить | Цитировать Сообщить модератору
 Re: Вставка строки  [new]
Михаил1989
Guest
invm
Михаил1989
merge работает как надо
Merge без serializable не работает как надо. Это легко проверяется.


Lock:Acquired 8 - IX 0 72057594039435264 1:169 6 - PAGE
Lock:Acquired 15 - RangeI-N 0 72057594039435264 (ffffffffffff) 7 - KEY
Lock:Acquired 5 - X 0 72057594039435264 (8194443284a0) 7 - KEY


RangeI-N разве не оно? вчера вроде тестировал несколько сессий которые долбят один и тот же merge параллельно - нормально. ни разу duplicate key не вылетело. и одна строка в таблице так и осталась.
12 янв 14, 14:42    [15404529]     Ответить | Цитировать Сообщить модератору
 Re: Вставка строки  [new]
Михаил1989
Guest
leov
alexeyvg
пропущено...
Тут ведь тоже возможна вставка между INSERT и UPDATE, ничего не поменялось по сравнению с исходным запросом ТС.
Без блокировки не обойтись...
это как она может быь возможна?
если моя вставка прошла то никакая другая аналогичная уж точно не пройдет


и вылетит с ошибкой. а должна понять что надо стать апдейтом.
12 янв 14, 14:43    [15404534]     Ответить | Цитировать Сообщить модератору
 Re: Вставка строки  [new]
invm
Member

Откуда: Москва
Сообщений: 9915
leov
это как она может быь возможна?
Еще как возможна. Если нет ограничения уникальности. Если есть, то в одном из сеансов будет ошибка вставки дубликата ключа.
Вы почему-то считаете, что insert в разных сеансах выстроятся в очередь, а это не так.
12 янв 14, 14:47    [15404541]     Ответить | Цитировать Сообщить модератору
 Re: Вставка строки  [new]
invm
Member

Откуда: Москва
Сообщений: 9915
Михаил1989
RangeI-N разве не оно
Нет, не оно. RangeI-N - проверка диапазона ключа при вставке. Посмотрите в доке на ее матрицу совместимости.
Михаил1989
вчера вроде тестировал несколько сессий которые долбят один и тот же merge параллельно - нормально
Это вам просто повезло или тест был некорректный. Проверить можно так:
+ Подготовка
use tempdb;
go

if object_id('dbo.t1', 'U') is not null
 drop table dbo.t1;
go

create table dbo.t1 (id int not null primary key, v int);
insert into dbo.t1 values (1, 1), (3, 1);
go

+ 1-й сеанс
use tempdb;
go

with s as
(
 select 2 as id, 2 as v
)
merge into dbo.t1 t
--merge into dbo.t1 with (serializable) t
using s on s.id = t.id
when not matched and tools.clrfnWT_Delay(10000) = 1/*Задержка 10 сек*/ then
 insert
  (id, v)
 values
  (s.id, s.v)
when matched then
 update
  set
   v = s.v;

+ 2-й сеанс
use tempdb;
go

with s as
(
 select 2 as id, 2 as v
)
merge into dbo.t1 t
--merge into dbo.t1 with (serializable) t
using s on s.id = t.id
when not matched then
 insert
  (id, v)
 values
  (s.id, s.v)
when matched then
 update
  set
   v = s.v;

В первом сеансе получите нарушение PK. А потом выполните тоже самое, но с хинтом serializable.
Код tools.clrfnWT_Delay не привожу. Вместо нее можете написать свою скалярную функцию, обеспечивающую приемлемую задержку.
12 янв 14, 15:04    [15404574]     Ответить | Цитировать Сообщить модератору
 Re: Вставка строки  [new]
Михаил1989
Guest
invm,

спасибо, добрый человек. мдя.
12 янв 14, 15:13    [15404583]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить