대규모 동시 트랜잭션이 일반적인IT 환경에서, DBMS의 Locking 메커니즘과 동시성 제어는 데이터베이스의 성능과 직결되어있다고 해도 과언이 아닐 것이다. 본 기사에서는 DB2 UDB의 Locking 메커니즘과 동시성 제어 방식을 테스트를 통해 살펴봄으로써, DB2 UDB의 락 발생 원인 분석의 효율성과 동시성 향상을 도모하고자 한다.
본 기사의 내용은 DB2 UDB 8.2.3을 이용하여 테스트한 결과를 기반으로 작성하였으며, 다음과 같은 순서로 DB2 UDB의 내부 동작원리를 파악해볼 것이다.
1. DB2가 지원하는 Isolation level
2. 왜 DB2 UDB에서는 SELECT 시에도 락 대기가 발생할까?
3. Lock escalation이란?
4. DB2 UDB Lock Mode의 이해
5. 동시성 향상을 위한 DB2 레지스트리 변수 및 Case별 테스트
1. DB2 UDB가 지원하는 Isolation level
DB2 UDB에서는 ANSI SQL 표준으로 정의된 4가지 Isolation level을 모두 지원한다. ANSI SQL 표준의 4가지 Isolation level은 Read Uncommitted, Read Committed, Repeatable Read, Serializable이며, DB2 UDB에서는 각각의 Isolation level을 UR (Uncommitted Read), CS (Cursor Stability), RS (Read Stability), RR (Repeatable Read)로 지원하고 있다(4개의 Isolation level에 대한 설명은 [표 1.1]을 기술하였다).DB2 UDB가 이러한 4개의 Isolation level을 제공하는 목적은, 특정 트랜잭션 별로 자신에게 필요한 수준의 Isolation level을 선택적으로 사용하게 함으로써, 트랜잭션의 동시성을 높이는 것이다. [표 1.2]에서는 DB2 UDB에서 제공하는 Isolation level과 각Isolation level에서 허용하는 이상현상을 보여준다.
[표 1.1] DB2 UDB에서 제공하는 Isolation level
DB2 UDB Isolation level | 설 명 |
UR (Uncommitted Read) | Uncommitted Read는 “Dirty Read” 라고도 불리며, DB2 UDB 에서 제공하는 가장 낮은 Isolation level이다. 이름에서도 알 수 있듯이, 현재 레코드가 변경 중(즉, COMMIT 또는 ROLLBACK으로 트랜잭션이 종료되지 않은 상태)이라도, 해당 레코드가 조건에 만족하면 리턴 한다. Uncommitted Read는 다른 트랜잭션의 락을 릴리즈 할 때까지 대기하지 않으므로, 가장 높은 동시성을 제공하지만,가장 낮은 데이터 일관성을 제공하므로, 일반적으로 READ onLY 테이블을 조회할 때 사용한다. |
CS (Cursor Stability) | Cursor Stability는 DB2 UDB의 기본설정 값이며, 현재 커서가 위치한 레코드에 대해서 락을 건다. 해당 레코드에 대한 락은 다음 레코드를 페치(fetch) 하기 전, 또는 UOW가종료될 때까지 유지된다. UOW는 COMMIT 또는ROLLBACK명령문을 수행하면 종료된다. Cursor Stability는 항상 커밋된 레코드만을 읽으며, 만일 커서가가리키는 레코드에 락이 걸려있다면, 해당 쿼리는 락 대기상태에 빠진다. 또한, 현재 커서가 위치한 레코드에는 락이걸려있으므로, 다른 트랜잭션에서 해당 레코드에 대한 변경작업을 수행할 수 없게 된다. 커밋된 레코드만을 읽으면서, 동시성을 극대화시키려면, CS Isolation level을 사용하여야 한다. |
RS (Read Stability) | Read Stability는 UOW 단위 안에서, result set에 해당되는모든 레코드에 대해 락을 획득한다. 예를 들어 EMP 테이블에서 DEPTNO=10인 조건을 만족하는 레코드가 3건이라면, 3건의 레코드에 대해 락을 획득하게 된다. Result set에해당되는 레코드에 대해 락을 획득하므로, 다른 트랜잭션에서 EMP 테이블에 DEPTNO=10인 레코드를 입력하는 것이 가능하다(즉, phantom reads 발생이 가능하다). Read Stability 역시 항상 커밋된 레코드만을 읽게 되며, 다른 트랜잭션에서 해당 result set에 대한 변경작업을 수행할 수없다. |
RR (Repeatable Read) | Repeatable Read는 DB2 UDB에서 제공하는 가장 높은Isolation level이다. Repeatable Read는 UOW 단위 안에서,커서에서 참조하는 모든 레코드에 대해 락을 획득한다. 즉, Read Stability의 예에서는 다른 트랜잭션에서DEPTNO=10인 레코드를 입력하는 것이 가능했지만, Repeatable Read에서는 불가능하다(즉, phantom reads 발생을 막을 수 있다). 이러한 이유로 Repeatable Read로 설정할 경우에 쿼리 옵티마이저는 LOCKLIST 및MAXLOCKS와 같은 파라미터 설정을 고려하여, 레코드 단위가 아닌 테이블 레벨에 락을 획득하는 방식을 사용하기도 한다. Repeatable Read 역시 항상 커밋된 레코드만을읽게 되며, 다른 트랜잭션에서 해당 커서에서 참조하는 레코드들에 대한 변경작업을 수행할 수 없다. |
[표 1.2] DB2 UDB에서 제공하는 Isolation level과 각 Isolation level에서 허용하는 이상현상
DB2 UDB Isolation level | Lost Updates | Dirty Reads | Non-Repeatable Reads | Phantom Reads |
UR (Uncommitted Read) | N | Y | Y | Y |
CS (Cursor Stability) | N | N | Y | Y |
RS (Read Stability) | N | N | N | Y |
RR (Repeatable Read) | N | N | N | N |
Isolation level에 따라 발생할 수 있는 이상현상은 Lost Updates, Dirty Reads, Non-Repeatable Reads, Phantom Reads이다. File System과 달리, 모든 사용 DBMS에서는Dirty Write가 허용되지 않으므로, Lost Updates 현상은 발생하지 않는다. 또한 Dirty Reads는 말 그대로 언커밋된 레코드를 읽는 것을 의미한다. 따라서, 조금 생소한 Non-Repeatable Reads와 Phantom Reads를 예를 들어 설명하기로 한다.
예) EMP 테이블에는 다음과 같은 레코드가 저장되어있다.
EMPNO | DEPTNO | SAL |
1 | 10 | 100 |
2 | 10 | 100 |
3 | 10 | 100 |
1. Non-Repeatable Reads
Transaction 1 | Transaction 2 |
SELECT SAL FROM EMP WHERE EMPNO=1 Result: 100
|
|
UPDATE EMP SET SAL=200 WHERE EMPNO=1; COMMIT; | |
SELECT SAL FROM EMP WHERE EMPNO=1 Result: 200 |
|
Non-Repeatable Reads란 위의 예처럼, 하나의 UOW 안에서 동일한 레코드를 2번 이상조회하는 경우에, 다른 트랜잭션에서 해당 레코드를 변경함으로 인해, 조회 시마다 결과가 다르게 나타나는 현상이다. 이러한 이상현상을 해결하기 위해서는 쿼리의 result set에 해당되는 레코드에 로우 레벨의 락을 설정하는 RS (Read Stability) Isolation level을 사용하면 된다.
2. Phantom reads
Transaction 1 | Transaction 2 |
SELECT SUM(SAL) FROM EMP WHERE DEPTNO=10 Result: 300 |
|
| INSERT INTO EMP VALUES (4,10,200); COMMIT; |
SELECT SUM(SAL) FROM EMP WHERE DEPTNO=10 Result: 500 |
|
Phantom Reads란 기존 수행한 쿼리에 해당되는 레코드들에 대한 변경이 아닌, 신규로삽입된 레코드에 의해 쿼리 결과가 다르게 나타나는 현상을 의미한다. 이러한 이상 현상을 해결하기 위해서는, 커서가 참조하는 모든 레코드에 대해 락을 설정하는 RR (Repeatable Read) Isolation level을 사용하면 된다.
2. 왜 DB2 UDB에서는 SELECT 시에도 락 대기가 발생할까?
DB2 UDB의 CS (Cursor Stability) Isolation level부터는, 현재 변경이 진행중인 레코드를 페치(fetch)할 경우에 락 대기 현상이 발생한다. 또한 현재 커서가 위치한 레코드에대해서, 다른 트랜잭션에서 변경을 시도할 경우에도 락 대기가 발생하게 된다. 다시 말하면 WRITE가 READ를 블로킹하고, READ가 WRITE를 블로킹하게 된다. 오라클에 익숙한 사용자라면, 이러한 현상에 대해서, 의아하게 생각할 것이다. 하지만, ANSI-SQL표준에 의하면, 이러한 현상은 당연한 것이라 할 수 있다. 왜냐면, CS (Cursor Stability) Isolation level부터는 SELECT 시에도 레코드에 로우 레벨 락을 설정함으로써, Isolation을 보장하기 때문이다. 다만, 오라클에서는 언두 세그먼트(Undo Segment)를이용한 MVCC (Multi-Version Concurrency Control) 기법을 이용하여, WRITE와READ간의 블로킹 현상을 방지한 것이다. 현재 MVCC를 지원하는 상용 DBMS는ORACLE, MS-SQL 2005, ALTIBASE 정도이다. MVCC를 지원한다는 것이 트랜잭션의동시성을 높일 수 있다는 장점은 있으나, 언두 블록 I/O, CR Copy 오퍼레이션, CR 블록관리와 같은 부가적인 작업이 필요하다는 것도 간과해서는 안될 부분이다.
Lock escalation이란 로우 레벨 락이 테이블 락으로 전이되는 현상을 의미한다. Lock escalation은 DB2 UDB와 같이 락 정보를 메모리상에서 관리하는 DBMS에서는 공통적으로 발생할 수 있는 현상이다. 즉, 한정된 메모리 공간에서 락 정보를 관리함에 따라,락 정보를 관리하는 메모리 공간이 부족할 경우, 필연적으로 Lock escalation이 발생하게 되는 것이다. 일반적으로 Lock escalation이 발생할 경우, 트랜잭션의 동시성은 급격히 감소하여, 전반적인 성능 문제를 유발하게 된다. 따라서 DB2 UDB에서 Lock escalation이 발생하는 경우와 Lock escalation이 발생 여부를 감지하는 방법에 대해서설명하고자 한다. DB2 UDB에서 Lock escalation과 관련된 DB Configuration 파라미터는 LOCKLIST와 MAXLOCKS 이다.
LOCKLIST | 락 정보를 관리하는 메모리 공간의 크기를 지정하는 파라미터이다. LOCKLIST의 크기를 증가하는 것은 동적으로 가능하지만, LOCKLIST의 크기를 감소시키려면 DB를 재기동해야 한다.해당 파라미터의 단위는 4KB이다. |
MAXLOCKS | 하나의 응용프로그램에서 획득할 수 있는 락 정보의 최대 크기를, LOCKLIST에 대한 백분율로 나타내는 파라미터이다. 기본설정 값은 10% 이다. 이 말은 LOCKLIST의 10%의 공간까지 하나의 응용프로그램에서 사용이 가능하며, 이 비율을 넘기는 시점에 로우 레벨 락이 테이블 레벨 락으로 Lock escalation이 발생하게 된다. |
DB2 UDB에서 Lock escalation이 발생하는 경우는 크게 2가지로 구분할 수 있다.
1. 하나의 응용프로그램이 MAXLOCKS 비율이상으로 락을 획득한 경우
2. LOCKLIST를 정의된 메모리 공간에 더 이상의 락 정보를 저장할 수 없을 경우이다.
이 중에서 1번에 대해서 테스트를 통해 Lock escalation의 동작원리를 살펴보도록 하자.
테스트1:
하나의 응용프로그램이 MAXLOCKS 비율이상으로 락을 획득한 경우.
테스트 환경: AIX 5.3 64 Bit, DB2 UDB 8.2.3
-------- 테스트 시작 -------
--현재의 LOCKLIST 설정 값을 확인한다.
$ db2 get db cfg | grep LOCKLIST
Max storage for lock list (4KB) (LOCKLIST) = 100
--테스트의 편의성을 위해 LOCKLIST를 최소값으로 설정한 후 DB를 재 기동한다.
$ db2 update db cfg using LOCKLIST 4
$ db2 terminate
$ db2 force application all
$ db2 deactivate db sample
$ db2 activate db sample
$ db2 connect to sample
$ db2 get db cfg | grep LOCKLIST
Max storage for lock list (4KB) (LOCKLIST) = 4
*) DB 재 기동 후에 LOCKLIST가 4로 변경되었음을 알 수 있다. 즉, LOCKLIST로 지정된 메모리 공간은 16KB(4*4KB)이고, MAXLOCKS는 10% 이므로, 하나의 응용프로그램은 1.6KB(1638 Bytes) 만큼의 락 메모리 공간을 사용하는 것이 가능하다.
--테스트를 위한 테이블을 생성하고 1건을 입력한다.
$ db2 “create table lock_escals_test (id integer, name char(10))”
$ db2 +c -- 테스트를 위해 auto commit mode를 해제한다.
db2 => insert into lock_escals_test values (1,’1’)
-- 다른 터미널에서 락 정보를 조회한다.
$ db2 –tf lock_info.sql
AGENT_ID LOCK_MODE LOCK_OBJECT_TYPE TABLE_NAME
-------- -------------------------- --------------------- ----------------
1031 Exclusive Lock table row lock type LOCK_ESCALS_TEST
1031 Intention Exclusive Lock table lock type LOCK_ESCALS_TEST
*) LOCK_ESCALS_TEST 테이블에, IX(Intention Exclusive) 모드로 1개의 테이블 락을획득하였고, X(Exclusive) 모드로 1개의 로우 락을 획득한 것을 알 수 있다.
-- 추가로 1건을 입력한 후에 락 정보를 조회해보자.
$ db2 –tf lock_info.sql
AGENT_ID LOCK_MODE LOCK_OBJECT_TYPE TABLE_NAME
-------- -------------------------- --------------------- ----------------
1031 Exclusive Lock table row lock type LOCK_ESCALS_TEST
1031 Exclusive Lock table row lock type LOCK_ESCALS_TEST
1031 Intention Exclusive Lock table lock type LOCK_ESCALS_TEST
*) 조회 결과, DB2 UDB에서는 로우 단위의 락이 메모리에서 관리됨을 명확히 확인할 수있다. 이러한 방식으로 MAXLOCKS에 도달할 때까지 락 메모리상에 로우 락 정보가 저장될 것이다.
-- LOCK_ESCALS_TEST에 45건을 입력한 시점의 락 정보는 다음과 같다.
AGENT_ID LOCK_MODE LOCK_OBJECT_TYPE TABLE_NAME
-------- ----------------------------------------------- ----------------
1031 Exclusive Lock table row lock type LOCK_ESCALS_TEST
1031 Exclusive Lock table row lock type LOCK_ESCALS_TEST
....
1031 Exclusive Lock table row lock type LOCK_ESCALS_TEST
1031 Intention Exclusive Lock table lock type LOCK_ESCALS_TEST
46 record(s) selected.
*) LOCK_ESCALS_TEST 테이블에, IX(Intention Exclusive) 모드로 1개의 테이블 락을획득하였고, X(Exclusive) 모드로 45개의 로우 락을 획득한 것을 알 수 있다.
-- 추가로 1건을 입력한 후에 락 정보를 조회해보자.
AGENT_ID LOCK_MODE LOCK_OBJECT_TYPE TABLE_NAME
-------- ----------------------------------------------------------------
1031 Exclusive Lock table lock type LOCK_ESCALS_TEST
*) LOCK_ESCALS_TEST 테이블에, IX(Intent Exclusive) 모드로 획득한 테이블 락이X(Exclusive) 모드의 락으로 변경되었고, 기존에 획득한 로우 락이 모두 사라졌음을 알수 있다. 즉, Lock escalation이 발생한 것이다. 테이블 락을 X(Exclusive) 모드로 획득하였으므로, 다른 응용프로그램에서는 LOCK_ESCALS_TEST 테이블에 대한 SELECT및 DML 수행이 불가능하다. 그 이유는 락 모드간의 호환성이 없기 때문인데, 락 모드간의 호환성에 대해서는 “4. DB2 UDB Lock Mode의 이해” 에서 설명하기로 한다. Lock escalation의 발생 여부를 확인하는 방법은 몇 가지가 있지만, 가장 인지 하기 쉬운 방법은 db2diag.log를 확인하는 것이다. Lock escalation에 대한 상세 정보를db2diag.log에서 확인하기 위해서는 DBM Configuration 파라미터인 NOTIFYLEVEL이 3또는 4이어야 한다(기본설정 값은 3). 아래는 Lock escalation이 발생한 경우에db2diag.log에 출력되는 내용이다.
PID : 1634496 TID : 1 PROC : db2agent (SAMPLE) 0 INSTANCE: db2inst1 NODE : 000 DB : SAMPLE APPHDL : 0-1031 APPID: *LOCAL.db2inst1.070424014503 FUNCTION: DB2 UDB, data management, sqldEscalateLocks, probe:3 MESSAGE : ADM5502W The escalation of "46" locks on table "DB2INST1.LOCK_ESCALS_TEST" to lock intent "X" was successful. |
*) DB2INST1.LOCK_ESCALS_TEST 테이블에 대해 46개의 락이 X 모드의 테이블 락으로 escalation 되었다는 정보를 확인할 수 있다. 즉, 현재의 LOCKLIST, MAXLOCKS 설정으로, 하나의 응용프로그램은 하나의 테이블에 대해서 46개의 락 정보를 락 메모리공간에 저장할 수 있다.
-------- 테스트 종료 -------
지금까지의 테스트 내용은 하나의 응용 프로그램에서 하나의 테이블에 대해MAXLOCKS로 지정된 비율 이상으로 락을 획득한 경우의 Lock escalation 발생 현상을살펴보았다. 그렇다면, 하나의 응용 프로그램에서 2개 이상의 테이블에 대한 락을 획득하였고, 특정 1개의 테이블에 대한 과도한 로우 락 획득으로 인해 MAXLOCKS 비율 이상으로 락을 획득한 경우를 살펴보도록 하자. Lock escalation이 어떻게 발생할 것인가? 모든 테이블에 대해서 Lock escalation이 발생할 것인가? 아니면 가장 많은 로우 락이 설정된 테이블에 대해서만 Lock escalation이 발생할 것인가? 상식적으로 생각해도,후자가 맞을 것이다. 테스트를 통해서 확인해 보도록 하자.
-------- 테스트 시작 -------
-- LOCK_ESCALS_TEST2 테이블 생성 후 1건 입력
$ db2 “create table lock_escals_test2 (id integer, name char(10))”
$ db2 +c -- 테스트를 위해 auto commit mode를 해제한다.
db2 => insert into lock_escals_test2 values (1,’1’)
-- LOCK_ESCALS_TEST 테이블에 38건 입력 후에 락 정보 확인
$ db2 –tf lock_info.sql
AGENT_ID LOCK_MODE LOCK_OBJECT_TYPE TABLE_NAME
-------- ------------------------ --------------------- ------------------
1162 Exclusive Lock table row lock type LOCK_ESCALS_TEST
....
1162 Exclusive Lock table row lock type LOCK_ESCALS_TEST
1162 Exclusive Lock table row lock type LOCK_ESCALS_TEST2
1162 Intention Exclusive Lock table lock type LOCK_ESCALS_TEST
1162 Intention Exclusive Lock table lock type LOCK_ESCALS_TEST2
41 record(s) selected.
*) LOCK_ESCALS_TEST2 테이블에 IX 모드의 테이블 락 1개, X 모드의 로우 락 1개,LOCK_ESCALS_TEST 테이블에 IX 모드의 테이블 락 1개, X 모드의 로우 락 38개를 회득하였다. 즉, 현재까지 총 41개의 락이 하나의 응용프로그램에서 획득한 상태이다.
이 경우에 로우 락을 적게 설정한 LOCK_ESCALS_TEST2 테이블에 추가로 3건을 입력한 후에 락 정보와 db2diag.log의 내용을 확인해 보자.
$ db2 –tf lock_info.sql
AGENT_ID LOCK_MODE LOCK_OBJECT_TYPE TABLE_NAME
-------- ------------------------- ----------------------- ------------------
1162 Exclusive Lock table row lock type LOCK_ESCALS_TEST2
1162 Exclusive Lock table row lock type LOCK_ESCALS_TEST2
1162 Exclusive Lock table row lock type LOCK_ESCALS_TEST2
1162 Exclusive Lock table row lock type LOCK_ESCALS_TEST2
1162 Exclusive Lock table lock type LOCK_ESCALS_TEST
1162 Intention Exclusive Lock table lock type LOCK_ESCALS_TEST2
6 record(s) selected.
이 시점에서의 db2diag.log의 내용은 아래와 같다.
PID : 987356 TID : 1 PROC : db2agent (SAMPLE) 0 INSTANCE: db2inst1 NODE : 000 DB : SAMPLE APPHDL : 0-1162 APPID: *LOCAL.db2inst1.070424024716 FUNCTION: DB2 UDB, data management, sqldEscalateLocks, probe:3 MESSAGE : ADM5502W The escalation of "38" locks on table "DB2INST1.LOCK_ESCALS_TEST" to lock intent "X" was successful. |
*) LOCK_ESCALS_TEST 테이블에 대해, 38개의 락이 X 모드의 테이블 락으로 Lock escalation 되었음을 알 수 있으며, LOCK_ESCALS_TEST2 테이블에 대해서는 Lock escalation이 발생하지 않았다. 이 테스트를 통해, Lock escalation은 가장 많은 락이 설정된 테이블에 대해서 우선적으로 수행됨을 알 수 있다. 만일 해당 테이블의 Lock escalation 후에도 MAXLOCKS로 지정된 비율보다 높을 경우에는 다음 순위의 테이블에 대해서 Lock escalation이 발생할 것이다.
-------- 테스트 완료 -------
테스트 결과에서 알 수 있듯이, 하나의 테이블에 대해 락을 획득한 경우와, 두 개 이상의 테이블에 대해 락을 획득한 경우에, 락을 저장하는 개수가 약간 상이한 것을 알 수있다. 테스트 결과에 의하면 대략 1개의 락 당 40 bytes 정도의 락 메모리 공간을 차지한다는 것을 알 수 있다. 일반적으로 Lock escalation은 거의 발생하지 않는 것이 정상이지만, 만일 Lock escalation 현상이 발견된다면, 1차적으로는 LOCKLIST DB Configuration 파라미터의 현재설정 값을 확인한 후, 해당 수치를 증가시키는 것이다. 2차적으로는 db2diag.log 의 내용을 참조하여, 해당 어플리케이션에서 필요이상의 락을장기간 획득해야만 하는지에 대한 타당성 점검을 할 필요성이 있다.
DB2 UDB Lock Mode와 Lock Compatibility
DB2 UDB 사용 시 락 대기 현상이 발생할 경우, 문제 원인 분석을 위해서는 DB2 UDB 에서 사용되는 락 모드와 락 호환성에 대한 이해가 선행되어야 한다. DB2 UDB에서는None 모드를 포함하여 12개의 락 모드를 사용한다. ORACLE 데이터베이스의 경우, X(Exclusive), SSX(Sub Share Exclusive), SX(Sub Exclusive), S(Shared), SS(Sub Share), N(Null) 와 같이 6개의 락 모드를 사용한다. 락 모드에 대한 정의와 예제는 다음과 같다. 락 모드에 대한 이해를 높이기 위해, AIX 5.3 64비트, DB2 UDB 8.2.3 환경의 테스트를 통해 설명하였다.
Lock Mode | IN (Intent None) |
Applicable Object | Table space, blocks, table |
Description | “Dirty Read” 즉, UR Isolation level을 사용하여 데이터를 조회하는 경우에 IN 모드의 테이블 락을 획득해야 한다. |
IN 모드와 호환성이 없는 Z 모드의 락을 T2 트랜잭션에서 획득한 상태에서 T3 트랜잭션에서 “Dirty Read”를 수행한다. IN 모드와 Z 모드간에 락 호환성이 없으므로, T3 트랜잭션은 락 대기를 할 것이다. 또한 Z 모드의 락을 유지하기 위해서, Z 모드와 호환성이 없는 IX 락을 T1 트랜잭션에서 획득한 상태로 테스트를 수행한다.
T1 트랜잭션: (Agent ID 1313)
$ db2 +c “insert into lock_test values(3,’3’)
DB20000I The SQL command completed successfully.
* 데이터 입력 시 IX 모드의 테이블 락과 X 모드의 로우 락을 획득한다.
T2 트랜잭션: (Agent ID 292)
$ db2 “alter table lock_test add juso char(100)”
* IX 모드와 Z 모드간의 호환성이 없으므로 T2 트랜잭션은 락을 대기한다.
T3 트랜잭션: (Agent ID 1345)
$db2 “select * from lock_test with
* Z 모드와 IN 모드간의 호환성이 없으므로 T3 트랜잭션은 락을 대기한다.
-- 현재의 Lock 대기 상태를 확인해보자.
$ db2 –tf lock_wait.sql
AGENT_ID LCK_MODE LCK_REQ LOCK_OBJECT_TYPE TABLE_NAME HOLDER_ID
-------- -------- ------- ---------------- ---------- ---------
292 IX Z table lock LOCK_TEST 1313
1345 IN IN table lock LOCK_TEST 292
* T3 트랜잭션(Agent ID 1345)이 IN 모드의 테이블 락을 대기하는 것을 알 수 있다. 이를통해, “Dirty Read” 시에 IN 모드의 테이블 락을 획득한다는 것을 간접적으로 확인할 수있다.
Lock Mode | IS (Intent Share) |
Applicable Object | Table space, blocks, tables |
Description | CS 이상의 Isolation level을 사용하여 데이터를 조회하는 경우에 IS 모드의 테이블 락을 획득해야 한다. |
IS 모드와 호환성이 없는 X 모드의 락을 T1 트랜잭션에서 획득한 상태에서 T2 트랜잭션에서 읽기 작업을 수행한다. IS 모드와 X 모드간에 락 호환성이 없으므로, T23 트랜잭션은 락 대기를 할 것이다.
T1 트랜잭션: (Agent ID 1313)
$ db2 +c “lock table lock_test in exclusive mode
DB20000I The SQL command completed successfully.
T2 트랜잭션: (Agent ID 292)
$ db2 “select * from lock_test with cs”
*) X 모드와 IS 모드간의 호환성이 없으므로 T2 트랜잭션은 락을 대기한다
-- 현재의 Lock 대기 상태를 확인해보자.
$ db2 –tf lock_wait.sql
AGENT_ID LCK_MODE LCK_REQ LOCK_OBJECT_TYPE TABLE_NAME HOLDER_ID
-------- -------- ------- ---------------- ---------- ---------
292 X IS table lock LOCK_TEST 1313
* T2 트랜잭션(Agent ID 292)이 IS 모드의 테이블 락을 대기하는 것을 알 수 있다. 이를통해, CS 이상의 Isolation level에서 데이터 조회 시, IS 모드의 테이블 락을 획득한다는것을 확인할 수 있다.
Lock Mode | NS (Next Key Share) |
Applicable Object | Rows |
Description | CS 또는 RS Isolation level에서 데이터를 조회하는 경우에 현재 커서가 위치한 레코드에 대해 NS 모드의 로우 락을 획득해야 한다. |
NS 모드와 호환성이 없는 X 모드의 락을 T1 트랜잭션에서 획득한 상태에서, T2 트랜잭션에서 CS Isolation level로 데이터 조회를 수행한다.
T1 트랜잭션: (Agent ID 1313)
$ db2 +c “insert into lock_test values(3,’3’)
DB20000I The SQL command completed successfully.
* 데이터 입력 시 IX 모드의 테이블 락과 X 모드의 로우 락을 획득한다.
T2 트랜잭션: (Agent ID 292)
$ db2 “select * from lock_test with cs”
* X 모드와 NS 모드간의 호환성이 없으므로 T2 트랜잭션은 락을 대기한다.
-- 현재의 Lock 대기 상태를 확인해보자.
$ db2 –tf lock_wait.sql
AGENT_ID LCK_MODE LCK_REQ LOCK_OBJECT_TYPE TABLE_NAME HOLDER_ID
-------- -------- ------- ------------------- ---------- ---------
292 X NS table row lock type LOCK_TEST 1313
* T2 트랜잭션(Agent ID 292)이 NS 모드의 로우 락을 대기하는 것을 알 수 있다.
Lock Mode | S (Share) |
Applicable Object | Rows, blocks, tables |
Description | 데이터의 변경을 막기 위해서 설정하는 락 모드이다. 예를 들어, 인덱스 생성 시에는 테이블 데이터의 변경을 막아야 하므로, S 모드의 테이블 락을 획득해야 한다. |
S 모드와 호환성이 없는 IX 모드의 락을 T1 트랜잭션에서 획득한 상태에서, T2 트랜잭션에서 인덱스 생성을 시도한다.
T1 트랜잭션: (Agent ID 1313)
$ db2 +c “insert into lock_test values(3,’3’)
DB20000I The SQL command completed successfully.
* 데이터 입력 시 IX 모드의 테이블 락과 X 모드의 로우 락을 획득한다.
T2 트랜잭션: (Agent ID 292)
$ db2 “create index lock_test_s01 on lock_test(id)”
* IX 모드와 S 모드간의 호환성이 없으므로 T2 트랜잭션은 락을 대기한다.
-- 현재의 Lock 대기 상태를 확인해보자.
$ db2 –tf lock_wait.sql
AGENT_ID LCK_MODE LCK_REQ LOCK_OBJECT_TYPE TABLE_NAME HOLDER_ID
-------- -------- ------- ---------------- ---------- ---------
292 IX S table lock LOCK_TEST 1313
* T2 트랜잭션(Agent ID 292)이 S 모드의 테이블 락을 대기하는 것을 알 수 있다.
Lock Mode | IX (Intent Exclusive) |
Applicable Object | Table spaces, blocks, tables |
Description | 테이블내의 레코드에 대한 변경(delete, update) 및 레코드 입력(insert) 시에 IX 모드의 테이블 락을 획득해야 한다. |
IX 모드와 호환성이 없는 S 모드의 락을 T1 트랜잭션에서 획득한 상태에서, T2 트랜잭션에서 데이터 삭제를 시도한다.
T1 트랜잭션: (Agent ID 1313)
$ db2 +c “lock table lock_test in share mode “
DB20000I The SQL command completed successfully.
T2 트랜잭션: (Agent ID 292)
$ db2 “delete from lock_test where id=1”
* S 모드와 IX 모드간의 호환성이 없으므로 T2 트랜잭션은 락을 대기한다.
-- 현재의 Lock 대기 상태를 확인해보자.
$ db2 –tf lock_wait.sql
AGENT_ID LCK_MODE LCK_REQ LOCK_OBJECT_TYPE TABLE_NAME HOLDER_ID
-------- -------- ------- ---------------- ---------- ---------
292 S IX table lock LOCK_TEST 1313
* T2 트랜잭션(Agent ID 292)이 IX 모드의 테이블 락을 대기하는 것을 알 수 있다.
Lock Mode | SIX (Share with Intent Exclusive) |
Applicable Object | Tables, blocks |
Description | IX 모드의 테이블 락을 획득한 상태에서 추가로 S 모드의 테이블 락을 획득하거나, 그 반대의 경우에 SIX 모드의 테이블락을 획득해야 한다. |
SIX 모드와 호환성이 없는 S 모드 락을 T1 트랜잭션에서 획득한 상태에서, T2 트랜잭션에서 S 모드 락을 획득한 후, 추가로 IX 모드의 락을 획득하려고 시도한다.
T1 트랜잭션: (Agent ID 1313)
$ db2 +c “lock table lock_test in share mode “
DB20000I The SQL command completed successfully.
T2 트랜잭션: (Agent ID 292)
$ db2 +c
db2=> lock table lock_test in share mode
DB20000I The SQL command completed successfully.
db2=> delete from lock_test where id=1
* S 모드의 테이블 락을 획득한 상태에서, 삭제(delete)작업을 위해 IX 모드의 테이블 락을 추가로 획득하기 위해 SIX 모드로 락 컨버젼을 시도한다. 하지만, T1 트랜잭션이 획득하고 있는 S 모드와 T2 트랜잭션에서 획득해야 하는 SIX 모드간의 호환성이 없으므로 락을 대기한다.
-- 현재의 Lock 대기 상태를 확인해보자.
$ db2 –tf lock_wait.sql
AGENT_ID LCK_MODE LCK_REQ LOCK_OBJECT_TYPE TABLE_NAME HOLDER_ID
-------- -------- ------- ---------------- ---------- ---------
292 S SIX table lock LOCK_TEST 1313
* T2 트랜잭션(Agent ID 292)이 SIX 모드의 테이블 락을 대기하는 것을 알 수 있다.
Lock Mode | U (Update) |
Applicable Object | Rows, blocks, tables |
Description | SELECT.. FOR UPDATE 시에 U 모드의 로우 락을 획득해야 한다. |
U 모드와 호환성이 없는 X 모드 락을 T1 트랜잭션에서 획득한 상태에서, T2 트랜잭션에서 SELECT.. FOR UPDATE를 시도한다.
T1 트랜잭션: (Agent ID 1313)
$ db2 +c “delete from lock_test where id=1 “
DB20000I The SQL command completed successfully.
* 데이터 삭제 시 IX 모드의 테이블 락과 X 모드의 로우 락을 획득한다.
T2 트랜잭션: (Agent ID 292)
$ db2 “select * from lock_test where id=1 for update”
* T1 트랜잭션에 의해 해당 레코드에, X 모드의 로우 락이 획득된 상태이므로, T2 트랜잭션은 락을 대기한다..
-- 현재의 Lock 대기 상태를 확인해보자.
$ db2 –tf lock_wait.sql
AGENT_ID LCK_MODE LCK_REQ LOCK_OBJECT_TYPE TABLE_NAME HOLDER_ID
-------- -------- ------- ------------------- ---------- ---------
292 X U table row lock type LOCK_TEST 1313
* T2 트랜잭션(Agent ID 292)이 U 모드의 로우 락을 대기하는 것을 알 수 있다.
Lock Mode | X (Exclusive) |
Applicable Object | Rows, blocks, tables, buffer pools |
Description | 테이블 레벨, 로우 레벨에 대한 변경작업을 하려고 할 때 X 모드의 락을 획득해야 한다. X 모드는 테스트 없이도 쉽게 이해가능하므로, 테스트를 생략한다. |
Lock Mode | Z (Super Exclusive) |
Applicable Object | Table spaces, tables |
Description | 테이블 DROP, ALTER 와 같은 오퍼레이션 시에 Z 모드의 테이블 락을 획득해야 한다. IN 모드 설명 시에 Z 모드 테스트를수행하였으므로, 별도의 테스트를 수행하지 않는다. |
Lock Mode | NW (Next Key Weak Exclusive) |
Applicable Object | Rows |
Description | 테이블에 Non unique 인덱스가 생성되어있고, 입력하려고 하는 레코드가, 현재 다른 트랜잭션에서 RR 스캔에 의해 락이설정된 레코드와 동일한 값일 경우에 NW 모드의 락을 대기하게 된다. NW 모드는 쉽게 이해되지 않는 모드 중의 하나이다. 따라서 이 부분은 테스트 결과를 통해, 언제 NW 모드가사용되는지 유추하는 방식을 사용하였다. |
T1 트랜잭션에서 RR 스캔을 수행하고 있는 상태에서, T2 트랜잭션에서 T1 트랜잭션의RR 스캔의 레코드에 해당되는 값을 입력한다.
앞선 테스트에서는 LOCK_TEST 테이블에 인덱스가 존재하지 않았으나, NW 모드 테스트를 위해 LOCK_TEST 테이블에 Non Unique 인덱스를 생성한다.
$ db2 “create index lock_test_s01 on lock_test(id)”
T1 트랜잭션: (Agent ID 1313)
$ db2 +c “select * from lock_test where id=1 with rr“
DB20000I The SQL command completed successfully.
T2 트랜잭션: (Agent ID 292)
$ db2 “insert into lock_test values(1,'10')”
-- 현재의 Lock 대기 상태를 확인해보자.
$ db2 –tf lock_wait.sql
AGENT_ID LCK_MODE LCK_REQ LOCK_OBJECT_TYPE TABLE_NAME HOLDER_ID
-------- -------- ------- ------------------- ---------- ---------
292 S NW table row lock type LOCK_TEST 1313
* T1 트랜잭션(Agent ID 292)은 인덱스 RR 스캔을 수행한 결과, S 모드의 로우 락을 획득한 상태이고, S 모드와 T2 트랜잭션의 NW 모드는 호환성이 없으므로, T2 트랜잭션은 락을 대기하는 현상을 보인다.
Lock Mode | W (Weak Exclusive) |
Applicable Object | Rows |
Description | Unique 인덱스가 생성된 테이블에 대해, 중복 키 입력이 발생가능성을 막기 위해 용도로 사용된다. |
테스트를 위해 LOCK_TEST 테이블에 Non Unique 인덱스를 생성한 것을 DROP하고Unique 인덱스를 생성한다.
$ db2 “drop index lock_test_s01”
$ db2 “create unique index lock_test_uk on lock_test(id)”
T1 트랜잭션: (Agent ID 1313)
$ db2 +c “insert into lock_test values(3,’3’)“
DB20000I The SQL command completed successfully.
T2 트랜잭션: (Agent ID 292)
$ db2 “insert into lock_test values(3,’3’)“
-- 현재의 Lock 대기 상태를 확인해보자.
$ db2 –tf lock_wait.sql
AGENT_ID LCK_MODE LCK_REQ LOCK_OBJECT_TYPE TABLE_NAME HOLDER_ID
-------- -------- ------- ------------------- ---------- ---------
292 S W table row lock type LOCK_TEST 1313
* Unique 인덱스가 생성되어 있으므로, 중복 키 입력을 방지하기 위해 T2 트랜잭션은W 모드의 로우 락을 대기한다. T1 트랜잭션이 커밋을 수행할 경우 T2 트랜잭션은SQL0803N 에러코드를 발생시키며, 트랜잭션을 롤백하며, T1 트랜잭션이 롤백을 수행할 경우에 T2 트랜잭션은 락 대기 상태에서 벗어나서, 트랜잭션을 진행하게 된다.
[표 4.1] 락 호환성 테스트 결과 매트릭스
| None | IN | IS | NS | S | IX | SIX | U | X | Z | NW | W |
None | O | O | O | O | O | O | O | O | O | O | O | O |
IN | O | O | O | O | O | O | O | O | O | X | O | O |
IS | O | O | O | O | O | O | O | O | X | X | X | X |
NS | O | O | O | O | O | X | X | O | X | X | O | X |
S | O | O | O | O | O | X | X | O | X | X | X | X |
IX | O | O | O | X | X | O | X | X | X | X | X | X |
SIX | O | O | O | X | X | X | X | X | X | X | X | X |
U | O | O | O | O | O | X | X | X | X | X | X | X |
X | O | O | X | X | X | X | X | X | X | X | X | X |
Z | O | X | X | X | X | X | X | X | X | X | X | X |
NW | O | O | X | O | X | X | X | X | X | X | X | O |
W | O | O | X | X | X | X | X | X | X | X | O | X |
DB2 UDB V8부터 동시성 향상을 위해 DB2_EVALUNCOMMITTED, DB2_SKIPDELETED, DB2_SKIPINSERTED 라는 3개의 레지스트리 변수를 제공한다.이번 섹션에서는 각각의 레지스트리 변수의 의미와 적용 시의 효과를 살펴보기로 한다.
DB2_EVALUNCOMMITTED (DB2 UDB V8.1.4부터 지원)
DB2_EVALUNCOMMITTED는 CS(Cursor Stability) 또는 RS(Read Stability) Isolation레벨로 테이블 및 인덱스 스캔시에, 데이터가 검색 조건에 맞는지 먼저 체크한 후에, 조건에 맞는 경우에만 해당 레코드에 락을 거는 방식(defer row locking)을 사용한다. 따라서 조건에 맞지 않는 언커밋 상태의 레코드에 대한 스킵이 가능하다. 뿐만 아니라 테이블 스캔의 경우에는 언커밋 상태의 Deleted 로우에 대한 스킵도 가능하다. 하지만 인덱스 스캔의 경우에는 언커밋 상태의 Deleted 로우는 스킵이 불가능하다. 이것을 가능하게 하기 위해서는 뒤에서 설명할 DB2_SKIPDELETE를 ON으로 설정하여야 한다. 해당레지스트리 변수의 효과를 테스트를 통해서 확인해보자.
테스트 1: DB2_EVALUNCOMMITTED=OFF (기본설정 값)인 경우
T1 트랜잭션 (Agent ID 8)
$ db2 +c “insert into lock_test values(9,'9')”
DB20000I The SQL command completed successfully
T2 트랜잭션 (Agent ID 9)
$ db2 “select * from lock_test where id between 1 and 5 with cs”
*) T1 트랜잭션에서 입력한 레코드가 T2 트랜잭션의 쿼리 조건에 해당되지는 않는다.하지만, 현재 커서가 위치한 다음(Next) 레코드에 NS 모드의 로우 락을 획득한 후에 해당 레코드가 조건에 맞는지 체크하는 방식을 사용하므로, T2 트랜잭션은 락을 대기한다.
-- 현재의 락 대기상태 확인
$ db2 –tf lock_wait.sql
AGENT_ID LCK_MODE LCK_REQ LOCK_OBJECT_TYPE TABLE_NAME HOLDER_ID
-------- -------- ------- -------------------- ---------- ---------
9 X NS table row lock type LOCK_TEST 8
테스트 2: DB2_EVALUNCOMMITTED=ON으로 변경한 경우
--DB2_EVALUNCOMMITTED를 ON으로 설정
$ db2set DB2_EVALUNCOMMITTED=ON
--DB를 재 기동한다.
T1 트랜잭션 (Agent ID 8)
$ db2 +c “insert into lock_test values(9,'9')”
DB20000I The SQL command completed successfully
T2 트랜잭션 (Agent ID 9)
$ db2 “select * from lock_test where id between 1 and 5 with cs”
ID NAME
----------- ----------
1 1
2 2
3 3
5 5
4 record(s) selected
*) DB2_EVALUNCOMMITTED=ON 효과에 의해, T2 트랜잭션이 락 대기 없이 수행되었다. 즉, 현재 커서가 위치한 다음(Next) 레코드를 페치(fetch)한 후에 해당 레코드가 조건에 만족하는지 체크한 것이다. 만일 조건에 만족하지 않을 경우 해당 레코드에 대한 로우 락 획득이 필요 없으므로, 락 대기현상이 발생하지 않게 된다. 이러한 defer row locking 방식에는 여러 가지 제약 사항이 존재하지만, 동시성을 높일 수 있다는 것은 분명한 사실이다.
DB2_SKIPDELETED (DB2 UDB V8.1.4부터 지원)
DB2_SKIPDELETED는 CS (Cursor Stability) 또는 RS (Read Stability) Isolation 레벨로테이블 및 인덱스 스캔 시에, 언커밋 된 Deleted 로우를 스킵하는 것을 가능하게 한다.
테스트 1: DB2_SKIPDELETED=OFF (기본설정 값)인 경우
T1 트랜잭션 (Agent ID 7)
$ db2 +c “delete from lock_test where id=3”
DB20000I The SQL command completed successfully
T2 트랜잭션 (Agent ID 8)
-- 테이블 스캔을 수행하여 테스트를 해보자.
$ db2 “select * from lock_test”
ID NAME
----------- ----------
1 1
2 2
4 4
10 10
4 record(s) selected.
*) T1 트랜잭션에 의해 삭제된 레코드가 T2 트랜잭션의 쿼리 조건에 만족하지만, 테이블 스캔을 수행하므로, DB2_EVALUNCOMMITTED를 ON 시킨 효과에 의해 락 대기를하지 않고 결과가 출력된다. 즉, DB2_EVALUNCOMMITTED는 테이블 스캔에 대해 언커밋 Deleted 로우를 스킵하는 것을 가능하게 한다.
-- 인덱스 스캔을 수행하여 테스트를 해보자.
$ db2 “select * from lock_test where id between 1 and 4”
*) T2 트랜잭션에서 인덱스 스캔을 수행하므로 락 대기를 하게 된다.
즉, DB2_EVALUNCOMMITTED은 인덱스 스캔에 대한 언커밋 Deleted 로우를 스킵하는것은 불가능함을 알 수 있다.
-- 현재의 락 대기상태 확인
$ db2 –tf lock_wait.sql
AGENT_ID LCK_MODE LCK_REQ LOCK_OBJECT_TYPE TABLE_NAME HOLDER_ID
-------- -------- ------- ------------------- ---------- ---------
8 X NS table row lock type LOCK_TEST 7
테스트 2: DB2_SKIPDELETED=ON으로 변경한 경우
--DB2_SKIPDELETED를 ON으로 설정
$ db2set DB2_SKIPDELETED=ON
--DB를 재 기동한다.
T1 트랜잭션 (Agent ID 7)
$ db2 +c “delete from lock_test where id=3”
DB20000I The SQL command completed successfully
T2 트랜잭션 (Agent ID 8)
$ db2 “select * from lock_test where id between 1 and 4”
ID NAME
----------- ----------
1 1
2 2
4 4
3 record(s) selected
*) DB2_SKIPDELETED를 ON으로 설정함에 따라, 인덱스 스캔 시에도 언커밋 된Deleted 로우를 스킵하는 것이 가능하다.
DB2_SKIPINSERTED (DB2 UDB V8.2.2부터 지원)
DB2_SKIPDELETED는 CS (Cursor Stability) 또는 RS (Read Stability) Isolation 레벨로테이블 및 인덱스 스캔 시에, 언커밋 된 Inserted 로우를 스킵하는 것을 가능하게 한다.
테스트 1: DB2_SKIPINSERTED=OFF (기본설정 값)인 경우
T1 트랜잭션 (Agent ID 7)
$ db2 +c “insert into lock_test values(5,'5')”
DB20000I The SQL command completed successfully
T2 트랜잭션 (Agent ID 8)
$ db2 “select * from lock_test where id between 1 and 5”
*) T1 트랜잭션에서 입력한 레코드가 언커밋 상태이고, T2 트랜잭션 쿼리의 조건에 만족하므로, T2 트랜잭션은 락 대기를 하게 된다.
-- 현재의 락 대기상태 확인
$ db2 –tf lock_wait.sql
AGENT_ID LCK_MODE LCK_REQ LOCK_OBJECT_TYPE TABLE_NAME HOLDER_ID
-------- -------- ------- -------------------- ---------- ---------
8 X NS table row lock type LOCK_TEST 7
테스트 2: DB2_SKIPINSERTED=ON으로 변경한 경우
--DB2_SKIPINSERTED를 ON으로 설정
$ db2set DB2_SKIPINSERTED=ON
--DB를 재 기동한다.
T1 트랜잭션 (Agent ID 7)
$ db2 +c “insert into lock_test values(5,'5')”
DB20000I The SQL command completed successfully
T2 트랜잭션 (Agent ID 8)
$ db2 “select * from lock_test where id between 1 and 5”
ID NAME
----------- ----------
1 1
2 2
3 3
4 4
4 record(s) selected
*) DB2_SKIPINSERTED를 ON으로 설정함에 따라, 언커밋 된 Inserted 로우를 스킵하는것이 가능하다.
글을 마치며
앞선 내용에서 DB2 UDB의 Isolation level, Lock escalation, Lock Mode 및 동시성 향상을 위한 DB2 레지스트리 변수를 살펴보았다. DB Configuration 파라미터의 적절한 설정과, 잘 설계된 Application을 사용하는 사이트의 경우에는 Lock escalation이나, 락 대기현상과 같은 락 문제가 거의 발생하지 않을 수도 있을 것이다. 하지만 이러한 동작 원리에 대한 이해가 진정한 성능관리 전문가로 거듭나는 시발점이라고 생각하며 이 기사를통해 독자들이 궁금했던 부분에 대해 한가지라도 이해하는데 도움이 되었으면 하는 바램을 가지며 글을 마친다.
1. lock_info.sql
SELECT substr(char(agent_id),1,7) agent_id ,
CASE t4.lock_mode
WHEN 1
THEN 'Intention Share Lock'
WHEN 2
THEN 'Intention Exclusive Lock'
WHEN 3
THEN 'Share Lock'
WHEN 4
THEN 'Share with Intention Exclusive Lock'
WHEN 5
THEN 'Exclusive Lock'
WHEN 6
THEN 'Intent None (For Dirty Read)'
WHEN 7
THEN 'Super Exclusive Lock'
WHEN 8
THEN 'Update Lock'
WHEN 9
THEN 'Next-key Share Lock'
WHEN 10
THEN 'Next-key Exclusive Lock'
WHEN 11
THEN 'Weak Exclusive Lock'
WHEN 12
THEN 'Next-key Weak Exclusive Lock'
CASE t4.lock_object_type
WHEN 1 THEN 'table lock type'
WHEN 2 THEN 'table row lock type'
WHEN 3 THEN 'Internal lock type'
WHEN 4 THEN 'Tablespace lock type'
WHEN 5 THEN 'end of table lock'
WHEN 6 THEN 'key value lock'
WHEN 7 THEN 'Internal lock on the sysboot table'
WHEN 8 THEN 'Internal Plan lock'
WHEN 9 THEN 'Internal Variation lock'
WHEN 10 THEN 'Internal Sequence lock'
WHEN 11 THEN 'Bufferpool lock'
WHEN 12 THEN 'Internal Long/Lob lock'
WHEN 13 THEN 'Internal Catalog Cache lock'
WHEN 14 THEN 'Internal online Backup lock'
WHEN 15 THEN 'Internal Object Table lock'
WHEN 16 THEN 'Internal Table Alter lock'
WHEN 17 THEN 'Internal DMS Sequence lock'
WHEN 18 THEN 'Inplace reorg lock'
WHEN 19 THEN 'Block lock type'
ELSE char(t4.lock_object_type)
substr(t4.table_name,1,18) table_name
FROM TABLE( snapshot_lock( 'sample' , - 1 ) ) t4
WHERE table_name LIKE 'LOCK_ESCALS%';
2. lock_wait.sql
SELECT substr(char(t1.agent_id),1,8) agent_id,
CASE t4.lock_mode
WHEN 1 THEN 'IS'
WHEN 2 THEN 'IX'
WHEN 3 THEN 'S'
WHEN 4 THEN 'SIX'
WHEN 5 THEN 'X'
WHEN 6 THEN 'IN'
WHEN 7 THEN 'Z'
WHEN 8 THEN 'U'
WHEN 9 THEN 'NS'
WHEN 11 THEN 'W'
WHEN 12 THEN 'NW'
CASE t4.lock_mode_requested
WHEN 1 THEN 'IS'
WHEN 2 THEN 'IX'
WHEN 3 THEN 'S'
WHEN 4 THEN 'SIX'
WHEN 5 THEN 'X'
WHEN 6 THEN 'IN'
WHEN 7 THEN 'Z'
WHEN 8 THEN 'U'
WHEN 9 THEN 'NS'
WHEN 11 THEN 'W'
WHEN 12 THEN 'NW'
CASE t4.lock_object_type
WHEN 1 THEN 'table lock'
WHEN 2 THEN 'table row lock type'
WHEN 3 THEN 'Internal lock type'
WHEN 4 THEN 'Tablespace lock type'
WHEN 5 THEN 'end of table lock'
WHEN 6 THEN 'key value lock'
WHEN 7 THEN 'Internal lock on the sysboot table'
WHEN 8 THEN 'Internal Plan lock'
WHEN 9 THEN 'Internal Variation lock'
WHEN 10 THEN 'Internal Sequence lock'
WHEN 11 THEN 'Bufferpool lock'
WHEN 12 THEN 'Internal Long/Lob lock'
WHEN 13 THEN 'Internal Catalog Cache lock'
WHEN 14 THEN 'Internal online Backup lock'
WHEN 15 THEN 'Internal Object Table lock'
WHEN 16 THEN 'Internal Table Alter lock'
WHEN 17 THEN 'Internal DMS Sequence lock'
WHEN 18 THEN 'Inplace reorg lock'
WHEN 19 THEN 'Block lock type'
ELSE char(t4.lock_object_type)
substr(t4.table_name,1,10) table_name,
substr(char(t4.agent_id_holding_lk),1,8) AS holder_id
FROM TABLE( snapshot_appl_info( 'sample' , -1)) t1 ,
TABLE( snapshot_appl( 'sample' , -1 )) t2,
TABLE( snapshot_statement( 'sample', -1)) t3,
TABLE( snapshot_lockwait( 'sample', -1)) t4
WHERE t1.agent_id=t2.agent_id
AND t1.agent_id=t3.agent_id
AND t1.agent_id=t4.agent_id;
참고 문헌
DB2 UDB V8.2 Administration Guide :Performance
Lock avoidance in DB2 UDB V8 Improving concurrency with new registry variables
Diagnosing and Resolving Lock Problems with DB2 Universal Database
KDUG- DB2 UDB의 Locking 매커니즘과 동시성 제어: Part 2 – Lock Mode와 Lock 호환성 테스트
KDUG- DB2 UDB의 Locking 매커니즘과 동시성 제어: Part 3 – 레지스트리 변수 및 Case별 테스트
'Db2 > Db2 reference' 카테고리의 다른 글
| HADR 구성 순서 (0) | 2011.09.27 |
|---|---|
| link: db2top (0) | 2011.09.18 |
| tablespace 컨테이너 추가할 때의 주의사항 (0) | 2011.04.24 |
| DB2 9.7에서 Oracle 기능을 사용하기 위한 순서 (0) | 2011.04.21 |
| 오늘 날짜 조회하기 (0) | 2011.04.21 |