Oracle (PL/SQL): Is UPDATE RETURNING concurrent?

Posted by Jaap on Stack Overflow See other posts from Stack Overflow or by Jaap
Published on 2010-05-28T16:20:30Z Indexed on 2010/05/28 16:32 UTC
Read the original article Hit count: 253

Filed under:
|
|

I'm using table with a counter to ensure unique id's on a child element.

I know it is usually better to use a sequence, but I can't use it because I have a lot of counters (a customer can create a couple of buckets and each of them needs to have their own counter, they have to start with 1 (it's a requirement, my customer needs "human readable" keys).

I'm creating records (let's call them items) that have a prikey (bucket_id, num = counter).

I need to guarantee that the bucket_id / num combination is unique (so using a sequence as prikey won't fix my problem).

The creation of rows doesn't happen in pl/sql, so I need to claim the number (btw: it's not against the requirements to have gaps).

My solution was:

   UPDATE bucket
      SET counter = counter + 1
    WHERE id = param_id
RETURNING counter INTO num_forprikey;

PL/SQL returns var_num_forprikey so the item record can be created.

Question:

Will I always get unique num_forprikey even if the user concurrently asks for new items in a bucket?

© Stack Overflow or respective owner

Related posts about Oracle

Related posts about concurrency