Re: Table locking

In article <38750188.D79D5820_at_tstnet.com>,   Bret <barkndog_at_mindspring.com> wrote:
> I am using Oracle 8.0.5.
> I need to lock an individual table, not only to updates but to
queries.
> I cannot find a way to make the server queue queries to a table while
I
> am updating it (before I commit the changes). Currently the database
> will queue updates to a table, but will allow queries.
>
> I do not need this behavior for every table, just a few particular
> ones. Could anyone give me any information or hints as to how to
> accomplish this? The documentation is unclear, and I couldn't find
this
> question at orafaq.com. Thanks for any help.
>
> Bret
>
>

Oracle has made the statement that updaters never block readers so I am not sure there is anyway to do what you want.

You can issue a 'lock table in exclusive mode' command (see SQL manual) to stop anyone else from issuing DML against the table, but I do not think this statement stops readers. Once you update a row in the table readers will be given access to the original unchaged data from the rollback segments. Since readers (excluding read for update) never block updates are you sure you really need to block them out.

But speaking of read for update, will that meet your needs? It will retrieve every row that meets your where clause requirements and place a lock each row. But it does not lock unaffected rows in the table, and a since a select for update can not span commits it can be a very large transaction requiring a lot of rbs space to support. If the transaction is long the effect on your other transaction can be long waits.

You may want to check the SQL manual or probably better yet the Application Developers Guide which has a complete section on locking.

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Jan 07 2000 - 12:19:48 CST