아래의 표는 LOCK WAIT를 발생한 예입니다.
X$LOCK_WAIT의 결과값은 없어도 X$TRANSACTION에 LOCKABLE은 언제든지 나타날 수 있습니다.
아래의 방법으로 해당 세션을 찾을 수 있습니다.
* X$LOCK_WAIT --> X$TRANSACTION --> X$SM_SESS_ENV --> X$SESSION
gSQL> select * from x$lock_wait; GRANTED_TRANSACTION_SLOT_ID REQUEST_TRANSACTION_SLOT_ID --------------------------- --------------------------- 10 11 gSQL> select * from x$transaction; ID SLOT_ID STATE ATTRIBUTE ISOLATION_LEVEL VIEW_SCN COMMIT_SCN TCN BEGIN_TIME PROPAGATE_LOG ------------ ------- ------ ------------------------------------------------- --------------- -------- ---------- --- -------------------------- ------------- -65534 2 ACTIVE READ_ONLY READ COMMITTED 454 -1 0 2014-01-08 13:15:27.477607 TRUE 700079669258 10 ACTIVE READ_ONLYUPDATABLE | LOCKABLE READ COMMITTED 469 -1 1 2014-01-08 13:19:59.398726 TRUE 794580942859 11 ACTIVE READ_ONLYUPDATABLE | LOCKABLEUPDATABLE | LOCKABLE READ COMMITTED 469 -1 1 2014-01-08 13:20:09.765603 TRUE gSQL> select * from x$sm_sess_env where value=700079669258; NAME ID VALUE -------- -- ------------ TRANS_ID 10 700079669258 gSQL> select * from x$sm_sess_env where value=794580942859; NAME ID VALUE -------- -- ------------ TRANS_ID 11 794580942859 gSQL> select * from x$session where id=10 or id=11; ID SERIAL TOP_LAYER CONNECTION USER_ID STATUS WATCH SERVER PROCESS LOGON_TIME PROGRAM -- ------ --------- ---------- ------- --------- ----- --------- ------- -------------------------- ------- 10 15 11 DA 6 CONNECTED LEAVE DEDICATED 5067 2014-01-08 13:19:57.127504 gsql 11 13 11 DA 6 CONNECTED ENTER DEDICATED 4958 2014-01-08 13:19:07.040071 gsql |
