RE: Off Topic: Row Locking

From: <jkstill_at_cybcon.com>
Date: Tue, 13 Feb 2001 09:57:20 -0800
Message-ID: <F001.002B2A6E.20010213082045@fatcity.com>

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).

Received on Tue Feb 13 2001 - 11:57:20 CST

RE: Off Topic: Row Locking - Row Id