'SQL Profiler' 태그의 글 목록

'SQL Profiler'에 해당되는 글 2건

2017.06.22 SQL Server DeadLock 2편
2010.07.25 TransactionScope 그리고 Stored Procedure 4

SQL Server DeadLock 2편

SQL Server 2017. 6. 22. 22:41

SQL Server DeadLock 2편에서는 리소스에 대한 개념을 정리하고 1편에서 이야기했던 Lock 종류에 대한 내용을 예제를 통해서 살펴보겠습니다. 리소스라는 용어가 지칭하는 것들이 많아서 어떤 것인지 명확하지 않게 느껴집니다. 일단 리소스의 종류는 다음과 같습니다.

1. KEY (Key)

2. PAG (Page)

3. TAB (Table)

http://www.dbguide.net/db.db?cmd=view&boardUid=148215&boardConfigUid=9&categoryUid=216&boardIdx=138&boardStep=1

=> 1편에서 공유해드렸던 사이트를 참고하면 도움이 되며 KEY는 인덱스(Row)에 해당한다고 생각하시면 됩니다. PAG는 데이터 페이지 또는 인덱스 페이지처럼 일정 범위에 해당하며 TAB 등등이 있다 정도로 이해하시면 될 것 같습니다. 일반적으로 리소스에 LOCK이 걸리면 하나의 리소스에만 걸리는 것이 아니라 KEY -> PAG -> TAB처럼 여러 리소스에 각각 다른 종류의 잠금이 걸린다고 보시면 됩니다. TAB만 잠금이 걸릴수는 있지만 KEY만 잠금이 걸리는 것은 없다고 생각하시면 됩니다.

그럼 예제를 통해서 기본적인 SQL구문에 대한 잠금에 대해서 확인해 보겠습니다.

먼저 테스트를 위한 테이블 및 데이터, 인덱스를 아래와 같이 설정합니다.

--//테이블 삭제 스크립트

IF OBJECT_ID('dbo.Test','U') IS NOT NULL

DROP TABLE dbo.Test

--//테이블 생성 스크립트

CREATE TABLE dbo.Test

(

ClusteredIndex INT NOT NULL

, NonClusteredIndex INT NULL

, Normal INT NULL

, CreateDate DATETIME2 NOT NULL

)

--//인덱스 생성 스크립트

CREATE UNIQUE CLUSTERED INDEX ClusteredIndex ON dbo.Test(ClusteredIndex)

CREATE INDEX NonClusteredIndex ON dbo.Test(NonClusteredIndex)

--//100,000개 데이터 입력

--//데이터 입력

SET NOCOUNT ON

DECLARE @i INT = 1

WHILE(@i < 100000)

BEGIN

INSERT INTO dbo.Test VALUES(@i,@i,@i,GETDATE())

SET @i = @i + 1

END

SET NOCOUNT OFF

=> 특별한 부분은 없고 클러스터드 인덱스와 넌클러스터드 인덱스, 그냥 일반 컬럼(int, datetime2)을 가지고 있는 테이블입니다.

1. 단순 Insert 구문

BEGIN TRAN

EXEC sp_lock 52

INSERT INTO dbo.Test VALUES(100001,100001,100001,GETDATE())

EXEC sp_lock 52

ROLLBACK TRAN

=> SSMS에서 '새쿼리'로 창을 열면 보이는 숫자는 세션번호입니다. sp_lock명령어에 해당 숫자를 넣으면 해당 세션에 대한 LOCK에 대한 정보를 아래와 같이 보여줍니다.

spid	dbid	Objid	IndId	Type	Resource	Mode	Status
52	5	0	0	DB		S	GRANT
52	1	1467152272	0	TAB		IS	GRANT
52	32767	-571204656	0	TAB		Sch-S	GRANT

=> 첫번째 sp_lock에서는 '새쿼리'창을 통하면 아래와 같이 TAB(Table)과 DB에 S-LOCK, Sch-S(스키마 공유)가 기본으로 보이므로 해당 LOCK에 대해서는 무시하시면 됩니다.

spid	dbid	Objid	IndId	Type	Resource	Mode	Status
52	5	370100359	2	KEY	(d91e288f817d)	X	GRANT
52	5	370100359	2	PAG	1:430	IX	GRANT
52	5	370100359	1	PAG	1:439	IX	GRANT
52	5	370100359	1	KEY	(74a07545ba5b)	X	GRANT
52	5	370100359	0	TAB		IX	GRANT

=> 두번째 sp_lock의 결과를 살펴보면 KEY, PAG, TAB에 X, IX, IX모드로 잠금이 걸린 것을 확인하실 수 있습니다. KEY가 2개가 보이는데 눈치가 빠른 분들은 아시겠지만 해당 테이블에 클러스터드 인덱스와 넌클러스터드 인덱스 2개가 존재하기 때문입니다. KEY에 해당하는 각각 PAG잠금이 보이며 TAB은 당연히 Test테이블 기준이므로 1개만 확인이 되고 있습니다.

=> KEY에 해당하는 리소스의 값은 해쉬값이며 PAG의 1:430은 {FileNumber}:{PageNumber}입니다.

2. 단순 Select 구문

BEGIN TRAN

SELECT * FROM Test WHERE ClusteredIndex = 1

EXEC sp_lock 52

ROLLBACK TRAN

=> 결과는 1번의 첫번째 sp_lock과 동일한 결과입니다. 해당 부분이 1편에서 설명해드렸던 트랜잭션이 있다고 꼭 LOCK이 존재하는 것은 아닌 상태입니다. 물론 Select 구문이 실행되는 그 순간에는 S-LOCK이 걸리지만 INSERT 구문과는 다르게 LOCK이 트랜잭션 내에서 구문 실행이후 해제가 된다는 것입니다.

3. 단순 Update 구문

BEGIN TRAN

UPDATE Test SET Normal = 1 WHERE Normal = 1

EXEC sp_lock 52

ROLLBACK TRAN

spid	dbid	Objid	IndId	Type	Resource	Mode	Status
52	5	674101442	1	PAG	1:162	IX	GRANT
52	5	674101442	1	KEY	(8194443284a0)	X	GRANT
52	5	674101442	0	TAB		IX	GRANT

=> Update 구문 역시 KEY에 X-LOCK이 걸립니다. 당연한 사실을 언급하면 Update 구문이라고 U-LOCK이 걸리는 것은 아닙니다. ^^;; 또 한가지 특이한 점은 INSERT가 될때에는 인덱스가 2개여서 KEY가 2개 보였지만 Update 구문은 1개만 보인다는 점인데 이것은 KEY 잠금에 대한 인덱스 종류(클러스터드, 넌클러스터드)를 구분할 필요가 없음을 의미합니다. 심지어 Normal이라는 컬럼이 조건절에 있지만 인덱스에 잠금이 걸리는 것도 알 수 있습니다.

4. 단순 DELETE 구문

=> 이건 별도로 결과를 첨부하지 않고 INSERT와 동일하다고 생각하시면 됩니다. INSERT에는 KEY가 인덱스 개수만큼 생성되고 DELETE에는 인덱스 개수만큼 삭제됩니다.

그럼 SQL 구문과 함께 Lock에 대해서 살펴보았고 이제 드디어 이 글의 제목에 해당하는 DeadLock과 관련된 내용을 살펴보도록 하겠습니다.

-- 세션1

BEGIN TRAN

DECLARE @Key bigint = 1

-- 실행1

INSERT INTO Test VALUES (200001, @Key, 1, GETDATE())

-- 실행3

SELECT * FROM Test

-- 데이터 초기화

ROLLBACK TRAN

-- 세션2

BEGIN TRAN

DECLARE @Key bigint = 1

-- 실행2

INSERT INTO Test VALUES (200002, @Key, 1, GETDATE())

-- 실행4

SELECT * FROM Test

-- 데이터 초기화

ROLLBACK TRAN

=> Test 테이블의 구조를 다시 복기를 해보면 ClusteredIndex, NonClusteredIndex, Normal, CreateDate 컬럼을 가지고 있는 테이블입니다. ClusteredIndex는 자동증가 컬럼은 아니지만 유일한 값만 가진 컬럼이라고 생각을 하고 NonClusteredIndex는 중복은 되지만 조회 조건에 꼭 필요한 정보라고 정의하도록 하겠습니다. (예를 들어 충전 테이블의 키는 자동증가이고 충전 대상에 해당하는 사용자의 정보가 NonClusteredIndex라고 보시면 됩니다)

=> 사전 준비 작업으로 SQL Profiler를 실행하여 Locks의 Deadlock graph 이벤트를 체크합니다. SSMS에서는 세션2개를 열고 실행순서에 맞게 실행을 합니다. SQL 구문 사이에 WaitFor Delay 구문을 넣는 것보다는 세션을 2개 열어서 구문을 하나씩 실행하면서 블럭이 되는 구간이나 LOCK에 대한 정보등을 확인하면서 찾는 것이 좋다고 생각됩니다. 코드가 더 익숙한 개발자는 테스트 코드로 재현을 하려고 하실 수도 있는데 개인적으로 코드로 인한 버그로 인하여 미궁으로 빠질 수도 있기 때문에 DB에서 재현을 해보는 것이 좋다고 생각됩니다.

SQL Profiler화면에서 Deadlock graph를 선택하면 아래와 같은 그림이 보입니다.

=> 서버 프로세스 53번이 1번 세션이고 54번이 2번 세션입니다. 실행4번을 하고 나면 아래와 같은 메세지를 SSMS에서 확인을 하셨을 겁니다.

트랜잭션(프로세스 ID 54)이 잠금 리소스에서 다른 프로세스와의 교착 상태가 발생하여 실행이 중지되었습니다. 트랜잭션을 다시 실행하십시오.

=> 그림으로 표현되는 데이터는 XML포맷이며 어느 구문이 DeadLock 구문인지 정도를 제외하면 그림에서 표시해주는 정보만으로도 대부분 원인 파악이 가능합니다.

=> 54번 서버 프로세스가 교착상태(DeadLock)로 실행이 중지되었고 그림에도 X로 표시되어 중지되었음을 가시적으로 보여주고 있습니다.

=> 어떤 리소스의 어떤 Lock에 의해서 DeadLock이 발생하였는지 확인을 해보면 페이지 리소스에 각 프로세스(트랜잭션)에서 X-LOCK과 S-LOCK이 순환 참조하게 되면서 DeadLock이 발생합니다.

=> 해당 예제를 DeadLock 첫번째 예제로 선택한 이유는 트랜잭션 내의 Select구문에 의해서도 DeadLock이 걸릴 수 있다는 것을 보여주기 위함입니다.

=> sp_lock을 통해서 확인 및 추측을 해보면 Insert구문은 KEY에는 X-LOCK이 걸리고 PAG에는 IX-LOCK, TAB에도 IX-LOCK이 걸리는 단순한 구조입니다. Select구문은 sp_lock으로 확인이 어렵습니다. Select 구문이 실행 중인 상태에서 Lock이 발생하지만 구문이 완료되고 나면 트랜잭션 내에서 Lock을 유지하지 않기 때문입니다.

DeadLock이 페이지 잠금이 발생하는데 이 부분이 이해가 잘되지 않습니다. 그래서 Select 구문에 WITH(HOLDLOCK)구문을 추가하면 TAB에 S-LOCK 잠금을 확인할 수 있고 DeadLock 그래프에서는 키잠금으로 리소스가 변경되는 것을 확인할 수 있습니다. 해당 부분에 대해서 정리를 하면 아래 표와 같습니다.

	INSERT	1. SELECT	2. SELECT WITH(HOLDLOCK)	3. SELECT WITH(HOLDLOCK) WHERE ClusteredIndex = 다른 세션에서 추가한 값
KEY	X	알수없음		S
PAG	IX	알수없음		IS
TAB	IX	알수없음	S	IS
잠금 리소스		페이지(S)	키(RangeS-S)	키

잠금의 호환성 (Lock Compatibility)

Requested mode	Existing granted mode
	IS	S	U	IX	SIX	X
Intent shared (IS)	Yes	Yes	Yes	Yes	Yes	No
Shared (S)	Yes	Yes	Yes	No	No	No
Update (U)	Yes	Yes	No	No	No	No
Intent exclusive (IX)	Yes	No	No	Yes	No	No
Shared with intent exclusive (SIX)	Yes	No	No	No	No	No
Exclusive (X)	No	No	No	No	No	No

(테이블 출처 : http://msdn.microsoft.com/en-us/library/ms172925.aspx)

=> 3번만 명확하게 이해가 됩니다. KEY 리소스에 INSERT는 X-LOCK, SELECT는 S-LOCK이기 때문에 세션1에서도 블럭, 세션2에서도 블럭 2개의 트랜잭션 모두 커밋이 될수 없기 때문에 교착상태가 맞습니다. 1번, 2번은 도저히 이해가 안됩니다. 그래서 Test테이블의 데이터를 모두 삭제하고 다시 1번의 Select구문으로 확인을 하니 3번과 동일한 결과로 변경됩니다. 어떤 리소스의 어떤 잠금이 걸리는지 아는 것도 중요하지만 얼만큼의 데이터를 가지고 있는지에(바꿔 말하면 SQL Server 옵티마이저가 어떻게 최적화 하는지) 따라서 잠금이 발생하는 리소스도 변경될 수 있다는 점입니다.

DeadLock에 대한 첫 예제를 마무리를 하려고 합니다. 이론적으로 이해를 100%하면 좋지만 아무래도 이런 부분들은 여러 가지 상황에 대한 경험이 필요합니다. 즉 관심을 가지고 여러가지 케이스를 테스트 해보아야 하고 실제 본인의 업무에도 적용을 해야 진짜 경험이 될 수 있다고 생각합니다. (당연한 말이네요 ㅎㅎㅎ)

저작자표시 비영리 변경금지

Posted by resisa

TransactionScope 그리고 Stored Procedure

개발/.NET 2010. 7. 25. 16:41

드디어 Strored Procedure(이하 SP)를 사용하는 프로젝트에 참여하고 있습니다. 아쉽게(?) 실질적으로 SP를 호출하고 결과를 받아서 UI에 보여주는 작업은 아닙니다.

SP로 실질적인 프로젝트를 하지 않아서 전부터 궁금하던 사실이 있었습니다. 'SP를 사용하면 굳이 서버쪽에 Layer가 필요한 것일까?' 란 의문이였습니다. 프로젝트의 규모가 작고 하나의 SP에서 대부분의 로직을 처리할 수 있다면 굳이 필요하지 않아보입니다만 또 하나의 의문은 SP로 작성된 구문 사이의 트랜잭션의 범위에 대한 의문이였습니다. 일반적으로 Biz Layer에서 트랜잭션을 발생시켜 주고 Dac Layer에서 여러 SP를 호출해줄 경우 Biz에서 발생하는 트랜잭션과 SP내의 트랜재션과의 관계와 그 결과에 대한 의문이였습니다.

조금 더 이해를 돕기 위해서 Biz Layer에서는 TransactionScope 클래스를 사용하였다고 가정하고 SP내에서는 ADO.NET 트랜잭션 메소드(BeginTransaction/Commit/Rollback)를 사용하였다는 가정입니다. TransactionScope는 System.Transaction 네임스페이스 하위의 클래스이며 System.Transaction 네임스페이스에서 관리하는 트랜잭션은 항상 LTM(Lightweight Transaction Manager)을 통해서 관리된다고 합니다. 따라서 해당 테스트는 LTM과 ADO.NET 트랜잭션 메소드 사이의 관계에 대해서 설명하자고 하는 글입니다.

LTM에 대해서는 아래의 사이트를 참고하세요.

http://www.simpleisbest.net/articles/996.aspx

그럼 이제 테스트를 해보도록 하겠습니다. (참고 : 테스트 DB는 SQL Server 2008(R2는 아님)입니다.)

DB에 데이터 베이스는 ID(identity), Name를 가지고 있는 테이블입니다.

이제 SP를 DB에 만들어 보겠습니다. SP의 내용은 아래와 같습니다.

CREATE PROCEDURE EFSP

@name nvarchar(Max),

@txOutput int OUTPUT

BEGIN TRAN

insert [dbo].[Entity1Set]([Name])

values (@name)

select [Id]

from [dbo].[Entity1Set]

where @@ROWCOUNT > 0 and [Id] = scope_identity()

-- 위의 insert 구문 2개 더 추가

SET @txOutput = @@TRANCOUNT

COMMIT TRAN

Entity Framework에서 해당 SP를 Function으로 Import를 할 수 있습니다. System.Data.SqlClient를 사용하셔도 됩니다. 여기서 @@TRANCOUNT로 발생하는 트랜잭션의 개수를 SP의 output 파라미터에 대입해주는 것을 확인하실 수 있습니다. ADO.NET 트랜잭션 메소드는 SQL Profiler의 SQLTransaction으로 표현이 되는데 SQLTransaction이 해당 테스트와 상관없이 너무나 자주 발생하여 트랜잭션이 발생하는 개수를 파악하기 위한 방법으로 사용되었습니다.

그러면 이제 단순히 SP를 실행해보도록 하겠습니다.

=> 위에 말했던 대로 SP내부에서 사용한 ADO.NET 트랜잭션 메소드는 SQLTransaction으로 표시가 되며 EventSubClass 열로 트랜잭션이 어느 부분부터 시작되며 어느 부분에서 종료가 되는지 아실 수 있습니다. ObjectName를 통해서는 사용자가 발생한 트랜잭션인지 SQL Server내부에서 발생하는 트랜잭션인지를 구분할 수 있습니다.

그러면 이제 TransactionScope 클래스와 함께 SP를 실행해보도록 하겠습니다.

=> TransactionScope를 클래스를 사용하였기 때문에 TM(LTM)이 관여하는 것을 보실 수 있으며 DBM_INIT이라는 내부 Object에 의해서 무엇인지는 정확하게 알 수 없지만 SQLTransaction이 발생한 것도 볼 수 있습니다. 여기서 중요한 것은 바로 sp_reset_connection 함수입니다. 이 함수는 커넥션 풀링이 되었다는 의미입니다. 이상합니다. 하나의 SP만을 실행준 것인데 왜 커넥션 풀링이 필요한 것일까요?

TransactionScope 클래스를 사용한 구문을 보도록 하겠습니다.

using (TransactionScope scope = new TransactionScope())

{

// 하나의 SP실행

scope.Complete();

}

=> 앞에서 말했듯이 System.Transaction 네임스페이스의 트랜잭션은 TM이 관리하였습니다. 단순히 SP만을 실행해줄 경우에는 SQLTransaction에서 commit이 된 이후에 커넥션이 닫혔습니다. 그러나 TransactionScope안에서 SP를 실행할 경우에 SQLTransaction이 commit되기 전에 커넥션이 먼저 닫히게 됩니다. 이것으로 보아 TransactionScope를 사용하게 되면서 ADO.NET 트랜잭션 메소드가 TM이 관리하는 트랜잭션으로 관리된다는 것을 알 수 있습니다. TM이 Commit이 되기 전에 커넥션이 닫혔기 때문에 TM에서는 커넥션 풀링을 통해서 다시 커넥션을 맺고 내부의 SQLTransaction이 발생한 이후에 SQLTransaction이 commit해주며 그 이후에 TM도 Commit이 되고 마지막으로 커넥션을 닫아주는 것입니다.

몇 가지 테스트를 통해서 TransactonScope의 옵션(TransactionScopeOption)에 따라서 ADO.NET 트랜잭션 메소드가 참여하게 된다는 사실을 확인할 수 있었습니다.

이렇게 하여 LTM과 SQLTransaction의 관계에 대해서 대부분(?!) 파악이 되었습니다. 한 가지 의문점은 SP안에서는 트랜잭션의 Scope 범위(TransactionScopeOption)를 지정하는 부분은 없어보이지만 IsolationLevel에 대해서는 지정을 할 수 있습니다. 또한 TransactionScope에서도 IsolationLevel에 대해서 지정할 수 있습니다. 그래서 IsolationLevel에 따른 LTM과 SQLTransaction의 관계가 어떻게 될지도 살짝 궁금해집니다. 하지만 실질적으로 이러한 것을 프로젝트에서 정확하게 알고 사용하는 개발자가 얼마나 될지도 의문이며 IsolationLevel은 트랜잭션 사이의 잠금(lock)에 대한 것이기 때문에 성능보다는 안전성을 중요시하는 우리 나라의 대부분의 프로젝트에서는 ReadCommitted로 사용하는 것이 개발자의 정신건강에 이로워 보입니다. ^^