Re: Serialize access to tables

On Tue, 24 Oct 2000 19:28:26 GMT, "Shelby Cain" <scain_at_remove-all-this-aisconsulting.com> wrote:

>Is there any way to lock a table (Oracle 8.0.x or 8.1.x) such that only one
>session at a time could access it (even for just reading)? I need to be
>able to implement some sort of mutex in PL/SQL and this is driving me crazy
>as it seems I can lock a table to deny writes to other sessions -- but that
>does little good for me if I'm updating a value and before I've committed it
>another session reads the old value.
>
>Any help or suggestions would be appreciated.
>
>Regards,
>
>Shelby Cain

Use a second table for serialising. Let's call it the dummy table.

Your transaction would then be

Start trans

   explicitly lock dummy table
   work on real table
Commit

What then happens is other users are free to commence this transaction as well, but they will wait until the explicit lock is released on the dummy table from a "previous" user before getting to access/work with the table you're interested in.

Advantages: Very simple, very easy, completely platform independent (works for any RDBMS), bog standard SQL (doesn't rely on flavour-of-the-month techniques)

Disadvantages: Make sure there's no human interaction in this transaction, or people will be blocked while some bozo goes to lunch.

I'm sure you'll see plenty of other techniques suggested too.

Ciao
Fuzzy
:-) Received on Tue Oct 24 2000 - 17:54:18 CDT