Oracle : Create session sequence?

Question

I have a table as follows

The table contains my application users and stores their clients. The column User Client ID refers to a foreign key linked to a different table that stores the clients details.

I need another column (User Client Counter) which is a just a counter of the clients of each user. I need it to start from 1 and goes up for each individual application user.

For the moment I'm populating this by counting the number of clients for each user + 1 before inserting a new row in the table :

    select count(*) + 1 into MyVariable from Mytable where UserClientId = Something

Then I use MyVariable in the column User Client Counter

This methods works quite well, but in case the user is connected from two different sessions, the query may produce a wrong number of counts... in addition to that the performance may be bad in case of big tables...

Is there anyway better way to replace such process by using sequences ?

I've been looking to session sequences but there are reset after the end of each session.

(This column is a business need and cannot be replaced by something like rownumber in restitution queries. Since every client has to keep always the same identifier for the application user)

Thank you in advance.

Cheers,

Why do you need this in the first place? Will the column `User Client Counter` be exposed somewhere? Why not a unique numbering across all companies? I'm trying to figure out the use case. — The Impaler, Oct 28 '20 at 17:45
Every application user needs to see some simple identifiers for his clients (Client 1, Client 2, Client 3...). Not a global identifier which may be meaningless for him. — Thomas Carlton, Oct 28 '20 at 17:46
I would use a queued process, either inside the app, or a single instance running on a separate server. Do you run a single instance or multiple ones? This solves the problem of a centralized UNIQUE process that synchronizes critical tasks, like this one. Then each time you need to insert a new row this process can provide the new value for it, or could even insert the row. — The Impaler, Oct 28 '20 at 17:48
If you like pl/sql you can use a synchronized block to deal with simultaneous executions. See https://stackoverflow.com/questions/17321687/synchronisation-of-pl-sql-procedure-how-to-guaranty-execution-of-procedure-only. — The Impaler, Oct 28 '20 at 17:50
"Every application user needs to see some simple identifiers for his clients" What is the business definition of a "client" in this context? If all you need is a "simple identifier", why not the client_id? Why would a 'global identifier' be meaningless? Surely he can only see his own, anyway. — EdStevens, Oct 28 '20 at 18:21
@TheImpaler can you please explain what do you mean by queued process ? Thanks — Thomas Carlton, Oct 28 '20 at 23:12
By *queued process* I mean a process that listens to a message queue. It could be a bona fide separate server that listens to many other servers, or it could be as simple as an internal synchronized queue (e.g. in Java could be a BlockingQueue). Every time a concurrent thread needs to produce a new number, it will ask this centralized process to compute it; there will be no more collisions. Or... you could use a synchronized PL/SQL procedure/function, as I mentioned before. Either will work. Also, you could use caching to speed up the process and avoid running `SELECT MAX(id)` every time. — The Impaler, Oct 29 '20 at 15:08

score 0 · Answer 1 · answered Oct 28 '20 at 17:58

0

I think you can just create a unique index on the app user and the running number:

create unique index idx on mytable (app_user_id, num);

And then insert with max + 1:

insert into mytable (app_user_id, client_id, num)
values 
(
  :app_user_id, 
  :client_id,
  coalesce((select max(num) + 1 from mytable where app_user_id = :app_user_id), 1)
);

answered Oct 28 '20 at 17:58

Thorsten Kettner

89,309
7
49
73

And then have error handling for when that scalar subquery doesn't get the correct answer (because it can't see the rows in an uncommitted transaction) – Andrew Sayer Oct 28 '20 at 18:39
Correct. With concurrent writes, the insert can fail and must be run again. That's the idea with the unique index. – Thorsten Kettner Oct 28 '20 at 19:02
I still think that a synchronized process (even written in pl/sql) is better than hanlding the collisions. – The Impaler Oct 28 '20 at 19:31

score 0 · Answer 2 · answered Oct 28 '20 at 18:37

For this sort of requirement to be safe you will need to be able to lock rows at the right level so that you don't have two sessions that think the they are allowed to use the same value. The impact of this is that while one session is inserting a row for the 'Company X' user, another session will wait for the first user to commit if they're also trying to insert a row for 'Company X'.

This is super easy when you just have a table that stores information at the right level.

You can have a table of your users with a counter column which starts at 0.

MY_APPLICATION_USER                                CLIENT_COUNTER
-------------------------------------------------- --------------
Company X                                                       1
Company Y                                                       3
Company Z                                                       1

As you insert rows into your main table, you update this table first setting the client_counter to be client_counter + 1 (you do this as one insert statement, no risky select then update!), then you return the updated value into your value for the client_id. This can all be done with a simple trigger.

create or replace trigger app_clients_ins
before insert 
on app_clients
for each row
declare
begin
  update app_users
  set    client_counter = client_counter + 1
  where  my_application_user = :new.my_application_user
  return client_counter into :new.user_client_number;
  
end;
/

Of course, like any sequence if you delete a row it's not going to allow your next insert to fill that gap.

(db<>fiddle https://dbfiddle.uk/?rdbms=oracle_18&fiddle=7f1b4f4b6316d5983b921cae7b89317a )

score -2 · Answer 3 · answered Oct 28 '20 at 18:25

if you want to have unique values to be inserted and there are chances that multiple users can insert rows into the same table at the same time then it is better to user Oracle Sequence.

CREATE SEQUENCE id_seq INCREMENT BY 1;
INSERT INTO Mytable(id) VALUES (id_seq.nextval);

In you case I think you want different sequence created for each Customer, How many different Customers you have, if you have in 100's then i don't think create sequence will work as you may have to create as many sequence .

Oracle : Create session sequence?

3 Answers3