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

Откуда:
Сообщений: 1497
Подскажите, что не так в процедуре. Получаю частые ошибки
"Транзакция (идентификатор процесса 204) вызвала взаимоблокировку ресурсов блокировка с другим процессом и стала жертвой взаимоблокировки. Запустите транзакцию повторно."
Чаще всего они возникают на инструкции следующей за SET @pnt = '14', редко за SET @pnt = '10'
По записям в логе ошибок вижу, что ошибка возникает, когда одновременно с десяток юзеров выполняют "продление работы с объектом"
В таблице около 50-100 записей

Текст процедуры:
+
/****** Object:  StoredProcedure [dbo].[CheckDocOpen]    Script Date: 09/17/2015 14:14:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<diam>
-- Create date: <Create Date,,>
-- Description:	Проверка открыт ли документ. 1=открыт. 0=закрыт (т.е. можно открывать)
-- =============================================
ALTER PROCEDURE [dbo].[CheckDocOpen]
(
	@Action smallint
/*9
0 = проверить статус документа, 
1 = проверить статус, и если не открыт, то записать открытие документа, 
2 = удалить данные об открытии документа
3 = продлить время работы с документом
*/
	, @vidDoc smallint
/*
   1=заявка
   2-договор
   3-платеж по договору
   4=клиент
   5=платеж через киви. Используется только на сервере. В программе НЕ используется!!!!
   6=Заявка с сайта
   7=Реестр передаваемых документов
*/	
	, @keyDoc int = 0
	, @keyW int --ключ пользователя
	, @Silent bit = 0 --0=всегда возвращать результат 1=без возврата результата (для Action=2,3)
	, @keyDocTxt nvarchar(160)='' --ключ документа, но в виде текста (например фио. Если задано это значение, то keyDoc должно быть = 0
	, @FromExtProc smallint = 0 --( -1 = признак, что процедура вызывается из другой процедуры)
)	
	
AS
BEGIN	
	SET NOCOUNT ON;
	declare @txt nvarchar(MAX)='', @res smallint, @pnt nvarchar(2)=''
	
	BEGIN TRY
	--print '1'
		IF @Action <> 3 BEGIN-- если это не продление действия, то отметим к удалению
			SET @pnt = '1'
			--те данные, которые там висят более 60 секунд
			UPDATE TOP (100) DocumentsInUse WITH(READPAST)
			SET ForDel = 1, datUpd = GETDATE()
			WHERE ForDel = 0 AND [datUpd] < dateadd(s, -60, GETDATE())
			
			SET @pnt = '2'
			WHILE @@ROWCOUNT > 0
				UPDATE TOP (100) DocumentsInUse WITH(READPAST)
				SET ForDel = 1, datUpd = GETDATE()
				WHERE ForDel = 0 AND [datUpd] < dateadd(s, -60, GETDATE())		

			--удалим те данные, которые там висят более 15 минут
			SET @pnt = '3'
			DELETE TOP (100) FROM DocumentsInUse WITH(READPAST)
			WHERE ForDel = 1 AND [datUpd] < dateadd(n, -15, GETDATE())
			
			SET @pnt = '4'
			WHILE @@ROWCOUNT > 0
				DELETE TOP (100) FROM DocumentsInUse WITH(READPAST)
				WHERE ForDel = 1 AND [datUpd] < dateadd(n, -15, GETDATE())
		END
		
		IF @Action=0 OR @Action=1 --если запрос состояния или открытие документа, то 
		BEGIN
			SET @pnt = '10'
			SET @txt = ''
			--запрашиваем состояние
			SELECT @txt = isnull(D.txt, '') 
				+ 'Продолжительность работы с документом: ' + cast(round(datediff(s, d.datEnter, getdate())/60,1) as varchar)  + ' мин.'
			FROM DocumentsInUse D
			WHERE vidDoc = @vidDoc and keyDoc = @keyDoc AND ForDel = 0
			
			IF @txt = '' BEGIN -- если текста нет, значит и записи нет				
				SET @res=0 -- можно открывать документ
				IF @Action=1 BEGIN -- если надо прописать открытие, то прописываем
					SET @pnt = '11'
					INSERT INTO DocumentsInUse(vidDoc, keyDoc, keyW, datEnter, datUpd, txt)
					VALUES(@vidDoc, @keyDoc, @keyW, GETDATE(), GETDATE() --, 'Документ открыт другим пользователем')
						,'Документ уже открыт' + CHAR(10) 
							+ 'Пользователь: ' +
								CASE WHEN Isnull(@keyW,0)=-1 THEN 'Система'
								ELSE ISNULL((SELECT ISNULL(w.Fam,'') + ' ' + ISNULL(w.Ima,'') + ' '+ ISNULL(w.Otc,'') 
												FROM Workers W 
												WHERE W.keyW=@keyW), '')
								END 
							+ CHAR(10) 
							+ 'Время открытия: ' + convert(varchar, GETDATE(), 108) + CHAR(10)
						) 	
				END
			END
			ELSE BEGIN
				SET @res = 1 --если текст есть, значит запись открыта, возвращаем фальш (открывать док. нельзя)
			END
		END 
		
		ELSE IF @Action = 2 --удалить информацию об открытом документе
		BEGIN				
			DELETE FROM DocumentsInUse 
			WHERE vidDoc=@vidDoc AND keyDoc=@keyDoc AND ForDel = 0			
		END
		
		ELSE IF @Action = 3 --продлить открытие документа
		BEGIN
			SET @pnt = '14'
			UPDATE D
			SET datUpd=GETDATE()
			FROM DocumentsInUse D
			WHERE vidDoc=@vidDoc AND keyDoc=@keyDoc -- AND ForDel = 0 --возьмем даже помеченные к удалению записи				
		END		
		
	END TRY
	BEGIN CATCH
		SET @res = 1 --документ открывать НЕЛЬЗЯ
		SET @txt = CAST(ERROR_LINE() as varchar) + ' ' + ERROR_MESSAGE()
		goto mEr
	END CATCH
	
mEx:
	IF @Action=0 OR @Action=1 OR @Silent = 0
		IF @FromExtProc = 0 --если допускается вставка результата работы процедуры в таблицу
			SELECT @res Res, @txt txt
		ELSE --если недопускается
			INSERT INTO #CheckDocOpen_T (res, txt)
			SELECT @res Res, @txt txt
	return
	
mEr:		
	declare @errComm nvarchar(250)
	SET @errComm = LEFT('exec CheckDocOpen(pnt' + @pnt + ') ' + LTRIM(str(@Action,5,0)) + ',' +LTRIM(str(@vidDoc,5,2)) + ',' + LTRIM(str(@keyDoc, 10,0)) 
		+ ',' + LTRIM(str(@keyW,10,0)) + ',' + LTRIM(str(@Silent,3,0))+','''+@keyDocTxt+''',' + LTRIM(str(@FromExtProc,5,0)), 250)
	exec spLogErr 0, 'CheckDocOpen', @errComm
	goto mEx	
	
END



Таблица DocumentsInUse:
+

/****** Object: Table [dbo].[DocumentsInUse] Script Date: 09/17/2015 14:37:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DocumentsInUse](
[keyR] [int] IDENTITY(1,1) NOT NULL,
[vidDoc] [int] NULL,
[keyDoc] [int] NULL,
[keyW] [int] NULL,
[datEnter] [datetime] NULL,
[datUpd] [datetime] NULL,
[txt] [nvarchar](500) NULL,
[ForDel] [bit] NULL,
[keyDocTxt] [nvarchar](160) NULL,
CONSTRAINT [PK_DocumentsInUse] PRIMARY KEY CLUSTERED
(
[keyR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


/****** Object: Index [IX_DocumentsInUse] Script Date: 09/17/2015 14:37:03 ******/
CREATE NONCLUSTERED INDEX [IX_DocumentsInUse] ON [dbo].[DocumentsInUse]
(
[vidDoc] ASC,
[keyDoc] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


/****** Object: Index [IX_DocumentsInUse_1] Script Date: 09/17/2015 14:37:03 ******/
CREATE NONCLUSTERED INDEX [IX_DocumentsInUse_1] ON [dbo].[DocumentsInUse]
(
[keyW] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


/****** Object: Index [IX_DocumentsInUse_2] Script Date: 09/17/2015 14:37:03 ******/
CREATE NONCLUSTERED INDEX [IX_DocumentsInUse_2] ON [dbo].[DocumentsInUse]
(
[datUpd] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


/****** Object: Index [IX_DocumentsInUse_3] Script Date: 09/17/2015 14:37:03 ******/
CREATE NONCLUSTERED INDEX [IX_DocumentsInUse_3] ON [dbo].[DocumentsInUse]
(
[ForDel] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'вид документа' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DocumentsInUse', @level2type=N'COLUMN',@level2name=N'vidDoc'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ключ документа' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DocumentsInUse', @level2type=N'COLUMN',@level2name=N'keyDoc'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ключ сотрудника открывшего документ keyDoc' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DocumentsInUse', @level2type=N'COLUMN',@level2name=N'keyW'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'дата открытия документа' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DocumentsInUse', @level2type=N'COLUMN',@level2name=N'datEnter'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'дата последнего обновления (должно обновляться каждые 15 секунд, иначе следующий кто будет открывать этот документ эту запись сотрут)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DocumentsInUse', @level2type=N'COLUMN',@level2name=N'datUpd'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'текст для того кто пытается открыть документ, а он уже открыт кем-то другим' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DocumentsInUse', @level2type=N'COLUMN',@level2name=N'txt'
GO

ALTER TABLE [dbo].[DocumentsInUse] ADD CONSTRAINT [DF_DocumentsInUse_ForDel] DEFAULT ((0)) FOR [ForDel]
GO
17 сен 15, 14:45    [18161152]     Ответить | Цитировать Сообщить модератору
 Re: Частые взаимоблокировки ресурсов. Помогите разобраться.  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
Граф дедлока выкладывайте. В формате xdl.
17 сен 15, 14:57    [18161257]     Ответить | Цитировать Сообщить модератору
 Re: Частые взаимоблокировки ресурсов. Помогите разобраться.  [new]
Диам
Member

Откуда:
Сообщений: 1497
invm, отсюда https://www.sql.ru/faq/faq_topic.aspx?fid=962 выполнил действия. Но там пишется в текстовый файл. Как получить в формате xdl?
17 сен 15, 15:15    [18161444]     Ответить | Цитировать Сообщить модератору
 Re: Частые взаимоблокировки ресурсов. Помогите разобраться.  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
Диам
Как получить в формате xdl?
Профайлером
17 сен 15, 15:27    [18161551]     Ответить | Цитировать Сообщить модератору
 Re: Частые взаимоблокировки ресурсов. Помогите разобраться.  [new]
клиентодзынь
Guest
Диам,

одновременно catch и "goto error:"? т.е. уже все сделано чтобы выкинуть goto к херам собачьим, но цепляемся за него всеми силами?

в первом же ифе сделано все, чтобы дедлоки не кончались никогда.
есть, правда, один махынькый момент:
транзакция где открывается? с клиента?
17 сен 15, 16:23    [18161898]     Ответить | Цитировать Сообщить модератору
 Re: Частые взаимоблокировки ресурсов. Помогите разобраться.  [new]
Диам
Member

Откуда:
Сообщений: 1497
клиентодзынь, спасибо за замечания по коду. Если прокомментите еще - буду благодарен. А где вы увидели "goto error". Раньше я его действительно использовал, но сейчас вроде в этой процедуре все почищено.

Транзакция нигде не начинается .
17 сен 15, 16:57    [18162109]     Ответить | Цитировать Сообщить модератору
 Re: Частые взаимоблокировки ресурсов. Помогите разобраться.  [new]
клиентодзынь
Guest
Диам,

по goto
я извиняюсь, в коде две метки и как минимум один goto

не очень понимаю, как можно решать вопрос наличия заведомо известной строчки в коде который находится у вас через форум.
нажмите ctrl+f

транзакция обязательно где-нибудь стартует. у вас же дедлок. могло конечно через fk, но его вроде как нет.
триггеры есть?

граф дедлока будет?
17 сен 15, 17:19    [18162265]     Ответить | Цитировать Сообщить модератору
 Re: Частые взаимоблокировки ресурсов. Помогите разобраться.  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
Если в таблице всего 50 100 записей то ридпаст вас не спасет. Сразу получить лок всей таблицы а не строк
17 сен 15, 21:35    [18163162]     Ответить | Цитировать Сообщить модератору
 Re: Частые взаимоблокировки ресурсов. Помогите разобраться.  [new]
Диам
Member

Откуда:
Сообщений: 1497
Граф дедлоков прилагаю.
Триггеров нет.

Модератор: Вложение удалено.


Сообщение было отредактировано: 18 сен 15, 07:18
17 сен 15, 22:36    [18163370]     Ответить | Цитировать Сообщить модератору
 Re: Частые взаимоблокировки ресурсов. Помогите разобраться.  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Диам,

trancount="2"


так и планировались или где-то пропущен commit?
17 сен 15, 22:53    [18163456]     Ответить | Цитировать Сообщить модератору
 Re: Частые взаимоблокировки ресурсов. Помогите разобраться.  [new]
в целом конечно да...
Guest
churupaha,

если честно, по схеме (первый) дедлок что-то не пойму. он же не может из разных транзакций одну и ту же решетку мучать?

а так, в целом, ТС, финита ля "можно я сам попробую". валится на всем подряд.
17 сен 15, 23:02    [18163476]     Ответить | Цитировать Сообщить модератору
 Re: Частые взаимоблокировки ресурсов. Помогите разобраться.  [new]
Диам
Member

Откуда:
Сообщений: 1497
churupaha
Диам,

trancount="2"


так и планировались или где-то пропущен commit?

вы имеете ввиду, что это вложенная транзакция?
17 сен 15, 23:34    [18163580]     Ответить | Цитировать Сообщить модератору
 Re: Частые взаимоблокировки ресурсов. Помогите разобраться.  [new]
хмхмхм
Guest
Диам
churupaha
Диам,

trancount="2"


так и планировались или где-то пропущен commit?

вы имеете ввиду, что это вложенная транзакция?


автор
Транзакция нигде не начинается .
18 сен 15, 08:31    [18163863]     Ответить | Цитировать Сообщить модератору
 Re: Частые взаимоблокировки ресурсов. Помогите разобраться.  [new]
Диам
Member

Откуда:
Сообщений: 1497
граф дедлока

К сообщению приложен файл (deadlock.xdl - 82Kb) cкачать
18 сен 15, 10:06    [18164169]     Ответить | Цитировать Сообщить модератору
 Re: Частые взаимоблокировки ресурсов. Помогите разобраться.  [new]
komrad
Member

Откуда:
Сообщений: 5758
пара мыслей ...
+

автор
DomeName\sdfachkin.s

- Не DomeName, а Domain
- Фачкин - это реальный персонаж?
18 сен 15, 10:29    [18164274]     Ответить | Цитировать Сообщить модератору
 Re: Частые взаимоблокировки ресурсов. Помогите разобраться.  [new]
Диам
Member

Откуда:
Сообщений: 1497
komrad, это вымышленные названия
18 сен 15, 10:35    [18164306]     Ответить | Цитировать Сообщить модератору
 Re: Частые взаимоблокировки ресурсов. Помогите разобраться.  [new]
Диам
Member

Откуда:
Сообщений: 1497
Кто-нибудь может помочь? Что сделать, на что обратить внимание?
18 сен 15, 13:08    [18165309]     Ответить | Цитировать Сообщить модератору
 Re: Частые взаимоблокировки ресурсов. Помогите разобраться.  [new]
что тут сказать
Guest
Диам,

на что обратить внимание - в графе дедлока написано

что сделать - переписать


+
ps
один мой знакомый сидит по случаю кризиса без работы и от лишних единовременнопроектных денег за рефакторинг легаси приложения я бы не отказался. за месячишко думаю можно нормально перелопатить. смотря сколько там всего.
18 сен 15, 13:43    [18165547]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить