Write consistency : Not all restarts are equals

When writing my blog post Write consistency and DML restart i noticed something that had motivated me to dig deeper. And here we go !

TEST ENV : OEL 7.5 / UEK5 / ORACLE 12.2.0.1 /stap 4.0

Test script : I used a similar script as  in my previous blog post

drop table DEMO;
create table DEMO (ID ,STATUS,NAME,SALT,SALT2, CREATED)
as select rownum,cast('HOLDING' as varchar2(10)),cast(null as varchar2(10)),cast(rpad('*',2000) as varchar2(2000)),cast(rpad('*',2000) as varchar2(2000)),sysdate+rownum/24/60 from xmltable('1 to 20');

exec dbms_stats.gather_table_stats(null,'DEMO');

Here is the rows distribution :

select HEADER_BLOCK,BLOCKS from dba_segments where segment_name = 'DEMO';

select DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) "block#" ,id from demo;

So every block contain only one row.

And here is my test case :

Session 1	Session 2	Session 3 (Write consistency issue)
UPDATE DEMO SET STATUS = ‘NEW’ WHERE id = 10 and STATUS = ‘HOLDING’ ;
	UPDATE DEMO SET STATUS = ‘NEW’ WHERE id = 11 and STATUS = ‘HOLDING’ ;
		alter session set events ‘trace[DML] disk=high’; UPDATE DEMO SET STATUS = ‘NEW’ WHERE id in ( 9,10,11,12) and STATUS = ‘HOLDING’ ;
commit;
	commit;
		Statement will restart multiple times under the hood

We will focus on 4 rows with ID 9,10,11,12 stored respectively in BLOCKS 139,140,141,142.

I Created a small systemtap script  write_consistency.stp (stap -v write_consistency.stp object# -x porcess_pid) to analyze what’s going on in session 3. I also used gdb to stop execution at different point and take a block dump of BLOCK 139 where row with ID 9 is stored.

Let’s interpret the  result :

Block dump of block 139 before doing anything :

Start the test (Tracing session 3 using our systemtap script ) :

The NOT LOCKED phase

The row with ID 9 stored in BLOCK 139 has been updated. We can  verify also using a block dump  :

When we tried to update row with ID 10 stored in block 140 we noticed that this row is locked so we started waiting for session 1.

After the session one has committed and we checked the current image of the block we found a write consistency issue as the value of the status column changed .The statement is then immediately rolled back and restarted entering the LOCK phase.

A block dump of BLOCK 139 (ID=9) after the rollback :

The LOCK phase

The statement is now running in the LOCK phase and will go into SELECT FOR UPDATE mode. A block dump of BLOCK 139 (ID=9)  after the locking :

When we tried to lock row with ID 11 stored in block 141 we noticed that this row is already locked so we started waiting for session 2.

After the session two has committed and we checked the current image of the block we found a write consistency issue as the value of the status column changed .The statement in this case is not immediately restarted. It continue executing the LOCK phase  !

The LOCK phase again

After hitting the second write consistency issue the LOCK phase is restarted again after the first LOCK phase is finished.

The ALL LOCKED phase

As there is no more write consistency issue ! The update enter now the “ALL LOCKED” phase and run the UPDATE against that locked set of data, thus ensuring this time that it can complete without restarting.

A block dump of BLOCK 139 (ID=9) after the update :

Basically if a write consistency issue is hit in the NO LOCKED phase the statement is immediately rolled back and restarted to enter the LOCK phase.In the case another  consistency issue is hit in the LOCK phase the statement is not restarted  immediately this time but it wait until the current LOCK phase is finished and then restart the LOCK phase again.

That’s it 😀