Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why does not SELECT query start the transaction? [Oracle]

I am trying to debug next oracle procedure:

create or replace PROCEDURE CASE_6_TRANS_2
AS
   V_NUM NUMBER(38,0);
BEGIN
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  SELECT COUNT(AE.ID) INTO V_NUM FROM AUDIT_EVENT AE WHERE ID=10; 
  COMMIT; --BREAKPOINT
END CASE_6_TRANS_2;

But when I want to see current transaction at breakpoint , the V$TRANSACTION table is empty.

The documentation says:

A transaction begins when the first executable SQL statement is encountered. An executable SQL statement is a SQL statement that generates calls to a database instance, including DML and DDL statements and the SET TRANSACTION statement.

According to documentation SELECT query is the DML

  1. Does transaction starts but it is not shown in V$TRANSACTION?
  2. Or transaction does not starts at all. Why if not?
like image 597
Ilya Rochev Avatar asked Oct 19 '22 07:10

Ilya Rochev


1 Answers

The transaction starts with SET TRANSACTION, but the allocation of the transaction_id is delayed until first DML (real DML - in my experiance not a SELECT statement)

The same source says...

When a transaction begins, Oracle Database assigns the transaction to an available undo data segment to record the undo entries for the new transaction. A transaction ID is not allocated until an undo segment and transaction table slot are allocated, which occurs during the first DML statement.

It makes sense for me, there is no reason to commit or rollback a SELECT.

like image 176
Marmite Bomber Avatar answered Oct 28 '22 20:10

Marmite Bomber