Re: Row-X (SX) lock on materialized view

From: joel garry <>
Date: Thu, 4 Jun 2009 10:51:32 -0700 (PDT)
Message-ID: <>

On Jun 3, 2:04 pm, (Malcolm Dew-Jones) wrote:
> Mark D Powell ( wrote:
> : On Jun 3, 1:27=A0pm, (Malcolm Dew-Jones): wrote:
> : > Of course my subject is wrong, the lock is on the LOG (on the master
> : > database), not on the materialized view on the other database.
> : >
> : > Malcolm Dew-Jones ( wrote: : I am examining a
> : >
> : > database. =A0The tables are all replicated to a second : database every t=
> : en
> : > minutes (so the users can do ad-hoc queries without : touching the real
> : > database).
> : >
> : > : When the user's have a form open then the record they are editing is :
> : > locked. =A0I can run a query against v$lock (etc) and see who has what :
> : > tables locked. =A0Mostly, everything is as expected.
> : >
> : > : My question revolves around the following.
> : >
> : > : For one particular table, the materialized view log has also turned up
> : > in : the lock list some times (Row-X (SX), same ctime as the data table).=
> :  =A0
> : > I : haven't seen that for other tables.
> : >
> : > : Is that normal? =A0Perhaps the other snapshot logs are locked and I jus=
> : t :
> : > don't happen to see it ever. =A0Is this something that needs investigatin=
> : g?
> : >
> : > : Opinions welcome.
> : >
> : > : Thanks, Malcolm Dew-Jones
> : Oracle uses tables to hold the logs used to update the materialized
> : view so if you insert a new row into the base table then the log table
> : is also updated as part of your transaction.  If you rollback then
> : there is not log row to be copied to the materialized view.  This
> : seems normal to me though I currently have no materialized view using
> : refresh logs to check against.
> : Being that writers do not block readers why does it matter?  Is this
> : just the first time you remember seeing the assoicated locks or was
> : something happening that you were checking into when you saw this?
> I have been asked to check why an application has locking issues, so I am
> investigating that.
> One key table typically has a dozen row locks (users editing in the main
> form of the app), and a few other tables also turn up regularly.  All of
> those tables have corresponding MLOG$_x tables, but Of those tables, I
> have only seen one (now two) that ever show a corresponding lock on their
> corresponding MLOG$_x table, I was not sure why this should be so.

It could be that the others are so fast you never see them?

> Forms locks a row when the user edits the fields in the form, but nothing
> in the database changes until they save, at which point the data is
> committed as well, so I guess that any lock on the MLOG$_x would only be
> for that breif period between the UPDATE and the COMMIT, so I guess it
> makes sense that I never normally see locks on the MLOG$_x tables.

Welllllll, are you sure nothing in the database changes? The data may be in the dirtied SGA buffers, not written to the db, but need undo to be reconstructed by other transactions, and of course the transaction needs to be able to see its own changes. The commit merely means it could be written out to the db sometime in the future - it assures that the redo log has been written.

And of course, PL/SQL does asynchronous commits:

> So I guess as I think out loud, that that must mean that the unusual table
> (that shows a lock on the MLOG$_x)  has had an UPDATE (generating a lock
> on its MLOG$_x) but has not yet had a COMMIT.  Since the forms interface
> always COMMITS as part of the UPDATE or INSERT that it generates, then
> there must be some other code in here that is doing an UPDATE (or INSERT)
> against that one table outside of the normal edit/save/commit cycle of
> oracle forms.

Or could it be some race condition for the single transaction v. the mlog? (I don't know, I'm thinking out loud too.)

> As for "writers do not block readers why does it matter?", reading is not
> a problem, but writing is - when many people are using the application
> then apparently there are times when "everybody" is locked up. I never
> entirely trust peoples descriptions of their problems, but I suspect that
> what is happening at least part of the time is that one user, while
> filling in a form, causes code to run that does an INSERT that is not
> committed, and that that insert then prevents the next user from
> completing an INSERT into that same table, and that makes it appear as if
> the application is hung (which it is, sort of).  Unlike when multiple
> people try to edit the same row at the same time, there is no built in way
> to detect the insert issue ahead of time (unlike SELECT FOR UPDATE
> NOWAIT), and no way to do anything in the form (such as show an error -
> "Sorry, you waiting for someone else to commit before your insert will
> procede").

Note that forms does a select for update nowait (assuming this is not out of date):

> I'm not sure (yet) how this situation is best viewed in the various
> session locking tables to try to confirm this and confirm which tables
> might be involved, it doesn't appear to turn up as a normal lock, but
> perhaps that's because that is not the problem, or perhaps I simply don't
> know enough yet.
> $0.10

Is there a difference between how the mviews are defined? I'm wondering if it is something like the strange one uses on commit, or something.

What was the locking problem you were investigating?


-- is bogus.
"If there are no stupid questions, then what kind of questions do
stupid people ask? Do they get smart just in time to ask questions?" -
Scott Adams
Received on Thu Jun 04 2009 - 12:51:32 CDT

Original text of this message