Redo and Undo

1. Create a redo change vector describing how to insert an undo record into an undo block.

2. Create a redo change vector for the data block change.

3. Combine the redo change vectors into a redo record and write it to the log buffer.

4. Insert the undo record into the undo block.

5. Change the data block.

Here’s a symbolic dump of the fifth row in the block before and after the update:

tab 0, row 4, @0x1d3f
tl: 117 fb: --H-FL-- lb: 0x0 cc: 4
col 0: [ 2] c1 0a
col 1: [ 2] c1 06
col 2: [ 6] 78 78 78 78 78 78
col 3: [100]
30 30 30 30 30 30 30 30 … 30 30 30 30 30 (for 100 characters)
tab 0, row 4, @0x2a7
tl: 121 fb: --H-FL-- lb: 0x2 cc: 4
col 0: [ 2] c1 0a
col 1: [ 2] c1 06
col 2: [10] 59 59 59 59 59 59 59 59 59 59
col 3: [100]
30 30 30 30 30 30 30 30 … 30 30 30 30 30 (for 100 characters)

Dump of the current redo log file:

TYP:0 CLS: 1 AFN:11 DBA:0x02c0018a SCN:0x0000.03ee485a SEQ: 2 OP:11.5
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x0080009a.09d4.0f
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x02c0018a hdba: 0x02c00189
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 4(0x4) flag: 0x2c lock: 2 ckix: 16
ncol: 4 nnew: 1 size: 4
col 2: [10] 59 59 59 59 59 59 59 59 59 59

I’ll pick out just the most significant bits of this change vector. You can see that the Op code: in line 5 is URP (update row piece). Line 6 tells us the block address of the block we are updating (bdba:) and the segment header block for that object (hdba:). In line 7 we see that the transaction doing this update is using ITL entry 2 (itli:), which confirms what we saw in the block dump: it’s an update to tabn: 0 slot: 4 (fifth row in the first table; remember that blocks in a cluster can hold data from many tables, so each block has to include a list identifying the tables that have rows in the block). Finally, in the last two lines, we see that the row has four columns (ncol:), of which we are changing one (nnew:), increasing the row length (size:) by 4 bytes, and that we are changing column 2 to YYYYYYYYYY.

Dump of the relevant undo block:

*-----------------------------
* Rec #0xf slt: 0x1a objn: 45810(0x0000b2f2) objd: 45810 tblspc: 12(0x0000000c)
* Layer: 11 (Row) opc: 1 rci 0x0e
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x0080009a.09d4.0d
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x02c0018a hdba: 0x02c00189
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 4(0x4) flag: 0x2c lock: 0 ckix: 16
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78

  

We need another redo change vector for undo:

TYP:0 CLS:36 AFN:2 DBA:0x0080009a SCN:0x0000.03ee485a SEQ: 4 OP:5.1
ktudb redo: siz: 92 spc: 6786 flg: 0x0022 seq: 0x09d4 rec: 0x0f
xid: 0x000a.01a.0000255b
ktubu redo: slt: 26 rci: 14 opc: 11.1 objn: 45810 objd: 45810 tsn: 12
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x0080009a.09d4.0d
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x02c0018a hdba: 0x02c00189
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 4(0x4) flag: 0x2c lock: 0 ckix: 16
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78

ACID

Atomicity : A transaction must be invisible or complete.

Consistency : The database must be self-consistent at the start and end of each transaction.

Isolation : A transaction may not see results produced by another incomplete transaction.

• isolation levels: read committed (the default), read only, and serializable
• Session 1: select from t1;
  Session 2: insert into t1 select * from t1;
  Session 2: commit;
  Session 1: select from t1;
  Session 1: insert into t2 select * from t1;
  If session 1 is operating at isolation level read committed, it will select one row on the first select, select two rows on the second select, and insert two rows.
  If session 1 is operating at isolation level read only, it will select one row on the first select, select one row on the second select, and fail with Oracle error “ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction.”
  If session 1 is operating at isolation level serializable, it will select one row on the first select, select one row on the second select, and insert one row.
  

Durability : A committed transaction must be recoverable after a system failure.

Redo generation

The critical bottleneck in redo generation:

A single session might make many changes in a very short period of time, and there could be many sessions operating concurrently—and there’s only one redo log buffer that everyone wants to access.

Solution : redo allocation latch which avoids the threat of having multiple processes overwrite the same piece of memory in the log buffer.

Private redo

• A process generate all its change vectors and storing them in a pair of private redo log buffers. When the transaction completes, the process copies all the privately stored redo into the public redo log buffer.
• This means that a process acquires the public redo allocation latch only once per transaction, rather than once per change.
• The option for multiple log buffers with the log_parallelism parameter(unless you had at least 16 CPUs)
• v$latch   v$sesstat   v$latch_children   x$kcrfstrand(private redo)   x$ktifp(in-memory undo pool)
  

In-memory undo

• Store one undo for per change not transaction. We now hit only one latch (In memory undo latch) instead of two (redo allocation and redo copy)
• Although we now hit an In memory undo latch just as many times as we used to hit the redo allocation latch, we are spreading the access across far more latches.
  

Undo Complexity

Read Consistency

Rollback