SQL & PL/SQL

I am a software developer in Pawning project.

Here I developed the system to generate the Pawning Receipt No for each new pawning transaction. Also it prints on Pawning Receipt.
This system is designed to service nearly 100 clients concurrently.


No format is like this:-

this is serialno :- AA00025

AM/NEW/PA/AA00025
AM/NEW/PA/AA00026
AM/NEW/PA/AA00027
...........................28
...........................29

Here I can't use sequences due to the reason of serialno should be generated on Branch and Scheme basis. For example :-


AM/NEW/PA/AA00025 :- this is receipt no gerated by 'AM' branch

AV/NEW/PA/AA00025 :- receipt no gerated by 'AV' branch (which are two diffident receipt nos generated in two branches.)

Currently I am using this method

I have a table called "Serialno_Tab" which contains "Branch","Scheme","Last Serialno"
Then what i do is, in stored procedure I read the last serialno for the given branch & scheme from the above table and update the "Last Serialno" incrementing by 1

here i useed "for update" statement with select query to avoid duplicates. When increasing the no of users working concurrently it caused to hang the database from not released table locks.

So, I think this is not best solution for generating nos

So, What is the best approach to generate a unique Pawning Receipt Nos for this requirement?

Thx a lot.