Re: Row-X (SX) lock on materialized view
Date: 3 Jun 2009 14:04:37 -0700
Mark D Powell (Mark.Powell_at_eds.com) wrote:
: On Jun 3, 1:27=A0pm, yf..._at_vtn1.victoria.tc.ca (Malcolm Dew-Jones)
: > 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. =A0The tables are all replicated to a second : database every t=
: > 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).=
: > 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=
: > : 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 Received on Wed Jun 03 2009 - 16:04:37 CDT