Re: Constraint policing - redo generation? transaction failing
Date: Sat, 23 Nov 2019 17:41:13 +0530
Message-ID: <CAP-RywyBp-MMHv_59PH1wE1R4UYzZUGST2x_n108hijerh5gEQ_at_mail.gmail.com>
Tested again using several cases, this time with a more visible case
instead of an update that results in just one value.
create table temp (roll number, name varchar2(20), mark1 number, mark2
number);
alter table temp add constraint temp_pk primary key (mark1,roll);
loaded the entires such that about 99% of the rows in the table has mark1
as 0.
and again updated a single row with mark1 column as 99 as follows:
SQL> select max(roll) from temp where mark1 =0; MAX(ROLL)
999979
SQL> insert into temp values(999979,'VISHNU',99,100,100);
1 row created.
SQL> commit;
Commit complete.
SQL> update temp set mark1=99 where mark1=0;
update temp set mark1=99 where mark1=0
*
ERROR at line 1:
ORA-00001: unique constraint (VISHNU.TEMP_PK) violated
This time checked redo/undo generation for the session, the redo/undo
generation is substantial
NAME VALUE
---------------------------------------------------------------- ----------redo size 76368316
undo change vector size 19558228
NAME VALUE
---------------------------------------------------------------- ----------redo size 142302356
undo change vector size 34318536
the log file dump
[oracle_at_oracle trace]$ cat noncdb_ora_22462.trc| grep "restore leaf" | wc -l
1186
in this case Oracle probably has no way of knowing that a value may collide with an existing value since we are updating only the leading columns... could be the reason they didn't add the constraint check policy before the update... not sure though..
Jonathan,
thanks again, your last mail reply which included the context "Oracle
assumes or infers" did change the way I looked at things...
Thanks,
Vishnu
On Sat, Nov 23, 2019 at 4:37 PM Vishnu Potukanuma < vishnupotukanuma_at_gmail.com> wrote:
> 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-lReceived on Sat Nov 23 2019 - 13:11:13 CET