갈아타 갈아타

출처 : http://www.linxus.co.kr/post/43376

exec sp_lock

잠금 관련 정보를 보고합니다.

exec sp_who {sp_id}

현재 Microsoft? SQL Server™ 사용자 및 프로세스에 관한 정보를 제공합니다. 유휴 상태가 아닌 프로세스만 반환하려면 반환되는 정보를 필터링하면 됩니다.

DBCC INPUTBUFFER( sp_id )

클라이언트에서 Microsoft? SQL Server™에 최종적으로 보낸 명령문을 표시합니다.

DBCC INPUTBUFFER는 다음과 같은 열이 있는 행 집합을 반환합니다.

DBCC INPUTBUFFER (<spid>)

SELECT open_tran FROM SYSPROCESSES WHERE SPID=<blocking SPID number>
go

   SPIDs at the head of blocking chains
   spid
   ------
   9
   10

   spid   status                         blocked
    9     sleeping                       0
   10     sleeping                       0
   11     sleeping                       13
   12     sleeping                       10
   13     sleeping                       9
   14     sleeping                       12

   DBCC INPUTBUFFER FOR SPID 9
   EventType      Parameters EventInfo
   -------------- ---------- --------------------------------------------
   Language Event 0          update titles set title = title

BEGIN TRAN
SELECT * FROM SYSOBJECTS S1, SYSOBJECTS S2

-- Issue this after canceling query
SELECT @@TRANCOUNT
ROLLBACK TRAN

 SPID1------잠금(Lock)으로 ------->SPID2
  /\          블로킹됨        (클라이언트로 결과
  |                            기록 대기)
  |                                 |
  |                                 |                           서버측
  | ================================|==================================
  |     <-- 단일 스레드 -->         |                     클라이언트측
  |                                 \/
 dbproc1   <-------------------   dbproc2
 (다음 행                      (dbproc1에서 효과적으로 블로킹되어
  반입 대기)                    실행하기 위해 단일 스레드 실행 대기)

SPID1--------잠금(Lock)으로 -------->SPID2
  /\           블로킹됨        (네트 쓰기 시 대기)               서버측
  |                                 |
  |                                 |
  | INSERT                          |SELECT
  | ================================|==================================
  | <-- 각 dbproc 당 하나의  -->    |                      클라이언트측
  |           스레드                \/
 dbproc1   <-----데이터 행-----    dbproc2
 (삽입 시                      (dbproc1에서 블로킹되어 버퍼에서
  대기)                         행 읽는 작업 대기)

KILL 9

출처 : http://www.devpia.co.kr/Maeul/Contents/Detail.aspx?BoardID=42&MAEULNO=17&no=40&page=3




원문 : [SQL Server 2005에서 좋아진 점 No.1] - New isolation level








1. 시작하기 전에

파일 시스템과 데이터베이스 시스템과의 차이점은 어떤 것들이 있을까? 여러 가지가 있겠지만,

 이번 강좌에서 다뤄볼 것은 트랜잭션(Transaction)과 동시성(Concurrency)에 관한 것들이다.

이 두가지 개념으로 인하여 많은 상황이 발생한다. 예를 들자면, 이런 것들이다.

 - 한 사용자가 데이터를 읽어나가고 있는데, 다른 사용자가 그 데이터를 변경하려 한다면?

 - 한 사용자가 데이터 변경작업을 해나가고 있는데, 다른 사용자가 그 데이터를 읽으려

   한다면 변경중인 데이터를 읽어야 할까? 아님 변경이 읽어나기전의 데이터를 읽어야 할까?

이런 상황들에 대해서 어느 정도 수준까지 동시성을 제공해줄 수 있는 가를 정의한 것이

격리수준(Isolation level)이라는 것이다. 어느 정도의 격리수준을 제공하는 가에 따라서

동시성이 높아질 수도 낮아질 수도 있다.

개인적으로 SQL Server는 중소형 규모에 적절한 시스템이라 생각한다. 왜 그렇게 생각하는가 하면,

SQL Server가 제공하는 격리수준이 상대적으로 타사의 제품에 비해서 떨어지는 편이기 때문이다.

SQL Server의 기본적인 동시 사용 제어(Concurrency Control)는 비관적 동시 사용 제어 개념을

사용한다. 이 말은 쓰기 작업이 읽기 작업에 영향을 줄 수 있고, 그 반대로 읽기 작업이 쓰기 작업에

영향을 줄 수 있기 때문에 하나의 자원에 대해서 한 가지 형태의 작업이 이뤄지고 있으면 다른 형태의

작업이 접근하지 못하도도록 자원을 잠궈버린다.(Locking)

이 때에 다른 작업은 선행 작업이 끝날때 까지 그 자원에 접근을 하지 못하며 대기 상태가 된다.(Blocking)

근데 이 정도에서 상황이 종료되는 것이 아니다. 기본적으로는 row 단위의 잠금을 하지만,

자원에 대해서 잠금 현상이 점점 많아지게 되면 SQL Server는 잠금에 사용되는 자원를 보다 손쉽게

관리하기 위해서 잠금의 단계를 높여버리게 된다.

예를 들어서 row단위의 잠금을 table단위로 올리는 것이다. 이것을 lock escalation 이라는 말로 표현한다.

잠금의 사용되는 자원을 보다 손쉽게 관리한다는 측면 - SQL Server의 입장에서는 맞는 말일수도 있겠지만,

사용자 측면에서는 잠금 현상의 확대로 인해서 데드 락이 발생하여 무한 대기 상태 또는 세션의 강제 종료 등과

같은 별로 달갑지 않는 현상이 발생해 버린다.

이런 현상을 해결하기 위해서 SQL Server 2000 버전까지는 각 트랜잭션 단위를 빠르고 신속하게 처리할 수 있도록

Query를 튜닝하거나 with nolock 같은 힌트를 이용하는 등을 이용하여 격리수준을 낮춰버리는 등의 작업으로 해결하곤 했다.

각 트랜잭션 단위가 빠르게 수행되도록 하는 Query 튜닝은 어떤 DBMS 제품을 이용하더라도 필수적인 일이겠으나,

운영중인 시스템들은 두 서너개의 Query만을 가지고 있는 것이 아니라, 몇 백, 몇 천개가 넘어가는 Query를 가지는

경우가 대부분이기 때문에 그런 Query들을 튜닝한다는 말은 그리 쉬운 일이 아니다.

그리고 격리 수준을 낮춘다는 말은 데이터의 일관성(consistency)이 깨져버린 믿지 못할 데이터를 이용하고 있다는

말 밖에는 되지 않는다. 자신들이 이용하는 데이터가 신뢰성이 결여된 것을 고객들이 알게 된다면 어떤 일이 벌어질까?

SQL Server 2005에서는 이런 동시성을 높여줄 새로운 Isolation level이 추가 되었다.

사용방법은 정말 간단하다. 옵션의 플래그 값만을 변경해주면 가능하다.

하지만, 그 간단한 작업의 뒷편에는 개념에 대한 충분한 이해와 새로운 이 기능을 적용하기 위해서 고려해야할 사항들이 있다.

그냥 막연하게 새로운 기능이니 좋겠지, 한번 해볼까? 하는 생각만으로 옵션을 변경하지는 말자.

2. 개념

앞서 장황하게 설명했던 새로이 추가된 격리 수준은 SNAPSHOT ISOLATION이다.

이 격리수준은 row versioning이라는 기술을 기반으로 구현된 것이다. 각 row 단위에 14byte의 unique한 식별자를

부여한다. 이 식별자를 기반으로 버전 관리를 하여서 읽기/쓰기 작업에 대한 요청이 들어올 때에 작업에 적절한

버전을 반환하여 해당 버전에 대한 데이타를 작업에 사용하게 하도록 한다.

SNAPSHOT이라는 말에서도 느낌이 오듯이 해당 버전에 대한 데이타의 복사본을 만들어서 요청에

응답하기 때문에 앞서 말한 BLOCKING이나 일관성(CONSISTENT)이 깨진 데이타를 제공하지는 않는다.

이 때문에 잠금 현상이 줄어들어 동시성을 높여줄 수가 있게 되는 것이다.

예를 들어서 설명을 하자면, 이러하다.

VER 1.0에 대한 데이타를 읽기 트랜잭션이 시작되어서 읽어나가고 있다고 하자.

이 때에 쓰기 트랜잭션이 시작되었다면, 블록킹 현상없이 해당 데이터의 버전을

VER 1.1로 기록하고 작업을 해나가면 된다.

또, 쓰기 트랜잭션이 계속되고 있는 상황에서 읽기 트랜잭션이 시작되었다면,

아직 COMMIT이 되지 않아서 반영이 되지 않은 데이타이므로, VER 1.0의 데이타를

블록킹 없이 읽어나가면 된다. 그리고, COMMIT이 된 후에 시작된 읽기

트랜잭션이라면 VER 1.1의 데이타를 읽어가게 될 것이다.

역시 쓰기 작업이 진행되고 있는 상태에서 읽기 작업이 시작되었지만, 블록킹 현상없이 작업이 처리가 될 것이다.

좀 더 세부적으로 알아보자. 크게 두가지 형태로 SNAPSHOT이 제공된다.

SNAPSHOT isolation level

READ COMMITTED SNAPSHOT isolation level

이 두 격리수준의 차이점은 어느정도 수준까지 적용을 해줄 것인가에 관한 것이다.

먼저 SNAPSHOT isolation level은 트랜잭션 단위까지 위의 기술을 제공한다.

두번째로 READ COMMITTED SNAPSHOT ISOLATION LEVEL은 QUERY 단위까지 지원을 한다.

먼저 SNAPSHOT isolation level에서의 예를 들어보면 이렇다.

하나의 트랜잭션이 시작되고 읽기 작업이 시작되었다. VER 1.0의 데이타를 반환하였다.

그리고 중간에 다른 쓰기 트랜잭션이 시작되어 데이타를 변경해버렸다.

그 이후에 첫번째 트랜잭션이 다시 데이타를 읽는 다면 VER 1.0의 데이타를 반환하게 된다.

두번째 READ COMMITTED SNAPSHOT ISOLATION LEVEL은 조금 다르다.

첫번째 커넥션에서 읽기 작업을 했더니 VER 1.0의 데이타를 반환하였다.

중간에 다른 커넥션에서 해당 데이터를 VER 1.1로 반환하였다.

첫번째 커넥션에서 다시 읽기 작업을 하게 되면 VER 1.1로 반환을 하게 된다.

말그대로 작업 시점에 COMMITTED가 된 DATA를 반환하게 되는 것이다. 물론 잠금이나 블록킹 없이 말이다.

근데, 여기서 SNAPSHOT ISOLATION LEVEL에 대해 좀 더 생각하다보면 이런 상황이 발생할 수 있다.

첫번째 트랜잭션에서 읽기 작업이 이뤄져 VER 1.0의 데이타를 반환하였다.

그런데, 첫번째 트랜잭션에서 읽기 작업이 이뤄지는 중에 다른 트랜잭션에서 데이타를 VER 1.1로 변경한 후

COMMIT까지 이뤄져버렸다. 그 후에 첫번째 트랜잭션에서 읽기 작업으로 얻은 데이타를 쓰기 작업을

하려 한다면 어떻게 될까?

VER 1.2가 기록이 되면서 정리가 될까? 아니다.

에러가 나면서 트랜잭션은 롤백처리가 되어버린다. 아주 당연한 일이겠지만, 해당 데이타에 갱신을

하게되는데에 잘못된 데이타 버전을 기반으로 작업을 하려 한 것을 갱신하려고 한 것이기 때문에

데이타의 갱신이 이뤄지면 않된다.

그런 부분까지 꼼꼼히 챙기고 있기 때문에 DATA 일관성에는 별 문제가 없다.

3. 고려해야 할 점

여기까지 읽어본 느낌이 어떠한가? 어라~ 많이 좋아졌네? 이 좋은 기능을 왜 진작에 제공을 하지 않았는가?

등등의 여러 느낌이 있으리라 생각된다.

앞서 이런 말을 했다. 그냥 좋은가보다라는 생각으로 이 옵션을 적용하지 말라고~

새로 제공되는 isolation level은 잠금 현상을 줄여서 동시 사용성을 높여주지만, 대신에 나빠지는 것도 있다.

각 데이터의 버전에 대한 SNAPSHOT을 만든다는 것을 기억하는가?

SQL Sever에서는 row versiong을 위한 data snapshot을 tempdb database에 만들어서 관리한다.

때문에 이 기능을 사용하게 되면 tempdb database에 I/O이 늘어날 수 밖에 없다.

다른쪽에 LOCKING으로 발생하는 병목현상은 해결했지만, TEMPDB DATABASE에 대한 병목현상이

새로이 생겨버리게 된다. 때문에 TEMPDB DATABASE에 대한 I/O 증가로 인한 병목현상에 대한

고려가 있어야 한다.

TEMPDB DATABASE에 대한 확장 옵션, TEMPDB DATABASE가 저장되어 잇는 디스크 시스템 등에

대한 점검과 고려를 충분히 해보고, 테스트를 충분히 해본 다음에 적용을 하길 바란다.

이 옵션은 DEFAULT가 아니다. 이런 부분들에 대한 고려를 해본 다음에 적용하라는 사연이

있어서 그런 것이 아닌가하고 생각해본다. 장점만 있는 기능이라면 DEFAULT가 OFF는 아닐테니 말이다.

4. 적용하기

드디어 적용하기에 돌입했다. 앞서 말한 것처럼 장황하게 부연설명한 것에 비해선 허무할 정도로 간단하다.

먼저 SQL Server에 있는 데이타베이스들에 해당 옵션이 적용되어 있는지를 살펴보자.

아래의 쿼리를 쿼리창을 열어서 실행시켜보자.

SELECT NAME, SNAPSHOT_ISOLATION_STATE,

SNAPSHOT_ISOLATION_STATE_DESC, IS_READ_COMMITTED_SNAPSHOT_ON

FROM SYS.DATABASES

SNAPSHOT_ISOLATION_STATE, IS_READ_COMMITTED_SNAPSHOT_ON의 값이 1이면

적용이 되고 있다는 것이고, 0은 적용이 되고 있지 않다는 뜻이다.

SNAPSHOT ISOLATION LEVEL를 적용하기 위해선 아래의 쿼리를 실행하면 된다.

ALTER DATABASE 데이터베이스명 SET ALLOW_SNAPSHOT_ISOLATION ON

당연히 기능을 정지하려고 할 때에는 OFF로 플래그만 변경해서 실행하면 된다.

READ COMMITTED ISOLATION LEVEL를 적용하기 위해선 아래의 쿼리를 실행한다.

ALTER DATABASE 데이터베이스명 SET READ_COMMITTED_SNAPSHOT ON

역시 해당 기능을 정지하려고 할 때에는 OFF로 플래그만 변경하면 된다.

위 기능을 적용하기 위해서는 데이터베이스에 접속되어 있는 사용자가 없어야한다.

5. 맺음말

이 강좌에서는 개념적인 설명이 많았다. 적용하는 명령은 솔직히 한 줄로도 끝난다.

하지만, 제대로 사용하기 위해서는 개념에 대한 이해가 있어야 하기 때문이다.

이전 버전에 비해선 장족의 발전(?)이라 생각을 한다. 하지만, 아직 완전한 기능(?)은

아닌 것 같다는게 개인적인 느낌이다.

이전 버전과의 호환성 등을 이유로 Default가 OFF인 형태로 제공된듯 하지만,

고려할 점에서 언급한 것처럼 I/O에 대한 추가적인 부하가 있기 때문이다.

그리고 여기서 언급한 isolation 레벨은 오라클에서는 예전부터 제공이 되고 있던 기능이다.

개인적인 느낌으론 TPC에서 나오는 SQL서버 관련 수치들은 앞서 언급한 기능들을 배제하고

만들어낸, 차떼고 포떼고 해서 만들어낸 수치일수도 있다.

그렇다면, 오라클만 좋고 SQL서버는 나쁜 것일까?

 회색분자 같이 들릴 수도 있지만, 개인적으론 오라클과 SQL서버 모두 좋아한다.

각각의 DBMS가 주는 장단점이 다르다고 생각하기 때문이다.

정원혁님의 마이크로소프트웨어 기사에서처럼 현시점에선 비용기반으론 SQL 서버가 오라클보다

우위에 있기 때문이다. 비용을 고려하지 않고 프로젝트를 진행하는 곳이 얼마나 될까?

프로젝트를 진행한다는 말은 결국 비용과의 싸움이니까 말이다.

장인은 연장을 탓하지 않는 다는 말이 있다.

최고의 성능을 가진 오라클에서도 엉망진창으로 설계와 코딩을 진행하면 무지하게 느려질 수도 있다.

SQL 서버에서도 제품이 가진 특성을 잘 고려해서 설계와 운영을 해나간다면,

아주 만족스러운 성능을 뽑아낼 수 있는 좋은 제품이라 생각한다.

느려진 DBMS를 탓하기 전에 사용하고 있는 DBMS의 아키텍처는 얼마나 잘 이해하고 있으며,

설계와 관리는 어떻게 하고 있는가를 자문하는 시간을 먼저 가져보는 것이 좋지 않을까 생각한다.