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

Откуда:
Сообщений: 1420
Здравствуйте. Вопрос в теме. Есть таблица для регистрации, что с таким-то объектом работают.
И есть процедура для регистрации.
Процедура добавляет одну запись в таблицу при регистрации и обновляет эту же запись при продлении работы с объектом.
При каждом старте процедуры, выполняется отметка к удалению устаревших данных - для всех записей старше ххх секунд.
При обращении многих пользователей к этой процедуре возникает ошибка:

"Сообщение 1205, уровень 13, состояние 51, процедура CheckDocOpen, строка 40
Транзакция (идентификатор процесса 82) вызвала взаимоблокировку ресурсов блокировка с другим процессом и стала жертвой взаимоблокировки. Запустите транзакцию повторно."

Возникает она как раз при попытке отметить данные для удаления.
Как от этого избавиться?

+
/****** Object:  StoredProcedure [dbo].[CheckDocOpen]    Script Date: 01/30/2014 15:01:25 ******/
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=Заявка с сайта
*/	
	, @keyDoc int
	, @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
--print '1'
	IF @Action <> 3 BEGIN-- если это не продление действия, то отметим к удалению
		--те данные, которые там висят более 60 секунд
/*!!!!!!!!!!!!!!!!	ВОТ ЗДЕСЬ ОШИБКА    !!!!!!!!!!!!!!!!!!!!!!!!!*/
		UPDATE DocumentsInUse 
		SET ForDel = 1
			, datUpd = GETDATE()
		WHERE ForDel = 0 AND datediff(s, [datUpd], GETDATE()) > 60
--print '2'
		--удалим те данные, которые там висят более часа
		DELETE FROM DocumentsInUse 
		WHERE ForDel = 1 AND datediff(n, [datUpd], GETDATE()) > 60
	END
	
--print '3'
	IF @keyDoc = 0 AND @keyDocTxt <> '' --если ключ задан текстом
	BEGIN	
		IF @Action=0 OR @Action=1 --если запрос состояния или открытие документа, то 
		BEGIN
			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 keyDocTxt = @keyDocTxt AND ForDel = 0
			IF @@ERROR <> 0 GOTO mEr
			
			IF @txt = '' BEGIN -- если текста нет, значит и записи нет
				SET @res=0 -- можно открывать документ
				IF @Action=1 BEGIN -- если надо прописать открытие, то прописываем			
					INSERT INTO DocumentsInUse(vidDoc, keyDoc, keyDocTxt, keyW, datEnter, datUpd, txt)
					VALUES(@vidDoc, 0, @keyDocTxt, @keyW, GETDATE(), GETDATE(), 'Документ открыт другим пользователем')
					IF @@ERROR <> 0 GOTO mEr
					
					UPDATE D 
					SET	txt = 'Документ уже открыт' + CHAR(10) 
						+ 'Пользователь: ' +
							CASE WHEN Isnull(D.keyW,0)=-1 THEN 'Система'
							ELSE ISNULL(w.Fam,'') + ' ' + ISNULL(w.Ima,'') + ' '+ ISNULL(w.Otc,'') END 
						+ CHAR(10) 
						+ 'Время открытия: ' + convert(varchar, d.datEnter, 108) + CHAR(10) 						
					FROM DocumentsInUse D LEFT JOIN Workers W ON d.keyW = w.keyW
					WHERE d.vidDoc=@vidDoc AND d.keyDoc=0 AND d.keyDocTxt=@keyDocTxt AND D.ForDel = 0
					IF @@ERROR <> 0 GOTO mEr
				END
			END
			ELSE BEGIN
				SET @res = 1 --если текст есть, значит запись открыта, возвращаем фальш (открывать док. нельзя)
			END

		END 
		ELSE IF @Action = 2 --удалить информацию об открытом документе
		BEGIN
			--DELETE FROM DocumentsInUse WHERE vidDoc=@vidDoc AND keyDoc=@keyDoc
			UPDATE DocumentsInUse 
			SET ForDel = 1
				, datUpd = GETDATE()
			WHERE vidDoc=@vidDoc AND keyDoc=0 AND keyDocTxt=@keyDocTxt AND ForDel = 0
				AND keyW = @keyW --только тот кто вешал блокировку может удалить её
			IF @@ERROR <> 0 GOTO mEr
		END
		ELSE IF @Action = 3 --продлить открытие документа
		BEGIN
			UPDATE D
			SET datUpd=GETDATE()
			FROM DocumentsInUse D
			WHERE vidDoc=@vidDoc AND keyDoc = 0 AND keyDocTxt=@keyDocTxt AND ForDel = 0
			IF @@ERROR <> 0 GOTO mEr
		END
	END		
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:	
	print '^Ошибка определения блокировки документа!'
	SET @res = 1 --документ открывать НЕЛЬЗЯ-- можно
	SET @txt = 'Ошибка определения блокировки документа!'
	goto mEx	
	
END


30 янв 14, 15:52    [15495455]     Ответить | Цитировать Сообщить модератору
 Re: Как проапдейтить данные, не трогая заблокированные записи  [new]
Chippollino.01
Member

Откуда: изнемагаю
Сообщений: 35527
попробуйте делать обновление только НЕ заблокированных строк
получив их список через хинт READPAST

UPDATE D 
SET ForDel = 1, datUpd = GETDATE()
FROM DocumentsInUse D 
INNER JOIN (SELECT KeyF FROM DocumentsInUse WITH(READPAST)
                  WHERE ForDel = 0 AND datediff(s, [datUpd], GETDATE()) > 60) T
ON D.KeyF=T.KeyF


где D.KeyF=T.KeyF - условие кластерного индекса для однозначной идентификации строки (если у вас несколько полей то их список через and)

учтите каскадирование блокировок - возможно придется понизить до уровня строк
30 янв 14, 16:17    [15495623]     Ответить | Цитировать Сообщить модератору
 Re: Как проапдейтить данные, не трогая заблокированные записи  [new]
Chippollino.01
Member

Откуда: изнемагаю
Сообщений: 35527
UPDATE D WITH(ROWLOCK)
SET ForDel = 1, datUpd = GETDATE()
FROM DocumentsInUse D 
INNER JOIN (SELECT KeyF FROM DocumentsInUse WITH(READPAST)
                  WHERE ForDel = 0 AND datediff(s, [datUpd], GETDATE()) > 60) T
ON D.KeyF=T.KeyF


или просто добавить эти хинты в ваш UPDATE

UPDATE DocumentsInUse WITH(ROWLOCK,READPAST)
		SET ForDel = 1
			, datUpd = GETDATE()
		WHERE ForDel = 0 AND datediff(s, [datUpd], GETDATE()) > 60
30 янв 14, 16:33    [15495727]     Ответить | Цитировать Сообщить модератору
 Re: Как проапдейтить данные, не трогая заблокированные записи  [new]
aleks2
Guest
Chippollino.01
попробуйте делать обновление только НЕ заблокированных строк
получив их список через хинт READPAST

Не порите чушь. SQL не допустит такое.

Тредстатеру. Юзайте sp_getapplock.
Хотя бы для "выполняется отметка к удалению устаревших данных - для всех записей старше ххх секунд.".
Нафега вам параллельное "выполняется отметка к удалению устаревших данных - для всех записей старше ххх секунд."?

И будет вам щастье.
30 янв 14, 16:48    [15495845]     Ответить | Цитировать Сообщить модератору
 Re: Как проапдейтить данные, не трогая заблокированные записи  [new]
Chippollino.01
Member

Откуда: изнемагаю
Сообщений: 35527
>Не порите чушь. SQL не допустит такое.
чего не допустит?

BOL
Если указан аргумент READPAST, то блокировки уровня строк будут пропускаться. Это означает, что компонент Database Engine будет пропускать строки вместо блокировки текущей транзакции до тех пор, пока блокировки не будут сняты. Например, предположим, что в таблице T1 есть один целочисленный столбец со значениями 1, 2, 3, 4, 5. Если транзакция A изменит значение 3 на 8, но еще не будет зафиксирована, то инструкция SELECT * FROM T1 (READPAST) возвратит значения 1, 2, 4, 5. Параметр READPAST главным образом используется для устранения конфликта блокировок при реализации рабочей очереди, использующей таблицу SQL Server. Средство чтения очереди, использующее аргумент READPAST, пропускает прошлые записи очереди, заблокированные другими транзакциями, до следующей доступной записи очереди, не дожидаясь, пока другие транзакции снимут свои блокировки.
30 янв 14, 16:56    [15495904]     Ответить | Цитировать Сообщить модератору
 Re: Как проапдейтить данные, не трогая заблокированные записи  [new]
Диам
Member

Откуда:
Сообщений: 1420
aleks2
Нафега вам параллельное "выполняется отметка к удалению устаревших данных - для всех записей старше ххх секунд."?

Вот этого не понял...
Сейчас сделал так как описано тут 6555329 - обновление делаю маленькими порциями в цикле.
И дело пошло, но возможно это только временное решение.


почитаю про ROWLOCK,READPAST и sp_getapplock
30 янв 14, 16:56    [15495906]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить