RE: redo stream

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

--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

Other related posts: