Re: Re : Row Level Share Locks

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 16 Sep 1998 19:57:50 GMT
Message-ID: <36101730.101768775@192.86.155.100>

A copy of this was sent to "Alice" <Alice.Explorer_at_mtu-net.ru> (if that email address didn't require changing) On Wed, 16 Sep 1998 20:59:46 +0400, you wrote:

>My idea of using smth like row level share locks was to do the following :
>
>Suppose, a lot of operators are placing orders into a db. Each order will be
>processed by the db according to the customer parameters (each order
>references a customer).
>I want to make sure that during that processing customer parameters will not
>change.
>Because one of the consequences of changing a customer's parameters would be
>changing some orders (all customers' parameters changes are logged in a
>separate table).
>My idea of resolving such a situation was to place a share row level lock on
>customer's record.
>
>Regards,
>George
>

Ok, that would be a use of it. Here is a way to do it in Oracle. We will use the dbms_lock package. The client that wants a shared lock on a customer row will have to call the procedure "share_lock_customer( customerid )" to share lock that row -- many people can call that routine successfully. The updater of the customer table will not have to do anything special, a trigger will take care of them.

It might look like:

create table customers(  custid     number primary key,
                         parameters varchar2(25) );

create table orders( orderid        number,
                     custid         number references customers(custid),
                     somedata       varchar2(25) );

create or replace trigger customers_bufer before update on customers
for each row
declare

    resource_busy exception;
    pragma exception_init( resource_busy, -54 ); begin

    if ( dbms_lock.request(  id                => :new.custid,
                             lockmode          => dbms_lock.x_mode,
                             timeout           => 0,
                             release_on_commit => TRUE ) <> 0 )
    then
        raise resource_busy;

    end if;
end;
/

create or replace procedure share_lock_customer( p_custid in number ) as

    resource_busy exception;
    pragma exception_init( resource_busy, -54 ); begin

    if ( dbms_lock.request(  id                => p_custid,
                             lockmode          => dbms_lock.sx_mode,
                             timeout           => 0,
                             release_on_commit => TRUE ) <> 0 )
    then
        raise resource_busy;

    end if;
end;
/

The way it currently is setup, you will get an immediate nonblocking "Resource Busy" message if you attempt to update a row and there is a 'share lock' on it. Likewise, attempts to get a 'share lock' will result in a "Resource busy" message in the event someone has it 'locked' already.

>
>Thomas Kyte wrote
><35ffc0f9.14161102_at_192.86.155.100> ...
>>A copy of this was sent to tjb839_at_0.0.0.0 (Tim Boemker)
>>(if that email address didn't require changing)
>>On 15 Sep 1998 18:10:05 GMT, you wrote:
>>
>>>Why doesn't Oracle have row share locks? (Maybe an Oracle engineer
>>>could answer this one...)
>>>
>>
>>I would ask why would you want them?
>>
>
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Sep 16 1998 - 14:57:50 CDT