Re: Constraint policing - redo generation? transaction failing

From: Vishnu Potukanuma <vishnupotukanuma_at_gmail.com>
Date: Sat, 23 Nov 2019 16:37:04 +0530
Message-ID: <CAP-Ryww131Q4fLhoQrMwJo_L5YWeuCCQwLuSwVsmGRnFXKE8jQ_at_mail.gmail.com>

Hi,

looks like, it deletes the leaf block entries first, then undoes the change again. the following is the redo dump.
I mean no-one does a update an primary key columns to collide with existing values, since most of these values are populated by either a application sequence or database sequence. Thanks again jonathan for pointing this out that to use redo and test...

REDO RECORD - Thread:1 RBA: 0x000256.00002616.0010 LEN: 0x0298 VLD: 0x0d CON_UID: 0
SCN: 0x0000000000513e16 SUBSCN: 1 11/23/2019 10:54:32 (LWN RBA: 0x000256.00002616.0010 LEN: 0x00000003 NST: 0x0001 SCN: 0x0000000000513e16)
CHANGE #1 CON_ID:0 TYP:0 CLS:1 AFN:7 DBA:0x01c689b1 OBJ:76831 SCN:0x00000000005138ad SEQ:2 OP:11.5 ENC:0 RBL:0 FLG:0x0000 KTB Redo
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x0004.018.00000b2f uba: 0x01012014.0312.10 KDO Op code: URP row dependencies Disabled   xtype: XA flags: 0x00000000 bdba: 0x01c689b1 hdba: 0x01c021da

itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 32(0x20) flag: 0x2c lock: 2 ckix: 0
ncol: 5 nnew: 1 size: 2

col 0: [ 4] c3 0a 64 64
CHANGE #2 CON_ID:0 TYP:0 CLS:23 AFN:4 DBA:0x010000b0 OBJ:4294967295 SCN:0x0000000000513dd8 SEQ:1 OP:5.2 ENC:0 RBL:0 FLG:0x0000 ktudh redo: slt: 0x0018 sqn: 0x00000b2f flg: 0x0012 siz: 136 fbi: 0

            uba: 0x01012014.0312.10 pxid: 0x0000.000.00000000 CHANGE #3 CON_ID:0 TYP:0 CLS:1 AFN:7 DBA:0x01c681dc OBJ:76832 SCN:0x00000000005138ad SEQ:2 OP:10.4 ENC:0 RBL:0 FLG:0x0000 index redo (kdxlde): delete leaf row
KTB Redo
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x0004.018.00000b2f uba: 0x01012014.0312.11 REDO: 0x0 SINGLE / -- / --
itl: 2, sno: 0, row size 13
CHANGE #4 CON_ID:0 TYP:0 CLS:24 AFN:4 DBA:0x01012014 OBJ:4294967295 SCN:0x0000000000513dd7 SEQ:1 OP:5.1 ENC:0 RBL:0 FLG:0x0000

ktudb redo: siz: 136 spc: 5940 flg: 0x0012 seq: 0x0312 rec: 0x10
            xid:  0x0004.018.00000b2f
ktubl redo: slt: 24 wrp: 1 flg: 0x0c08 prev dba:  0x00000000 rci: 0 opc:

11.1 [objn: 76831 objd: 76831 tsn: 4]
[Undo type ] Regular undo [User undo done ] No [Last buffer split] No [Temp object] No [Tablespace Undo ] No [User only ] No Begin trans
 prev ctl uba: 0x01012014.0312.0f prev ctl max cmt scn: 0x000000000051362f  prev tx cmt scn: 0x0000000000513637
 txn start scn: 0x0000000000513e0d logon user: 106  prev brb: 0x01012012 prev bcl: 0x00000000 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: URP row dependencies Disabled   xtype: XA flags: 0x00000000 bdba: 0x01c689b1 hdba: 0x01c021da

itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 32(0x20) flag: 0x2c lock: 0 ckix: 0
ncol: 5 nnew: 1 size: -2

col 0: [ 2] c1 02
CHANGE #5 CON_ID:0 TYP:0 CLS:24 AFN:4 DBA:0x01012014 OBJ:4294967295 SCN:0x0000000000513e16 SEQ:1 OP:5.1 ENC:0 RBL:0 FLG:0x0000

ktudb redo: siz: 100 spc: 5802 flg: 0x0022 seq: 0x0312 rec: 0x11
            xid:  0x0004.018.00000b2f
ktubu redo: slt: 24 wrp: 2863 flg: 0x0000 prev dba:  0x00000000 rci: 16

opc: 10.22 [objn: 76832 objd: 76832 tsn: 4] [Undo type ] Regular undo [User undo done ] No [Last buffer split] No [Temp object] No [Tablespace Undo ] No [User only ] No index undo for leaf key operations
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0xffff.000.00000000 uba: 0x00000000.0000.00

                      flg: C---    lkc:  0     scn:  0x0000000000513485

Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x1c681da block=0x01c681dc (kdxlre): restore leaf row (clear leaf delete flags) key :(3): 02 c1 02
keydata/bitmap: (6): 01 c6 89 b1 00 20

Thanks,
Vishnu

On Sat, Nov 23, 2019 at 4:04 PM Vishnu Potukanuma < vishnupotukanuma_at_gmail.com> wrote:

> Sure Jonathan, I will do the same, for the initial tests i used x$bh to
> verify that the block is dirty or not..
>
> Thanks,
> Vishnu
>
> On Sat, Nov 23, 2019 at 3:18 PM Jonathan Lewis <
> jonathan_at_jlcomp.demon.co.uk> wrote:
>
>>
>> You need to do a couple more experiments.
>>
>> In general the uniqueness constraint can only be checked after the
>> statement is (nearly) complte because you could have multi-row updates e.g.
>>
>> update temp set roll = decode (roll,1,999999,999999,1) where roll in
>> (1,999999);
>>
>> It's is perfectly feasible, though, that the single row update (which can
>> obviously be recognised by the optimizer) has been coded specially to
>> attempt the index leaf block insert before the index leaf block delete
>> specifically because that avoids a little of the work needed in a rollback
>> on duplicate key.
>>
>> You really need to dump the redo log for your transaction to check
>> exactly what Oracle does do in this case; but another test you could do is
>> to attempt to change two rows with entries in different leaf blocks to the
>> same pre-existing key value, e.g.
>>
>> update temp set roll = 1 where roll in (80001,90001);
>>
>> and see which blocks become dirty.
>>
>>
>> Regards
>> Jonathan Lewis
>>
>>
>> ________________________________________
>> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
>> behalf of Vishnu Potukanuma <vishnupotukanuma_at_gmail.com>
>> Sent: 23 November 2019 08:51
>> To: Oracle L
>> Subject: Constraint policing - redo generation? transaction failing
>>
>> Hi,
>>
>> the scenario is this.
>> create table temp (roll number, name varchar2(20), mark1 number, mark2
>> number);
>> alter table temp add constraint temp_pk primary key (Roll);
>> insert into temp select rownum, 'VISHNU',12,123) from dual connect by
>> level < 10;
>> commit;
>>
>> now we have 10 rows in the table with rolls as 1,2,3 ... 10
>> when i insert the following row into the table.
>> insert into temp values (1,'vishnu',123,123,123,);
>>
>> Since the index is a unique... oracle checks to see if a row exists
>> already even before modifying the index leaf block and since the row exists
>> already it throws an exception ORA-00001: unique constraint
>> (VISHNU.TEMP_PK) violated.. in this case, the index leaf block retains its
>> status before and after. this is not the case when the index is a non
>> unique index.
>>
>> But the handling appears to be different for update statements, the same
>> case as above when the constraint is policed via an unique index...
>> consider the statement
>> update temp set roll=2 where roll = 1;
>> we know that roll=2 already exists in table, and the constraint check
>> appears to be postponed until after the update statement is executed or the
>> block is modified, since the index leaf block becomes dirty in this case...
>> i was wondering whether it is during the deletion phase or insertion phase,
>> i believe update is equivalent to delete and insert when it comes to
>> indexes, i may not be 100% correct here but please be patient.
>>
>> now we know that the block is being modified. since here both the rows
>> 1,2 are present in the same leaf block, i tried a different strategy, in
>> this case i recreated the same table and loaded over 100k rows, created the
>> constraint (unique index).
>>
>> now things get a little weird, the two distinct leaf blocks say A and B
>> where A stores the value 1 and B stores the value 99999. their block status
>> are clean (I have restarted the database).
>> now i issue the update statement as follows:
>> update temp set roll=99999 where roll = 1;
>>
>> so we know before modifying a block, unique constraints are validated
>> when we have an unique index. the result of the above update statement is
>> as follows:
>> leaf block A (which holds the key value 1) becomes dirty (or modified).
>> Leaf block B retains the clean status.
>>
>> A valid explanation to this can be that only before modifying a block to
>> add an new entry to the leaf block oracle checks the unique constraint, but
>> since the update statement, it has to modify the leaf block by deleting an
>> entry in the initial position, but in our case, the update statement looks
>> like it failing after deleting the entry and during inserting.
>>
>> Can someone please tell me whether I am correct or I am missing anything
>> here.
>>
>> Thanks,
>> Vishnu
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>

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

Received on Sat Nov 23 2019 - 12:07:04 CET