데이터베이스 질문과 답변
안녕하세요 망설이다가 문의 드립니다.
첨부파일에 나타난 SQL server의 Lock모니터에서 149번 세션의 select구문에의해 DB가 lock걸려
일시적 장애가 발생했다고 합니다. (DBA왈)
저 나름 해석한 부분이 맞는지 확인 부탁드려봅니다.
첨부파일의 이미지상 149번 세션은 1번 row의 select후 (해당 procedure는 132ms로 엄청 빠르며, 정말 단순 select이며 plan은 PK인덱스 스켄)
바로 Update구문을 수행하면서 Lock을 걸었을것이고 이에 다른 세션들(125,188...)이 대기하지 않았나 판단합니다.
맞나요? ㅠㅠ
도움이 절실한 상태입니다. (SQL Server는 좀 낮설어서요 ㅠㅠ)
말씀하신 해석 부분은 정상적인 프로세스입니다.
일시적 장애 발생이 교착상태를 의미한다면 update로 인한 lock이 왜 해제되지 않았는지를 확인해봐야 합니다.
해당 DBA는 그 원인이 149와 125 세션에 있다는 것 같습니다.
149세션의 select에는 nolock 힌트가 명시되어 있을까요?
안녕하세요
회신 감사드리며, select에는 nolock 힌트가 없습니다. 해당 프로시저는 아래와 같습니다. 제가 해석한 부분이 맞나요?
USE [DBORG]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
-- **************************************************************************
ALTER PROCEDURE [dbo].[flx_spOperationStepXmlSelectByPK]
(
@ID int,
@Active bit,
@__SupportBLOB__ int
)
AS
SELECT
[ID] AS [ID],
[FUID] AS [FUID],
[Properties] AS [Properties],
[ReferenceID] AS [ReferenceID],
[LastUpdateOn] AS [LastUpdateOn],
[LastUpdatedBy] AS [LastUpdatedBy],
[CreatedOn] AS [CreatedOn],
[CreatedBy] AS [CreatedBy],
[Active] AS [Active],
[LastDeleteOn] AS [LastDeleteOn],
[LastDeletedBy] AS [LastDeletedBy],
[LastReactivateOn] AS [LastReactivateOn],
[LastReactivatedBy] AS [LastReactivatedBy],
[ArchiveID] AS [ArchiveID],
[LastArchiveOn] AS [LastArchiveOn],
[LastArchivedBy] AS [LastArchivedBy],
[LastRestoreOn] AS [LastRestoreOn],
[LastRestoredBy] AS [LastRestoredBy],
[RowVersionStamp] AS [RowVersionStamp]
FROM
[OPERATION_STEP]
WHERE
(
([OPERATION_STEP].[ID] = @ID)
)
AND
([OPERATION_STEP].[Active] = @Active)
해석하신 부분은 맞지 않습니다.
nolock힌트가 없으므로 select시 공유잠금을 획득하는 149번(select) 세션과
update시 베타적 잠금을 획득하는 125번(update) 세션이 서로 교착상태에 빠진것으로 추측됩니다.
축구쟁이님 확인 감사드립니다.
select(149)와 update(125)간에 deadlock이 발생된 것으로 말씀하셨는데.
149번 세션의 select는 123ms이며, 125번 세션은 3초이상 대기로 나타나는데요.
저는 왜 이해가 안될까요?
아~~그리고 125번 세션의 sql구문입니다.
UPDATE DI_CO_MI_SERIAL
SET TransactionStatus = 'Y'
WHERE Facility = @Facility
AND WorkCenter = @WorkCenter
AND SerialNo = @SerialNo
AND Status = @Status
일단 교착상태 발생은 주어진 일부 정보를 통해 추측하는 부분이며 자세한 원인은 쿼리나 테이블 인덱스 정보를 알아야 확인이 가능합니다.
말씀하신 149번 세션의 123ms는 대기 유형의 대기시간이며 쿼리의 duration을 의미하는 것이 아닙니다.
축구쟁이님. 지속적인 관심에 감사드립니다.
추가적으로 알아야 할 쿼리가 또 뭐가 있을까요? ㅠㅠ, 이 시점에서 DBA에게 무엇을 더 요청해야 할까요?
149번 세션의 123ms은 대기시간입니다. 맞습니다..
149번 select상태는 PAGEIOLATCH_SH로 io를 일으키려? 즉, caching중이었고, 이때 125세션의 update는 149번과 다른 table인것으로 봐서는
대기하고 있을 이유가 전혀 없어보입니다. 아 어렵네요
위 정보만으로는 정확한 원인 확인은 어렵습니다.
말씀하신대로 149와 125는 다른 테이블을 접근하기에 그렇게 생각하실수도 있고 실제 그럴수도 있습니다.
다만 lock의 종류는 행, 페이지, 익스텐트, 테이블, DB로 나뉘어져 있어 동일한 테이블이 아니라도 대기가 발생할 수 있고
스샷의 waitting type에 RID의 정보가 5:1:~인것으로 보아 동일한 페이지 번호 내에서 접근하였다는 것을 유추해볼 수 있습니다.
축구쟁이님
축구쟁이님의 말씀이 맞는 것 같습니다. RID정보가 5:1:~인것으로 보아 동일한 페이지 번호 내에서 접근한것으로 보여지네요.
그렇다면, 149번세션의 io(caching)으로 125번 세션은 RID가 같은 Page를 Access하려고 대기하고 있었다라고 추즉성 결론을 낼 수가 있겠군요.
이제 좀 뭔가가 이해됩니다. 진심 진심으로 감사드립니다.
최대한 도움 드리고자 했지만 실제 문제와 다르거나 틀린 부분이 있을 수 있습니다.
혹시라도 그런 부분이 확인되시면 답변을 부탁 드립니다.
저에게도 많은 도움이 될 것 같습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.