기술자료
DBMS, DB 구축 절차, 빅데이터 기술 칼럼, 사례연구 및 세미나 자료를 소개합니다.
DB2 UDB의 Locking 매커니즘과 동시성 제어: Part 3- 레지스트리 변수 및 Case별 테스트 DB2 UDB V8부터 동시성 향상을 위해 DB2_EVALUNCOMMITTED, DB2_SKIPDELETED, DB2_SKIPINSERTED 라는 3개의 레지스트리 변수를 제공한다. 이번 섹션에서는 각각의 레지스트리 변수의 의미와 적용 시의 효과를 살펴보기로 한다. DB2_EVALUNCOMMITTED (DB2 UDB V8.1.4부터 지원) 테스트 1: DB2_EVALUNCOMMITTED=OFF (기본설정 값)인 경우 테스트 2: DB2_EVALUNCOMMITTED=ON으로 변경한 경우 DB2_SKIPDELETED (DB2 UDB V8.1.4부터 지원) 테스트 1: DB2_SKIPDELETED=OFF (기본설정 값)인 경우 테스트 2: DB2_SKIPDELETED=ON으로 변경한 경우 DB2_SKIPINSERTED (DB2 UDB V8.2.2부터 지원) 테스트 1: DB2_SKIPINSERTED=OFF (기본설정 값)인 경우 글을 마치며DB2 UDB의 Locking 매커니즘과 동시성 제어: Part 3 레지스트리 변수 및 Case별 테스트
$ db2 +c “insert into lock_test values(9,'9')”
DB20000I The SQL command completed successfullyT2 트랜잭션 (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
$ db2set DB2_EVALUNCOMMITTED=ON
--DB를 재 기동한다.T1 트랜잭션 (Agent ID 8)
$ db2 +c “insert into lock_test values(9,'9')”
DB20000I The SQL command completed successfullyT2 트랜잭션 (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 +c “delete from lock_test where id=3”
DB20000I The SQL command completed successfullyT2 트랜잭션 (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
$ db2set DB2_SKIPDELETED=ON
--DB를 재 기동한다.T1 트랜잭션 (Agent ID 7)
$ db2 +c “delete from lock_test where id=3”
DB20000I The SQL command completed successfullyT2 트랜잭션 (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 +c “insert into lock_test values(5,'5')”
DB20000I The SQL command completed successfullyT2 트랜잭션 (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 successfullyT2 트랜잭션 (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 로우를 스킵하는 것이 가능하다.
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'
END AS lock_mode,
CASE t4.lock_object_type
WHEN 1 THEN 'table lock type'
WHEN 2 THEN 'table row lock type'
WHEN 3THEN 'Internal lock type'
WHEN 4 THEN 'Tablespace lock type'
WHEN 5 THEN 'end of table lock'
WHEN 6 THEN 'key value lock'
WHEN 7THEN '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)
END AS 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'
END AS lck_mode,
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'
END AS lck_req,
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)
END AS 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
제공 : DB포탈사이트 DBguide.net