'트랜잭션 잠금' 태그의 글 목록

Search

'트랜잭션 잠금'에 해당되는 글 1건

2011.08.23 [MS-SQL2011강좌] 23. 잠금에 대한 이해 by 강동운님

[MS-SQL2011강좌] 23. 잠금에 대한 이해 by 강동운님

프로그램&DB/MS-SQL 2011. 8. 23. 09:50 Posted by Josep.H.S

이 내용은 2011년 6월 현재 SQL2011(코드명 Denali) Beta를 기준으로 작성 되었으며 SQL2011(코드명 Denali) 공식버전(RTM) 발표까지 꾸준히 업데이트 예정입니다.
아울러, 현재 Beta버전이지만 강좌를 따라해 보시는데 아무 문제 없으실거에요. 도움 되시길 바랍니다.
==================================================================================================

안녕하세요. 이스트럭(강동운) 입니다.

오늘은 트랜잭션에 대해서 알아보도록 하겠습니다.

원문: http://www.sqler.com/127372

트랜잭션이라는 녀석과 잠금이라는 녀석은 대단히 밀접한 관계가 있습니다.

-- 그럼 강좌를 마치겠습니다.

라고 하면 물론 집에 가는 길에 등뒤에서 번쩍이는 칼을 보게 될지도 모르니..

계속 진행하겠습니다. -_-;;

잠금을 설명할 때 좋은 샘플이 있습니다.

예를 들어.. 공유 폴더에 워드 문서를 두시고..

워드 문서를 오픈 하고.. 다른 사용자나.. – 한번 더 워드 파일을 오픈 하면?

다른 프로세스나 사용자가 현재 해당 파일을 오픈 하고 있습니다.

읽기 전용으로 여시겠습니까? 라는 메세지와 함께

파일은 읽기 전용으로 열리거나 열수 없을 것입니다.

이런 상황이 바로 잠금의 개념과 비슷합니다.

두 사용자가 모두 쓰기를 해 버리면? 어느 것을 적용시켜야 할지 알 수 없겠지요?

DB의 경우라면 더더욱 큰 문제가 됩니다.

기본적으로 병행DBMS는 기본적으로 여러 명의 사용자프로세스가 붙어서 작업을

하게 되니 위와 같은 병행처리는 기본적으로 제공이 되어야 하지요.

그 개념이 바로 잠금이며 SQL서버는 이 잠금이 대단히 다양하고 세분화 되어 있습니다.

차근차근 봐 나가도록 하지요.

먼저 잠금 내역을 봐 보도록 할까요?

억지로 잠금을 보실 수 있는 쿼리 입니다.

--//1번 세션

use pubs

--가격* 2

begin tran

update titles set price = price * 2

--//2번 세션

--하나의창을더오픈한다.

--파일-> 연결을하고같은서버에접속한다.

use pubs

--데이터조회

select * from titles

--조회가불가하다.

--//3번 세션

--하나의창을더오픈한다.

--파일-> 연결을하고같은서버에접속한다.

use pubs

--잠금정보를조회한다.

sp_lock

맨처음 보시는 쿼리는 begin tran으로 트랜잭션 범위 내에서 시작하고 commit이나

rollback 등 트랜잭션 끝 이라는 키워드가 없습니다.

즉, 트랜잭션 중으로 하겠다는 것이지요. 그리고 이때 다른 사용자는 계속 pubs에서

대기하게 되는 겁니다. - 나중에 배우시겠지만 블러킹(Blocking)이라고 합니다.

바로 현재 spid가 53번인 부분에 Status가 WAIT인 것을 볼 수 있습니다.

자 그러면 조금더 sp_lock 한 결과를 조금 봐 보도록 하지요.

먼저 type으로 잠금의 타입인데요. 잠금이 걸리는 형식 입니다.

보시면 DB, PAG, KEY, TAB등이 있지요.

잠금이 걸리는 단위는 무엇일까요?

이 단위를 상세하게 알아 보도록 하지요.

리소스 유형	설명
RID	테이블에 있는 한 행을 잠그기 위한 행 식별자입니다.
KEY	인덱스에 있는 행 잠금입니다. 순차 가능한 트랜잭션에서 키 범위를 보호하기 위해 사용합니다.
PAG	데이터 또는 인덱스 페이지입니다.
EXT	인접한 여덟 개의 데이터 페이지 또는 인덱스 페이지 그룹입니다.
TAB	모든 데이터와 인덱스가 포함된 전체 테이블입니다.
DB	데이터베이스입니다.

이렇게 잠금이 걸릴 수 있습니다.

로우, 키, 페이지, 익스텐츠등으로 조금더 다양 하지요~

SQL서버는 이렇게 잠금의 단위를 쿼리에 따라서 자동으로 설정합니다.

물론 사용자가 개입해서 잠금을 걸 수도 있습니다. 하지만 이렇게 사용자가 잠금을

거는것은 거의 권장되지 않으며 오로지 BCP, Bulk insert등과 같은 대용량 벌크 작업시

TABLE Lock을 걸 경우를 걸때를 제외하고는 권장하지 않습니다.

벌크작업시는 걸고 하셔야만 제속도를 얻을 수 있습니다. - 많은 분들이 아마 모르셨을듯..

간단히 사용자가 직접 정의해 잠금을 거는 샘플을 보여 드리면..

use pubs

begin tran

select * from authors (ROWLOCK) where au_id = '172-32-1176'

이렇게 사용이 가능하며..

잠금의 힌트로 사용 가능한 옵션들은... 아래와 같습니다.

잠금 참고	설명
HOLDLOCK	필요한 테이블, 행 또는 데이터 페이지가 더 이상 필요 없게 되자마자 해제하지 않고 트랜잭션이 완료될 때까지 공유 잠금을 보유합니다. HOLDLOCK은 SERIALIZABLE과 같은 의미입니다.
NOLOCK	공유 잠금을 실행하거나 단독 잠금을 유지하지 않습니다. 이 옵션을 적용하면 커밋되지 않은 트랜잭션이나 읽는 중 롤백된 페이지 집합을 읽을 수 있습니다. 커밋되지 않은 읽기가 가능합니다. SELECT 명령문에만 적용됩니다.
PAGLOCK	주로 단일 테이블 잠금이 취해지는 곳에서 페이지 잠금을 사용합니다.
READCOMMITTED	READ COMMITTED 격리 수준에서 실행되는 트랜잭션과 같은 잠금 방법을 사용하여 스캔을 수행합니다. 기본적으로, SQL Server 2000은 이 격리 수준에서 실행됩니다.
READPAST	잠겨 있는 행을 건너뜁니다. 이 옵션을 사용하면 다른 트랜잭션이 이러한 행에 대해 잠금을 해제할 때까지 기다리지 않고 다른 트랜잭션에 의해 잠겨 있는 행을 건너뜁니다. 그렇지 않으면 일반적으로 결과 집합에 나타납니다. READPAST 잠금 참고는 READ COMMITTED 격리 수준에서 작동하는 트랜잭션에만 적용되며 행 수준 잠금 뒤만 읽습니다. SELECT 문에만 적용됩니다.
READUNCOMMITTED	NOLOCK과 같습니다.
REPEATABLEREAD	REPEATABLE READ 격리 수준에서 실행되는 트랜잭션과 같은 잠금 방법으로 스캔을 수행합니다.
ROWLOCK	성긴 페이지 잠금 및 테이블 수준의 잠금 대신 행 수준 잠금을 사용합니다.
SERIALIZABLE	SERIALIZABLE 격리 수준에서 실행되는 트랜잭션과 같은 잠금 방법으로 스캔을 수행합니다. HOLDLOCK과 같습니다.
TABLOCK	세부적인 행 또는 페이지 수준 잠금 대신 테이블 잠금을 사용합니다. SQL Server는 명령문이 끝날 때까지 이 잠금을 보유합니다. 그러나HOLDLOCK을 함께 지정했으면 트랜잭션이 끝날 때까지 잠금이 보유됩니다.
TABLOCKX	테이블에 대해 단독 잠금을 사용합니다. 이 잠금을 사용하면 다른 트랜잭션이 테이블을 읽거나 업데이트할 수 없고 명령문이나 트랜잭션이 끝날 때까지 보유됩니다.
UPDLOCK	테이블을 읽는 중 공유 잠금 대신 업데이트 잠금을 사용하며 명령문이나 트랜잭션이 끝날 때까지 보유됩니다. UPDLOCK을 사용하면 다른 트랜잭션이 읽는 것을 차단하지 않고 데이터를 읽을 수 있고 마지막으로 읽은 후 데이터가 변경되지 않으며 나중에 업데이트할 수 있습니다.
XLOCK	명령문에 의해 처리되는 모든 데이터에 대해 트랜잭션이 끝날 때까지 보유될 단독 잠금을 사용합니다. 이 잠금은 PAGLOCK 또는 TABLOCK으로 지정할 수 있으며 이 경우 단독 잠금이 해당 세부성 수준에 적용됩니다.

분명히 말씀 드리지만.. 사용자가 잠금 힌트를 주는 것 보다는..

SQL서버가 자동으로 정하게 하는 것이 대부분의 상황에서 좋으며 잠금의 범위가

커질 경우 역시 SQL서버가 자동으로 락을 에스켈레이션 하게 하는 것이 좋습니다.

자.. 형식은 어떤 것인지 감이 조금 잡히실 겁니다. 역시나 형식과 함께 이어지는

리소스 컬럼의 정보 입니다.

리소스 유형	설명
RID	테이블 내의 잠겨진 행의 행 식별자입니다. 행은 필드:페이지:rid 조합으로 식별됩니다. 여기서 rid는 해당 페이지의 행 식별자입니다.
KEY	SQL Server에서 내부적으로 사용되는 16진수입니다.
PAG	페이지 번호입니다. 페이지는 필드:페이지 조합으로 식별됩니다. 여기서 파일 번호는 sysfiles 테이블의 fileid이며, 페이지는 해당 파일 내의 논리적인 페이지 번호입니다.
EXT	익스텐트에서 첫 번째로 잠글 페이지 번호입니다. 페이지는 필드:페이지 조합으로 식별됩니다.
TAB	ObjId 열에 테이블의 개체 ID가 이미 있으므로 아무 정보도 제공되지 않습니다.
DB	dbid 열에 데이터베이스의 데이터베이스 ID가 이미 있으므로 아무 정보도 제공되지 않습니다.

이어서 잠금의 모드 입니다. 이 부분이 조금 난해할 수 있으니 잘 봐 보세요.

잠금 모드	설명
공유(S)	SELECT 문처럼 데이터를 변경하거나 업데이트하지 않는 작업(읽기 전용 작업)에 사용합니다.
업데이트(U)	업데이트할 수 있는 리소스에 사용합니다. 여러 개의 세션이 리소스를 읽고, 잠그고, 나중에 업데이트할 때 발생하는 일반적인 교착 상태를 방지합니다.
단독(X)	INSERT, UPDATE, DELETE와 같은 데이터 수정 작업에 사용합니다. 여러 개의 업데이트 작업이 같은 리소스에 대해 동시에 이루어지지 못하게 합니다.
내재	잠금 계층 구조를 만드는 데 사용합니다. 내재된 잠금의 종류에는 내재된 공유(IS) 잠금, 내재된 단독(IX) 잠금, 공유 및 내재된 단독(SIX) 잠금이 있습니다.
스키마	테이블의 스키마에 종속되는 작업이 실행될 때 사용합니다. 스키마 잠금의 종류에는 스키마 수정(Sch-M)과 스키마 안전성(Sch-S) 두 가지가 있습니다.
대량 업데이트(BU)	데이터를 테이블로 대량 복사하는 경우와 TABLOCK 참고가 지정된 경우 사용합니다.

그리고 이어서 내재 라고 되어있는 잠금이 있는데요.

잠금 모드	설명
내재된 공유(IS) 잠금	트랜잭션이 각 리소스에 대해 S 잠금을 설정하여 계층의 아래쪽에 있는 일부 리소스를 읽으려 하는 것입니다.
내재된 단독(IX) 잠금	트랜잭션이 각 리소스에 대해 X 잠금을 설정하여 계층의 아래쪽에 있는 일부 리소스를 수정하려 하는 것입니다. IX는 IS의 상위 집합입니다.
공유 및 내재된 단독(SIX) 잠금	트랜잭션이 각 리소스에 대해 IX 잠금을 설정하여 계층의 아래쪽에 있는 모든 리소스를 읽고 일부 리소스를 수정하려 하는 것입니다. 최상위 수준 리소스에서는 동시 IS 잠금이 허용됩니다.예를 들어, 테이블에 대한 SIX 잠금은 테이블에 대해 SIX 잠금을 설정하여 동시 IS 잠금을 허용하고, 수정 중인 페이지에 IX 잠금을 설정하고 수정된 행에 대해 X 잠금을 설정합니다. 리소스 당 한 번에 하나의 SIX 잠금을 설정할 수 있으므로 다른 트랜잭션이 테이블 수준에서 IS 잠금을 얻어 계층 아래쪽에 있는 리소스를 읽을 수 있어도 다른 트랜잭션이 리소스를 업데이트할 수는 없습니다.

이렇게 내재된 잠금에 대한 정보 역시 있습니다. 내재 잠금은..

계층으로 볼때.. 예를들어.. 여러개의 로우에 잠금이 걸린다면? - 페이지 잠금등으로

잠금을 관리 할 수 있습니다. 그런 계층적인 잠금에서 상위 잠금에 대해서 내재된 잠금을

걸고 처리하게 되는 것이지요. 보시는 바와 같이 하위에 대해서 X(단독잠금)이 걸릴 경우

상위 개체는 (IX)내재된 단독 잠금 모드가 걸리게 된다는 의미 입니다.

자~~ 여기까지가 잠금 / 개체에 대한 이야기이구요. 잠금이 걸린 여러 시스템 개체를 보신

것입니다.

중요하게 또한 보셔야 하는 부분은 잠금 / 프로세스 입니다.

예를들어.. 맨 위의 쿼리를 생각해 보세요.

그 쿼리는 update titles set price = price * 2 라는 구문으로 다른 사용자의 select * from titles

쿼리를 막고 있었습니다. 그렇다면.. 바로 문제가 되는 update쿼리의 사용자가

잠금의 차단 주체(차단하는 녀석)이 될 것이고 select하는 사용자는? 차단을 받고

있을 것입니다. 이것을 볼 수 있게 해주는 것은 sp_who라는 명령이 있는데요.

sp_who2

하시게 되면 blk라는 컬럼에 차단 주체를 볼 수 있게 됩니다.

이렇게 SPID 54번 프로세스가 막고 있다고 하네요.

SPID 54번 프로세스는 53번 바로 아래 있습니다.

자 그럼 이제 잠금의 차단 주체가 어떤 녀석인지 역시 감이 잡히실 겁니다. ^_^

이렇게 차단이 일어날 경우를 블러킹(Blocking)이 일어난다고 보통 말을 하게 되며..

블러킹은 데이터베이스 리소스를 여러명이 사용하기 때문에 당연히 일어나는

것입니다. - 특히 돈이나 재무와 관련되는 프로세스일 경우는 대부분 이 트랜잭션을

이용한 처리를 하기 때문에 종종 블러킹이 일어 나지요. 하지만 대단히 응답속도가

늦거나 - CPU는 놀고 있는데도 불구하고.. 그렇다면 여러 블러킹 처리를 봐 보셔야

하지요.

감사합니다.

[출처] SQLER.COM http://www.sqler.com/394745

SQL2011(코드명 Denali) 시리즈 강좌 리스트
[SQL2011강좌] 1. 코드명 Denali 설치 방법
[SQL2011강좌] 2. SSMS 접속 및 간단한 쿼리 실행
[SQL2011강좌] 3. SQL Server 의 MDF, LDF 그리고 데이터베이스 생성하기
[SQL2011강좌] 4. DDL 1탄: 테이블 생성
[SQL2011강좌] 5. DDL 2탄: 쿼리를 이용한 테이블 컬럼 추가 및 삭제
[SQL2011강좌] 6. Primary key와 Unique 제약조건
[SQL2011강좌] 7. DML 1탄 INSERT
[SQL2011강좌] 8. DML 1탄 INSERT(identity 속성)
[SQL2011강좌] 9. Sequence
[SQL2011강좌] 10. pubs, northwind Database 예제 설치
[SQL2011강좌] 11. SELECT(단일 테이블)
[SQL2011강좌] 12. SELECT(JOIN)
[SQL2011강좌] 13. UPDATE, DELETE 절
[SQL2011강좌] 14. DISTINCT, UNION, UNION ALL 키워드
[SQL2011강좌] 15. ORDER BY, GROUP BY, HAVING
[SQL2011강좌] 16. SubQuery(서브쿼리)
[SQL2011강좌] 17. 뷰에 대한 이해 및 생성, 수정, 삭제
[SQL2011강좌] 18. 저장 프로시저에 대한 이해, 생성, 수정, 삭제
[SQL2011강좌] 19. 인덱스에 대한 이해
[SQL2011강좌] 20. 클러스터드 인덱스와 넌 클러스터드 인덱스
[SQL2011강좌] 21. 트랜잭션에 대한이해
[SQL2011강좌] 22. 트랜잭션의 격리수준 4가지
[SQL2011강좌] 23. 잠금에 대한 이해
[SQL2011강좌] 24. 백업과 복구에 대한 이해(풀백업, 차등백업, 트랜잭션 로그 백업)
[SQL2011강좌] 25. 백업과 복구 전략
[SQL2011강좌] 26. 커서란?

'프로그램&DB > MS-SQL' 카테고리의 다른 글

[MS-SQL2011강좌] 25. 백업과 복구 전략 by 강동운님 (0)	2011.08.23
[MS-SQL2011강좌] 24. 백업과 복구에 대한 이해(풀백업, 차등백업, 트랜잭션 로그 백업) by 강동운님 (0)	2011.08.23
[MS-SQL2011강좌] 22. 트랜잭션의 격리수준 4가지 by 강동운님 (0)	2011.08.23
[MS-SQL2011강좌] 21. 트랜잭션에 대한이해 by 강동운님 (0)	2011.08.23
[MS-SQL2011강좌] 20. 클러스터드 인덱스와 넌 클러스터드 인덱스 by 강동운님 (0)	2011.08.23

«이전 1 다음»

NewsTwit

카테고리

Search

'트랜잭션 잠금'에 해당되는 글 1건

[MS-SQL2011강좌] 23. 잠금에 대한 이해 by 강동운님

'프로그램&DB > MS-SQL' 카테고리의 다른 글

tag cloud

공지사항

최근댓글

최근기사

글보관함

링크

티스토리툴바

« 2025/4 »
일	월	화	수	목	금	토
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30