RE: redo stream
- From: "Henry Poras" <henry@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 8 Jun 2005 14:43:21 -0400
Well, I started mining through our dev data to track down the unique
constraint error and found some strange stuff. There is one scenario I =
can
construct which fits the data, but our developers claim it is =
impossible.
Just wondering if anyone can think of any other models. This is a =
simplified
version of what I found:
--At ~11am there is a txn with rollbacks applied to tables a, b, and c.
DELETE from table a (rollback of insert. Rollback flag=3D1), UPDATE of =
table b
(rollback flag=3D1), DELETE from table c (rb flag=3D1).=20
--I extended my search on this txn forwards another 10 hours and =
backwards
another 30 hours (logs no longer available prior to this). There were no
other entries for this txn. No start txn (set transaction read write), =
no
end txn (commit, rollback), no statements with the initial INSERTs, =
UPDATE
(with rb flag =3D 0) which I saw rolled back.
Here is my model:
--There must have been an INSERT in this txn since there was a rollback. =
It
must have happened more than 30 hours ago.
--Since there was no explicit ROLLBACK statement in the logs, the =
rollback
was not issued by the application, but by Oracle, and it must be within =
an
already open txn.
--Therefore, for my txn to insert 30 hours ago, but not get the error =
until
now, some other txn must have done an INSERT prior to my txn, but not
committed until now.
--After the other txn commits, my txn rolls back, but the txn remains =
open
(no commit or rollback).=20
The developers claim that the code run in my txn opens and closes the =
txn in
an order of seconds (minutes is pushing it). There is no way it would =
have
been open for more than 30 hours (really 40 as I never saw it closed).
Moreover, having another txn open this long without commiting for >30 =
hrs
makes the impossible even more improbable.
I agree that the scenario doesn't sound very likely, but what else fits =
my
observations? Unfortunately I can't get any more logminer info at this
point, so a certain amount of speculation is necessary.
Thanks.
Henry
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Henry Poras
Sent: Tuesday, June 07, 2005 2:29 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: redo stream
OK, here is my simple(minded) test. There are four parts. After creating =
=3D a
test table (with a PK), I:
1. Ran some Inserts, Updates, Deletes followed by a COMMIT
2. Ran some Inserts, Updates, Deletes followed by a ROLLBACK
3. Ran an Insert which would fail (non-unique value) as a standalone
statement=20
4. Ran an Insert which would fail within an existing transaction
What I found was that when a statement causing a Unique Constraint error =
=3D
is the initial statement in a txn, Oracle rolls it back and then =3D
explicitely adds a ROLLBACK statement to end the txn. If the error =
statement
is =3D within an existing txn, the offending statement is rolled back
(rollback flag =3D =3D3D 1 in v$logmnr_contents) but there is no =
explicit
rollback statement =3D generated. (this makes sense as an explicit =
rollback
would terminate the txn).
Henry
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =3D =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Henry Poras
Sent: Monday, June 06, 2005 1:40 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: redo stream
We got a Unique Constraint error in our application. I wanted to =
find=3D20 the
sql which caused this. Since I found out about the error a bit after =3D
the fact, my thoughts turned to using logminer. When thinking about this =
=3D
I wondered wether the SQL which caused the error would make it to =
the=3D20
redo stream. If it did, the error would then need to be rolled back. But =
=3D
this would need to be a special kind of rollback, one which =
wouldn't=3D20
terminate the transaction. So maybe the kernal would notice the =
error=3D20 on
checking and cancel the statement before it got to the redo stream.=3D20
However, even if that happened in this case, what about ROLLBACK TO=3D20
SAVEPOINT. These statements defiinitely make the redo stream.
I'm putting together a test case, so I'll post what I find as soon as I =
=3D
can. (probably with other questions)
Henry
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
