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

I'd figure out how to make sequences work. It's the only guarantee, though an exception clause could be coded

http://www.orafaq.com/forum/t/83382/0/ The benefit to sequences (and they could be dynamically created, is you can specify nocache and guarantee order)

Answer 2

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.

Answer 3

I seem to recall this problem from many years back working on of all things an INGRES database. There were no sequences in those days so a lot of effort was put into finding the best scaling solution for this problem by the top INGRES minds of the day. I was fortunate enough to be working along side them so that even though my mind is pitifully smaller than any of theirs, proxmity = residual affect and I retained something. This was one of the things. Let me see if I can remember.

1) for each counter you need row in a work table.
2) each time you need a number
  a) lock the row
  b) update it
  c) get its new value (you use returning for this which I avoid like the plague)
  d) commit the update to release your lock on the row

The reason for the commit is for trying to get some kind of scalability. There will always be a limit but you do not serialize on getting a number for any period of time.

In the oracle world we would improve the situation by using a function defined as an AUTONOMOUS_TRANSACTION in order to acquire the next number. IF you think about it, this solution requires that gaps be allowed which you said is OK. By commiting the number update independently of the main transaction, you gain scalability but you introduce gapping.

You will have to accept the fact that your scalability will drop dramatically in this scenario. This is due to at least two reasons:

1) the update/select/commit sequence does its best to reduce the time during which the KEY row is locked, but it is still not zero. Under heavy load, you will serialize and eventually be limited.

2) you are commiting on every key get. A commit is an expensive operation requiring many memory and file management actions on the part of the database. This will limit you also.

In the end you are likely looking at three or more orders of magnitude drop in concurrent transaction load because you are not using sequences. I base this on my experience of the past.

But if you customer requires it, what can you do right?

Good luck. I have not tested the code for syntax errors, I leave that to you.

create or replace function get_next_key (key_name_p in varchar2) return number is
   pragma autonomous_transaction;
   kev_v number;
begin

   update key_table set key = key + 1 where key_name = key_name_p;

   select key_name into key_name_v from key_name where key_name = key_name_p;

   commit;

   return (key_v);

end;
/
show errors

Answer 4

You can still use sequences, just use the row_number() analytic function to please your users. I described it here in more detail: http://rwijk.blogspot.com/2008/01/sequence-within-parent.html

Regards, Rob.