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

Откуда:
Сообщений: 265
Подскажите если у меня в цикле проходит обращение в процедуре и происходит взаимоблокировка, как я могу избежать этого?

Посоветовали concurent dictionary - то я даже нагуглить не смог

Пробовал SERIALIZABLE - не помогло
17 авг 18, 15:54    [21646143]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36696
Начните, для начала, с публикации сообщения об ошибке.
17 авг 18, 15:59    [21646149]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки  [new]
Владимир Затуливетер
Member

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

ConcurrentDictionary это вроде как из C#, вам точно это для SQL Server посоветовали?
https://msdn.microsoft.com/ru-ru/library/dd287191(v=vs.110).aspx

Вам вероятно придется код скинуть этих процедур, иначе сложно помочь.
17 авг 18, 16:00    [21646152]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки  [new]
Pabl0
Member

Откуда:
Сообщений: 265
Да вот я тоже увидел что это для C, сначала я пытался эмитировать взаимо блокировку, а теперь нужно решение как обойти))

	SET NOCOUNT ON;
	
	set xact_abort on; -- специально 
	
		begin TRANSACTION
		
Какой то код ...
		
		commit transaction;

	
	return 0
17 авг 18, 18:47    [21646350]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки  [new]
Pabl0
Member

Откуда:
Сообщений: 265
В том то и дело, что ошибки у меня нету, но когда выполняется в цикле то иногда выдает "0"
17 авг 18, 18:50    [21646351]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36696
Pabl0, ваш поток сознания слишком мутный, чтобы в нем что-то углядеть.
Взаимоблокировка имеет четкое определение и всегда заканчивается тем, что один из потоков отстреливается с соответствующей ошибкой.
17 авг 18, 19:20    [21646391]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки  [new]
Pabl0
Member

Откуда:
Сообщений: 265
У меня цикл включается в API и потом пуляет на SQL я просто тупо не знаю как мне увидеть эту взаимоблокировку.

Когда во время своего цикла я нажимаю sp_lock то показывает очень много Х

Я смотрел в Управление - Журнале MS SQL но так не чего и не увидел там

Ошибки происходят 100, потому что из цикла к примеру на 100 оборотов, добавляет в базу только 70+-, а должен добавить 100

И тут вопрос толи он не успевает добавить в базу, то ли взаимо блокировки но где ошибку посмотреть
17 авг 18, 19:27    [21646395]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36696
Ну так разберитесь в вашем API, как получать ошибки, и залогируйте их.
17 авг 18, 19:29    [21646398]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки  [new]
Pabl0
Member

Откуда:
Сообщений: 265
Такс отлично, ошибок я получил 180 штук, все идентичны

"Транзакция (идентификатор процесса РАЗНОЕ ЧИСЛО) вызвала взаимоблокировку ресурсов блокировка с другим процессом и стала жертвой взаимоблокировки. Запустите транзакцию повторно."

Теперь мы уверены что это взаимно блокировки, теперь если я подниму уровень изоляции до Repetable Read это меня спасет?
17 авг 18, 19:51    [21646419]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки  [new]
Pabl0
Member

Откуда:
Сообщений: 265
SERIALIZABLE даже он не помог :(
17 авг 18, 19:57    [21646422]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки  [new]
Pabl0
Member

Откуда:
Сообщений: 265
Вот код

	SET NOCOUNT ON;
	
	set xact_abort on;
	
		begin TRANSACTION
		
    	insert into dbo.City([From])
		select @from
		
		IF NOT EXISTS(SELECT * FROM Estimate (NOLOCK) a WHERE a.id = @Id)
		BEGIN
			INSERT INTO Estimate (id, sum)
			SELECT @Id, -@sum
		END 
		ELSE 
		BEGIN
			UPDATE Estimate
				SET sum = sum - @sum
			WHERE id = @Id
		END


		IF NOT EXISTS(SELECT * FROM Estimate (NOLOCK) a WHERE a.id = @Id)
		BEGIN
			INSERT INTO Estimate (id, sum)
			SELECT @Id, -@sum
		END 
		ELSE 
		BEGIN
			UPDATE Estimate
				SET sum = sum - @sum
			WHERE id = @Id
		END

		
		commit transaction;;

	
	return 0
17 авг 18, 20:10    [21646436]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки  [new]
Pabl0
Member

Откуда:
Сообщений: 265
Если все 3 блока разбить на отдельные транзакции все будет работать, скажите правильно ли это?
17 авг 18, 20:26    [21646451]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки  [new]
nvv
Member

Откуда:
Сообщений: 54
Pabl0, вероятно две сессии пишут один id. Или по id нет индекса.
Очевидно же, что дедлок на апдейте.
Serializable - помочь не сможет, на блокировке диапазонов сделает только хуже.
18 авг 18, 11:36    [21646700]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки  [new]
aleks222
Member

Откуда:
Сообщений: 855
Pabl0
Вот код

	SET NOCOUNT ON;
	
	set xact_abort on;
	
		begin TRANSACTION
		
    	insert into dbo.City([From])
		select @from
		
		IF NOT EXISTS(SELECT * FROM Estimate (NOLOCK) a WHERE a.id = @Id)
		BEGIN
			INSERT INTO Estimate (id, sum)
			SELECT @Id, -@sum
		END 
		ELSE 
		BEGIN
			UPDATE Estimate
				SET sum = sum - @sum
			WHERE id = @Id
		END


		IF NOT EXISTS(SELECT * FROM Estimate (NOLOCK) a WHERE a.id = @Id)
		BEGIN
			INSERT INTO Estimate (id, sum)
			SELECT @Id, -@sum
		END 
		ELSE 
		BEGIN
			UPDATE Estimate
				SET sum = sum - @sum
			WHERE id = @Id
		END

		
		commit transaction;;

	
	return 0


Ну, если у вас все верно написано - это эвивалентно

begin TRANSACTION;
            
		
    	    insert into dbo.City([From])
		    select @from;
		
		
            with t as ( SELECT * FROM Estimate  )
               , x as ( SELECT id = @Id, sum = - @sum )
              merge t using x on t.id = x.id
                when not matched then insert(id, sum) values(id, sum)
                when matched then update set sum = sum + 2 * x.sum
		
commit transaction;
18 авг 18, 12:17    [21646710]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки  [new]
Pabl0
Member

Откуда:
Сообщений: 265
nvv, да, я с вами полностью согласен на изменении дедлоки
19 авг 18, 07:31    [21647017]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки  [new]
Pabl0
Member

Откуда:
Сообщений: 265
aleks222, я сильно извиняюсь, но с кодом ппц намудрил, вот как должно быть

	SET NOCOUNT ON;
	
	set xact_abort on;
	
		begin TRANSACTION
		
    	insert into dbo.City([From])
		select @from
		
		IF NOT EXISTS(SELECT * FROM Estimate (NOLOCK) a WHERE a.id = @to)
		BEGIN
			INSERT INTO Estimate (id, sum)
			SELECT @to, -@sum
		END 
		ELSE 
		BEGIN
			UPDATE Estimate
				SET sum = sum - @sum
			WHERE id = @to
		END


		IF NOT EXISTS(SELECT * FROM Estimate (NOLOCK) a WHERE a.id = @from)
		BEGIN
			INSERT INTO Estimate (id, sum)
			SELECT @from, +@sum
		END 
		ELSE 
		BEGIN
			UPDATE Estimate
				SET sum = sum + @sum
			WHERE id = @ from
		END

		
		commit transaction;;

	
	return 0


извините еще раз пожалуйста
19 авг 18, 07:58    [21647022]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки  [new]
invm
Member

Откуда: Москва
Сообщений: 9128
Pabl0,

Замените вашу череду excists/insert/update на это
merge into Estimate t
using
(
 values
  (@from, @sum), (@to, -@sum)
) s(id, sum)
when not matched then
 insert (id, sum) values (s.id, s.sum)
when matched then
 update set sum += s.sum;

О дедлоке можно предметно говорить, если покажете его граф (в виде ml, картинка бесполезна).
19 авг 18, 10:01    [21647043]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки  [new]
Pabl0
Member

Откуда:
Сообщений: 265
А если как я сделал в отдельные транзакции это не правильно?
19 авг 18, 13:54    [21647176]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки  [new]
Владимир Затуливетер
Member

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

Если логика позволяет сделать без одной общей транзакции, т.е. каждый стейтмент в отдельной транзакции (кстати явно транзакцию для одного стейтмента указывать не нужно), то это вероятно будет работать, дедлоков не должно быть.

Лучше попробуйте разобраться с предложенным merge решением. Вы сохраняете транзакцию и не делаете лишних движений.
19 авг 18, 14:17    [21647193]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки  [new]
invm
Member

Откуда: Москва
Сообщений: 9128
Pabl0
А если как я сделал в отдельные транзакции это не правильно?
Ну если вам наплевать, что, например, к @from @sum прибавится, а от @to @sum не отнимется, то можете вообще не заморачиваться с транзакциями.

Если таки решите разобраться с транзакциями и дедлоками, то мой пример лучше переписать так:
declare @s table (id ... primary key, sum ...);
insert into @s
values
 (@from, @sum), (@to, -@sum)

merge into Estimate t
using @s s on s.id = t.id
when not matched then
 insert (id, sum) values (s.id, s.sum)
when matched then
 update set sum += s.sum;

ЗЫ: А исходный пример надо бы поправить
merge into Estimate t
using
(
 values
  (@from, @sum), (@to, -@sum)
) s(id, sum) s on s.id = t.id
when not matched then
 insert (id, sum) values (s.id, s.sum)
when matched then
 update set sum += s.sum;
19 авг 18, 14:43    [21647216]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
invm
лучше переписать так

Поясните пожалуйста почему лучше?
20 авг 18, 10:03    [21647695]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки  [new]
invm
Member

Откуда: Москва
Сообщений: 9128
Владимир Затуливетер
Поясните пожалуйста почему лучше?
Чтобы снизить вероятность дедлока, если несколько сессий пересекуться по (@from, @to).
20 авг 18, 10:12    [21647711]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки  [new]
Владислав Колосов
Member

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

IF NOT EXISTS(SELECT * FROM Estimate (NOLOCK) a WHERE a.id = @from)


(NOLOCK) не используйте в процедурах, изменяющих данные или готовящих изменения. Потом будете списывать загадочные случаи на "компьютерный сбой" :)
20 авг 18, 14:33    [21648224]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить