ReSQL.ru
     
powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Многопоточная вставка и выборка одной и той же строки. Блокировки
25 сообщений из 32, страница 1 из 2
  1  все
Многопоточная вставка и выборка одной и той же строки. Блокировки
    #40120823
notemac
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
notemac
Гость
Коллеги, добрый день!
Имеется процедура:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
ALTER PROCEDURE [dbo].[AddPageUrl]
	@Url VARCHAR(300)
	, @Id INT OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;

	DECLARE @qqq INT;
	BEGIN TRY
		BEGIN TRANSACTION

		SELECT @Id = Id FROM dbo.PageUrls WHERE Url = @Url;

		IF @Id IS NULL
		BEGIN
			INSERT INTO dbo.PageUrls (Url)
			VALUES (@Url);

			SELECT @Id = SCOPE_IDENTITY();
		END

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

		SELECT @Id = Id FROM dbo.PageUrls WHERE Url = @Url;
	END CATCH

	RETURN 0;
END


Данная процедура может вызываться параллельно с одинаковым Url:
поток 1: exec [dbo].[AppPageUrl] 'http:\\page1'
поток 2: exec [dbo].[AppPageUrl] 'http:\\page1'
...
поток N: exec [dbo].[AddPageUrl] 'http:\\page1'

Необходимо гарантия, что не будет дедлоков и процедура ВСЕГДА вернет Id СУЩЕСТВУЮЩЕЙ строки.
Т.е. ситуации быть не должно:
1) Поток 1 вставляет строку 'http:\\page1'
2) Поток 2 вставляет строку 'http:\\page1', но возникает исключение, т.к. Поток 1 уже вставил данную строку.
3) Срабатывает блок CATCH в Потоке 2 и 'Select @Id = Id from ...' вернет NULL
4) Происходит COMMIT в Потоке 1 и строка 'http:\\page1' теперь имеется в БД
Возможна ли данная ситуация для приведенного выше кода?

Я проверил несколько раз, запуская сотни потоков, все работает без ошибок, но что-то не уверен.
С блокировкиами только начал знакомиться, пока не хватает времен погрузиться в материал, а уже нужно в ПРОД)))
...
Рейтинг: 0 / 0
16.12.2021, 21:56
| Ответить | Цитировать | Написать  
Многопоточная вставка и выборка одной и той же строки. Блокировки
    #40120827
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
notemac,

insert ... output ... все остальное выкиньте.
...
Рейтинг: 0 / 0
16.12.2021, 22:18
| Ответить | Цитировать | Написать  
Многопоточная вставка и выборка одной и той же строки. Блокировки
    #40120834
Фотография a_voronin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
a_voronin
Участник
notemac,

А вы xто с помощью try catch определяете есть ли запись?
...
Рейтинг: 0 / 0
16.12.2021, 22:37
| Ответить | Цитировать | Написать  
Многопоточная вставка и выборка одной и той же строки. Блокировки
    #40120885
notemac
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
notemac
Гость
Владислав Колосов,
почитал про output и что-то не пойму, как он поможет упростить код. Процедура всегда должна возвращать корректный @Id только что вставленной или уже существуещей записи, т.е. один из потоков обязательно раньше других вставит новую запись и вернет ее @Id, а остальные потоки просто вернут ее @Id.

a_voronin,
я хочу гарантировать, что ситуация из 4 пунктов, которая описана в посте, будет исключена.
try catch для отлова исключения, если запись была уже вставлена.

Но что-то не удается воссоздать ситуацию, чтобы сработал try catch. Код отрабатывает нормально при множестве потоков (запускаю в несколько потоков из C#), т.е. INSERT в первом потоке успевает отработать до SELECT'ов в остальных потоках? Хрень какая-то... Ладно, посмотрим что будет на проде.

Есть вариант блочить в первом SELECT таблицу (HOLDLOCK, XLOCK) для конца транзакции:
Код: sql
1.
SELECT @Id = Id FROM dbo.PageUrls WITH (HOLDLOCK, XLOCK) WHERE Url = @Url;


А если параллельно какие-нибудь запросы для отчетов будут SELECT делать, не будет ли здесь дедлоков, когда в этот же момент будет попытка захвата HOLDLOCK, XLOCK
...
Рейтинг: 0 / 0
17.12.2021, 04:35
| Ответить | Цитировать | Написать  
Многопоточная вставка и выборка одной и той же строки. Блокировки
    #40120887
notemac
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
notemac
Гость
XLOCK на INSERT, чтобы другие потоки не могли вставить запись, пока первый поток это делает
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
ALTER PROCEDURE [dbo].[AddPageUrl]
	@Url VARCHAR(300)
	, @Id INT OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		SELECT @Id = Id FROM dbo.PageUrls WHERE Url = @Url;

		IF @Id IS NULL
		BEGIN
			INSERT INTO dbo.PageUrls WITH(XLOCK) (Url)
			VALUES (@Url);

			SELECT @Id = SCOPE_IDENTITY();
		END

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

		SELECT @Id = Id FROM dbo.PageUrls WHERE Url = @Url;
	END CATCH

	RETURN 0;
END


Либо убрать XLOCK и добавить UNIQUE constraint на поле Url
...
Рейтинг: 0 / 0
17.12.2021, 04:58
| Ответить | Цитировать | Написать  
Многопоточная вставка и выборка одной и той же строки. Блокировки
    #40120889
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
notemac
Коллеги, добрый день!
Имеется процедура:
Код: sql
1.
2.
3.
	SET XACT_ABORT ON;

	BEGIN TRY



Прям слезу умиления выжимает.


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
set @Id = null;
with x as ( select @Url as Url )
    ,  t  as ( select * from dbo.PageUrls with(rowlock) )
  merge t using x on t.Url = x.Url
    when not matched then insert(Url) values(Url)
    when matched then update set @Id = t.Id
;
if @Id is null set @Id = SCOPE_IDENTITY();

+
create unique index UX_Url on dbo.PageUrls(Url) with (IGNORE_DUP_KEY = ON);
...
Рейтинг: 0 / 0
17.12.2021, 05:52
| Ответить | Цитировать | Написать  
Многопоточная вставка и выборка одной и той же строки. Блокировки
    #40120925
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
notemac,

Спасибо поржал))
...
Рейтинг: 0 / 0
17.12.2021, 10:14
| Ответить | Цитировать | Написать  
Многопоточная вставка и выборка одной и той же строки. Блокировки
    #40120942
Фотография a_voronin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
a_voronin
Участник
notemac
В
a_voronin,
я хочу гарантировать, что ситуация из 4 пунктов, которая описана в посте, будет исключена.
try catch для отлова исключения, если запись была уже вставлена.


Если вы правильно напишите код, то такой ситуации в принципе не возникнет.

Напишите MERGE. В нем распишите, что делаете, если запись есть, и что делать если нет. У MERGE есть OUTPUT.

Поймите, любая нормальная БД ориентирована на массовую вставку. Создавая исключение вы тормозите БД.

Есть блокировки. Через них все решается. Начните думать в архитектуре БД, а не языка программирования.
...
Рейтинг: 0 / 0
17.12.2021, 10:52
| Ответить | Цитировать | Написать  
Многопоточная вставка и выборка одной и той же строки. Блокировки
    #40120952
Фотография a_voronin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
a_voronin
Участник
И кстати, вы не хотите хеш сдать для @Url VARCHAR(300) ?
...
Рейтинг: 0 / 0
17.12.2021, 11:11
| Ответить | Цитировать | Написать  
Многопоточная вставка и выборка одной и той же строки. Блокировки
    #40121004
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
notemac,

авторпочитал про output и что-то не пойму, как он поможет упростить код,

Да, не обратил внимание, что надо вернуть ID, вставлен он был или нет. В таком случае можно использовать merge + output.

 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
drop table if exists #t;
create table #t (f1 int identity(1,1), f2 varchar(10));
insert #t (f2) values ('qwe');
go

-- совпало
declare @t table (f1 int)

merge #t
using ( select 'qwe' ff2) m1
on (m1.ff2 = #t.f2)
when matched then update set f2 = f2
when not matched then insert (f2) values (m1.ff2)
output inserted.f1 into @t
;

select f1 from @t;
go

-- не совпало
declare @t table (f1 int)

merge #t
using ( select 'asd' ff2) m1
on (m1.ff2 = #t.f2)
when matched then update set f2 = f2
when not matched then insert (f2) values (m1.ff2)
output inserted.f1 into @t
;

select f1 from @t;

...
Рейтинг: 0 / 0
17.12.2021, 13:05
| Ответить | Цитировать | Написать  
Многопоточная вставка и выборка одной и той же строки. Блокировки
    #40121010
Фотография a_voronin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
a_voronin
Участник
Владислав Колосов
notemac,

авторпочитал про output и что-то не пойму, как он поможет упростить код,


Да, не обратил внимание, что надо вернуть ID, вставлен он был или нет. В таком случае можно использовать merge + output.

 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
drop table if exists #t;
create table #t (f1 int identity(1,1), f2 varchar(10));
insert #t (f2) values ('qwe');
go

-- совпало
declare @t table (f1 int)

merge #t
using ( select 'qwe' ff2) m1
on (m1.ff2 = #t.f2)
when matched then update set f2 = f2
when not matched then insert (f2) values (m1.ff2)
output inserted.f1 into @t
;

select f1 from @t;
go

-- не совпало
declare @t table (f1 int)

merge #t
using ( select 'asd' ff2) m1
on (m1.ff2 = #t.f2)
when matched then update set f2 = f2
when not matched then insert (f2) values (m1.ff2)
output inserted.f1 into @t
;

select f1 from @t;



Если не вставил, то select
...
Рейтинг: 0 / 0
17.12.2021, 13:24
| Ответить | Цитировать | Написать  
Многопоточная вставка и выборка одной и той же строки. Блокировки
    #40121017
Фотография a_voronin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
a_voronin
Участник
aleks222
notemac
Коллеги, добрый день!
Имеется процедура:
Код: sql
1.
2.
3.
	SET XACT_ABORT ON;

	BEGIN TRY



Прям слезу умиления выжимает.


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
set @Id = null;
with x as ( select @Url as Url )
    ,  t  as ( select * from dbo.PageUrls with(rowlock) )
  merge t using x on t.Url = x.Url
    when not matched then insert(Url) values(Url)
    when matched then update set @Id = t.Id
;
if @Id is null set @Id = SCOPE_IDENTITY();

+
create unique index UX_Url on dbo.PageUrls(Url) with (IGNORE_DUP_KEY = ON);



Эту задачу про тракинг урлов (а иногда и блобов, картинок) не раз встречал в работе и не раз спрашивал на собеседованиях и сам был спрашиваем.

То, что здесь написали многие, вызовет недовольство грамотных собеседующих.

Первое -- если объект крупный (больше 20 байт) сразу напрашивается поле с хешом и индекс на него. Если вы траките урлы, то скорее всего у вас будут группы урлов, у которых первые 20-60 символов совпадают. Без хеша дикая потеря производительности.

Второе, не надо никаких хинтов. Вы просто пишите мердж, который отрабатывает две ветки -- новая -- не новая. Если не новая, то Select. Или всегда Select.

Все это работает многопоточно, даже когда несколько потоков вставляют одно и тоже новое значение.
...
Рейтинг: 0 / 0
17.12.2021, 13:32
| Ответить | Цитировать | Написать  
Многопоточная вставка и выборка одной и той же строки. Блокировки
    #40121100
notemac
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
notemac
Гость
aleks222
notemac
Коллеги, добрый день!
Имеется процедура:
Код: sql
1.
2.
3.
	SET XACT_ABORT ON;

	BEGIN TRY




Прям слезу умиления выжимает.



Что не так с SET XACT_ABORT ON и BEGIN TRY, это разве не стандартный паттерн?
https://www.sommarskog.se/error_handling/Part1.html

Коллеги, на основе ваших ответов родил такое:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
CREATE TABLE [dbo].[PageUrls] (
    [Id]     INT           IDENTITY (0, 1) NOT NULL,
        VARCHAR (300) NOT NULL,
    _cs] AS            (checksum()),
    CONSTRAINT [PK_PageUrls] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
CREATE NONCLUSTERED INDEX [NCI_PageUrls_Url_cs]
    ON [dbo].[PageUrls](_cs] ASC);
GO
CREATE PROCEDURE [dbo].[AddPageUrl]
	@Url VARCHAR(300)
	, @Id INT OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		DECLARE @T TABLE (Id INT);

		;WITH t 
		AS 
		(
			SELECT Id, Url FROM dbo.PageUrls WHERE Url_cs = CHECKSUM(@Url)
		)
		MERGE t USING 
		(
			SELECT @Url AS Url
		) as s 
		ON t.Url = s.Url
		WHEN MATCHED THEN 
			UPDATE SET @Id = t.Id
		WHEN NOT MATCHED BY TARGET THEN 
			INSERT(Url) 
			VALUES(s.Url)
		OUTPUT inserted.Id INTO @T;

		SELECT @Id = Id FROM @T;

		COMMIT TRANSACTION
		RETURN 0;
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
		;THROW
		RETURN 1;
	END CATCH
END
...
Рейтинг: 0 / 0
17.12.2021, 17:34
| Ответить | Цитировать | Написать  
Многопоточная вставка и выборка одной и той же строки. Блокировки
    #40121114
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
a_voronin
aleks222
пропущено...

Прям слезу умиления выжимает.


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
set @Id = null;
with x as ( select @Url as Url )
    ,  t  as ( select * from dbo.PageUrls with(rowlock) )
  merge t using x on t.Url = x.Url
    when not matched then insert(Url) values(Url)
    when matched then update set @Id = t.Id
;
if @Id is null set @Id = SCOPE_IDENTITY();

+
create unique index UX_Url on dbo.PageUrls(Url) with (IGNORE_DUP_KEY = ON);



Эту задачу про тракинг урлов (а иногда и блобов, картинок) не раз встречал в работе и не раз спрашивал на собеседованиях и сам был спрашиваем.

То, что здесь написали многие, вызовет недовольство грамотных собеседующих.

Первое -- если объект крупный (больше 20 байт) сразу напрашивается поле с хешом и индекс на него. Если вы траките урлы, то скорее всего у вас будут группы урлов, у которых первые 20-60 символов совпадают. Без хеша дикая потеря производительности.

Второе, не надо никаких хинтов. Вы просто пишите мердж, который отрабатывает две ветки -- новая -- не новая. Если не новая, то Select. Или всегда Select.

Все это работает многопоточно, даже когда несколько потоков вставляют одно и тоже новое значение.

Ну... собеседователь вы ишо тот.

1. Пока размер данных меньше границы индекса 900 байт - всякое "хэширование" зло и мартышкин труд.
2. Хинт тут никому не мешает, а помочь может. Ибо мы гарантированно ищем ОДНУ строку.
...
Рейтинг: 0 / 0
17.12.2021, 18:02
| Ответить | Цитировать | Написать  
Многопоточная вставка и выборка одной и той же строки. Блокировки
    #40121116
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
notemac

Что не так с SET XACT_ABORT ON и BEGIN TRY, это разве не стандартный паттерн?

Бред невозможно комментировать. Им можно только любоваться.

ЗЫ. Ну начнем с простого: "нафига тут транзакция?"
Шобы было?
...
Рейтинг: 0 / 0
17.12.2021, 18:09
| Ответить | Цитировать | Написать  
Многопоточная вставка и выборка одной и той же строки. Блокировки
    #40121120
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
a_voronin
Второе, не надо никаких хинтов. Вы просто пишите мердж, который отрабатывает две ветки -- новая -- не новая. Если не новая, то Select. Или всегда Select.

Все это работает многопоточно, даже когда несколько потоков вставляют одно и тоже новое значение.
Мерж без хинтов не гарантирует отсутствия дубликатов.

Подготавливаем таблицу
Код: sql
1.
2.
3.
4.
5.
6.
7.
use tempdb;
go

drop table if exists dbo.t;
create table dbo.t (id int primary key);
insert into dbo.t values (0);
go


Далее открываем три сессии в SSMS
В первой выполняем
Код: sql
1.
2.
3.
4.
5.
6.
use tempdb;
go

begin tran;
select top (0) * from dbo.t with (tablockx);
--commit;


Во второй и третьей запускаем
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
use tempdb;
set nocount on;
go

declare @id int = 1;

while 1 = 1
begin
 merge into dbo.t t
 using (select @id) s (id) on s.id = t.id
 when not matched
  then insert (id) values (s.id);

 set @id += 1;
end;


Далее в первой выполняем commit и ждем пока во второй или третьей не возникнет Violation of PRIMARY KEY constraint
И лечится это повышением TIL мержа до serializable
...
Рейтинг: 0 / 0
17.12.2021, 18:19
| Ответить | Цитировать | Написать  
Многопоточная вставка и выборка одной и той же строки. Блокировки
    #40121124
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
notemac,

авторЧто не так с SET XACT_ABORT ON и BEGIN TRY, это разве не стандартный паттерн?

ON и OFF перепутали. Тут надо или крестик снять или штаны надеть. То есть выбрать какой-то один способ обработки ошибки.
...
Рейтинг: 0 / 0
17.12.2021, 18:39
| Ответить | Цитировать | Написать  
Многопоточная вставка и выборка одной и той же строки. Блокировки
    #40121166
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Владислав Колосов
ON и OFF перепутали. Тут надо или крестик снять или штаны надеть. То есть выбрать какой-то один способ обработки ошибки.
А Эрланд написал, почему именно так.
Что у него неправильно?
Erland SommarskogПараметр XACT_ABORT необходим для более надежной обработки ошибок и транзакций. В частности, при настройках по умолчанию есть несколько ситуаций, когда выполнение может быть прервано без какого-либо отката транзакции, даже если у вас есть TRY-CATCH. Мы видели такой пример в предыдущем разделе, где мы выяснили, что TRY-CATCH не перехватывает ошибки компиляции, возникшие в той же области. Открытая транзакция, которая не была откачена из-за ошибки, может вызвать серьезные проблемы, если приложение работает дальше без завершения транзакции или ее отката.

Для надежной обработки ошибок в SQL Server вам необходимы как TRY-CATCH, так и SET XACT_ABORT ON. Среди них инструкция SET XACT_ABORT ON наиболее важна. Если для кода на промышленной среде только на нее полагаться не стоит, то для быстрых и простых решений она вполне подходит.
...
Рейтинг: 0 / 0
17.12.2021, 22:10
| Ответить | Цитировать | Написать  
Многопоточная вставка и выборка одной и той же строки. Блокировки
    #40121369
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
alexeyvg
Владислав Колосов
ON и OFF перепутали. Тут надо или крестик снять или штаны надеть. То есть выбрать какой-то один способ обработки ошибки.
А Эрланд написал, почему именно так.
Что у него неправильно?
Erland SommarskogПараметр XACT_ABORT необходим для более надежной обработки ошибок и транзакций. В частности, при настройках по умолчанию есть несколько ситуаций, когда выполнение может быть прервано без какого-либо отката транзакции, даже если у вас есть TRY-CATCH. Мы видели такой пример в предыдущем разделе, где мы выяснили, что TRY-CATCH не перехватывает ошибки компиляции, возникшие в той же области. Открытая транзакция, которая не была откачена из-за ошибки, может вызвать серьезные проблемы, если приложение работает дальше без завершения транзакции или ее отката.

Для надежной обработки ошибок в SQL Server вам необходимы как TRY-CATCH, так и SET XACT_ABORT ON. Среди них инструкция SET XACT_ABORT ON наиболее важна. Если для кода на промышленной среде только на нее полагаться не стоит, то для быстрых и простых решений она вполне подходит.


На солнце тоже есть пятна.

set xact_abort on;
Делает try чуть более чем бесполезным.
При любой ошибке транзакция переходит в нефиксируемое состояние.
Так зачем ее "обрабатывать"?
...
Рейтинг: 0 / 0
19.12.2021, 14:56
| Ответить | Цитировать | Написать  
Многопоточная вставка и выборка одной и той же строки. Блокировки
    #40121399
Фотография Ennor Tiegael
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ennor Tiegael
Участник
aleks222
set xact_abort on;
Делает try чуть более чем бесполезным.
При любой ошибке транзакция переходит в нефиксируемое состояние.
Так зачем ее "обрабатывать"?

Xact_abort нужен безотносительно try-catch, он выполняет другую задачу - гарантированный откат транзакции на тех ошибках, которые в catch не попадают, т.к. вызывают batch termination. 207, 208 и некоторые другие.

Когда хранимка валится с ошибкой, но при этом в соединении остается открытая транзакция - удовольствие крайне ниже среднего.
...
Рейтинг: 0 / 0
19.12.2021, 17:41
| Ответить | Цитировать | Написать  
Многопоточная вставка и выборка одной и той же строки. Блокировки
    #40121410
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
Ennor Tiegael
aleks222
set xact_abort on;
Делает try чуть более чем бесполезным.
При любой ошибке транзакция переходит в нефиксируемое состояние.
Так зачем ее "обрабатывать"?

Xact_abort нужен безотносительно try-catch, он выполняет другую задачу - гарантированный откат транзакции на тех ошибках, которые в catch не попадают, т.к. вызывают batch termination. 207, 208 и некоторые другие.

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


Я спрашивал зачем нужен xact_abort?
...
Рейтинг: 0 / 0
19.12.2021, 18:12
| Ответить | Цитировать | Написать  
Многопоточная вставка и выборка одной и той же строки. Блокировки
    #40121457
Ролг Хупин
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ролг Хупин
Участник
notemac
Коллеги, добрый день!
Имеется процедура:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
ALTER PROCEDURE [dbo].[AddPageUrl]
	@Url VARCHAR(300)
	, @Id INT OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;

	DECLARE @qqq INT;
	BEGIN TRY
		BEGIN TRANSACTION

		SELECT @Id = Id FROM dbo.PageUrls WHERE Url = @Url;

		IF @Id IS NULL
		BEGIN
			INSERT INTO dbo.PageUrls (Url)
			VALUES (@Url);

			SELECT @Id = SCOPE_IDENTITY();
		END

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

		SELECT @Id = Id FROM dbo.PageUrls WHERE Url = @Url;
	END CATCH

	RETURN 0;
END


Данная процедура может вызываться параллельно с одинаковым Url:
поток 1: exec [dbo].[AppPageUrl] 'http:\\page1'
поток 2: exec [dbo].[AppPageUrl] 'http:\\page1'
...
поток N: exec [dbo].[AddPageUrl] 'http:\\page1'

Необходимо гарантия, что не будет дедлоков и процедура ВСЕГДА вернет Id СУЩЕСТВУЮЩЕЙ строки.
Т.е. ситуации быть не должно:
1) Поток 1 вставляет строку 'http:\\page1'
2) Поток 2 вставляет строку 'http:\\page1', но возникает исключение, т.к. Поток 1 уже вставил данную строку.
3) Срабатывает блок CATCH в Потоке 2 и 'Select @Id = Id from ...' вернет NULL
4) Происходит COMMIT в Потоке 1 и строка 'http:\\page1' теперь имеется в БД
Возможна ли данная ситуация для приведенного выше кода?

Я проверил несколько раз, запуская сотни потоков, все работает без ошибок, но что-то не уверен.
С блокировкиами только начал знакомиться, пока не хватает времен погрузиться в материал, а уже нужно в ПРОД)))


Что бы это значило?
SELECT @Id = Id FROM dbo.PageUrls WHERE Url = @Url;

выбрать первую зись, у которой Url = @Url, ну, выберет всегда какую-то из 500 с Url = @Url и возьмет ее ид.
Когда появилсь структура таблицы - тем более . Шайтан
...
Рейтинг: 0 / 0
19.12.2021, 20:36
| Ответить | Цитировать | Написать  
Многопоточная вставка и выборка одной и той же строки. Блокировки
    #40121461
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
aleks222
set xact_abort on;
Делает try чуть более чем бесполезным.
При любой ошибке транзакция переходит в нефиксируемое состояние.
Так зачем ее "обрабатывать"?
Ну, он это делает для формирования сообщения об ошибке клиенту.
Я так не делаю, но в принципе нельзя же сказать, что там в обработчике будет неисполняемый код, т.к. до него никогда не дойдёт.
...
Рейтинг: 0 / 0
19.12.2021, 20:46
| Ответить | Цитировать | Написать  
Многопоточная вставка и выборка одной и той же строки. Блокировки
    #40121481
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Можно попасть в неприятную ситуацию, выполнив неконтролируемый откат при помощи SET XACT_ABORT ON. Эта настройка годится лишь для примитивных ситуаций вроде работ драйвера или ad-hoc выполнений.

Касаемо того, что не все ошибки ловит try-catch, то, во-первых, откат произойдет при таких ошибках независимо от XACT_ABORT, во-вторых, это аварийная ситуация и надо принимать меры по исправлению а не считать нормальным завершением работы.
...
Рейтинг: 0 / 0
19.12.2021, 21:29
| Ответить | Цитировать | Написать  
Многопоточная вставка и выборка одной и той же строки. Блокировки
    #40121539
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
Владислав Колосов
Можно попасть в неприятную ситуацию, выполнив неконтролируемый откат при помощи SET XACT_ABORT ON. Эта настройка годится лишь для примитивных ситуаций вроде работ драйвера или ad-hoc выполнений.

Касаемо того, что не все ошибки ловит try-catch, то, во-первых, откат произойдет при таких ошибках независимо от XACT_ABORT, во-вторых, это аварийная ситуация и надо принимать меры по исправлению а не считать нормальным завершением работы.


Это с try лехко залететь в "неприятную ситуацию".
А xact_abort on - православное воплощение принципа транзакционности: "либо целиком, либо никак".

Ваще то, надо читать мануалы до конца

SET XACT_ABORT ON instructs SQL Server to rollback the entire transaction and abort the batch when a run-time error occurs.

Эта установка вырубает пакет(процедуру) в точке ошибки. Никакие инструкции в текущем батче более не исполняются (ну.... окромя блока try).
Это самый простой и логичный способ обработки ошибок. Без try. TRY делает его слишком замудреным.

Нате вот, мусолили уже
https://www.sql.ru/forum/1276292-2/pokritikuyte-smeshivanie-xact-abort-try-i-tran-v-odnoy-procedure
...
Рейтинг: 0 / 0
20.12.2021, 06:57
| Ответить | Цитировать | Написать  
25 сообщений из 32, страница 1 из 2
  1  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Многопоточная вставка и выборка одной и той же строки. Блокировки
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&msg=40120952&tid=1683986]:
 0ms
get settings:
 11ms
get forum list:
 16ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 179ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 76ms
get tp. blocked users:
 2ms
others: 236ms
total:  544ms
созд. / загр.: 544ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]