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@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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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@i-Commerce Services INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Off Topic: Row Locking - Row Id
Johan [EMAIL PROTECTED] Services Tue, 13 Feb 2001 23:20:44 -0800
- Off Topic: Row Locking - Row Id Johan [EMAIL PROTECTED] Services
- Re: Off Topic: Row Locking - Row Id Saurabh Sharma
- Re: Off Topic: Row Locking - Row Id Riyaj_Shamsudeen
- RE: Off Topic: Row Locking - Row Id Johan [EMAIL PROTECTED] Services
- RE: Off Topic: Row Locking - Row Id Johan [EMAIL PROTECTED] Services
- RE: Off Topic: Row Locking - Row Id Riyaj_Shamsudeen
- RE: Off Topic: Row Locking - Row Id jkstill
- RE: Off Topic: Row Locking - Row Id Johan [EMAIL PROTECTED] Services
- RE: Off Topic: Row Locking - Row Id Johan [EMAIL PROTECTED] Services
- RE: Off Topic: Row Locking - Row Id jkstill
- Re: Off Topic: Row Locking - Row Id Thater, William
- Re: Off Topic: Row Locking - Row Id jkstill
- RE: Off Topic: Row Locking - Row Id Paul Baumgartel
- RE: Off Topic: Row Locking - Row Id jkstill
- RE: Off Topic: Row Locking - Row Id Rachel Carmichael
- RE: Off Topic: Row Locking - Row Id Rachel Carmichael
- RE: Off Topic: Row Locking - Row Id Mark Leith
- RE: Off Topic: Row Locking - Row Id jkstill