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

Question

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?

Answer 1

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

Yes, at least up to a point. The first user to issue that update gets a lock on the row. So no other user can successfully issue that same statement until user numero uno commits (or rolls back). So uniqueness is guaranteed.

Obviously, the cavil is regarding concurrency. Your access to the row is serialized, so there is no way for two users to get a new PRIKEY simultaneously. This is not necessarily a problem. It depends on how many users you have creating new Items, and how often they do it. One user peeling off numbers in the same session won't notice a thing.