RE: rollback after 0 rows affected

Hi Fred,

Even though the delete affected no rows, it does begin a transaction. That transaction takes up a slot in the SGA transaction table (X$KTCXB) but does not yet use a slot in one of the rollback segment header block transaction tables. V$TRANSACTION will not show you this transaction because the KTCXBFLG value is 1, but V$RESOURCE_LIMIT will show you that the slot is in use.

If you are not concerned about the retention of a slot in the SGA transaction table, then the only impact of not terminating the transaction explicitly with a COMMIT or ROLLBACK is that the snapshot SCN for consistent reads for subsequent statements will reflect the point in time at which the delete operation was attempted. If your next statement needs a new snapshot SCN for consistent reads then the transaction should be explicitly terminated. There is no performance difference between using COMMIT or ROLLBACK in this case. However, if your next statement does not need a new snapshot SCN then you can get a very minor performance saving by not terminating the transaction. All you are saving is the cost of the call itself, and the cost of establishing a new transaction next time. There is no impact of LGWR from null commits or rollbacks.

@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/

-----Original Message-----
Sent: Thursday, 15 February 2001 4:37
To: Multiple recipients of list ORACLE-L

I'm using transactions, with a delete followed by an insert.

If the delete affects 0 rows, there is nothing to do.

Do I need to rollback the transaction to abandon it, or is everything the way it should after a delete that affected 0 rows?

Thanks!

• Sincerely, Fred Frederik Lindberg, CTO, CheetahMail

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steve Adams
  INET: steve.adams_at_ixora.com.au

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).