잠금(Lock)의 이해

ms-sql 2015. 1. 5. 16:47

트랜젝션과 잠금처리 - 4. 잠금(Lock)의 이해

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

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

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

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

　

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

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

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

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

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

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

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

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

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

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

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

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

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

　

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

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

use pubs
go

--가격 * 2
begin tran
update titles set price = price * 2

--하나의 창을 더 오픈한다.
--파일 -> 연결을 하고 같은 서버에 접속한다.
use pubs
go

--데이터 조회
select * from titles

--조회가 불가하다.

--하나의 창을 더 오픈한다.
--파일 -> 연결을 하고 같은 서버에 접속한다.
use pubs
go

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

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

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

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

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

　

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

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

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

　

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

테이블? 페이지? SQL7부터는 로우단위 잠금이 가능했구요...

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

리소스 유형 설명

RID 테이블에 있는 한 행을 잠그기 위한 행 식별자입니다.

KEY 인덱스에 있는 행 잠금입니다. 순차 가능한 트랜잭션에서 키 범위를 보호하기 위해 사용합니다.

PAG 데이터 또는 인덱스 페이지입니다.

EXT 인접한 여덟 개의 데이터 페이지 또는 인덱스 페이지 그룹입니다.

TAB 모든 데이터와 인덱스가 포함된 전체 테이블입니다.

DB 데이터베이스입니다.

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

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

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

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

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

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

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

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

use pubs
go

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)내재된 단독 잠금 모드가 걸리게 된다는 의미 입니다.

　

역시나 이런 잠금은 EM으로 보실수 있습니다.

이렇게 EM의 관리 -> 현재동작 -> 잠금 / 개체 부분에서 보실 수 있지요.

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

것입니다.

　

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

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

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

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

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

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

sp_who

--또는 개인적으로 선호하는....

sp_who2

　

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

저의 경우는..

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

SPID 51번 프로세스는 바로???

요녀석일 겁니다... 네 저녀석이라는 것을 자알 알았습니다..

그렇다면??? 저 SPID 51번 녀석과 53번 녀석이 어떤 쿼리를 수행하는 중인지 알

수 없을까요? 있습니다. 바로.. DBCC명령중의 하나 입니다.

--DBCC INPUTBUFFER SPID 이렇게 수행합니다.
DBCC INPUTBUFFER (51)

--결과
EventType Parameters EventInfo
---------- ---------- -------------------------------------
Language Event 0 begin tran
update titles set price = price * 2

　

저는 이렇게 나오는군요.

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

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

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

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

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

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

하지요. - 이건 뒷부분에서 상세히 적어 보겠습니다.

　

자.. 쿼리로 하는 방법도 보셨구요. EM에서도 물론 보실 수 있습니다.

관리 -> 현재동작 -> 잠금 / 프로세스ID 부분에서 등록정보를 보시면? 볼 수 있지요.

　

이정도면 우선적인... 개략적인 잠금을 보신거구요..