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';

Capture 02


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

Capture 01

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 :

Capture 04

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

The NOT LOCKED phase

Capture 03

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

Capture 05

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.

Capture 15

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 :

Capture 07

The LOCK phase

Capture 09

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 :

Capture 10

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.

Capture 11

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

Capture 12

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

The ALL LOCKED phase

Capture 13

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 :

Capture 14

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 😀

2 thoughts on “Write consistency : Not all restarts are equals

  1. Hi
    In “before doing anything” block dump, column status has value HOLDING. Why? You created table with all rows on column status set to NEW value. Did I miss something?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s