1. DML Lock
   1. TX(Transaction) Locks
   2. TM (DML Enqueue) Locks

DML Lock

• DML Lock은 하나의 행을 한 번에 한 사람이 수정하는 것과 여러분이 일하고 있을 때 테이블을 Drop 할 수 없게 하기 위하여 사용된다.
• 오라클은 여러분들에게 이런 Lock들을 더욱 더 명확하게 보여줄 것이다.

TX(Transaction) Locks

• TX Lock은 트렌젝션이 처음 변경이 생길 때 획득되고 그것은 트렌젝션이 COMMIT 또는 ROLLBACK이 수행될 때까지 수행된다.
• 그것은 queuing 메커니즘으로 사용되기 때문에 다른 세션에서는 트렌젝션이 완벽히 수행될 때까지 기다릴 수 있다.
• 여러분은 각각 모든 행을 수정하거나 트렌젝션 안에 SELECT FOR UPDATE를 트렌젝션 동안에 관련된 TX Lock을 지정할 수 있다.
• 이것은 비싼 반면에 그렇지 않다. 이유를 이해하기 위해서는 여러분은 "live"가 어디에서 lock이 되는지 근본적인 이유와 그것들을 어떻게 관리하는지 알아야 한다.
• 오라클에서는, lock들은 데이터의 속성으로 저장된다. 오라클은 시스템 안에 lock이 수행된 모든 행의 긴 리스트를 유지하는데 전형적인 lock * 관리를 가지고 있지 않다.
• 많은 다른 데이터베이스들은 이 방식을 가지는데 그 이유는 lock들은 모니터링 하는데 사용하는 드문 리소스이기 때문이다.
• 더 많은 lock들이 사용되고 더 많은 시스템들에서 관리되기 때문에 그것은 만일 "too many" lock들이 사용된다면 시스템에서 주요 관심 대상이 된다.
• 전형적인 메모리 기반의 lock을 관리하는 데이터베이스에서 한 행의 locking 과정은 다음과 유사하다.

1. Lock을 원하는 행의 리스트를 찾는다.
2. Lock manager의 리스트(list)를 얻는다. (그것은 메모리 기반 구조이기 때문에 반드시 번호로 나열되어 있다.)
3. 리스트(list)를 잠근다.
4. 만일 어떤 사람이 또한 이 행을 잠근다면, 찾기 위하여 리스트를 조사한다.
5. 열의 잠금을 성립하기 위하여 리스트 안에 새로운 Entry를 생성한다.
6. 리스트의 잠금을 푼다. 위와 같이 여러분은 행을 잠글 수 있고 수정할 수 있다. 후에, 변경사항을 commit 할 때 여러분은 다음의 절차를 계속해야 한다.
7. 리스트를 다시 얻는다.
8. lock들의 리스트를 잠근다.
9. 리스트를 조사하고 lock들을 해제한다.
10. 리스트의 잠금을 푼다.

• 여러분이 보는 것과 같이 더 많은 lock들이 획득되고, 더 많은 시간이 데이터를 수정 전/후에 소요된다.
• 오라클은 이와 같은 방식을 사용하지 않는다. 오라클의 과정은 다음과 같다.
  • Lock을 원하는 행의 리스트를 얻는다.
  • 행에게 간다.
  • 행을 잠근다. (만일 이미 잠금 상태이면 잠금 상태가 종료될 때까지 기다린다. 만일 그렇지 않으면 NOWAIT 기능을 사용한다.)

• Lock이 데이터의 속성으로서 저장되기 때문에 오라클은 전형적인 lock manager가 필요하지 않다.
• 이 트렌젝션은 단순히 트렌젝션에 수행되고 그것을 잠근다. 이 흥미로운 것은 여러분이 그것을 획득할 때 잠금이 나타날 것이다.
• 비록 그렇지 않더라도 말이다. 여러분이 데이터의 행들을 잠글 때 행은 데이터를 포함하고 있는 블락을 저장하고 있는 트렌젝션 ID의 복사본을 가르칠 것이다.
• 그리고 lock이 해제될 때 트랜젝션 ID는 왼쪽에 위치한다.
• 이 트렌젝션 ID는 여러분의 트렌젝션에서 유일하고 rollback segment number, slot, sequence number를 나타낸다.
• 여러분은 다른 세션들에서 자신의 데이터라고 말할 수 있는 행을 포함하는 블락을 남긴다.
• 다른 세션들이 올 때, 그것은 Lock ID와 그것이 트렌젝션을 나타내는 사실의 사용을 보여줄 때, 그것은 만일 트렌젝션을 잡고 있는 lock이 아직도 살아있다면 빠르게 보여줄 수 있다.
• 만일 lock이 active 상태가 아니면 세션은 lock이 해제되자마자 변경사항을 세션에게 통보할 것이다.
• 그러므로 여러분은 queuing mechanism을 가진다.
• Lock를 요청하는 세션은 트렌젝션이 완료될 때까지 대기를 유지할 것이고 그것은 데이터를 획득할 것이다.
• 여기에 이것이 어떻게 일어나는 것에 대하여 V$table를 사용하여 보여줄 예제가 있다.

• V$TRANSACTION은 모든 활성 중인 트렌젝션에 대한 entry를 포함한다.
• V$SESSION은 세션들이 로그인 되는 것을 보여준다.
• V$LOCK은 lock을 기다리는 세션들에 대한 잠금을 유지하고 있는 모든 enqueue lock에 대한 entry를 포함한다. 여러분은 세션에 의하여 이 테이블 인에 잠긴 모든 열에 대한 이 관점에서 볼 수 없을 것이다. 전에 언급한 것처럼, row level lock의 마스터 리스트는 존재하지 않는다. 만일 세션이 잠긴 EMP 테이블의 한 행을 가지고 있다면, 거기에는 세션이 가르치는 관점 안에서 하나의 행들만 있다. 만일 세션이 잠긴 EMP 테이블 안에 백만개의 행들을 가지고 있다면, 거기에는 아직도 단지 하나의 행만 있다. 이 관점은 enqueue lock이 개인적인 세션을 가지고 있는 것을 보여준다.

• 첫 번째, 트렌젝션을 시작한다. (만일, DEPT 테이블이 없다면, 간단히 하나를 만들어라.)

SQL> update dept set deptno = deptno+10;
4 rows updated.

• 지금, 이 관점에서 시스템의 상태를 본다. 이 예제는 단일 사용자 시스템으로 가정한다. ; 반면에 여러분은 V$TRANSACTION 테이블 안에 많은 행들을 볼 수 있다. 심지어 단일 사용자 시스템에서 V$TRANSACTION 안에 하나의 행보다 많이 본다고 해서 놀라지 마라. 백그라운드 오라클 프로세스들이 수행되어지기 때문일 것이다.

SQL> select username,
2         v$lock.sid,
3         trunc(id1/power(2,16)) rbs,
4         bitand(id1,to_number('ffff','xxxx'))+0 slot,
5         id2 seq,
6         lmode,
7         request
8  from v$lock, v$session
9  where v$lock.type = 'TX'
10    and v$lock.sid = v$session.sid
11    and v$session.username = USER;
USERNAME   SID RBS SLOT    SEQ LMODE REQUEST
--------- ---- --- ---- ------ ----- -------
OPS$TKYTE  145   4   12  16582     6       0
SQL> select XIDUSN, XIDSLOT, XIDSQN
2    from v$transaction;
XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
4         12      16582

여기에 기록된 흥미로운 사실은 다음과 같다.

• LMODE V$LOCK 테이블 안에 6이고 요청은 '0'이다. 만일 여러분이 Oracle Server Reference 매뉴얼 안에 V$LOCK의 정의를 참조하고 있다면, 여러분은 LOMODE=6의 exclusive lock을 발견할 수 있다. 요청 안에 '0'의 값은 여러분이 요청을 만들지 않았다는 것을 의미한다. ; 여러분은 lock을 가진다.
• 거기에는 테이블 안에 단지 하나의 행만 있다. 이 V$LOCK 테이블은 lock 테이블 보다 더 많은 queuing table을 가진다. 많은 사람들이 거기에는 우리가 4개의 열이 잠기었기 때문에 V$LOCK 안에 4개의 열들이 있을 것으로 기대한다. 그러나, 오라클은 어디에서나 잠긴 모든 행의 마스터 리스르를 저장하지 않는 것을 기억해야 한다. 만일 행이 잠긴 것을 확인하기 위해서는 우리는 행을 나가야 한다.
• 나는 ID1과 ID2 컬럼을 가졌고 그것 위에서 어떤 조작이 수행되었다. 오라클은 3개의 16 비트 넘버들을 저장하는 것을 필요로 하였을 뿐만 아니라 그것을 하기 위하여 2개의 컬럼들을 가졌다. 그래서 첫 번째 컬럼 ID1은 이 숫자들의 2개응 잡았다. trunc(id1/power(2,16)) rbs 와 함께 2^16에 의하여 나누어지고 bitand(id1, to_number('ffff','xxxx'))+0 slot와 함께 높은 비트가 masking 됨에 따라서 나는 하나의 숫자를 숨겨 2개의 숫자를 돌릴 수 있었다.
• RBS, SLOT와 SEQ 값들은 V$TRANSACTION 정보와 일치한다. 이것이 트렌젝션 ID이다.
• 지금 우리는 같은 사용자 이름을 사용하여 다른 세션을 시작할 수 있고 EMP 안에 어떤 행을 수정하고 DEPT 테이블 수정을 시도할 것이다.

SQL> update emp set ename = upper(ename);
14 rows updated.
SQL> update dept set deptno = deptno-10;

• 우리는 이 세션 안에 지금 블락되었다. 만일 우리가 V$ 쿼리들을 다시 시도한다면, 우리는 다음을 볼 수 있다.

SQL> select username,
2         v$lock.sid,
3         trunc(id1/power(2,16)) rbs,
4         bitand(id1,to_number('ffff','xxxx'))+0 slot,
5         id2 seq,
6         lmode,
7         request
8  from v$lock, v$session
9  where v$lock.type = 'TX'
10    and v$lock.sid = v$session.sid
11    and v$session.username = USER;
USERNAME   SID RBS SLOT    SEQ LMODE REQUEST
--------- ---- --- ---- ------ ----- -------
OPS$TKYTE  144   4   12  16582     0       6
OPS$TKYTE  144   5   34   1759     6       0
OPS$TKYTE  145   4   12  16582     6       0

SQL> select XIDUSN, XIDSLOT, XIDSQN
2    from v$transaction;
XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
5         34       1759
4         12      16582

• 우리는 여기에서 (5,34,1759)의 트렌젝션 ID와 함께 새로운 트렌젝션 ID가 무엇인지 볼 수 있다. 우리의 새로운 세션 SID=144은 이 시점에 V$LOCK 안에 2개의 열을 가진다. 하나의 열은 그것이 (where LMODE=6)을 소유하고 있는 lock들을 나타낸다. 그것은 또한 '6'의 값과 함께 REQUEST를 보여주는 하나의 행을 가진다. 여기에서 흥미로운 사실은 요청된 열의 RBS/SLOT/SEQ가 lock의 holder의 트렌젝션 ID라는 것이다. SID=145와 함께 트렌젝션은 SID=144와 함께 트렌젝션을 막는다. 우리는 V$LOCK의 self-join을 함으로서 명백히 볼 수 있다.

SQL> select
2        (select username from v$session where sid=a.sid) blocker,
3         a.sid,
4        ' is blocking ',
5         (select username from v$session where sid=b.sid) blockee,
6             b.sid
7    from v$lock a, v$lock b
8   where a.block = 1
9     and b.request > 0
10     and a.id1 = b.id1
11     and a.id2 = b.id2;
BLOCKER    SID 'ISBLOCKING'  BLOCKEE    SID
--------- ---- ------------- --------- ----
OPS$TKYTE  145  is blocking  OPS$TKYTE  144

• 지금 만일 우리가 원래 SID=145의 트렌젝션을 commit 한고, 우리의 lock 쿼리를 재실행 하면 우리는 요청된 열을 발견할 수 있다.

SQL> select username,
2         v$lock.sid,
3         trunc(id1/power(2,16)) rbs,
4         bitand(id1,to_number('ffff','xxxx'))+0 slot,
5         id2 seq,
6         lmode,
7         request
8  from v$lock, v$session
9  where v$lock.type = 'TX'
10    and v$lock.sid = v$session.sid
11    and v$session.username = USER;
USERNAME   SID RBS SLOT    SEQ LMODE REQUEST
--------- ---- --- ---- ------ ----- -------
OPS$TKYTE  144   5   34   1759     6       0
SQL> select XIDUSN, XIDSLOT, XIDSQN
2    from v$transaction;
XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
5         34       1759

• 요청된 행은 다른 세션이 그것의 lock을 포기하면 순간 사라진다. 요청된 행은 queuing mechanism이다. 데이터베이스는 트렌젝션이 일시적으로 완결될 때 블락된 세션들의 중요성을 알고 있다. 다양한 GUI 툴들과 함께 무한히 "pretty" 가 있으나, 여러분이 보기를 원하는 테이블의 지식을 획득하는 것은 매우 유용하다.
• 그러나, 우리가 오라클 작업 안에 잠긴 행을 어떻게 잘 이해하는 것을 말할 수 있기 전에, 우리는 반드시 마직만 토픽을 봐야 한다. ; 어떻게 locking과 트렌젝션 정보가 데이터와 함께 관리되는 것을 알아야 한다. 그것은 block overhead 파트이다. 쳅터 9 안에, 우리는 블락 형식의 세부사항을 볼 것이나, 블락을 위하여 트렌젝션 테이블을 저장하기 위하여 "overhead"공간이 데이터베이스 블락의 정상에서 어떤 것을 말하는지 이해를 해야 한다. 이 트렌젝션 테이블은 블락 안에 어떤 데이터가 블락 되었는지 각각 "real" 트렌젝션에 대한 entry이다. 이 구조의 사이즈는 객체에 대하여 CREATE 문장 위에 2개의 물리적인 속성 파라메터들에 위하여 조정된다.

• INITRANS : 초기에 이 구조에 미리 할당된 사이즈. 인덱스와 테이블에 대하여 기본값은 '2'이다.
• MAXTRANS : 이 구조가 최대로 늘어날 수 있는 사이즈이다. 기본 '255'와 최소 '2'이다. 오라클 10g에서 이 설정은 반대되어서, 그것은 더 이상 적용되지 않는다. MAXTRANS은 이 버전에서 불과하고 '255'이다.

• 각각 블락은 기본적으로 2개의 트렌젝션 slots으로 시작한다. 블락이 가질 수 있는 동시에 활성화된 트렌젝션의 숫자는 MAXTRANS의 값과 블락 위에 이용할 수 있는 공간에 의하여 결정된다. 여러분은 만일 거기에 구조를 늘릴 수 있는 충분한 공간이 없다면 블락 위에 255개의 동시 트렌젝션을 성취할 수 없을 것이다.
• 우리는 설정된 MAXTRANS와 함께 테이블을 생성하는 작업 방법을 인의적으로 선언할 수 있다. 우리는 오라클 9i을 사용하는 것이 필요하거나 이 전에 오라클 10g에서는 MAXTRANS가 무시되었다. 오라클 10g에서 비록 MAXTRANS가 설정되었어도 오라클은 블락 위에 공간을 생성할 수 있는 동안 트렌젝션 테이블을 늘릴 수 있을 것이다. 오라클 9i와 그 전에 MAXTRANS 값이 비록 블락에 도달했더라도 트렌젝션은 테이블은 늘어나지 않을 것이다 아래는 그 예제이다.

ops$tkyte@ORA9IR2> create table t ( x int ) maxtrans 2;
Table created.
ops$tkyte@ORA9IR2> insert into t select rownum from all_users;
24 rows created.
ops$tkyte@ORA9IR2> commit;
Commit complete.
ops$tkyte@ORA9IR2> select distinct dbms_rowid.rowid_block_number(rowid) from t;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
18

• 그래서 우리는 24개의 열을 가지고 그것들이 모두 같은 데이터베이스 블락 위에 있는 검증하였다,. 지금 우리는 하나의 세션을 이슈 한다.

ops$tkyte@ORA9IR2> update t set x = 1 where x = 1;
1 row updated.

• 그리고 다른 하나, 우리는 이슈 한다.

ops$tkyte@ORA9IR2> update t set x = 2 where x = 2;
1 row updated.

• 마지막으로 세 번째 세션, 우리는 이슈 한다.

ops$tkyte@ORA9IR2> update t set x = 3 where x = 3;

• 지금, 세 개의 행들이 같은 데이터베이스 블락에 있기 때문에 그리고 우리가 MAXTRANS을 '2'로 설정했기 대문에 세 번째 세션을 잠길 것이다.
• Note 오라클 10g에서 이 blocking은 예제 안에서와 같이 일어나지 않을 것을 기억해라. - MAXTRANS은 그럼에도 불구하고 '255'로 설정한다. 이 버전에서는 blocking을 보기 위해서는 트렌젝션 테이블을 늘리기 위하여 블락 위에 불충분한 공간을 가질 것이다.

• 이 예제에서 하나의 MAXTRANS 트렌젝션 보다 많이 동시에 같은 블락에 접근을 시도할 때 무엇이 일어나는지 보여준다.
• 유사하게 blocking은 또한 만일 INTRANS가 적게 설정되고 거기에 트렌젝션을 동적으로 확장할 충분한 공간이 없으면 발생할 것이다.
• 이와 같은 대부분의 예제에서 INITRANS에 대한 기본값 '2'는 충분하다. 트렌젝션 테이블이 동적으로 성장하기 때문이다.
• 그러나, 어떤 환경에서 여러분은 동시성을 증가시키고 waits를 줄이는 설정을 증가할 필요성을 느낄 것이다.
• 예를 들어 여러분이 테이블을 할 필요성을 느끼거나 심지어는 종종 변경되는 인덱스가 변경되는 필요할 때의 예제이다.
• 여러분은 예상되는 동시 트렌젝션에 대한 블락 위에 충분한 공간 ㄱ시간에 앞서 위치 한 PCTFREE나 INITRANS를 증가할 필요성을 느낄 것이다.
• 이것은 만일 여러분이 블락들이 거의 가득 차는 것이 예상 된다면 특별히 사실이다.
• 거기에는 블락 위에 트렌젝션 테이블의 동적인 확장에 대한 공간이 충분하지 않다는 것을 의미한다.

TM (DML Enqueue) Locks

• TM Locks는 여러분이 내용을 수정하는 동안에 데이터의 구조를 변경되지 않을 때 확인하는데 사용된다. 예를 들어, 만일 여러분이 테이블을 수정한다면, 여러분은 테이블 위에 TM lock을 요구할 것이다. 이것은 테이블 위에 DROP 이나 ALTER 명령어를 실행하는 것으로부터 다른 사용자를 지켜줄 것이다. 만일 다른 사용자가 여러분이 TM lock을 가지고 있는 동안에 테이블 위에 DDL을 수행하는 것을 시도한다면 다음과 같은 에러 메시지를 받을 것이다.

drop table dept
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

• 이것은 거기에는 DROP TABLE 위에 NOWAIT나 WAIT를 명시하기 위한 방법이 없기 때문에 첫 번째 메시지는 혼란된다. 이것은 단지 여러분이 잠긴 동작을 수행을 시도할 때 보는 일반적인 메시지이다. 그러나 작동은 blocking을 금지하지 않는다. 여러분이 전에 본거와 같이 이것은 만일 여러분이 잠긴 열에 대하여 SELECT FOR UPDATE NOWAIT를 설정했을 때 보는 메시지와 같은 것이다.
• 다음은 어떻게 이러한 lock들이 V$LOCK 테이블 안에 나타나는 것을 보여준다.

SQL> create table t1 ( x int );
Table created.
SQL> create table t2 ( x int );
Table created.
SQL> insert into t1 values ( 1 );
1 row created.
SQL> insert into t2 values ( 1 );
1 row created.
SQL> select (select username
2                         from v$session
3                        where sid = v$lock.sid) username,
4         sid,
5         id1,
6         id2,
7         lmode,
8         request, block, v$lock.type
9    from v$lock
10   where sid = (select sid
11                  from v$mystat
12                 where rownum=1)
13  /
USERNAME   SID     ID1    ID2 LMODE REQUEST BLOCK TYPE
--------- ---- ------- ------ ----- ------- ----- ----
OPS$TKYTE  161  262151  16584     6       0     0 TX
OPS$TKYTE  161   62074      0     3       0     0 TM
OPS$TKYTE  161   62073      0     3       0     0 TM
SQL> select object_name, object_id
2    from user_objects
3   where object_name in ('T1','T2')
4  /
OBJECT_NAME   OBJECT_ID
------------ ----------
T1                62073
T2                62074

• 우리가 트렌젝션 당 하나의 TX lock을 가지는 반면, 우리는 우리가 수정할 객체로부터 많은 TM lock들을 얻을 수 있다.
• 여기에 흥미로운 사실은 TM lock에 대한 ID1 컬럼이 DML-locked object의 object ID 이다.
• 그래서, 그것을 lock이 설정되어 있는 object를 찾는 것은 쉽다.

TM lock에 대한 흥미로운 것들

• 시스템 안에 허용되는 TM lock들의 전체 숫자는 여러분에 의하여 구성된다.
• 그것은 사실 '0'에서 설정된다. 이것은 여러분의 데이터베이스 read-only 모드로 데이터베이스 되는 것을 의미하지 않는다.
• 그러나 다소 DDL은 금지되지 않는다. 이것은 매우 특별한 어플리케이션에서 유용하다.
• RAC 구축과 같이 발생하는 인프라 인스턴스의 조합을 양을 줄이기 위한 경우이다.
• 여러분은 또한 ALTER TABLE TABLENAME DISABLE TABLE LOCK 명령어를 사용한 object-by-object 기반의 TM lock들을 얻기 위한 능력을 제거할 수 있다.
• 이것은 여러분이 테이블을 drop 하기 전에 테이블 lock을 이용하지 못하게 하는 것처럼 테이블을 사고로 drop 할 때 "harder"로 만드는 가장 쉬운 방법이다.
• 이것은 또한 우리가 전에 토론한 unidexed 외래 키의 결과로부터 전체 테이블 lock.을 보호하는데 사용할 수 있다.