Duplicate records inserted

[rk68]

Hi,
We have a portal where multiple users upload transaction. A key Id is generated at time of insertion (e.g. FY20/JAN20/XXXXX) Key is Financial year FY20; month & year and XXXXX is a running serial no. by picking up max number + 1
if certain criteria is met else the Key Id is blank.
At times it happens 2 users uploads transaction at same time & both get same Key Id.
How can I avoid duplication of Key Id.
Can't have Unique Key on Key Id since some of the records are duplicate key Ids.
What other option is available?

TIA
Raj

 

[Andrzejek]

XXXXX is a running serial no. by picking up max number + 1"
Is this done at the time when the INSERT statement is executed?


---- Andy

There is a great need for a sarcasm font.

 

[johnherman]

Lock the table that contains the serial number during the insert operation. That prevents double assignment. You would also need to put code in the transaction to check for the lock and wait up to (say) 2 minutes for the lock to be released before putting out an error. This solution GUARANTEES that you don't get duplicates. There may be other solutions as well.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[rk68]

Hi Andy,
the serial number is max + 1 at time of insert.

Thanks Johnherman. The serial number as Andy rightly mentioned is picked from the same table with max no. + 1
and inserted along with rest of the fields.
So in case where 2 users are submitting at same time , get the same serial no.

 

[Andrzejek]

the serial number is max + 1 at time of insert."
Do you mean something like:

INSERT INTO SomeTable (SerialNo, ...)
VALUES ((Select MAX(SerialNo) + 1 From SomeTable), ...

"in case where 2 users are submitting at same time , get the same serial no"
Not if you COMMIT your transaction after every INSERT...


---- Andy

There is a great need for a sarcasm font.

 

[Beilstwh]

"the serial number is max + 1 at time of insert."
Do you mean something like:

INSERT INTO SomeTable (SerialNo, ...)
VALUES ((Select MAX(SerialNo) + 1 From SomeTable), ...

"in case where 2 users are submitting at same time , get the same serial no"
Not if you COMMIT your transaction after every INSERT...

Unless there is a unique index on the table key that is not correct. However a way that will always work is to do the following. Lock grabs the table for all non readers, get your max number, insert using your max number, release the lock by committing.

LOCK TABLE SomeTable IN EXCLUSIVE MODE;
select max(serialno)+1 into nextkey from sometable;
INSERT INTO SomeTable (SerialNo, ...)
VALUES (nextkey, ...);
commit;


Bill
Lead Application Developer
New York State, USA

 

[rk68]

Thanks Andy & Bill.

I have applied UNIQUE index now & that's taken care. Since there are duplicate records & can't be deleted I created Unique Index with ENABLE NOVALIDATE.

The max SR # is stored in a variable & then passed in the Insert sql.