Home » SQL & PL/SQL » SQL & PL/SQL » Read Lock (11.2.0.3.0)
Read Lock [message #576352] Mon, 04 February 2013 00:44 Go to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Hi Experts

I have a table with 3 records, there is package which will insert record in this table after doing some calculation for one column which will have unique identifier values(col1),

col1  col2
----- -----
1     ris1
2     ris2
3     ris3


so it will generate something as below

col1  col2
----- -----
1     ris1
2     ris2
3     ris3
4     tyu


There is a possibility that two transactions at a time use this package and come up with value as 4 for col1, how this can be prevented?
I read table to generate the next value to be inserted so i want a read lock on table so that other package can't read it.

I don't want to use sequence for this column.

Re: Read Lock [message #576353 is a reply to message #576352] Mon, 04 February 2013 00:51 Go to previous messageGo to next message
Littlefoot
Messages: 19335
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Lock table in exclusive mode.
Re: Read Lock [message #576355 is a reply to message #576353] Mon, 04 February 2013 01:04 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Thanks Littlefoot for the reply

But even when the tables are locked in exclusive mode we can read them from other sessions

My requirement is that my package will read table data and it will get last value inserted i.e 3 and now it has to insert 4
but at the same time other user can also fire same package and it can also calculate same value 4

what i want is one transaction should wait and at a time only one package should calculate the value

[Updated on: Mon, 04 February 2013 01:05]

Report message to a moderator

Re: Read Lock [message #576356 is a reply to message #576355] Mon, 04 February 2013 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 58630
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use DBMS_LOCK to create and use a custom lock that your package will get and release.

Of course, this means serializing all sessions using this package, the good implementation should be to use a sequence.

Regards
Michel
Re: Read Lock [message #576357 is a reply to message #576355] Mon, 04 February 2013 01:09 Go to previous messageGo to next message
Littlefoot
Messages: 19335
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You can't prevent readers from reading. Your problem doesn't seem to be that, but the way you calculate the next value.

As you don't want to use a sequence (why not, by the way?), you could create another table which would contain only the next COL1 value. Once your session picks it up (using SELECT FOR UPDATE, for example), it releases it and makes it available for anyone else.
Re: Read Lock [message #576362 is a reply to message #576357] Mon, 04 February 2013 01:27 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Thanks Michel, apparently dbms_lock can work out

Thanks Littlefoot, because of some application issue we are not using sequence, BTW i love sequences Cool
Re: Read Lock [message #576372 is a reply to message #576362] Mon, 04 February 2013 03:56 Go to previous messageGo to next message
John Watson
Messages: 4406
Registered: January 2010
Location: Global Village
Senior Member
Have you defined a uniqure constraint on the column? If so, the attempt to insaert a duplicate value will generate an ora-00001. You could catch this in an exceptions clause, and retry for the next number.
Re: Read Lock [message #576382 is a reply to message #576372] Mon, 04 February 2013 06:02 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
John, valid approach but dbms_lock did the trick for me , thanks

Thanks Michel & Littlefoot for your valuable suggestions, at last i was able to serialize my package execution

SQL> select * from test2 order by 1;

        ID NAME
---------- -------------------------------
         2 ris2
         5 ris5
         6 ris6

SQL> CREATE OR REPLACE package packa IS
  2  PROCEDURE p_ins;
  3  END;
  4  /

Package created.


SQL> CREATE OR REPLACE
  2  PACKAGE BODY packa
  3  IS
  4  PROCEDURE P_ins
  5  IS
  6    l_count      INTEGER :=0;
  7    v_val        INTEGER;
  8    l_lockhandle VARCHAR2(128);
  9    l_return     INTEGER;
 10  BEGIN
 11    dbms_lock.allocate_unique( lockname => 'SHERLOCK$', lockhandle => l_lockh
andle);
 12    l_return := dbms_lock.request (lockhandle => l_lockhandle , lockmode => d
bms_lock.x_mode , release_on_commit => true);
 13    SELECT COUNT(*) INTO l_count FROM test2;
 14    --DBMS_OUTPUT.PUT_LINE(l_count);
 15    BEGIN
 16      SELECT id
 17      INTO v_val
 18      FROM
 19        (SELECT level AS id FROM dual CONNECT BY level<=l_count
 20        MINUS
 21        SELECT id FROM test2
 22        )
 23      WHERE rownum<2;
 24      --DBMS_OUTPUT.PUT_LINE(v_val);
 25    EXCEPTION
 26    WHEN no_data_found THEN
 27      IF v_val IS NULL THEN
 28        v_val  :=l_count+1;
 29      END IF;
 30    END;
 31    DBMS_OUTPUT.PUT_LINE('Insert value='|| v_val);
 32    INSERT INTO test2 VALUES
 33      (v_val,'ris_new'||v_val
 34      );
 35    dbms_lock.sleep
 36    (
 37      15
 38    )
 39    ;
 40    COMMIT;
 41  END;
 42  END packa;
 43  /

Package body created.


session 1
SQL> select sys_context('userenv','sid')as sid from dual;

SID
---------------------------------------------------------

149

SQL> exec packa.p_ins
Insert value=1

PL/SQL procedure successfully completed.


session 2
SQL> select sys_context('userenv','sid')as sid from dual;

SID
------------------------------------------------------------------

156

SQL> exec packa.p_ins
Insert value=3

PL/SQL procedure successfully completed.


SQL> select * from test2 order by 1;

        ID NAME
---------- ---------------------------------
         1 ris_new1
         2 ris2
         3 ris_new3
         5 ris5
         6 ris6




Re: Read Lock [message #576516 is a reply to message #576382] Tue, 05 February 2013 15:23 Go to previous messageGo to next message
Bill B
Messages: 1068
Registered: December 2004
Senior Member
Why are you using a minus query when you can do a simple

select max(id) into v_val from test2;

and why sleep. The commit releases the lock and commits the row at the same time.

[Updated on: Tue, 05 February 2013 15:25]

Report message to a moderator

Re: Read Lock [message #576530 is a reply to message #576516] Tue, 05 February 2013 23:22 Go to previous message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Dear Bill,

select max(id) into v_val from test2;
will give me max id , i am trying to find missing id values , If no missing then next id.

Quote:
why sleep
Session 1 and Session 2 were fired simultaneously with a minute difference of 2 secs ,since Session 1 got the lock on the package using DBMS_LOCK so now when Session 2 will wait until Session 1 release the lock So when Session 1 sleeps Session 2 wait for 15 sec to get the lock. Sleep is just to check it works , This is just a demo code, In my actual code there are lot of stuff going on which will eat lot of time
Previous Topic: Collections
Next Topic: Conditional query using SYSDATE ignored and all rows returned, regardless
Goto Forum:
  


Current Time: Thu Jul 31 02:04:52 CDT 2014

Total time taken to generate the page: 0.12438 seconds