| Read Lock [message #576352] |
Mon, 04 February 2013 00:44  |
 |
rishwinger
Messages: 101
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 #576355 is a reply to message #576353] |
Mon, 04 February 2013 01:04  |
|
rishwinger
Messages: 101
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 #576372 is a reply to message #576362] |
Mon, 04 February 2013 03:56 |
John Watson
Messages: 3112
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 |
|
rishwinger
Messages: 101
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 |
Bill B
Messages: 989
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 |
|
rishwinger
Messages: 101
Registered: November 2011
|
Senior Member |
|
|
Dear Bill,
select max(id) into v_val from test2;
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
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
