How should I lock to avoid duplicate numbers

I have a table A (SQL Server 2008) that contains sets of min and max numbers. In one of my stored procedures I use this table joined with a product table B to find an available product number that's between min and max and then insert a new product with this product number. So I want the next free number that's between min/max.

Between finding out the next available number and inserting the product row I want a lock to prevent anyone of finding the same number (and giving a duplicate).

How should I think in this situation? Should I get an update lock on the A table even though I never modify it? The lock should be released after I do the insert into table B and the transaction finishes? Will this update lock prevent other transactions from reading table A?

Edit: The min/max table is a table for different product series. Depending on which serie you want i want to try and find an available number in this sequence. The productnr is not unique, it would be possible to make it unique in combination with a second column though. Simplified sp:

CREATE PROCEDURE [dbo].[Insert开发者_如何学运维Product]
(
@Param1 int,
@Param2 bit,
...
@Param20 int) AS 
BEGIN
    DECLARE @ProductNr int
    --Here I do a query to determine which ProductNr I should have. Checking that the number is between max/min in the series and that no other product has this productnr.
    --Now insert the row
    INSERT INTO Products VALUES (@Param1, @ProductNr, ...., @Param2
END

---

Your question is kind of obscure, it would help if you included some sample data.

Regardless, a tactic that I have used in the past is to try and wrap everything withing a single statement -- here, it would be an INSERT. Every SQL statement is de-facto wrapped in its own implicit transaction (that's atomicity, what the "A" in the ACID of relational database properties fame stands for). In psuedo-code, it'd look something like:

INSERT MyTable (Id, Plus, Other, Columns)
 select CalcForNewId, Plus, Other, Columns
 from [what may be a pertty convoluted query to determine the "next" valid Id]

This only works if you can write your business logic as a single reasonable query (where "reasonable" means it doesn't lock, block, or deadlock either the current or any other user for an unreasonable length of time). That can be a pretty tall order, but I'd take that over having to write complex BEGIN TRANSACTION/COMMIT/ROLLBACK code intermixed with TRY...CATCH blocks any day. (That will of course work, and I've upvoted @Scott Bruns accordingly.)

---

Just insert the next number into table B. If it commits it is yours to use. If you get a Key Violation it means that a different process has just entered the new number. In that case increment the new number and try again.

The database will automatically handle the concerency for you. No manual locking is required.