Row-X (SX) lock on materialized view
Malcolm Dew-Jones
database every ten 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. I can run a query against v$lock (etc) and see who has what
tables locked. Mostly, 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). I
haven't seen that for other tables.
Is that normal? Perhaps the other snapshot logs are locked and I just
don't happen to see it ever. Is this something that needs investigating?
Opinions welcome.
Thanks, Malcolm Dew-Jones
Malcolm Dew-Jones
database), not on the materialized view on the other database.
Malcolm Dew-Jones (yf...@vtn1.victoria.tc.ca) wrote: : I am examining a
Mark D Powell
wrote:
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?
HTH -- Mark D Powell --
Malcolm Dew-Jones
: On Jun 3, 1:27=A0pm, yf...@vtn1.victoria.tc.ca (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 (yf...@vtn1.victoria.tc.ca) wrote: : I am examining a
: >
: 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 :
: > 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
: =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.
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.
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.
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").
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
Mark D Powell
wrote:
Thank you for the update. Question, now I am not a coder but our java
coders have had to disable the normal automatic commit that java
issues after every statement in order to create transactions with
several table updates all included in order to construct proper units
of work. I believe that web based Forms also offers this option.
Does the Form in question have programmer controled transactions?
The reason I mention this is if you disable the feature in one portion
of the Form it may requrie the developer to code something different
elsewhere in the Form that was forgotten, that is, issuing a manual
commit or turning auto-commit back on.
This is the only idea that comes to mind.
joel garry
wrote:
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:
http://asktom.oracle.com/pls/asktom/f?p=100:11:3752586594366850::::P11_QUESTION_ID:1415454871121#20061201507022
>
> 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): http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:851827223957
>
> 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?
jg
--
@home.com 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
Malcolm Dew-Jones
: On Jun 3, 2:04=A0pm, yf...@vtn1.victoria.tc.ca (Malcolm Dew-Jones): > : On Jun 3, 1:27=3DA0pm, yf...@vtn1.victoria.tc.ca (Malcolm Dew-Jones): w=
: rote:
: >
: > : > 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.
: > : >
: a
: > : >
: > : > database. =3DA0The tables are all replicated to a second : database e=
: very t=3D
: > : en
: > : > minutes (so the users can do ad-hoc queries without : touching the re=
: al
: > : > database).
: > : >
: > : > : When the user's have a form open then the record they are editing i=
: s :
: > : > locked. =3DA0I can run a query against v$lock (etc) and see who has w=
: hat :
: > : > tables locked. =3DA0Mostly, everything is as expected.
: > : >
: > : > : My question revolves around the following.
: > : >
: up
: > : > in : the lock list some times (Row-X (SX), same ctime as the data tab=
: le).=3D
: > : =A0=3DA0
: > : > I : haven't seen that for other tables.
: > : >
: > : > : Is that normal? =3DA0Perhaps the other snapshot logs are locked and=
: I jus=3D
: > : t :
: > : > don't happen to see it ever. =3DA0Is this something that needs invest=
: igatin=3D
: > : 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
: > : there is not log row to be copied to the materialized view. =A0This
: > : seems normal to me though I currently have no materialized view using
: > : refresh logs to check against.
: >
: > : 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
: f
: > 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:
: http://asktom.oracle.com/pls/asktom/f?p=3D100:11:3752586594366850::::P11_QU=
: ESTION_ID:1415454871121#20061201507022
: >
: > So I guess as I think out loud, that that must mean that the unusual tabl=
: e
: > (that shows a lock on the MLOG$_x) =A0has had an UPDATE (generating a loc=
: k
: > on its MLOG$_x) but has not yet had a COMMIT. =A0Since the forms interfac=
: e
: > 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). =A0Unlike when multiple
: > people try to edit the same row at the same time, there is no built in wa=
: y
: > 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): http://asktom.oracle.com/pls/asktom/f?p=3D100:11:0::::P11_QU=
: ESTION_ID:851827223957
: >
: > 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?
I use the term locking loosely, maybe its not locking.
The manager of one group reports that many people will complain that they
are hung up, some users may see the "cant reserve record" error, but
apparently not all users get that error, they just seem to hang. I get
contradictory answers as to whether screens unrelated to that part of the
application still work.
Naturally this is an external customer so I can't get my hands on
everything to examine it, like visiting the users to confirm the stories
they tell, and the databaseserver is run by the dba group of the customer
- I have a fair amount of access, but not to as much as our local dbas
have when running our own servers.
Anyway, I am going to try to get their dba's to turn on more detailed
logging to try to understand what is happening when they complain.
$0.10
Robert Klemme
> 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.
I have only very small experience with MV's so I might be totally off
mark here: Maybe an index is missing or invalid on that particular log
table so Oracle has to lock the whole table.
Another option that comes to mind is foreign keys: when there is no
index on the FK column then also table locks are held.
Kind regards
robert
--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/