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

Откуда: Москва
Сообщений: 4740
Господа, есть следующая ситуация

1) На Sql Server 2014 Ent была реализована критическая секция через sp_getapplock sp_releaseapplock (код ниже)
2) Код обеспечивал невозможность одновременного запуску процедуры в двух сессиях (по факту нескольких процедур). То есть невозможно запустить код, который запускает обновление данных более чем 1 раза.
3) Это успешно работало в том числе и при убивании и роллбеке транзакций
4) Но система взглюкнула при перезапуске сервера.


Сложились следующие условия
1) Транзакцию с этой процедурой найти не удается
2) Лок на накладывается
3) Некоторые записи, которые были вставлены в этой транзакции, видны, если использовать (NOLOCK)

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


ALTER PROCEDURE [Facts].[OrderDetails_Lock_Self]
AS
BEGIN
	-- procedure requires an open transaction 

	DECLARE @result int = -1;
	DECLARE @ResourceCount INT;
	DECLARE @Max_Retries int = 5;
	DECLARE @Retries int = @Max_Retries;

 	WHILE @Retries > 0 AND @result < 0
 	BEGIN  
 		SET @Retries =  @Retries - 1;
		SET @ResourceCount = 0;
--====== TABLE [Facts].[OrderDetails] ======
		EXEC @result = sp_getapplock @Resource = '[Facts].[OrderDetails]', @LockMode = 'Exclusive', @LockTimeout = 3000;
--====== OUTPUTS ======
 	END;

	IF @result < 0
	BEGIN 
		DECLARE @MSG NVARCHAR(MAX) = CONCAT(N'Despite having made ', @Max_Retries, N' retries cannot obtain exclusive lock on all of the resources required: '
			, N'[Facts].[OrderDetails]'
			,N'. Consider reorganizing sequence of updates.');
		THROW 51000, @MSG, 1;
	END;
END;

GO



ALTER PROCEDURE [Facts].[OrderDetails_Release_Self]
AS
BEGIN
	-- procedure requires an open transaction 
	DECLARE @result int;
--====== TABLE [Facts].[OrderDetails] ======
	EXEC @result = sp_releaseapplock @Resource = '[Facts].[OrderDetails]';
END;

GO



ALTER PROCEDURE [Facts].[OrderDetails_InitReload] (@From_LoadSeq INT = NULL)
AS
BEGIN
	
	  BEGIN TRAN 

	    EXEC [Facts].[OrderDetails_Lock_Self];

		--- SOME WORK

		EXEC [Facts].[OrderDetails_Release_Self];

	  COMMIT;
	


END;

GO
1 июн 17, 15:00    [20531604]     Ответить | Цитировать Сообщить модератору
 Re: sp_getapplock и перезапуск сервера  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36987
a_voronin
Транзакцию с этой процедурой найти не удается
Не удается найти где и кому?
a_voronin
Лок на накладывается
Лок не накладывается с каким результатом?
1 июн 17, 15:05    [20531628]     Ответить | Цитировать Сообщить модератору
 Re: sp_getapplock и перезапуск сервера  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4740
Гавриленко Сергей Алексеевич
a_voronin
Транзакцию с этой процедурой найти не удается
Не удается найти где и кому?
a_voronin
Лок на накладывается
Лок не накладывается с каким результатом?

1)
+
select
    SessionTrans.session_id as [SPID],
       percent_complete,
    total_elapsed_time as [Elapsed Time],
    cpu_time as [CPU Time],
    wait_type as [Wait Type],
    wait_time as [Wait Time],
    wait_resource as [Wait Resource],
    reads as [Reads],
    logical_reads as [Logical Reads],
    writes as [Writes],
    granted_query_memory as [Query Memory],
    SUBSTRING(SQLText.text,ExecReqs.statement_start_offset/2,(CASE WHEN ExecReqs.statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), SQLText.text)) * 2 ELSE ExecReqs.statement_end_offset end - ExecReqs.statement_start_offset)/2) AS query_text,
    database_transaction_log_record_count as [Log Records],
    database_transaction_log_bytes_used / 1024 as [Log KB Used],
    database_transaction_log_bytes_reserved / 1024 as [Log KB Reserved],
    database_transaction_log_bytes_used_system / 1024 as [Log KB Used (System)],
    database_transaction_log_bytes_reserved_system / 1024 as [Log KB Reserved (System)],
    database_transaction_replicate_record_count as [Replication Records],
    enlist_count as [Active Requests],
    ActiveTrans.transaction_id as [ID],
    ActiveTrans.name as [Name],
       ActiveTrans.transaction_begin_time as [Start Time],
    case transaction_type
        when 1 then 'Read/Write'
        when 2 then 'Read-Only'
        when 3 then 'System'
        when 4 then 'Distributed'
        else 'Unknown - ' + convert(varchar(20), transaction_type)
    end as [Transaction Type],
    case transaction_state
        when 0 then 'Uninitialized'
        when 1 then 'Not Yet Started'
        when 2 then 'Active'
        when 3 then 'Ended (Read-Only)'
        when 4 then 'Committing'
        when 5 then 'Prepared'
        when 6 then 'Committed'
        when 7 then 'Rolling Back'
        when 8 then 'Rolled Back'
        else 'Unknown - ' + convert(varchar(20), transaction_state)
    end as 'State',
    case dtc_state
        when 0 then NULL
        when 1 then 'Active'
        when 2 then 'Prepared'
        when 3 then 'Committed'
        when 4 then 'Aborted'
        when 5 then 'Recovered'
        else 'Unknown - ' + convert(varchar(20), dtc_state)
    end as 'Distributed State',
    DB.Name as 'Database',
    database_transaction_begin_time as [DB Begin Time],
    case database_transaction_type
        when 1 then 'Read/Write'
       when 2 then 'Read-Only'
        when 3 then 'System'
        else 'Unknown - ' + convert(varchar(20), database_transaction_type)
    end as 'DB Type',
    case database_transaction_state
        when 1 then 'Uninitialized'
        when 3 then 'No Log Records'
        when 4 then 'Log Records'
        when 5 then 'Prepared'
        when 10 then 'Committed'
        when 11 then 'Rolled Back'
        when 12 then 'Committing'
        else 'Unknown - ' + convert(varchar(20), database_transaction_state)
    end as 'DB State',
    command as [Command Type],
    --open_transaction_count as [Open Transactions],
    open_resultset_count as [Open Result Sets],
    row_count as [Rows Returned],
    nest_level as [Nest Level]
from sys.dm_tran_active_transactions ActiveTrans (nolock)
inner join sys.dm_tran_database_transactions DBTrans (nolock) on DBTrans.transaction_id = ActiveTrans.transaction_id
inner join sys.databases DB (nolock) on DB.database_id = DBTrans.database_id
left join sys.dm_tran_session_transactions SessionTrans (nolock) on SessionTrans.transaction_id = ActiveTrans.transaction_id
left join sys.dm_exec_requests ExecReqs (nolock) on ExecReqs.session_id = SessionTrans.session_id and ExecReqs.transaction_id = SessionTrans.transaction_id
outer apply sys.dm_exec_sql_text(ExecReqs.sql_handle) AS SQLText
where SessionTrans.session_id is not null -- comment this out to see SQL Server internal processes



не находит текст запроса или номера сессии (он был залогирован), в которой была наложена блокировка.
2)
EXEC @result = sp_getapplock @Resource = '[Facts].[OrderDetails]', @LockMode = 'Exclusive', @LockTimeout = 3000;


Возвращает -1
1 июн 17, 15:23    [20531741]     Ответить | Цитировать Сообщить модератору
 Re: sp_getapplock и перезапуск сервера  [new]
a_voronin
Member

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

sp_who2 также не находит сессии с соответствующим номером
1 июн 17, 15:25    [20531752]     Ответить | Цитировать Сообщить модератору
 Re: sp_getapplock и перезапуск сервера  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36987
Т.е. вы номер сессии ищите в каких-то логах, но не смотрите, кем заблокирован sp_getapplock перед тем, как он отваливается по таймауту?
1 июн 17, 15:33    [20531799]     Ответить | Цитировать Сообщить модератору
 Re: sp_getapplock и перезапуск сервера  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4740
Гавриленко Сергей Алексеевич
Т.е. вы номер сессии ищите в каких-то логах, но не смотрите, кем заблокирован sp_getapplock перед тем, как он отваливается по таймауту?


Не только номер сеcсии, если таблица под лог. В ней есть протокол работы процедуры (около 20 записей). Одна из записей и содержит номер сессии. Поскольку в SQL SERVER нет автономных транзакций, то эти записи откатываются по мере ROLLBACK. Но видны c NOLOCK пока rollback не закончился.

Вот сейчас они видны. А сессии нет. Текста запроса нет. Попытка наложить блокировку отваливается по -1
1 июн 17, 15:59    [20531921]     Ответить | Цитировать Сообщить модератору
 Re: sp_getapplock и перезапуск сервера  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4740
Гавриленко Сергей Алексеевич
Т.е. вы номер сессии ищите в каких-то логах, но не смотрите, кем заблокирован sp_getapplock перед тем, как он отваливается по таймауту?


Что значит "кем заблокирован sp_getapplock"?
1 июн 17, 16:00    [20531927]     Ответить | Цитировать Сообщить модератору
 Re: sp_getapplock и перезапуск сервера  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36987
a_voronin
Гавриленко Сергей Алексеевич
Т.е. вы номер сессии ищите в каких-то логах, но не смотрите, кем заблокирован sp_getapplock перед тем, как он отваливается по таймауту?


Что значит "кем заблокирован sp_getapplock"?
Еще раз -- вы ожидания сессии, пытающейся наложить applock, смотрели?
1 июн 17, 16:01    [20531933]     Ответить | Цитировать Сообщить модератору
 Re: sp_getapplock и перезапуск сервера  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4740
Гавриленко Сергей Алексеевич
a_voronin
пропущено...


Что значит "кем заблокирован sp_getapplock"?
Еще раз -- вы ожидания сессии, пытающейся наложить applock, смотрели?


"You attempted to acquire a transactional application lock without an active transaction." -- вы это имеете ввиду?

EXEC @result = sp_getapplock @Resource = '[Facts].[OrderDetails]', @LockMode = 'Exclusive', @LockTimeout = 3000;

три секунды не висит -- отваливается сразу.


Такое впечатление, что происходит некий скрытый rollback
1 июн 17, 16:05    [20531947]     Ответить | Цитировать Сообщить модератору
 Re: sp_getapplock и перезапуск сервера  [new]
invm
Member

Откуда: Москва
Сообщений: 9350
a_voronin
Что значит "кем заблокирован sp_getapplock"?
То и значит. Или по вашему самостоятельно по таймауту отваливается?
Временно уберите таймаут при вызове и смотрите в sys.dm_os_waiting_tasks кто мешает.
1 июн 17, 16:05    [20531949]     Ответить | Цитировать Сообщить модератору
 Re: sp_getapplock и перезапуск сервера  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4740
invm
a_voronin
Что значит "кем заблокирован sp_getapplock"?
То и значит. Или по вашему самостоятельно по таймауту отваливается?
Временно уберите таймаут при вызове и смотрите в sys.dm_os_waiting_tasks кто мешает.


Это

applicationlock hash=[F8292b3b4 databasePrincipalId=0 dbid=12 id=lock7114791780 mode=X
objectlock lockPartition=6 objid=1298871744 subresource=FULL dbid=10 id=lock4a5a37000 mode=IS associatedObjectId=1298871744
objectlock lockPartition=20 objid=1211151360 subresource=FULL dbid=10 id=lock1879fd0180 mode=S associatedObjectId=1211151360
1 июн 17, 16:24    [20532057]     Ответить | Цитировать Сообщить модератору
 Re: sp_getapplock и перезапуск сервера  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4740
a_voronin
Гавриленко Сергей Алексеевич
пропущено...
Еще раз -- вы ожидания сессии, пытающейся наложить applock, смотрели?


"You attempted to acquire a transactional application lock without an active transaction." -- вы это имеете ввиду?

EXEC @result = sp_getapplock @Resource = '[Facts].[OrderDetails]', @LockMode = 'Exclusive', @LockTimeout = 3000;

три секунды не висит -- отваливается сразу.


Такое впечатление, что происходит некий скрытый rollback




"You attempted to acquire a transactional application lock without an active transaction." -- это не то
1 июн 17, 16:26    [20532070]     Ответить | Цитировать Сообщить модератору
 Re: sp_getapplock и перезапуск сервера  [new]
a_voronin
Member

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

waiting_task_address session_id exec_context_id wait_duration_ms wait_type resource_address blocking_task_address blocking_session_id blocking_exec_context_id resource_description
0x0000000004AF44E8 126 0 568901 LCK_M_X 0x0000004AC8899D00 NULL -3 NULL applicationlock hash=[F8292b3b4 databasePrincipalId=0 dbid=12 id=lock7114791780 mode=X

blocking_session_id = -3 - это что значит?
1 июн 17, 16:29    [20532085]     Ответить | Цитировать Сообщить модератору
 Re: sp_getapplock и перезапуск сервера  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4740
blocking_session_id
ID of the session that is blocking the request. If this column is NULL, the request is not blocked, or the session information of the blocking session is not available (or cannot be identified).
-2 = The blocking resource is owned by an orphaned distributed transaction.
-3 = The blocking resource is owned by a deferred recovery transaction.
-4 = Session ID of the blocking latch owner could not be determined at this time because of internal latch state transitions.


Как поступать с этой штукой и как мониторить её прогресс?
1 июн 17, 16:32    [20532108]     Ответить | Цитировать Сообщить модератору
 Re: sp_getapplock и перезапуск сервера  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
a_voronin,
а тут кто?
SELECT 
*
FROM sys.dm_tran_locks
    WHERE request_session_id = -3
1 июн 17, 16:39    [20532160]     Ответить | Цитировать Сообщить модератору
 Re: sp_getapplock и перезапуск сервера  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36987
У вас третья фаза рекавери не завершилась, что ли?
1 июн 17, 16:42    [20532173]     Ответить | Цитировать Сообщить модератору
 Re: sp_getapplock и перезапуск сервера  [new]
invm
Member

Откуда: Москва
Сообщений: 9350
a_voronin
Как поступать с этой штукой и как мониторить её прогресс?
У вас редакция Ent, поэтому БД доступна во время фазы undo при recovery.
Мониторить можно по столбцу percent_complete в sys.dm_exec_requests.
1 июн 17, 16:55    [20532232]     Ответить | Цитировать Сообщить модератору
 Re: sp_getapplock и перезапуск сервера  [new]
CrazyDr1v3r
Guest
Read Committed Snapshot Isolation включен?
1 июн 17, 16:57    [20532236]     Ответить | Цитировать Сообщить модератору
 Re: sp_getapplock и перезапуск сервера  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4740
CrazyDr1v3r
Read Committed Snapshot Isolation включен?


ДА
1 июн 17, 17:00    [20532248]     Ответить | Цитировать Сообщить модератору
 Re: sp_getapplock и перезапуск сервера  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4740
invm
a_voronin
Как поступать с этой штукой и как мониторить её прогресс?
У вас редакция Ent, поэтому БД доступна во время фазы undo при recovery.
Мониторить можно по столбцу percent_complete в sys.dm_exec_requests.


Да нашлась

DB STARTUP percent_complete 7.5

И не двигается
1 июн 17, 17:04    [20532272]     Ответить | Цитировать Сообщить модератору
 Re: sp_getapplock и перезапуск сервера  [new]
msLex
Member

Откуда:
Сообщений: 8091
a_voronin
invm
пропущено...
У вас редакция Ent, поэтому БД доступна во время фазы undo при recovery.
Мониторить можно по столбцу percent_complete в sys.dm_exec_requests.


Да нашлась

DB STARTUP percent_complete 7.5

И не двигается

смотрите log SQLServer-а, там раз в 20 секунд добавляется запись с текущим состоянием рекавери (%, стадия) и "прогнозом" его окончания.
"прогноз", к сожалению, часто врет.
1 июн 17, 17:08    [20532294]     Ответить | Цитировать Сообщить модератору
 Re: sp_getapplock и перезапуск сервера  [new]
CrazyDr1v3r
Guest
a_voronin
CrazyDr1v3r
Read Committed Snapshot Isolation включен?


ДА

Ну вот - третья фаза recovery вкупе с RCSI дает такой эффект. Т.е. в базе уже как бы работать можно, но часть данных заблокирована процессом recovery плюс очистка ghost rows - процесс может затянутся. Вроде ничего не напутал :)
1 июн 17, 17:11    [20532306]     Ответить | Цитировать Сообщить модератору
 Re: sp_getapplock и перезапуск сервера  [new]
CrazyDr1v3r
Guest
msLex
"прогноз", к сожалению, часто врет.


На больших базах бывает, что прогнозируемое время восстановления увеличивается по мере увеличения процента завершения.
1 июн 17, 17:14    [20532337]     Ответить | Цитировать Сообщить модератору
 Re: sp_getapplock и перезапуск сервера  [new]
msLex
Member

Откуда:
Сообщений: 8091
CrazyDr1v3r
На больших базах бывает, что прогнозируемое время восстановления увеличивается по мере увеличения процента завершения.

К сожалению, оно часто просто врет, время прыгает вверх вниз, и даже % рекавери иногда уменьшается.





a_voronin,

Я бы на вашем месте все же глянул в лог сервера.
Пару раз наблюдал ситуацию, с безумными deadlock-ми между двумя системными процессами (сообщениями был завален весь лог).
После повторного ребута sql сервера, рекавери проходил достаточно быстро.
1 июн 17, 17:26    [20532404]     Ответить | Цитировать Сообщить модератору
 Re: sp_getapplock и перезапуск сервера  [new]
МуМу
Member

Откуда:
Сообщений: 1134
В таких ситуациях перезагрузка наше все.
2 июн 17, 08:32    [20533542]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить