Oracle (PL / SQL): UPDATE RETURNING at the same time?

Question

I use a table with a counter to provide a unique identifier for the child.

I know that it’s usually better to use a sequence, but I can’t use it because I have many counters (the client can create a pair of buckets, and each of them must have its own counter, they have to start with 1 (this is a requirement, by my client need "human readable" keys).

I create records (let them be called) that have prikey (bucket_id, num = counter).

I need to ensure that the bucket_id / num combination is unique (so using the sequence as prikey will not fix my problem).

Line creation does not happen in pl / sql, so I need to require a number (btw: it is not against the requirement to have spaces).

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 an item record can be created.

Question:

Will it always get a unique num_forprikey, even if the user simultaneously requests new items in the bucket?

Answer 1

Do I always get a unique num_forprikey even if the user simultaneously requests new items in the bucket?

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

Obviously, nit-picking refers to concurrency. Your access to the string is serialized, so there is no way for two users to simultaneously receive a new PRIKEY. This is not necessarily a problem. It depends on how many users you create new items and how often they do it. One user who shoots numbers in one session will not notice anything.

Answer 2

I seem to recall this problem from many years ago about how everything works in the INGRES database. There was no sequence in those days, so it took a lot of effort to find the best scaling solution for this problem with the best INGRES ideas of that day. I was fortunate enough to work with them so that, although my mind is sorry for less than any of them, proxmity = residual affect, and I saved something. That 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 fix is ​​to try to get some scalability. There will always be a limit, but you do not serialize receiving a number for any period of time.

In the oracle world, we would improve the situation using the function defined as AUTONOMOUS_TRANSACTION to get the next number. If you think about it, this decision requires the resolution of the spaces that you said in order. By starting a room update regardless of the main transaction, you get scalability, but you enter spaces.

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

1) the sequence update / select / commit does everything possible to reduce the time during which the KEY string is locked, but it is still non-zero. Under heavy load, you will serialize and ultimately be limited.

2) you commit every time you receive a key. The end is an expensive operation that requires a lot of memory and file management from the database. It will also limit you.

In the end, you probably look at three or more load drop orders for a simultaneous transaction because you are not using sequences. I base this on my experience of the past.

But if the client requires it, what can you do right?

Good luck. I have not tested the code for syntax errors, I leave it 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 3

You can still use sequences anyway, 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.

Answer 4

I would understand how to make work sequences. This is the only guarantee, although the exception condition can be encoded.

http://www.orafaq.com/forum/t/83382/0/ Advantage of sequences (and they can be dynamically created, you can specify nocache and warranty order)