RE: Off Topic: Row Locking

From: Johan Locke_at_i-Commerce Services <Locke_at_i-Commerce>
Date: Tue, 13 Feb 2001 23:20:44 -0800
Message-ID: <F001.002B3722.20010213232054@fatcity.com>

Hi Jared

Thanx for the reply.

I'll do some stress testing, and keep you up to date.

(I'm not too optimistic though)

Cheers
JL

-----Original Message-----
Sent: Tuesday, February 13, 2001 6:21 PM To: Multiple recipients of list ORACLE-L

Depending on your application design, you may find a locking scheme useful. Below is an example that uses rowid to take out a lock. Note that this is not a lock on an object, but simply a lock identified by a rowid.

As the demo is setup, the locks are automatically released on rollback or commit, which would include exiting a session.

Not for use on OPS w/MTS.

See dbmslock.sql for details.

Jared

run this part from session A:

drop table lock_test;

create table lock_test ( name varchar2(10) not null );

insert into lock_test values ( 'nebula');
insert into lock_test values ( 'quasar');
insert into lock_test values ( 'pulsar');
insert into lock_test values ( 'red dwarf');
insert into lock_test values ( 'Lister');
insert into lock_test values ( 'Rimmer');
insert into lock_test values ( 'Cat');

commit;

Then run this section from session A and session B

declare

        cursor c_hoser ( hoser_name_in lock_test.name%type )
        is
        select rowid
        from lock_test
        where name = hoser_name_in;

        lock_handle pls_integer;

        v_hoser_rowid rowid;

        v_lock_handle pls_integer;

        v_lock_result pls_integer;

        v_name_to_book lock_test.name%type;

begin

        v_name_to_book := 'Lister';
        open c_hoser(v_name_to_book);
        fetch c_hoser into v_hoser_rowid;
        close c_hoser;

        v_lock_handle :=

dbms_utility.get_hash_value(v_hoser_rowid,1000000,power(2,20));

        v_lock_result := dbms_lock.request(
                id => v_lock_handle
                , timeout => 1
                , release_on_commit => true
        );

        if v_lock_result = 0 then
                dbms_output.put_line( 'You have booked ' || v_name_to_book
);
        elsif v_lock_result = 1 then
                dbms_output.put_line( v_name_to_book || ' is currently
booked');
        else
                dbms_output.put_line( 'Error attempting to lock ' ||
v_name_to_book || ' return val: ' || to_char(v_lock_result));
        end if;

end;
/

On Mon, 12 Feb 2001, Johan Locke_at_i-Commerce Services wrote:

> Thanks Riyaj
>
> Unfortunately it doesn't solve my problem. It only helps if the

transaction

> is BLOCKING another transaction.
>
> This is my requirement, maybe somebody has a good solution.
>
> A product selection engine. There are a limited number of products, each
> unique.
>
> Person A comes in over the web (this important). Looks at the products and
> "books" the product he wants. At this stage I just want to issue a "SELECT
> FOR UPDATE" - without commiting. Person A goes through the payment
> selection, and if succesfull, the product is marked as "bought" and the
> transaction commited.
>
> If during the process of payment authorisation for Person A, person B

looks

> through the products, Person B must see the product person A is buying as
> "Booked - not yet bought".
>
> Why don't I just set a flag in the row, commit it, do the payment and

commit
> that?
>

> ------
> If for some reason person A's web session terminates voluntarily or
> involuntarily, I'd have to run a process in the background which cleans up
> the flags. As this is a very processing intensive table, it slows down the
> processing tremendously. Conversly, if I could use the user's web session
> termination, which will terminate the database session, to make oracle
> release the lock on that row it makes my life a lot easier.
>
> About 90% of the people will access the site within a period of about an

1-2
> hours (within which you're aiming to sell 54000 of the 60000 products) >

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: jkstill_at_cybcon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Johan Locke_at_i-Commerce Services
INET: Johan.Locke_at_za.didata.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

RE: Off Topic: Row Locking - Row Id