Home » SQL & PL/SQL » SQL & PL/SQL » Select "FOR UPDATE" (Oracle 10.2.0.3)
Select "FOR UPDATE" [message #389922] |
Wed, 04 March 2009 02:30 |
adit_me1
Messages: 49 Registered: October 2007 Location: BANGALORE
|
Member |
|
|
Hello All,
I have a situation here.
I want to sequence the records of a table in ascending order, then limit them by the rownum clause and lock them for update by doing a select "FOR UPDATE" on the result set.
I want this because there are mutliple users who want to update the first available rows of the table.
Oracle is not allowing me to do that. It says "cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc."
you can refer teh code below to see the problem at your end.
CREATE TABLE temp(ID NUMBER, NAME VARCHAR2(100));
INSERT INTO temp
VALUES (1, 'a');
INSERT INTO temp
VALUES (2, 'b');
INSERT INTO temp
VALUES (3, '4');
INSERT INTO temp
VALUES (4, '5');
INSERT INTO temp
VALUES (5, '6');
INSERT INTO temp
VALUES (6, 'd');
INSERT INTO temp
VALUES (7, '9');
INSERT INTO temp
VALUES (8, 'g');
INSERT INTO temp
VALUES (9, 'k');
INSERT INTO temp
VALUES (10, 'i');
SELECT ID, NAME
FROM (SELECT ID, NAME
FROM temp
ORDER BY ID)
WHERE ROWNUM < 5
FOR UPDATE NOWAIT SKIP LOCKED
error : cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
|
|
|
|
|
|
Re: Select "FOR UPDATE" [message #389941 is a reply to message #389922] |
Wed, 04 March 2009 04:24 |
joicejohn
Messages: 327 Registered: March 2008 Location: India
|
Senior Member |
|
|
@adit_me1,
SQL> SELECT * FROM (SELECT ID, NAME
2 FROM temp_tab
3 ORDER BY ID )
4 where rownum < 5
5 FOR UPDATE NOWAIT SKIP LOCKED;
SELECT * FROM (SELECT ID, NAME
*
ERROR at line 1:
ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
From Oracle Documentation |
Restrictions on the FOR UPDATE Clause:
You cannot specify this clause with the following other constructs: the DISTINCT operator, CURSOR expression, set operators, group_by_clause, or aggregate functions.
The tables locked by this clause must all be located on the same database, and on the same database as any LONG columns and sequences referenced in the same statement.
|
Session #1
SQL> SELECT * FROM temp_tab;
ID NAME
---------- --------------------
1 a
2 b
3 4
4 5
5 6
6 d
7 9
8 g
9 k
10 i
3 new value column -- Added this row to check
11 rows selected.
SQL> SELECT a1.ID, a1.NAME
2 FROM temp_tab a1,
3 (SELECT ID, NAME
4 FROM (SELECT ID, NAME
5 FROM temp_tab
6 ORDER BY ID)
7 WHERE ROWNUM < 5) b1
8 WHERE a1.ID = b1.ID AND a1.NAME = b1.NAME
9 FOR UPDATE OF a1.ID, a1.NAME NOWAIT SKIP LOCKED;
ID NAME
---------- --------------------
1 a
2 b
3 4
3 new value column
4 rows selected.
Session #2
SQL> SELECT a1.ID, a1.NAME
2 FROM temp_tab a1,
3 (SELECT ID, NAME
4 FROM (SELECT ID, NAME
5 FROM temp_tab
6 ORDER BY ID)
7 WHERE ROWNUM < 5) b1
8 WHERE a1.ID = b1.ID AND a1.NAME = b1.NAME
9 FOR UPDATE OF a1.ID, a1.NAME NOWAIT;
FROM temp_tab a1,
*
ERROR at line 2:
ORA-00054: resource busy and acquire with NOWAIT specified
-- See if any records are available for this session
SQL> SELECT a1.ID, a1.NAME
2 FROM temp_tab a1,
3 (SELECT ID, NAME
4 FROM (SELECT ID, NAME
5 FROM temp_tab
6 ORDER BY ID)
7 WHERE ROWNUM < 10) b1 -- changes made
8 WHERE a1.ID = b1.ID AND a1.NAME = b1.NAME
9 FOR UPDATE OF a1.ID, a1.NAME NOWAIT SKIP LOCKED;
ID NAME
---------- --------------------
4 5
5 6
6 d
7 9
8 g
5 rows selected.
Session #1
SQL> commit; -- Releasing locks set in session #1
Commit complete.
Session #2
SQL> SELECT a1.ID, a1.NAME
2 FROM temp_tab a1,
3 (SELECT ID, NAME
4 FROM (SELECT ID, NAME
5 FROM temp_tab
6 ORDER BY ID)
7 WHERE ROWNUM < 10) b1
8 WHERE a1.ID = b1.ID AND a1.NAME = b1.NAME
9 FOR UPDATE OF a1.ID, a1.NAME NOWAIT SKIP LOCKED;
ID NAME
---------- --------------------
1 a
2 b
3 4
4 5
5 6
6 d
7 9
8 g
3 new value column
9 rows selected.
I am just a newbie in the "locking" topic. I advice you to wait for an expert's comments. Hope this helps.
Regards,
Jo
[Updated on: Wed, 04 March 2009 04:27] Report message to a moderator
|
|
|
|
Re: Select "FOR UPDATE" [message #389946 is a reply to message #389941] |
Wed, 04 March 2009 04:54 |
joicejohn
Messages: 327 Registered: March 2008 Location: India
|
Senior Member |
|
|
Michel,
Please see the difference in output of the query which I posted in my previous example one with and one without SKIP LOCKED. I am trying to find more about this. If you do have some reference links, can you please post it?
joicejohn wrote on Wed, 04 March 2009 15:54 |
Session #2
SQL> SELECT a1.ID, a1.NAME
2 FROM temp_tab a1,
3 (SELECT ID, NAME
4 FROM (SELECT ID, NAME
5 FROM temp_tab
6 ORDER BY ID)
7 WHERE ROWNUM < 5) b1
8 WHERE a1.ID = b1.ID AND a1.NAME = b1.NAME
9 FOR UPDATE OF a1.ID, a1.NAME NOWAIT;
FROM temp_tab a1,
*
ERROR at line 2:
ORA-00054: resource busy and acquire with NOWAIT specified
-- See if any records are available for this session
SQL> SELECT a1.ID, a1.NAME
2 FROM temp_tab a1,
3 (SELECT ID, NAME
4 FROM (SELECT ID, NAME
5 FROM temp_tab
6 ORDER BY ID)
7 WHERE ROWNUM < 10) b1 -- changes made
8 WHERE a1.ID = b1.ID AND a1.NAME = b1.NAME
9 FOR UPDATE OF a1.ID, a1.NAME NOWAIT SKIP LOCKED;
ID NAME
---------- --------------------
4 5
5 6
6 d
7 9
8 g
5 rows selected.
|
Thanks,
Jo
|
|
|
Re: Select "FOR UPDATE" [message #389960 is a reply to message #389946] |
Wed, 04 March 2009 05:33 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
There is no documentation available on it in 10g.
It's an undocumented feature - this means that oracle do not provide any support for it's use.
Because there's no documentation ,you can't know what it does, or what it's intended to do, and if you run into any problems using it, Oracle won't help you out, because they don't provide support for it.
Now, it's part of 11g, and is documented for that release, but in my opinion you'd be foolish to use it in a production system prior to 11g.
|
|
|
Re: Select "FOR UPDATE" [message #389961 is a reply to message #389960] |
Wed, 04 March 2009 05:40 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
you can work around the problem by writing a function like this:create or replace function test_129_can_lock_id (p_id in number) return varchar2 is
v_id number;
e_locked exception;
pragma exception_init (e_locked,-00054);
pragma autonomous_transaction;
begin
select id
into v_id
from test_129
where id = p_id
for update nowait;
rollback;
return 'Y';
exception
when e_locked then
rollback;
return 'N';
end;
/
This will check whether a given row is locked, and based on this, you can select a set of ids that are lockable.
|
|
|
|
Goto Forum:
Current Time: Sat Nov 09 21:32:33 CST 2024
|