DB2 UDB의 Locking 매커니즘과 동시성 제어:Part 2 - Lock Mode와 Lock 호환성 테스트

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 환경의 테스트를 통해 설명하였다.<테스트 1>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 ur”
* 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 모드의 테이블 락을 획득한다는 것을 간접적으로 확인할 수 있다.
<테스트 2>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 모드의 테이블 락을 획득한다는 것을 확인할 수 있다.<테스트 3>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 모드의 로우 락을 대기하는 것을 알 수 있다.
<테스트 4>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 모드의 테이블 락을 대기하는 것을 알 수 있다
<테스트 5>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 모드의 테이블 락을 대기하는 것을 알 수 있다.
<테스트 6>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 모드의 테이블 락을 대기하는 것을 알 수 있다.
<테스트 7>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 모드의 로우 락을 대기하는 것을 알 수 있다.
<테스트 8>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 트랜잭션은 락을 대기하는 현상을 보인다.<테스트 9>테스트를 위해 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] 락 호환성 테스트 결과 매트릭스
제공 : DB포탈사이트 DBguide.net