Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question.

Asked: October 23, 2009 - 1:43 pm UTC

Answered by: Tom Kyte - Last updated: February 25, 2013 - 10:08 am UTC

Category: Developer - Version: 11.1.0.7

Viewed 10K+ times! This question is

You Asked

Hello Tom,

In 11g, there is a new clause added to SELECT called, FOR UPDATE SKIP LOCKED. Some people used this clause in 9i and 10g when it was not documented. Now that it is documented in 11g I have a question on it.

You have said in the past that Advanced Queueing (AQ) uses this construct internally to process items in high concurrency situations.

Can you demonstrate how I can avoid using AQ and make use of SKIP LOCKED to process items concurrently without contention?

I want to know how to use this clause when you have unique and non-unique keys to process.

e.g.
A unique key example would be processing rows from a table by its primary key. e.g. ORDERS table with ORDER_ID being the PK.
A non-unique key example would be processing rows from a table where there is a composite key. e.g. CUST_ORDERS table with CUST_ID, ORDER_ID being the unique combination.

I think unique key scenario is easy to code. But a non-unique key scenario is more challenging.

You can demo it with any example that is typical of an AQ scenario.

Thank you.

and we said...

... Can you demonstrate how I can avoid using AQ and make use of SKIP LOCKED to process items concurrently without contention? ...

assume you want to get the first row from a table that is not currently locked and matches some key - unique or otherwise (unique would not make sense, there would be only one - and it would be be trivial to determine if it is locked or not without the 'skip locked', you would just use nowait).

so, the unique key case isn't remotely interesting, we don't need - never needed - skip locked, just a nowait.

You just query and fetch, eg:


ops$tkyte%ORA11GR2> select empno
  2    from scott.emp
  3   where job = 'CLERK'
  4  /

     EMPNO
----------
      7369
      7876
      7900
      7934

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2      l_rec scott.emp%rowtype;
  3      cursor c is select * from scott.emp where job = 'CLERK' for update skip locked;
  4  begin
  5      open c;
  6      fetch c into l_rec;
  7      close c;
  8      dbms_output.put_line( 'I got empno = ' || l_rec.empno );
  9  end;
 10  /
I got empno = 7369

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2      pragma autonomous_transaction;
  3      l_rec scott.emp%rowtype;
  4      cursor c is select * from scott.emp where job = 'CLERK' for update skip locked;
  5  begin
  6      open c;
  7      fetch c into l_rec;
  8      close c;
  9      dbms_output.put_line( 'I got empno = ' || l_rec.empno );
 10      commit;
 11  end;
 12  /
I got empno = 7876

PL/SQL procedure successfully completed.



but you know what, you make me pretty suspicious with this comment:


how I can avoid using AQ


why would you want to avoid using the most obvious, correct way to process a queue?

and you rated our response

  (14 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

How to process multiple job types

October 26, 2009 - 10:07 am UTC

Reviewer: Developer from PA, USA

Thank you for your response.

I am sorry if I didn't explain the problem properly. Although here is what I am looking for:

e.g.

-- I have multiple job types.
CREATE TABLE EMP (EMPNO NUMBER(10) PRIMARY KEY, JOB_TYPE VARCHAR2(20), PROCESSED VARCHAR2(3), THREAD NUMBER(4));

INSERT INTO EMP VALUES (7125, 'MANAGER', 'NO', NULL);
INSERT INTO EMP VALUES (7250, 'MANAGER', 'NO', NULL);
INSERT INTO EMP VALUES (7400, 'MANAGER', 'NO', NULL);
INSERT INTO EMP VALUES (7445, 'CLERK', 'NO', NULL);
INSERT INTO EMP VALUES (7550, 'CLERK', 'NO', NULL);
INSERT INTO EMP VALUES (7600, 'CLERK', 'NO', NULL);
INSERT INTO EMP VALUES (7945, 'CLERK', 'NO', NULL);
INSERT INTO EMP VALUES (7965, 'ANALYST', 'NO', NULL);
INSERT INTO EMP VALUES (7970, 'ANALYST', 'NO', NULL);
INSERT INTO EMP VALUES (7975, 'ANALYST', 'NO', NULL);
INSERT INTO EMP VALUES (7980, 'ANALYST', 'NO', NULL);
INSERT INTO EMP VALUES (7985, 'ANALYST', 'NO', NULL);
INSERT INTO EMP VALUES (7990, 'ANALYST', 'NO', NULL);

COMMIT;

Now I want to spawn multiple parallel threads that will process each job type without waiting for each other. Once the row is processed, the thread will mark it processed (Update PROCESSED to YES and THREAD to thread number) and move on to the next row.

So finally, the table should look like this.
EMPNO   JOB_TYPE    PROCESSED   THREAD
7125    MANAGER     YES         1
7250    MANAGER     YES         1
7400    MANAGER     YES         1
7445    CLERK       YES         2
7550    CLERK       YES         2
7600    CLERK       YES         2
7945    CLERK       YES         2
7965    ANALYST     YES         3
7970    ANALYST     YES         3
7975    ANALYST     YES         3
7980    ANALYST     YES         3
7985    ANALYST     YES         3
7990    ANALYST     YES         3

Thread 1 processed MANAGER rows, thread 2 processed CLERK rows and thread 3 processed ANALYST rows.

I am thinking along the lines of:

Thread 1 comes in, BULK FETCHes all the MANAGER rows, locks them and processes them.
Thread 2 comes in, tries to BULK FETCH all the MANAGER rows, finds that it is locked, so skips to next JOB_TYPE. BULK FETCHes CLERK rows, locks them and processes them.
Thread 3 comes in, sees that MANAGER and CLERK is locked, so moves onto ANALYST rows.

Also note that each thread will process rows that are marked NO. So after one thread is complete with its processing and released the locks, the other thread should not re-process the same rows because they are now marked PROCESSED=YES.

In a production like environment, I am going to have hundreds of job types and thousands of rows per job type. No. of parallel threads will be controlled externally by a DBA depending upon amount of resources used in the database at any given time.

One caveat I found about FOR UPDATE SKIP LOCKED is that the locks are obtained when you actually fetch the rows (if you are using a cursor in a pl/sql) as opposed to when you open the cursor.

One important criteria here is that I DO NOT want 2 threads to process 1 job type.

Does this all make sense?

My comment about not using AQ is that I need additional privileges to set up AQ in the database whereas SKIP LOCKED seems like it might just be able to accomplish this. Are you saying that SKIP LOCKED cannot be used to replace AQ in 11g?

Thank you.
Tom Kyte

Followup  

October 26, 2009 - 2:29 pm UTC

... My comment about not using AQ is that I need additional privileges to set up AQ in the database whereas SKIP LOCKED seems like it might just be able to accomplish this. Are you saying that SKIP LOCKED cannot be used to replace AQ in 11g? ...

Oh my gosh. Are you *serious*

Are you *seriously serious*?

Are you kidding me or are you actually *serious*

I'm ready to give up working in this profession - every day it gets a little more depressing.


Holy cow.

since when does getting the correct and proper minimal set of privileges become a barrier to doing the right thing?

You need a privilege to create a table. Give them back that privilege if you have to trade them in, get the ones you need


Fact is, you don't need skip locked to do what you want, you appear to just want "for update nowait" and when you get ora-54, that means someone else got them - move onto the next job type (you have a missing table, the table of job types to process, it is key to this regardless)


But - are you *serious*???

No, none of this makes sense once you read

... My comment about not using AQ is that I need additional privileges to set up AQ in the database whereas SKIP LOCKED seems like it might just be able to accomplish this. Are you saying that SKIP LOCKED cannot be used to replace AQ in 11g? ...



skip locked could be used to BUILD YOUR OWN AQ in 11g. skip locked cannot replace AQ anymore than select can. It is a very small tool in a very large implementation

How to process multiple job types

October 26, 2009 - 12:28 pm UTC

Reviewer: VKOUL from WA, USA.

I think this can be achieved if one can create a parent table with all types (each type with one record) and lock and process them with SELECT FOR UPDATE SKIP LOCKED.

In this you fetch and lock a particular type from the parent/master table and progress on.

Can I know who is lock the table?

December 06, 2009 - 1:50 am UTC

Reviewer: abu mos'ab from Iug, GAZA PS

Hi Mr. Tom,

Can I know who is lock the table?

we are work in one company, and on lock, the oracle message tell me that : " n tries ... " .
But I want to know who is the locker user, because I'll phone him to exit from the table.

thank you.
Tom Kyte

Followup  

December 07, 2009 - 2:18 pm UTC

v$lock contains locking information.

how does oracle manages locks in 11g

March 08, 2010 - 12:03 pm UTC

Reviewer: anurag from india

Hi Tom,

would you please explain how does 11g manages lock contention (i am not asking about deadlock)
for example

if user A has issued (select * from table1 for update) as it results in lock on rows.
and after 2 minutes user B has issued same statement, as user B has to wait until user A completes the transaction.

but what if user A doesn't complete the transaction for 5 hours and user B is waiting. in oracle 10g user B will keep on waiting until we kill the session A or it completes the transaction.

Thank you
Tom Kyte

Followup  

March 09, 2010 - 11:42 am UTC

and 11g would do the same.

you can use "for update wait N" to only wait N seconds if you want (since 9i)

Why AQ instead of the example given by you?

August 11, 2010 - 12:11 am UTC

Reviewer: Pradeep from Redwood Shores, CA USA

Hi Tom,
The example you have given using a cursor to select the first row that is not currently locked is exactly what I am looking for. The code seems simple and straightforward. But I sense that you prefer using AQ to do the same. What are the reasons for that? Is the example in some way deficient to an AQ implementation? If yes, can you please show how to achieve the same using AQ. Also, is there some way to do this in SQL alone?
Tom Kyte

Followup  

August 18, 2010 - 11:47 pm UTC

Because using AQ it would be simply a call to "dequeue", nothing else.

Because using AQ, the "many producers, many consumers" issue has been dealt with.

Because using AQ, you get a lot of other functionality that people end up programming - such as peeking at a message queue, priorities, rules, filtering, etc.

Because it is already built and doesn't need to be built again.


Let me ask you this - The database is capable of creating tables, given that I can do that - I can build tables like EMP and EMP_HISTORY and DEPT and so on - in short, I can build a human resources system. So why would I purchase one? Why not just build it from scratch?

That is why "AQ"

December 09, 2010 - 2:31 am UTC

Reviewer: Le

Hi Tom, when we issue "select ... for update skip locked", the runtime seems to lock one row when it fetches that specified row. The underlying logic looks like engine fetches one row, checks if it has been locked. If it's locked, skip that row; otherwise, lock that row. This is different with "select ... for update nowait". When it's issued, instead of at fetching phase, at the cursor open phase , all the rows that hit the where clause are supposed to be locked. Is my understanding correct? Thanks.
Tom Kyte

Followup  

December 09, 2010 - 10:44 am UTC

yeah, skip locked is funny that way - you just made me realize something - that skip locked is in fact an inconsistent read (that is why select for update locks during the open - to ensure the consistent read).

July 08, 2011 - 2:02 am UTC

Reviewer: Miha from Russia

Why you say that select..for update..skip locked is incosistent read? "Data consistency means that each user sees a consistent view of the data, including visible changes made by the user's own transactions and transactions of other users" so if skip locked is means what it seems to mean and has no traps select statement will return consistent rows. Why not?
Tom Kyte

Followup  

July 08, 2011 - 2:43 pm UTC

it is an 'inconsistent read' for the simple reason that you are skipping (not reading, not seeing) locks rows. These rows match your predicate - under normal circumstances - you would see them - but you told us to skip them for now. the result set you get back is NOT a result set that actually exists in the database - it is missing some data, it is inconsistent.

each 'row' might be 'consistent', but the result set certainly is not.

would be nice if ...

July 09, 2011 - 12:11 pm UTC

Reviewer: Sokrates

skip locked

would be allowed in subquerys.

Any plans to implement this ?
Tom Kyte

Followup  

July 12, 2011 - 7:08 am UTC

not that I am aware of - it is very very restrictive and used specifically for one thing and one thing only: to implement a queue. It does what we need for advanced queues (what it was designed for initially in 8.0) and that is all.

I'd be afraid if it were made more general purpose. It would become a feature I would have to start treating like I do triggers, when others, autonomous transactions and so on - it would be misused more than it would be used correctly. (Yes, I understand - I fully understand there are people that use these features *correctly*. I also know they are outnumbered at least 100:1 by those that do not. Yes, I know that is technically not a reason to "outlaw" some feature - however, code reliability would probably increase a thousand fold without these features unfortunately...)

thanks

July 12, 2011 - 8:14 am UTC

Reviewer: Sokrates


how to process multiple job types

July 12, 2011 - 8:26 am UTC

Reviewer: jerry from norwich vt usa

The process that Developer from PA described is exactly how E-Business suite R12 upgrade creates multiple threads.
Tom Kyte

Followup  

July 13, 2011 - 1:50 pm UTC

the process that developer was describing had nothing to do with their premise that AQ required privileges. I'm not sure what your point is?

and e-business was written way before skip locked rows was available (or AQ), they do something that achieves what you see - but not in the way you say as far as I know.

SKIP LOCKED still runs into ORA-00054?

January 14, 2013 - 1:26 pm UTC

Reviewer: Vijay Devadhar from California, USA

We use SKIP LOCKED in 10gR2. I know it is undocumented in 10g.
Once in a while we still get ORA-00054 - : resource busy and acquire with NOWAIT; Is that expected? Does the SKIP LOCKED implementation have some points in time where this can happen? Before trying to code this exception as expected, I thought I will poke around a little bit.

Thanks
Vijay
Tom Kyte

Followup  

January 15, 2013 - 10:27 am UTC

example please

Self-reference

February 11, 2013 - 6:28 am UTC

Reviewer: Uwe from Frankfurt, Germany

Tom,
your link above points to this very thread; was that intended?
Cheers,
Uwe
Tom Kyte

Followup  

February 11, 2013 - 10:12 am UTC

I didn't realize I was on the page already :) i did the search for this article in another window.

so, I should have said "re-read this page", but I didn't realize I was on this page :) so yes, it was sort of intentional.

Aq license

February 20, 2013 - 2:42 pm UTC

Reviewer: Veslar from Bratislava,Slovakia

Hi,as I know from DBadmins in our company,one needs a license to use AQ feature.This could be a reason to avoid using it. Is that indeed for free? thanks
Tom Kyte

Followup  

February 25, 2013 - 10:08 am UTC

It is a feature of all editions, it just comes with the database.

Your DBA's are not correct.

http://docs.oracle.com/cd/E11882_01/license.112/e10594/toc.htm

December 03, 2014 - 3:22 am UTC

Reviewer: sire from USA


More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here