DBMS_TRANSACTOIN.STEP_ID로 내 세션의 TX 존재여부파악하기

How to check if I have a pending transaction?

10. 10. 6 작성자: Laurent Schneider의 Laurent Schneider

Pretty straightforward, check if dbms_transaction.step_id is null!

SQL> select dbms_transaction.step_id from dual;
        STEP_ID
---------------

SQL> insert into t values (1);
1 row created.

SQL> select dbms_transaction.step_id from dual;
        STEP_ID
---------------
114352430549782

SQL> commit;
Commit complete.

SQL> select dbms_transaction.step_id from dual;

        STEP_ID

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




STEP_ID가 어떤의미일까요?

메뉴얼엔 step_id function에 대해 아래와 같이 정의도어 있는데, 무슨 말인지 잘 모르겠네요. 

"This function returns local (to local transaction) unique positive integer that orders the DML operations of a transaction."

테스트를 해보자면...

SQL> delete from yhcho_temp where col1 = 'TURNER';

1 row deleted.

SQL> select dbms_transaction.step_id from dual;

                   STEP_ID

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

         37783619732783637

SQL> savepoint A ;

Savepoint created.

SQL> delete from yhcho_temp where col1 = 'KING';

1 row deleted.

SQL> select dbms_transaction.step_id from dual;

                   STEP_ID

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

         37783619732783639

SQL> rollback to savepoint A;

Rollback complete.

SQL> select dbms_transaction.step_id from dual;

                   STEP_ID

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

         37783619732783639

savepoint A까지 rollback 해도 step_id는 바뀌지 않는 군요.. 

단순히 transaction step에 대한 sequence 일까요? 

SQL> delete from yhcho_temp where col1 = 'KING';

1 row deleted.

SQL> select dbms_transaction.step_id from dual;

                   STEP_ID

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

         37783619732783641

특정 transaction을 가리키는 것 같진 않고 

위의 post 처럼 step_id로는 단순히 transaction이 있는지 여부만 확인할 수 있을 것 같네요..

SQL> desc dbms_transaction        

PROCEDURE ADVISE_COMMIT

PROCEDURE ADVISE_NOTHING

PROCEDURE ADVISE_ROLLBACK

PROCEDURE BEGIN_DISCRETE_TRANSACTION

PROCEDURE COMMIT

PROCEDURE COMMIT_COMMENT

 Argument Name                  Type                    In/Out Default?

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

 CMNT                           VARCHAR2                IN

PROCEDURE COMMIT_FORCE

 Argument Name                  Type                    In/Out Default?

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

 XID                            VARCHAR2                IN

 SCN                            VARCHAR2                IN     DEFAULT

FUNCTION LOCAL_TRANSACTION_ID RETURNS VARCHAR2

 Argument Name                  Type                    In/Out Default?

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

 CREATE_TRANSACTION             BOOLEAN                 IN     DEFAULT

PROCEDURE PURGE_LOST_DB_ENTRY

 Argument Name                  Type                    In/Out Default?

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

 XID                            VARCHAR2                IN

PROCEDURE PURGE_MIXED

 Argument Name                  Type                    In/Out Default?

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

 XID                            VARCHAR2                IN

PROCEDURE READ_ONLY

PROCEDURE READ_WRITE

PROCEDURE ROLLBACK

PROCEDURE ROLLBACK_FORCE

 Argument Name                  Type                    In/Out Default?

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

 XID                            VARCHAR2                IN

PROCEDURE ROLLBACK_SAVEPOINT

 Argument Name                  Type                    In/Out Default?

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

 SAVEPT                         VARCHAR2                IN

PROCEDURE SAVEPOINT

 Argument Name                  Type                    In/Out Default?

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

 SAVEPT                         VARCHAR2                IN

FUNCTION STEP_ID RETURNS NUMBER

PROCEDURE USE_ROLLBACK_SEGMENT

 Argument Name                  Type                    In/Out Default?

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

 RB_NAME                        VARCHAR2                IN