Re: Exclusive lock

Yoav wrote:

> Hi,
> Does anyone knows how can I issue a "real" exclusive lock in Oracle ?
> When saying "real", I mean that no one else can even SELECT the
> row/table.
>
> (I thought to use latch or try and update v$ tables).
>
> thanks,
>
> Joe
> ---------------------------------
> yoav_bz_at_netvision.net.il

1. I do not know what you want to solve !
2. I would do the following:
   • Revoke the select permission from the original table.
   • Create a table called e.g. lock_table in which you hold all rows which you want to have been locked for selecting. You should use a key field like a nr as primary key or something else.
   • Create a view which looks like the following: select * from <orig_table> where <key_field> not in (select <key_field> from <lock_table>); This is a very simple version with very poor performance. Try the same with the minus-operator or a join. This view returns only rows which are ___not___ listed in the lock_table. Locking and unlocking is managed over the lock_table.
   • Grant select permission only to this view.

Be careful: If you do so, the lock_table is not synchronized with any transactions. So the lock_table must be observed in order to prevent locking rows
forever when a client-connection crashes or something else.

By

Stephan Received on Sun Apr 19 1998 - 00:00:00 CDT