Thread: Transaction rollbacks
07-29-09, 12:00 #1Registered User
- Join Date
- Jul 2009
Unanswered: Transaction rollbacks
I have observed certain 'transaction rollbacks' while running my app
I collected these statistics from V$MYSTAT, V$STATNAME.
3.rollback changes - undo records applied
user rollbacks is always 0 (as far I understand, any rollbacks in app code or library(oci wrapper) would show up here). I don't understand why 'transaction rollback' happens! along with some value for 'rollback changes - undo records applied'. (e.g.: user rollbacks 0; transaction rollbacks 13; rollback changes - undo records applied 30) Further checks with the app has shown that this is happening during insertions. I commented out writing part of the app and then no 'transaction rollbacks' happen.
Also I see that these statistics are of class (128, DEBUG). So is it something that application cannot avoid but should be done at oracle end?
I would run this app in parallel batches.
Could this statistic contribute to a high rollback per transaction ratio?
Could this lead to performance issues?
I tried generating 10046 trace on the session. But I couldn't find anything related to rollbacks in the trace file or tkprof output.
Will putting a trigger on V$MYSTAT help to capture updates to the statistic value. In that case, is it possible to identify which insert(exact query) is causing the problem?
Thanks in advance.
07-29-09, 14:21 #2Registered User
Provided Answers: 1
- Join Date
- Aug 2003
- Where the Surf Meets the Turf @Del Mar, CA
>this is happening during insertions.
Please elaborate GREATLY.
On which system is INSERT actually running?
What is the source of the data being inserted?
Is data going into DB on a row by row basis?
Is data going into DB multiple rows from a single INSERT?
Please post actual INSERT & EXPLAIN PLAN from it.You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Good judgement comes from experience. Experience comes from bad judgement.