Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Off Topic: Row Locking - Row Id

RE: Off Topic: Row Locking - Row Id

From: <>
Date: Tue, 13 Feb 2001 09:57:20 -0800
Message-ID: <>

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.


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');



        cursor c_hoser ( hoser_name_in )
        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 := 'Lister';
        open c_hoser(v_name_to_book);
        fetch c_hoser into v_hoser_rowid;
        close c_hoser;

        v_lock_handle := 

        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');
                dbms_output.put_line( 'Error attempting to lock ' || v_name_to_book || 
' return val: ' || to_char(v_lock_result));
        end if;


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:

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: (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

Original text of this message