Skip to Main Content
  • Questions

  • Deadlocks caused by REFRESH FAST ON COMMIT?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Matthew.

Asked: February 13, 2012 - 10:46 am UTC

Last updated: February 14, 2012 - 12:19 pm UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Tom,

A few years ago, I was considering whether I could speed up a process I had by using a materialized view with REFRESH FAST ON COMMIT.

In testing, I noticed I starting getting deadlock errors, which had never happened in that process before.

Without time to investigate, I backed off of the approach for fear that multiple sessions were causing the MV to update and were each deadlocking trying to access the summary rows in the MV.

Now, in 11g, I am curious again so I put together a test...

create table matt_bt ( a number, b number )


create materialized view matt_mv (a, sum_b) refresh fast on commit
as select a, sum(b) sum_b from matt_bt group by a;

Session 1:

insert into matt_bt values (1, 5);

Session 2:

insert into matt_bt values (2, 10);

Session 1:

insert into matt_bt values (2, 10);

Session 2:

insert into matt_bt values (1, 5);

commit;

Session 1:

commit;

No deadlocks, and values in the MV are correct.

Was deadlocking ever a problem with REFRESH FAST ON COMMIT or did I just give up too easily? Is there, perhaps, a more complicated case in 11g where this could be a problem?

I've always been afraid of this type of MV because of this issue... I'd love for you to be able to set my mind as ease if you could!

Thanks in advance!

Matt





and Tom said...

the refresh fast on commit materialized view will cause serialization at commit time, during the refresh. I should only introduce deadlock issues if for some reason - someone explicitly took a lock out on the materialized view itself.


You should be concerned about the serialization issues at commit time, in a high end system anyway. Serialization points should in general be avoided.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks & followup question

Matthew McPeak, February 13, 2012 - 4:18 pm UTC

That is great. Getting lazy now, so please feel free to respond with a RTFM... but if the commit-time serialization grew to be excessive, how would that manifest itself (other than user complaints). That is, is there a particular wait event I'd see? Or would it be indistiguishable from other database activity going on?

Thanks again!
Tom Kyte
February 14, 2012 - 8:01 am UTC

it would manifest itself as a "enq: JI - contention" lock wait. the JI lock is the materialized view refresh lock.

If you want to see it, you can use this script:

set echo on

drop table t;
drop materialized view mv;
drop sequence s;


create sequence s;
create table t ( x int primary key, y int );
create materialized view log on t with rowid including new values;

create materialized view mv
refresh fast on commit
as select count(*) from t;


!sqlplus / @test2 &
!sqlplus / @test2 &
!sqlplus / @test2 &
!sqlplus / @test2 &
!sqlplus / @test2 &
!sqlplus / @test2 &
!sqlplus / @test2 &
!sqlplus / @test2 &
!sqlplus / @test2 &
!sqlplus / @test2 &
!sqlplus / @test2 &
!sqlplus / @test2 &
!sqlplus / @test2 &
!sqlplus / @test2 &
!sqlplus / @test2 &

exec dbms_lock.sleep(2);
exec statspack.snap
exec dbms_alert.signal( 'WAIT_FOR_ME', '' );
commit;
exec dbms_lock.sleep(2);
exec statspack.snap




where test2.sql is:

exec dbms_alert.register( 'WAIT_FOR_ME' );
insert into t values ( s.nextval, 42 );
variable m varchar2(255)
variable s number
exec dbms_alert.waitone( 'WAIT_FOR_ME', :m, :s );
commit;
exit


You might expect to see something like this:


Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
enq: JI - contention                                14           3    186   26.4
PL/SQL lock timer                                    1           2   2076   21.1
CPU time                                                         2          16.1
log file sync                                       17           2     92   15.9
buffer busy waits                                   17           0     22    3.8
          -------------------------------------------------------------



the 14 JI waits are the 14 sqlplus's waiting for the first sqlplus to finish and then the 14th and so on...

Thanks again

Matthew McPeak, February 14, 2012 - 8:46 am UTC

That's mindbogglingly useful and not even published on your site. It makes me wonder what other treasures you're hiding!

Thanks again!



Tom Kyte
February 14, 2012 - 9:04 am UTC

what isn't published on my site?

This question

Matthew McPeak, February 14, 2012 - 10:10 am UTC

You answered my question without publishing it. It made me wonder what gems there are in unpublished answers to other people's questions.

It was an off-hand comment -- didn't mean anything by it. Thanks again.

Tom Kyte
February 14, 2012 - 12:19 pm UTC

ah, I meant to make it viewable by all - sorry about that... changed the status

Materialized view issue after migration to 11g

Max, February 18, 2014 - 3:02 pm UTC

Dear Tom,

after migrating one of our databases from 10gR2 to 11gR2 we are experiencing problems with one of its materialized views (fast refresh on commit and additional check constraint). An application which used to work for years without such problems now just raises an exception: com.arjuna.ats.internal.jta.transaction.arjunacore.commitwhenaborted] Could not commit transaction.

Could you please help?

Thank you in advance.
Max
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.