Re: Row-X (SX) lock on materialized view
Date: 4 Jun 2009 14:13:54 -0700
Message-ID: <4a283912$1_at_news.victoria.tc.ca>
joel garry (joel-garry_at_home.com) wrote:
: On Jun 3, 2:04=A0pm, yf..._at_vtn1.victoria.tc.ca (Malcolm Dew-Jones)
: wrote:
: > Mark D Powell (Mark.Pow..._at_eds.com) wrote:
: > : On Jun 3, 1:27=3DA0pm, yf..._at_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.
: > : >
: > : > Malcolm Dew-Jones (yf..._at_vtn1.victoria.tc.ca) wrote: : I am examining=
: 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.
: > : >
: > : > : 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 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
: > : is also updated as part of your transaction. =A0If you rollback then
: > : 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.
: >
: > : Being that writers do not block readers why does it matter? =A0Is 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. =A0All o=
: 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 Received on Thu Jun 04 2009 - 16:13:54 CDT