[Database] goldilocks TRANSACTION_TIMEOUT 프로퍼티 정체 알아보기

TRANSACTION_TIMEOUT property (goldilocks)

: 특정 세션이 계속해서 하나의 트랜잭션을 물고있으면, ager가 밀려 자원을 재사용할 수 없음을 방지

: 쿼리 수행 후 Commit/ Rollback이 수행될 때까지의 시간에 대한 timeout (수행 질의는 롤백)

: 해당 transaction의 start time을 기준으로 timeout 측정, v$transaction에서 확인할 수 있음.

■ TRANSACTION_TIMEOUT 프로퍼티 정보

gSQL> select * from x$property where property_name like '%TRANSACTION_TIME%';

PROPERTY_ID # 268

PROPERTY_NAME # TRANSACTION_TIMEOUT

DESCRIPTION # transaction timeout(s)

GROUP_ID # TBD

DATA_TYPE_ID # 3

DATA_TYPE # BIGINT

STARTUP_PHASE # NO_MOUNT ABOVE

UNIT # NONE

VALUE # 0

SOURCE # DEFAULT

INIT_VALUE # 0

INIT_SOURCE # DEFAULT

SES_MODIFIABLE # FALSE

SYS_MODIFIABLE # IMMEDIATE

MIN # 0

MAX # 10000000

MODIFIABLE # TRUE

DOMAIN # EXTERNAL

CLUSTER_SCOPE # LOCAL

---

■ STANDALONE 확인

gSQL> alter system set TRANSACTION_TIMEOUT = 60;

System altered.

gSQL> select value from x$property where property_name = 'TRANSACTION_TIMEOUT';

VALUE

-----

60

하나의 세션이 트랜잭션 잡고있어, ager 밀리는 상황 연출

gSQL> select * from x$agable_info;

GLOBAL_SCN # 0.0.7

LOCAL_SCN # 0.0.455

MIN_TRANS_VIEW_SCN # 0.0.453

AGABLE_SCN # 0.0.455

AGABLE_SCN_GAP # 0.0.0

AGABLE_STMT_SCN # 0.0.455

AGABLE_VIEW_SCN # 0.0.453

LOCAL_AGABLE_SCN # 0.0.455

LOCAL_AGABLE_VIEW_SCN # 0.0.453

LOCAL_AGABLE_STMT_SCN # 0.0.455

LOCAL_MIN_TRANS_VIEW_SCN # 0.0.453

REMOTE_AGABLE_SCN # -1.-1.455

REMOTE_AGABLE_STMT_SCN # -1.-1.455

REMOTE_AGABLE_VIEW_SCN # -1.-1.453

SESSION_ID_BLOCKING_AGABLE_VIEW_SCN # 27

SESSION_ID_BLOCKING_AGABLE_STMT_SCN # -1

SESSION_ID_BLOCKING_AGABLE_SCN # -1

SESSION_ID_BLOCKING_MIN_TRANS_VIEW_SCN # 27

gSQL> select * from v$transaction;

TRANS_ID # 589851

SESSION_ID # 27

TRANS_SLOT_ID # 27

PHYSICAL_TRANS_ID # 1868311298075

TRANS_STATE # ACTIVE

IS_XA # FALSE

TRANS_ATTRIBUTE # UPDATABLE | LOCKABLE

ISOLATION_LEVEL # READ COMMITTED

TRANS_VIEW_SCN # 0.0.453

TCN # 2

TRANS_SEQ # 9

START_TIME # 2018-02-21 15:42:56.404086

트랜잭션 START_TIME 기준, TRANSACTION_TIMEOUT = 60 초 뒤인 15:43:56에 세션 종료 됨을 확인

ERR-08S01(11141): Communication link failure

system.trc

[2018-02-21 15:43:56.465217 INSTANCE(GOLDILOCKS) THREAD(22829,139713799251712)] [WARNING]

[CLEANUP] long transaction timeout - session(27), pid(23461), thread(140354224002816), program(gsqlnet.exe), trans_id(589851), transaction start time(2018-02-21 15:42:56.404086)

[2018-02-21 15:43:56.465322 INSTANCE(GOLDILOCKS) THREAD(22829,139713799251712)] [WARNING]

[CLEANUP] cleaning local session - env(26), session(27), local transaction(589851), program(gsqlnet.exe), pid(23461), thread(140354224002816)

[2018-02-21 15:43:56.809083 INSTANCE(GOLDILOCKS) THREAD(22829,139713799251712)] [WARNING]

[CLEANUP] cleaning up 1 sessions

scn도 맞추어짐

gSQL> select * from x$agable_info;

GLOBAL_SCN # 0.0.7

LOCAL_SCN # 0.0.456

MIN_TRANS_VIEW_SCN # 0.0.456

AGABLE_SCN # 0.0.456

AGABLE_SCN_GAP # 0.0.0

AGABLE_STMT_SCN # 0.0.456

AGABLE_VIEW_SCN # 0.0.456

LOCAL_AGABLE_SCN # 0.0.456

LOCAL_AGABLE_VIEW_SCN # 0.0.456

LOCAL_AGABLE_STMT_SCN # 0.0.456

LOCAL_MIN_TRANS_VIEW_SCN # 0.0.456

REMOTE_AGABLE_SCN # -1.-1.456

REMOTE_AGABLE_STMT_SCN # -1.-1.456

REMOTE_AGABLE_VIEW_SCN # -1.-1.456

SESSION_ID_BLOCKING_AGABLE_VIEW_SCN # -1

SESSION_ID_BLOCKING_AGABLE_STMT_SCN # -1

SESSION_ID_BLOCKING_AGABLE_SCN # -1

SESSION_ID_BLOCKING_MIN_TRANS_VIEW_SCN # -1

DA로 붙은 세션, TCP로 붙은 세션 동일하게 동작

---

■ CLUSTER 확인 (2by2)

2by2에서 GCN증가되는 트랜잭션 발생시킨 뒤, ager 밀리는 상황 연출 (GCN은 5, AGABLE_SCN의 GCN은 3에서 멈춤)

gSQL> select * from x$agable_info@local;

GLOBAL_SCN # 5.0.7

LOCAL_SCN # 5.0.10

MIN_TRANS_VIEW_SCN # 3.0.10

AGABLE_SCN # 5.0.10

AGABLE_SCN_GAP # 0.0.0

AGABLE_STMT_SCN # 5.0.10

AGABLE_VIEW_SCN # 3.0.0

LOCAL_AGABLE_SCN # 5.0.10

LOCAL_AGABLE_VIEW_SCN # 3.0.10

LOCAL_AGABLE_STMT_SCN # 5.0.10

LOCAL_MIN_TRANS_VIEW_SCN # 3.0.10

REMOTE_AGABLE_SCN # 5.0.10

REMOTE_AGABLE_STMT_SCN # 5.0.10

REMOTE_AGABLE_VIEW_SCN # 3.0.10

SESSION_ID_BLOCKING_AGABLE_VIEW_SCN # 52

SESSION_ID_BLOCKING_AGABLE_STMT_SCN # -1

SESSION_ID_BLOCKING_AGABLE_SCN # -1

SESSION_ID_BLOCKING_MIN_TRANS_VIEW_SCN # 52

이 상태에서 transaction timeout 60으로 변경. (이미 붙어있는 세션도 제대로 떨어지는지 확인을 위함)

gSQL> alter system set TRANSACTION_TIMEOUT = 60;

System altered.

세션 떨어지며, GCN 5로 맞춰짐 확인

ERR-08S01(11141): Communication link failure

gSQL> select GLOBAL_SCN, MIN_TRANS_VIEW_SCN, AGABLE_SCN, AGABLE_VIEW_SCN from x$agable_info;

GLOBAL_SCN MIN_TRANS_VIEW_SCN AGABLE_SCN AGABLE_VIEW_SCN

---------- ------------------ ---------- ---------------

5.0.7 5.0.11 5.0.11 5.0.0

5.0.7 5.0.874 5.0.874 5.0.0

5.0.7 5.0.874 5.0.874 5.0.0

5.0.7 5.0.874 5.0.874 5.0.0

DA로 붙은 세션, TCP로 붙은 세션 동일하게 동작