Best practice for managing/generating policy numbers

Question

I am currently working on a website which allows a prospect to generate a quote and purchase a policy. After they submit their payment the application should generate a policy number.

I was given a block of policy numbers to use incrementally. For this example, let's say 0100800 - 0100999. If I have two or more people who decide to purchase a policy at the same time, What are some best practices/algoithms to make sure I don't generate the same policy number for two or more people?

It's also possible that after the last available policy number 0100999, I may be given another block of policy number for example 0222100-0222399.

Answer 1

You will have to use a lock, take a lock on a shared object, get your id, increment.

Also, take a look at System.Threading.Interlocked.Increment, it's all that in one, single method.

Answer 2

Assuming you're not supposed to go BACK to your starting number when you run out (in your case 0100800), then you could use a SQL Server identity column and seed it as your starting pre-defined value and increment by 1.

I'm not positive of this, but I think you can accomplish the exact same thing in Oracle using a sequence, what I'm not sure about is if you can start a sequence at a pre-defined number.

Answer 3

I assume you are using a database to store this information? If that is correct (and assuming you are using SQL Server) you could specify one column as the identity column.

Once you specify it's an identity you can give it a:

seed

Is the value that is used for the very first row loaded into the table.

which in your case would be 100800. You could prepend the zero after you pull it from the db.

A good benefit of this is that you won't have to worry about two people creating policies at the exact same time having the same policy number.

Answer 4

The best idea is to perform this part in the database because there you can protect yourself through transactions.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

DECLARE @LastPolicyNr int
SELECT LastPolicyNr = MAX(PolicyNr) FROM [Policy]

DECLARE @NewPolicyNr int
SET @NewPolicyNr = @LastPolicyNr + 1

INSERT INTO [Policy] ([PolicyNr], ...)
VALUES (@NewPolicyNr, ...)

COMMIT TRANSACTION
END TRANSACTION

RETURN @NewPolicyNr

Answer 5

Typically, it depends on the storage in which you will keep the last id that you dispensed. Typically, this is a database. In this case, you would just have a stored procedure/set of commands which would read the current value, then increment it by one (so that the next read gets the incremented value and so on).

Of course, you have to serialize access to this, and for this, you would wrap it in a transaction.

Now, if you were using some other storage medium, you would need to serialize access differently. For example, in the case of a file, you would have to lock out read access (and have other processes spin until it can get read/write exclusive access), in memory, you would use a lock statement, etc, etc.

Answer 6

Developer Art is almost dead on.

Though you should include error check and rollback.

and minor issue ... missing an @ DECLARE @LastPolicyNr int SELECT LastPolicyNr = MAX(PolicyNr) FROM [Policy]

but I would make it one step DECLARE @NewPolicyNr int SELECT @NewPolicyNr= MAX(PolicyNr) +1 FROM [Policy] WHERE ID >= [YOUR MIN #In Your Block]

Answer 7

I would say the majority of these answers really miss the most correct answer.

You're situation is most suited to a HI-LO key algorithm: What's the Hi/Lo algorithm?