You Asked

Hi, Tom.
I am investigating a process of creating a transaction after a SELECT statement as a DML operator.
Here is a steps,

COMMIT;
ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE; 
SELECT * FROM V$TRANSACTION;
--Wait one minute to remember time of previous select execution
SELECT DBMS_TRANSACTION.LOCAL_TRANSACTION_ID FROM DUAL;
SELECT * FROM V$TRANSACTION;
--the transaction create time is before call DBMS_TRANSACTION.LOCAL_TRANSACTION_ID

The questions are,
We don't have any TX locks but the transaction exists. Why?
After call DBMS_TRANSACTION.LOCAL_TRANSACTION_ID we can see a new transaction in V$TRANSACTION but before don't. Why?
Also we don't see any transaction if ISOLATION_LEVEL = READ COMMITTED. Why?

According to your excellent book Expert Oracle Chapter 8, "A transaction implicitly begins with the first
statement that modifies data (the first statement that gets a TX lock)."

and Connor said...

Also take a look at these two links for more details

https://blogs.oracle.com/oraclemagazine/on-transaction-isolation-levels

https://asktom.oracle.com/pls/apex/asktom.search?tag=serializable-transaction-200203

but we also need to respect the moment in time you *logically* commenced a transaction, which in your case was the "alter session" command.

We can perhaps see more easily with this demo

SQL> select sysdate from dual;

SYSDATE
-------------------
22/07/2021 12:01:55

SQL> select start_time from v$transaction;

no rows selected

SQL> set transaction read write;       <<<===== you logically started NOW, but this is just recorded in the session memory, you can't "see" it

Transaction set.

SQL> select sysdate from dual;

SYSDATE
-------------------
22/07/2021 12:01:56

SQL> exec dbms_session.sleep(20);

PL/SQL procedure successfully completed.

SQL> select sysdate from dual;

SYSDATE
-------------------
22/07/2021 12:02:16

SQL> select * from emp for update;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO G
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- -
      7369 SMITH      CLERK           7902 17/12/1980 00:00:00        800                    20 F
      7499 ALLEN      SALESMAN        7698 20/02/1981 00:00:00       1600        300         30 F
      7521 WARD       SALESMAN        7698 22/02/1981 00:00:00       1250        500         30 F
      7566 JONES      MANAGER         7839 02/04/1981 00:00:00       2975                    20 F
      7654 MARTIN     SALESMAN        7698 28/09/1981 00:00:00       1250       1400         30 F
      7698 BLAKE      MANAGER         7839 01/05/1981 00:00:00       2850                    30 F
      7782 CLARK      MANAGER         7839 09/06/1981 00:00:00       2450                    10 F
      7788 SCOTT      ANALYST         7566 09/12/1982 00:00:00       3000                    20 M
      7839 KING       PRESIDENT            17/11/1981 00:00:00       5000                    10 M
      7844 TURNER     SALESMAN        7698 08/09/1981 00:00:00       1500                    30 M
      7876 ADAMS      CLERK           7788 12/01/1983 00:00:00       1100                    20 M
      7900 JAMES      CLERK           7698 03/12/1981 00:00:00        950                    30 M
      7902 FORD       ANALYST         7566 03/12/1981 00:00:00       3000                    20 U
      7934 MILLER     CLERK           7782 23/01/1982 00:00:00       1300                    10 U

14 rows selected.

SQL> select start_time from v$transaction;

START_TIME
--------------------
07/22/21 12:01:55

It makes sense for us *not* to go to the effort of allocating undo etc (ie, seeing something in v$transaction) until the time you actually need it because if you don't need it, then it would be wasted effort. But we do need to remember when *logically* you started the transaction.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Akal, July 22, 2021 - 10:03 am UTC

OK, Thanks. I totally understand Tom's book about a Transaction isolation and your link is not useful for me but thanks for the example and pointing to a logical moment of a transaction starting. That thing became clear for me now.

But in your example if we don't change a transaction level
<comment>
SQL> set transaction read write; <<<===== you logically started NOW, but this is just recorded in the session memory, you can't "see" it
</comment>
and we stay at the default transaction level (READ COMMITTED) in which moment a transaction is starting (logically as I suppose)?

July 23, 2021 - 7:32 am UTC

Just give it a run yourself and you'll get the answer

select sysdate from dual;
select start_time from v$transaction;
alter session set isolation_level = read committed;
select sysdate from dual;
exec dbms_session.sleep(20);
select sysdate from dual;
select * from emp for update;
select start_time from v$transaction;

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs

Transaction after SELECT statement

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs