Generating unique id for customer without using sequence while using multiple sessions in oracle

Question

I am generating customer id using select nvl(max(customerid),0)+1. It's working, but per hour almost 700 to 1000 customers are onboarding. So while using above code at fraction of some milliseconds the session getting same max of customer id. So it raises unique constraint violation. I don't want to use sequence because if transaction failed. I will lose sequence.

Below will be the sample code.

Create or replace procedure ADM_customerregisration
P_firstname varchar2,
P_lastname varchar2
As
V_customerid number;
Begin
Insert into mas_customerdetails
(
Customerid,
Firstname,
Lastname
)
Values
(
(Select nvl(max(customerid),0)+1 from mas_customerdetails),
P_firstname,
P_lastname
) returning customerid into v_customerid;

Commit;

Exception

When others then

Return;
End;

Answer 1

You really don't want to do this in a high transaction volume system. It will drastically decrease your concurrency and throughput and it will create ongoing support issues with blocking. If you want to generate sequential values and ensure no gaps, though, you would have to

• lock a shared resource so that no other session can be inserting a new customer
• generate the value
• commit your transaction and release the lock

One way to do this would be something like

create table my_slow_sequence (
  table_name varchar2(30),
  next_value integer
);

insert into my_slow_sequence( 'CUSTOMER', 1 );

create or replace procedure insert_customer( 
  p_firstname in varchar2,
  p_lastname  in varchar2
)
as
  l_customer_id integer;
begin
  select next_value + 1
    into l_customer_id
    from my_slow_sequence
   where table_name = 'CUSTOMER'
     for update; -- Lock the row so no one else can use it

  insert into customer( customer_id, first_name, last_name )
    values( l_customer_id, p_firstname, p_lastname );

  update my_slow_sequence
     set next_value = next_value + 1
   where table_name = 'CUSTOMER';
end;

The problems you'll experience will include

• By locking the shared resource, you're explicitly and intentionally preventing other sessions from inserting a customer at the same time. At low transaction volumes, this isn't a huge deal. As systems grow, however, and as you replicate this logic for other tables, you're going to get a lot of waiting. You need if you want to guarantee no gaps-- you can't get the next value until you've ensured that every previous transaction is complete.
• When you want larger transactions (creating a customer, for example, is probably not a transaction on its own, it's probably part of some other transaction) or you start to do realistic amounts of validation (i.e. you want to check the customer against a web service of blacklisted customers or look for duplicate customers in your system), you create the potential that a session is going to acquire a lock and then hang and never release the lock. Until a DBA gets an urgent page, of course, that the entire system has ground to a halt until she can kill the session. At which point she will curse the developer that wrote this sort of thing.
• If you have transactions that involve multiple tables, you need to ensure that you're acquiring the locks in the same order to prevent deadlocks.

I would strongly encourage you to push back on the "no gaps" requirement. This is realistically never something that is actually required, it is something that sounds easy to a business user. If you want to know that this was the nth customer created, you can calculate that. If you want to know that customers weren't deleted, you can audit that. If you show the business user the cost of such a requirement (i.e. we'll need to buy more powerful servers, we'll need to spend more time monitoring and resolving problems, we'll create situations where the entire system has to grind to a halt when there is a problem anywhere, we'll make things noticably slower for users), they're almost certainly willing to reconsider.

Answer 2

Do the actual IDs actually matter in your usecase, or do you just care about the uniqueness?

If it's the latter, it might be easier to use SYS_GUID() instead of trying to generate a unique number.