Host DB2 애플리케이션 튜닝 가이드, Part 8 - DB2 Locking에 대한 이해

www.dbguide.net/know/know102001.jsp?mode=view&pg=1&idx=3541">1.

Host DB2 애플리케이션 튜닝 가이드, Part 8 - DB2 Locking에 대한 이해

 

최근에 어느 사이트에서 “ 왜 DB2 에는 READ Lock 이 있는가 ” 라는 것 때문에 한 개발자가 매우 흥분하는 것을 본적이 있다 . 다른 DB 를 오래 사용하던 그 개발자가 그렇게까지 흥분한 이유는 DB2 의 READ Lock 으로 인해 불필요하게 DEADLOCK 이 많이 발생한다는 것 때문이었다 .그럼 READ Lock 이 없다면 어떻게 될까 ?
내 트랜잭션이 데이터를 READ 해서 계산 처리하고 있는 동안에 다른 트랜잭션이 해당 데이터를 UPDATE 한다면 ?
실제로 다른 트랜잭션이 UPDATE 해도 되기 때문에 Locking 경합 (contention) 을 피하기 위해 READ Lock 을 걸지 않고 싶다면 ?
SQL 자체에 상당히 튜닝이 잘 되어있는 애플리케이션도 Locking 경합이나 DEADLOCK 으로 응답시간에 저하를 가져오는 경우가 많은데 , 이번 장에는 DB2 트랜잭션 Locking 과 관련된 여러 요인에 대해 이야기 하도록 하겠다 .

 

 

<연재순서> 1. DB2 응답시간 분석에 대한 이해 2. 튜닝 flow에 대한 이해와 성능에 영향을 주는 요인 3-1. DB2 Access Path에 영향을 주는 요인 -1 3-2. DB2 Access Path에 영향을 주는 요인 -2 4. Plan_테이블의 이해 및 Access Path에 변형을 주는 요인 5. 효율적인 인덱스 설계 6. 효율적인 테이블스페이스 물리설계 7. 버퍼 풀 실행에 대한 이해 8. Part 8 - DB2 Locking에 대한 이해

 

트랜잭션 Locking 의 대상

 

SQL 이 수행되는 동안 다음의 DB2 Object 에 대해서 테이블의 Lock Size 에 따라 , SQL 의 유형에 의한 Lock Mode 로서 , BIND 파라미터 option 에 따른 duration 만큼의 Lock 이 점유된다 . 이 경우 RI 에 의해 연결된 테이블에 대해서도 Locking 이 걸린다는 것을 유의한다 .

• SQL 이 처리하는 테이블스페이스 / 테이블 / 페이지 / 열
• RI 에 의해 연결된 테이블스페이스 / 테이블 / 페이지 / 열
• Type 1 Index
• DB2 Catalog
• DB2 Directory

트랜잭션 Locking 에 영향을 미치는 요인

 

트랜잭션 Locking 에 영향을 미치는 요인은 크게 여섯 가지로 분류할 수 있다 . 트랜잭션 Lock Mode, 테이블스페이스 물리설계 파라미터 , BIND 파라미터 , DB2 서브시스템 파라미터 , 트랜잭션 내 Commit 주기 , SQL 코딩 등이 그것이다 . 이들이 어떻게 영향을 미치는지 차례로 살펴본다 .

 

1. SQL 유형에 따른 Locking Mode

 

SQL 유형에 따라 Locking Mode 가 결정되며 Lock 사이의 다음과 같은 비교성을 갖는다 .

< 표 1> SQL 유형에 따른 LOCK Mode

 

< 표 3 > SQL 유형에 따른 Page ( 혹은 Row) Lock 비교

 

2. 테이블스페이스 물리설계 파라미터

 

CREATE TABLESPACE … LOCKSIZE.. LOCKMAX.. LOCKPART.. MAXROWS.. PCTFREE.. COMPRESS

트랜잭션 Locking 의 단위 - LOCKSIZE


< 그림 1> 테이블 형태에 따른 Lock Size 종류 – DB2 V7 Admin Guide p.651 참조

< 그림 2> Lock Size 에 따른 CPU 비용과 동시성의 관계

 

LOCKSIZE ANY 를 사용하는 것은 Lock Escalation 을 발생시킬 가능성이 있음으로 가능한 사용하지 않도록 한다 . < 그림 2> 에서 보는 바와 같이 Lock 횟수가 많은 것은 (LOCKSIZE 가 작은 경우 ) CPU 오버헤드와 메모리에 대한 비용이 많이 발생하게 되므로 Access 요건에 맞는 LOCKSIZE 를 설계하는 것이 매우 중요하다 .

 

통계나 정보계 업무와 같은 Read Only 테이블에 대해서는 LOCKSIZE 테이블 혹은 테이블스페이스로 정의하여 CPU 비용을 줄이는 것을 고려한다 . 변경 , 입력 및 조회가 다양하게 또한 빈번하게 일어나는 경우 동시성을 위해 Row 로 지정하는 것을 고려하되 , 그 외에는 IRLM 에 의한 CPU 오버헤드 줄이기 위해 일반적으로 PAGE 를 지정하도록 한다 .

또한 Single Row 를 처리하는 경우에는 PAGE Lock 사이즈와 비교하여 CPU 오버헤드기 같고 다음과 같은 테이블은 Hot spot 일 경우가 많으므로 동시성을 고려하여 Row Level Lock 이 바람직하다 .

• 소형 테이블이면서 CRUD 가 동시에 일어나는 경우
• 순차적으로 데이터가 입력되는 테이블들

LOCKMAX 파라미터

 

테이블 하나 당 동시에 발생할 수 있는 Page 혹은 Row 단위의 최대 Lock 개수이며 , 다음과 같은 경우의 LOCKMAX 수를 초과하는 경우 Lock Escalation 이 발생한다 .

• SYSTEM : DSNZPARM NUMLKTS 값에 의해 결정되며 LOCKSIZE ANY 의 경우 default 값
• 0: Lock escalation이 일어나지 않음. LOCKSIZE가 Page나 Row인 경우 default 값
• 1 – 2,147,483,647까지의 값이 사용 가능하다

LOCKPART 파라미터

 

파티션 된 테이블스페이스에 대해 LOCKPART=YES 로 정의하면 해당 파티션에 대해서만 S,U,X Lock 이 가능하게 되어 다른 파티션에 대한 동시성이 향상된다 .

 

PCTFREE, MAXROWS, COMPRESS 파라미터

 

페이지의 PCTFREE, MAXROWS, COMPRESS 파라미터는 각 페이지 내 저장되는 row 의 개수에 영향을 주게 되며 LOCKSIZE PAGE 의 경우 점유되는 row 수에 영향을 주게 된다 .

 

 

필자소개

 

오은숙 컨설턴트

 

Radhesh Kumar는 수석 DB2 DBA 이며 아키텍트이다. 데이터베이스 관리와 애플리케이션 개발에 수년의 경력을 보유하고 있다. 현재 Stratavia s DataPalette의 설계 및 개발 작업을 진행하고 있다. IBM Certified Advanced DBA며 radheshk.blogspot.com에서 블로그도 관리하고 있다.

 

 

제공 : DB포탈사이트 DBguide.net