RE: redo stream
- From: "Henry Poras" <henry@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 7 Jun 2005 14:28:49 -0400
OK, here is my simple(minded) test. There are four parts. After creating =
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
4. Ran an Insert which would fail within an existing transaction
What I found was that when a statement causing a Unique Constraint error =
is
the initial statement in a txn, Oracle rolls it back and then =
explicitely
adds a ROLLBACK statement to end the txn. If the error statement is =
within
an existing txn, the offending statement is rolled back (rollback flag =
=3D 1
in v$logmnr_contents) but there is no explicit rollback statement =
generated.
(this makes sense as an explicit rollback would terminate the txn).
Following is the SQL and the logminer output.
PART I
SQL>DROP TABLE canary;
SQL>CREATE TABLE canary (tweet number primary key);
SQL>INSERT INTO canary VALUES (1);
SQL>INSERT INTO canary VALUES (2);
SQL>INSERT INTO canary VALUES (3);
SQL>UPDATE canary SET tweet =3D 4 WHERE tweet =3D 3;
SQL>DELETE FROM canary WHERE tweet =3D 2;
SQL>COMMIT;
TXN ROLLBACK OPERATIO =20
------------ ---------- -------- =20
SQL_REDO
-------------------------------------------------------------------------=
---
----
SQL_UNDO
-------------------------------------------------------------------------=
---
----
1.24.10606 0 START =20
set transaction read write;
=20
=20
1.24.10606 0 INSERT =20
insert into "HENRY"."CANARY"("TWEET") values ('1');
delete from "HENRY"."CANARY" where "TWEET" =3D '1' and ROWID =3D
'AAAHn7AAEAAAAGGAAA';
=20
1.24.10606 0 INTERNAL (insert into index
HENRY.CANARY,SYS_C0012278) =20
=20
=20
1.24.10606 0 INSERT =20
insert into "HENRY"."CANARY"("TWEET") values ('2');
delete from "HENRY"."CANARY" where "TWEET" =3D '2' and ROWID =3D
'AAAHn7AAEAAAAGGAAB';
=20
1.24.10606 0 INTERNAL (index)
=20
=20
1.24.10606 0 INSERT =20
insert into "HENRY"."CANARY"("TWEET") values ('3');
delete from "HENRY"."CANARY" where "TWEET" =3D '3' and ROWID =3D
'AAAHn7AAEAAAAGGAAC';
=20
1.24.10606 0 INTERNAL (index) =20
=20
=20
1.24.10606 0 UPDATE =20
update "HENRY"."CANARY" set "TWEET" =3D '4' where "TWEET" =3D '3' and =
ROWID =3D
'AAAHn7AAEAAAAGGAAC';
update "HENRY"."CANARY" set "TWEET" =3D '3' where "TWEET" =3D '4' and =
ROWID =3D
'AAAHn7AAEAAAAGGAAC';
=20
1.24.10606 0 INTERNAL (not sure what this is) =20
=20
=20
1.24.10606 0 INTERNAL (index) =20
=20
=20
1.24.10606 0 DELETE =20
delete from "HENRY"."CANARY" where "TWEET" =3D '2' and ROWID =3D
'AAAHn7AAEAAAAGGAAB';
insert into "HENRY"."CANARY"("TWEET") values ('2');
=20
1.24.10606 0 INTERNAL (not sure what this is, but there is no
index entry) =20
=20
1.24.10606 0 COMMIT =20
commit; =20
--Everything here is fairly straightforward. The most interesting points =
are
Oracle's automatic use of "set transaction read write" and the lack of =
any
index activity when a record is deleted.
PART II
SQL>INSERT INTO canary VALUES (5);
SQL>INSERT INTO canary VALUES (6);
SQL>UPDATE canary SET tweet =3D 7 WHERE tweet =3D 6;
SQL>DELETE FROM canary WHERE tweet =3D 1;
SQL>ROLLBACK; =20
TXN ROLLBACK OPERATIO =20
------------ ---------- -------- =20
SQL_REDO
-------------------------------------------------------------------------=
---
----
SQL_UNDO
-------------------------------------------------------------------------=
---
----
1.14.10603 0 START =20
set transaction read write;
=20
1.14.10603 0 INSERT =20
insert into "HENRY"."CANARY"("TWEET") values ('5');
delete from "HENRY"."CANARY" where "TWEET" =3D '5' and ROWID =3D
'AAAHn7AAEAAAAGGAAD';
=20
1.14.10603 0 INTERNAL (index) =20
=20
=20
1.14.10603 0 INSERT =20
insert into "HENRY"."CANARY"("TWEET") values ('6');
delete from "HENRY"."CANARY" where "TWEET" =3D '6' and ROWID =3D
'AAAHn7AAEAAAAGGAAE';
=20
1.14.10603 0 INTERNAL (index) =20
=20
=20
1.14.10603 0 UPDATE =20
update "HENRY"."CANARY" set "TWEET" =3D '7' where "TWEET" =3D '6' and =
ROWID =3D
'AAAHn7AAEAAAAGGAAE';
update "HENRY"."CANARY" set "TWEET" =3D '6' where "TWEET" =3D '7' and =
ROWID =3D
'AAAHn7AAEAAAAGGAAE';
=20
1.14.1060 0 INTERNAL (??) =20
=20
=20
1.14.10603 0 INTERNAL (index) =20
=20
=20
1.14.10603 0 DELETE =20
delete from "HENRY"."CANARY" where "TWEET" =3D '1' and ROWID =3D
'AAAHn7AAEAAAAGGAAA';
insert into "HENRY"."CANARY"("TWEET") values ('1');
=20
1.14.10603 0 INTERNAL (??? Again, no index entry for
corresponding delete) =20
=20
=20
1.14.10603 1 INTERNAL (the rollback starts. ROLLBACK field =
=3D 1)
=20
=20
1.14.10603 1 INSERT =20
insert into "HENRY"."CANARY"("TWEET") values ('1');
=20
=20
1.14.10603 1 INTERNAL =20
=20
=20
=20
1.14.10603 1 INTERNAL =20
=20
=20
=20
1.14.10603 1 UPDATE =20
update "HENRY"."CANARY" set "TWEET" =3D '6' where ROWID =3D
'AAAHn7AAEAAAAGGAAE'; =20
=20
=20
1.14.10603 1 INTERNAL =20
=20
=20
=20
1.14.10603 1 DELETE =20
delete from "HENRY"."CANARY" where ROWID =3D 'AAAHn7AAEAAAAGGAAE';
=20
=20
1.14.10603 1 INTERNAL =20
=20
=20
=20
1.14.10603 1 DELETE =20
delete from "HENRY"."CANARY" where ROWID =3D 'AAAHn7AAEAAAAGGAAD';
=20
=20
1.14.10603 0 ROLLBACK =20
rollback;
=20
=20
--OK, so the statements necessary for the rollback show up explicitely =
in
logminer with the rollback flag set to 1. This is followed by an =
explicit
ROLLBACK (rollback flag of 0). Do the INTERNAL statements preceding each
rollback have to do with the index? Everything else parallels the COMMIT
txn.
PART III
=20
SQL>INSERT INTO canary VALUES (4);
TXN ROLLBACK OPERATIO =20
------------ ---------- -------- =20
SQL_REDO
-------------------------------------------------------------------------=
---
----
SQL_UNDO
-------------------------------------------------------------------------=
---
----
1.41.10608 0 START =20
set transaction read write;
=20
=20
1.41.10608 0 INSERT =20
insert into "HENRY"."CANARY"("TWEET") values ('4');
delete from "HENRY"."CANARY" where "TWEET" =3D '4' and ROWID =3D
'AAAHn7AAEAAAAGGAAB';
=20
1.41.10608 1 DELETE =20
delete from "HENRY"."CANARY" where ROWID =3D 'AAAHn7AAEAAAAGGAAB';
=20
=20
1.41.10608 0 ROLLBACK =20
rollback;
=20
--Since a value of tweet=3D4 already exists in the table, the insert =
will
fail. Oracle rolls back automatically. Two interesting points. First,
Oracle ends the transaction by adding the 'rollback' statement. Since =
this
is a standalone INSERT, I started the txn by typing the INSERT. Oracle =
ends
it via the ROLLBACK. Second, there is no mention of the index.
PART IV
SQL>INSERT INTO canary VALUES (5);
SQL>INSERT INTO canary VALUES (4);
SQL>INSERT INTO canary VALUES (6);
SQL>COMMIT; =20
TXN ROLLBACK OPERATIO =20
------------ ---------- -------- =20
SQL_REDO
-------------------------------------------------------------------------=
---
----
SQL_UNDO
-------------------------------------------------------------------------=
---
----
3.30.10656 0 START =20
set transaction read write;
=20
=20
3.30.10656 0 INSERT =20
insert into "HENRY"."CANARY"("TWEET") values ('5');
delete from "HENRY"."CANARY" where "TWEET" =3D '5' and ROWID =3D
'AAAHn7AAEAAAAGGAAB';
=20
3.30.10656 0 INTERNAL (index) =20
=20
=20
=20
3.30.10656 0 INSERT =20
insert into "HENRY"."CANARY"("TWEET") values ('4');
delete from "HENRY"."CANARY" where "TWEET" =3D '4' and ROWID =3D
'AAAHn7AAEAAAAGGAAD';
=20
3.30.10656 1 DELETE =20
delete from "HENRY"."CANARY" where ROWID =3D 'AAAHn7AAEAAAAGGAAD';
=20
=20
3.30.10656 0 INSERT =20
insert into "HENRY"."CANARY"("TWEET") values ('6');
delete from "HENRY"."CANARY" where "TWEET" =3D '6' and ROWID =3D
'AAAHn7AAEAAAAGGAAD';
=20
3.30.10656 0 INTERNAL (index) =20
=20
=20
3.30.10656 0 COMMIT =20
commit;
=20
=20
--Oracle does an implicit rollback of the error statement. In this case =
it
has to be implicit as an explicit one as in the prior example would end =
my
txn. I can still see the rollback by the rollback flag set to 1.
Henry
=20
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =
[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=20
the sql which caused this. Since I found out about the error a bit after =
the fact, my thoughts turned to using logminer. When thinking about this =
I wondered wether the SQL which caused the error would make it to the=20
redo stream. If it did, the error would then need to be rolled back. But =
this would need to be a special kind of rollback, one which wouldn't=20
terminate the transaction. So maybe the kernal would notice the error=20
on checking and cancel the statement before it got to the redo stream.=20
However, even if that happened in this case, what about ROLLBACK TO=20
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 =
can. (probably with other questions)
Henry
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
