from Practical OWI | exem | exem
lock type - tx
tx는 ROW에 대해 걸리는 LOCK 이다.
TX Lock 경합 발생 원인으로
동일 Row 변경
Unique Key 충돌
ITL Entry 부족
Bitmap Index
Index Leaf Node Split
기타 등의 이유가 있다.
테스트는 9i 에서 확인하였다. v$lock 은 9i에도 있다. ( v$lock_type은 10g에만 있다. )
/*+ 9i 와 10g 비교 해 볼 것 */
각각의 예를 보기 전에 필요한 view 에 대해 이야기 하고자 한다. tx 락 경합발생 확인을 위해 v$lock view를 사용할 것이며 아래와 같은 쿼리를 사용할 것이다.
SQL> get w_cur_lck
1 select sid,type,id1,id2,lmode,request,ctime,block
2 from v$lock
3* where type in ( 'TX', 'TM')
lock type이 TX 나 TM 인 경우를 찾는 쿼리이다.
동일 Row 변경
SESSION #1 (SCOTT)
SQL> update emp set sal=1111 where empno=7788;
1 row updated.
SESSION #2 (SCOTT)
SQL> update emp set sal=9999 where empno=7788;
(SESSION#1 을 KILL 하거나 COMMIT/ROLLBACK 할때까지 대기상태에 빠진다.)
SESSION #3 (SYS)
SQL> get w_cur_lck
1 select sid,type,id1,id2,lmode,request,ctime,block
2 from v$lock
3* where type in ( 'TX', 'TM')
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
14 TX 196621 2609 6 0 71 1
14 TM 6432 0 3 0 71 0
16 TM 6432 0 3 0 1 0
16 TX 196621 2609 0 6 1 0
LMODE 컬럼은 LOCK MODE를 의미한다
- 0 - none
- 1 - null (NULL)
- 2 - row-S (SS)
- 3 - row-X (SX)
- 4 - share (S)
- 5 - S/Row-X (SSX)
- 6 - exclusive (X)
REQUEST의 숫자도 위의 정보를 의미하며 이는 해당 세션이 숫자에 해당하는 모드로 LOCK을 획득하려 한다는 의미이다.
위에서 SID가 14인 세션은 TX LOCK을 EXCLUSIVE 하게 획득하였다. 반대로 SID가 16일 세션은 TX LOCK을 EXCLUSIVE 하게 획득하려 한다. ( REQUEST )
CTIME은 현재 상태를 가진 이래로 지난 시간을 의미한다. (쿼리를 반복실행 한 결과 단위는 ‘초’ 인 듯 하다. )
BLOCK 값의 1은 해당 LOCK이 다른 LOCK을 차단하고 있다는 것을 의미한다. (LOCK HOLDER)
Unique Key 충돌
전제 : insert 의 타겟이 되는 컬럼에 unique 키 조건이 설정되어 있어야 한다.
Session #1
SQL> insert into emp(empno) values(8899);
1 row created.
Session #2
SQL> insert into emp(empno) values(8899);
(대기상태에 빠짐…)
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
9 TX 65541 2097 6 0 31 0
9 TM 6432 0 3 0 31 0
9 TX 524306 1905 0 4 31 0
10 TX 524306 1905 6 0 62 1
10 TM 6432 0 3 0 62 0
SID 9 인 세션이 EXCLUSIVE 로 TX를 EXCLUSIVE(6) 로 획득하였다. SID 10인 세션이 TX락을 SHARED 획득하려 함
SID 9 인 세션은 아직 TRANSACTION 이 종료 (COMMIT/ROLLBACK) 되지 않았기 때문에 이에 접근하는 타 세션은 대기하게 된다.
위 상태에서 Rollback 하면 대기하던 세션의 트랜잭션이 성공하고 반대로 commit 하면 unique 키 제약조건에 위배된다는 에러 메시지를 볼 수 있다.
Session #1 ( commit 의 경우 )
SQL> insert into emp(empno) values(8899);
1 row created.
<< Session #2 대기상태 >>
SQL> commit;
Commit complete.
Session #2 ( rollback 의 경우 )
SQL> insert into emp(empno) values(8899);
insert into emp(empno) values(8899)
<<대기상태>>
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.EMP_EMPNO_UK_IDX) violated
ITL Entry 부족
SQL> select table_name, ini_trans, max_trans
2 from user_tables;
TABLE_NAME INI_TRANS MAX_TRANS
------------------------------ ---------- ----------
BONUS 1 255
DEPT 1 255
DUMMY 1 255
EMP 1 255
SALGRADE 1 255
maxtrans 값을 낮춰준다.
SQL> alter table emp maxtrans 2;
Table altered.
maxtrans 값은 ITL 엔트리 수와 관계있다. 이를 할당 받지 못하면 DML 수행이 대기상태에 빠진다. ITL ( Interested Transaction List ) 은 BLOCK을 변경하고자 하는 Transaction 목록을 가지고 있으며 DML 트랜잭션은 블록을 변경하기 전 ITL 에 엔트리를 등록해야 한다.
/*+ INITRANS MAXTRANS 와 ITL 의 정확한 관계에 대해서… */
Session #1 ( USER )
SQL> update emp set ename='hello' where empno=1;
1 row updated.
Session #2 ( USER )
SQL> update emp set ename='hello' where empno=2;
1 row updated.
Session #3 ( USER )
SQL> update emp set ename='hello' where empno=1;
(대기상태에 빠짐)
Session #4 (SYS)
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
9 TX 393220 3826 6 0 23 0
9 TM 6432 0 3 0 23 0
10 TX 131081 2085 6 0 31 1
10 TM 6432 0 3 0 31 0
12 TM 6432 0 3 0 19 0
12 TX 131081 2085 0 4 15 0
SID 9,10 에 해당하는 SESSION이 TX락을 EXCLUSIVE 하게 잡았고 이후 12번이 TX를 SHARE 모드로 요청하였으나 WAIT 에 빠지게 된다. BLOCKER는 SID 10번의 SESSION 으로 나온다. 사실 10 뿐만 아니라 9번도 ITL 블록을 잡고 있기 때문에 BLOCKING 에 영향을 주고 있다.
해결안으로써 INITRANS 을 높이고 MAXTRANS 값을 낮추지 말 것 마지막으로 ROW MIGRATION과 ROW CHAINING 수치를 낮출 것 등이다. 후자의 경우 하나의 ROW UPDATE에 여러 블록의 ITL ENTRY를 할당하기 때문에 경합이 발생할 확률이 높아진다.
MAXTRANS 값의 경우 10g에서 255로 정해져있다. ( ALTER 명령으로 변경가능 )
위 상태 ( initrans 1, maxtrans 2, session 1, 2, 3 dml 실행 session 3 대기상태 ) 에서 3번 세션을 제외한 세션에서 commit 이나 rollback 을 수행하면 wait 상태였던 3번 세션이 작업을 다시수행하게 된다.
Bitmap
Index
샘플 세그먼트를 만들겠다. GENDER 값을 (1, 0, 1, 0, … 번갈아 가지게 하였다.)
SQL> create table bit
2 (id number,
3 gender number);
Table created.
SQL> begin
2 for i in 1..1000 loop
3 insert into bit(id) values(i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> update bit set gender = 1;
1000 rows updated.
SQL> begin
2 for i in 1..500 loop
3 update bit set gender=0 where id = (i * 2);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> create bitmap index bit_gender_bidx on bit(gender);
Index created.
Session #1 ( USER )
SQL> update bit set gender = 0 where id = 1;
1 row updated.
Session #2 ( USER )
SQL> update bit set gender = 0 where id = 3;
(대기 상태로 빠짐)
Session #3 ( SYS )
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
10 TX 131082 2073 6 0 12 0
10 TM 6460 0 3 0 12 0
10 TX 65554 2097 0 4 12 0
16 TX 65554 2097 6 0 55 1
16 TM 6460 0 3 0 55 0
SID가 16번인 세션이 BLOCKER 이다. TX LOCK을 EXCLUSIVE로 획득하였고 후에 10번인 세션이 TX LOCK 을 SHARE로 요구하였다. 이미 EXCLUSIVE로 LOCK이 잡혀있기 때문에 SHARE 모드는 대기할 수밖에 없으며 이는 16번 세션이 트랜잭션을 종료(COMMIT , ROLLBACK)하기 전까지 지속된다. 이와 같은 증상이 나타나는 이유는 BITMAP INDEX의 LEAF 노드가 COLUMN 값 뿐만 아니라 비트맵과 START ROWID ~ END ROWID 값을 가지기 때문이다. ROW에 변경 ( 비트맵을 사용하는 해당컬럼 ) 이 일어나면 리프노드의 비트맵을 매번 새로 계산해주어야 한다. 이때 TX락을 획득하게 되어 이후 오는 트랜잭션들은 대기하게 된다.
Index
Leaf Node Split
Index leaf Node 에 split 이 발생시 다른 프로세스는 완료시까지 대기상태
/*+ 아직 샘플을 만들지 못했음 */
/*+ split 에 대한 이야기 */
