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 Go to next message
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 #389923 is a reply to message #389922] Wed, 04 March 2009 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
 SKIP LOCKED

This does not exist.

Regards
Michel


Re: Select "FOR UPDATE" [message #389924 is a reply to message #389922] Wed, 04 March 2009 02:45 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
if i give the entire query i too getting the same exception

[Updated on: Wed, 04 March 2009 02:47]

Report message to a moderator

Re: Select "FOR UPDATE" [message #389925 is a reply to message #389924] Wed, 04 March 2009 02:52 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
http://www.orafaq.com/forum/t/46034/0/

May be it will be helpful to you
Re: Select "FOR UPDATE" [message #389941 is a reply to message #389922] Wed, 04 March 2009 04:24 Go to previous messageGo to next message
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 #389943 is a reply to message #389941] Wed, 04 March 2009 04:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SKIP LOCKED


This is for ORACLE INTERNAL USE ONLY.
It does not exist for you. You can't even don't know what it implies.

Regards
Michel
Re: Select "FOR UPDATE" [message #389946 is a reply to message #389941] Wed, 04 March 2009 04:54 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Select "FOR UPDATE" [message #390103 is a reply to message #389960] Wed, 04 March 2009 23:38 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Thanks JRowbottom & Michel,

I think I understand your point now. Thanks again. Hope OP understood it too.

Regards,
Jo
Previous Topic: Sql Database Dump... Please Help
Next Topic: how to calculate work time or lead time except weekends (merged)
Goto Forum:
  


Current Time: Sat Nov 09 21:32:33 CST 2024