기술자료

DBMS, DB 구축 절차, 빅데이터 기술 칼럼, 사례연구 및 세미나 자료를 소개합니다.

DB2 UDB의 Locking 매커니즘과 동시성 제어: Part 3 레지스트리 변수 및 Case별 테스트

기술자료
DBMS별 분류
DB2
작성자
admin
작성일
2021-02-23 15:24
조회
1521

DB2 UDB의 Locking 매커니즘과 동시성 제어: Part 3- 레지스트리 변수 및 Case별 테스트

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 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

테스트 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 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_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 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

테스트 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 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_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 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 로우를 스킵하는 것이 가능하다.

글을 마치며

앞선 내용에서 DB2 UDB의 Isolation level, Lock escalation, Lock Mode 및 동시성 향상을 위한 DB2 레지스트리 변수를 살펴보았다. DB Configuration 파라미터의 적절한 설정과, 잘 설계된 Application을 사용하는 사이트의 경우에는 Lock escalation이나, 락 대기 현상과 같은 락 문제가 거의 발생하지 않을 수도 있을 것이다. 하지만 이러한 동작 원리에 대한 이해가 진정한 성능관리 전문가로 거듭나는 시발점이라고 생각하며 이 기사를 통해 독자들이 궁금했던 부분에 대해 한가지라도 이해하는데 도움이 되었으면 하는 바램을 가지며 글을 마친다.APPENDIX
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