Thanks for the question, Sandeep.

Asked: February 28, 2012 - 12:42 am UTC

Last updated: August 17, 2012 - 1:21 pm UTC

Version: 11.2.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

After executing a simple select statement, do we need to end that transaction using a commit or rollback?

I tried running the following script to find out whether any transaction is getting created when i run a simple select statement.

SET SERVEROUTPUT ON;
declare
v_count number;
begin
select COUNT(1) into V_COUNT  from V$TRANSACTION;
DBMS_OUTPUT.PUT_LINE(V_COUNT);
select COUNT(1)  into V_COUNT from T1 where rownum<=1;
select COUNT(1) into V_COUNT  from V$TRANSACTION;
DBMS_OUTPUT.PUT_LINE(V_COUNT);
END;

The output which i got is as follows.

anonymous block completed
0
0

From the above script it seems that no transaction gets created for a simple select query (due to absence of record in V$TRANSACTION). So is there any need of commit or rollback after a select statement in any case (any transaction mode). If yes, please explain in detail.

I know when we query a table through a DB link, we have to commit the transaction. But i could not understand the need for it.

Thanks in advance.

Regards,
Sandeep

and Tom said...

It depends on your transaction isolation level really.

If you are in read committed, single database instance, you don't really need to commit or rollback.

If you are in serializable (or read only), single database instance, you need to commit when you would like to be able to see newly committed data from other sessions. In serializable - your view of the database is 'frozen' as of the point in time of your first statement - you don't see any other changes until you commit or rollback.

If you are distributed, you would want to commit to finish off anything that was implicitly started on the remote site.

So, in general, you need do nothing.

Rating

(2 ratings)

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

Comments

Tested

Sandeep Shenoy, February 29, 2012 - 7:06 am UTC

Hi Tom,

Thanks for your reply. I have tested it using the following steps.

open a session s1

Run the following scripts from s1
CREATE TABLE T1
( ID1 NUMBER(9,2) primary key,
NAME1 VARCHAR2(100));

insert into t1 values(1,'Tom');
commit;

open a new session s2
Run the following commands from s2
Set transaction isolation level serializable;
select * from t1;

from session s1 run the following command
delete from t1;
commit;

from session s2 run the following query
select * from t1;
--1 row will be returned as the result though it has been deleted from the table. Run the following commands now from s2
commit;
select * from t1;
---no rows will be returned which means if the isolation level is serializable (this is read committed by default in oracle), then we have to commit if we want to see the changes done by other sessions on the database.

But in Distributed environment, if the other database is also an oracle database. what's the need of ending the transaction using a commit or rollback. Because the isolation level is read committed by default in oracle and i am not setting the isolation level as serializable explicitly. But still, we have to commit or rollback if we select from a table using a database link. Can you explain with an example.

Thanks in advance.

February 29, 2012 - 7:48 am UTC

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8908307196113

simple question

napa, August 09, 2012 - 12:47 am UTC

how to rollback commited transaction in oracle.

pls give the example.

August 17, 2012 - 1:21 pm UTC

trick question.

You cannot rollback a committed transaction. That is what commit means - it is committed, permanent, persistent.

You do have flashback transaction capabilities, it is NOT rollback, it provides a set of statements that would logically undo the transaction - but it is not rollback.

http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_flashback.htm#ADFNS01005

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Tested

simple question

More to Explore

DBMS_OUTPUT

Do a Simple select query needs commit or rollback in any transaction mode

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Tested

simple question

More to Explore

DBMS_OUTPUT