OraFAQ Forum: SQL & PL/SQL » How to Exclude Locked Rows from Select

Home » SQL & PL/SQL » SQL & PL/SQL » How to Exclude Locked Rows from Select (11.2.0.3.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to Exclude Locked Rows from Select [message #634627]

Thu, 12 March 2015 05:34

supiash
Messages: 70
Registered: May 2012
Location: bangalore

Member

Hi,

My Oracle Version is as below

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production

I am currently trying to exclude the locked rows from the consecutive select statements

1) I perform Select * from emp where sal=54 and it returned 10 rows
2) I select the first row(out of 10 rows returned) for Select for update NOWAIT(using row id)
3) Is there a way when I do a select again as the query in Step 1 it returns me 9 rows i.e excluding the row selected in step 2 even though that row has still not been updated?

I understand there is a SKIP LOCKED clause that can be used with UPDATE which will lock all the 10 rows, i do not want to lock all the row, i just want to skip the locked row and just select remaining rows.

I could fetch the rows into cursor and trap the errors which are locked but it doesn't help in my case.

Any help is much appreciated

Report message to a moderator

Re: How to Exclude Locked Rows from Select [message #634628 is a reply to message #634627]

Thu, 12 March 2015 05:41

Maaher
Messages: 7065
Registered: December 2001

Senior Member

If you're not planning on modifying the records (i.e. locking the records), why would you be interested in skipping the locked records? As long as the other session did not commit, no modifications are final.

MHE

[Updated on: Thu, 12 March 2015 05:41]

Report message to a moderator

Re: How to Exclude Locked Rows from Select [message #634630 is a reply to message #634627]

Thu, 12 March 2015 05:44

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Skip locked can't be used with update, only select. You use it instead of nowait.
It won't skip records that are locked by your session though.

Report message to a moderator

Re: How to Exclude Locked Rows from Select [message #634631 is a reply to message #634628]

Thu, 12 March 2015 05:46

supiash
Messages: 70
Registered: May 2012
Location: bangalore

Member

Hi, Thanks for your response, i would be modifying the record, but that would take some time hence am looking to find a way any select issued before i modify should skip that row irrespective of what happens to that row even at later stages

Report message to a moderator

Re: How to Exclude Locked Rows from Select [message #634632 is a reply to message #634631]

Thu, 12 March 2015 05:49

supiash
Messages: 70
Registered: May 2012
Location: bangalore

Member

Hi, Yes when i meant update, it is select for update with skip locked, yes it doesn't skips the record by current session and also locks all the rows from doing a select by other session

Report message to a moderator

Re: How to Exclude Locked Rows from Select [message #634634 is a reply to message #634632]

Thu, 12 March 2015 06:00

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

supiash wrote on Thu, 12 March 2015 16:19

skip locked, yes it doesn't skips the record by current session and also locks all the rows from doing a select by other session

It will only lock the rows which it could select for update, the rest which are skipped are already locked by other session.

For example,

Session 1:

SQL> SELECT empno, deptno
  2    FROM emp  WHERE
  3   deptno = 10
  4  FOR UPDATE NOWAIT;

     EMPNO     DEPTNO
---------- ----------
      7782         10
      7839         10
      7934         10

SQL>

Session 2:

SQL> SELECT empno, deptno
  2    FROM emp  WHERE
  3   deptno in (10, 20)
  4  FOR UPDATE NOWAIT;
  FROM emp  WHERE
       *
ERROR at line 2:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Now let's skip the rows which are locked by session 1.

SQL> SELECT empno, deptno
  2    FROM emp  WHERE
  3   deptno IN (10, 20)
  4  FOR UPDATE SKIP LOCKED;

     EMPNO     DEPTNO
---------- ----------
      7369         20
      7566         20
      7788         20
      7876         20
      7902         20

SQL>

So, department = 10 were locked by session 1 and then department = 20 are locked by session 2.

Report message to a moderator

Re: How to Exclude Locked Rows from Select [message #634636 is a reply to message #634634]

Thu, 12 March 2015 06:07

Maaher
Messages: 7065
Registered: December 2001

Senior Member

I'm a bit confused here. Are we talking about multiple sessions or one session?

MHE

Report message to a moderator

Re: How to Exclude Locked Rows from Select [message #634644 is a reply to message #634636]

Thu, 12 March 2015 08:57

supiash
Messages: 70
Registered: May 2012
Location: bangalore

Member

Hi Lalit, thanks, i understand your illustration, i am looking to have a just select statement(with out locking, meaning noselect for update Nowait or select for update skip locked) which excludes the locked row but does not lock the current

1 query

[code]SQL> SELECT empno, deptno
  2    FROM emp  WHERE
  3   deptno = 10;

     EMPNO     DEPTNO
---------- ----------
      7782         10
      7839         10
      7934         10[/code]

2 QUERY


SQL> SELECT NULL
  2    FROM emp  WHERE
  3   deptno = 10 and empno =7782
  4   FOR UPDATE NOWAIT ;

     NULL
----------

I am looking for a query here which gives me the following output that is skip the locked row from consecutive select


SQL> SELECT empno, deptno
  2    FROM emp  WHERE
  3   deptno = 10;

     EMPNO     DEPTNO
---------- ----------
      7839         10
      7934         10

Report message to a moderator

Re: How to Exclude Locked Rows from Select [message #634646 is a reply to message #634644]

Thu, 12 March 2015 09:14

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

You haven't made clear if it's one session or two, but I'm going to have to assume one session.
There's no automatic way to do this. The locking mechanism is designed to deal with multiple sessions changing the same data at the same time. It doesn't exist to protect a session from itself.
If you want to skip the rows you've locked in subsequent queries then you'll have to store the primary keys or rowids of the records you've locked so far in an array and write a where clause that excludes all rows that correspond to the contents of the array.

Report message to a moderator