Re: Row-X (SX) lock on materialized view
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Wed, 3 Jun 2009 12:10:09 -0700 (PDT)
Message-ID: <b7c6b135-04bb-45ab-8eb8-dec943abe95d_at_n19g2000vba.googlegroups.com>
On Jun 3, 1:27 pm, yf..._at_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..._at_vtn1.victoria.tc.ca) wrote: : I am examining a
>
> database. The tables are all replicated to a second : 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
Date: Wed, 3 Jun 2009 12:10:09 -0700 (PDT)
Message-ID: <b7c6b135-04bb-45ab-8eb8-dec943abe95d_at_n19g2000vba.googlegroups.com>
On Jun 3, 1:27 pm, yf..._at_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..._at_vtn1.victoria.tc.ca) wrote: : I am examining a
>
> database. The tables are all replicated to a second : 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
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 -- Received on Wed Jun 03 2009 - 14:10:09 CDT