How to lock a table for inserting in sql?

Question

Hi,

I have a web application in which I have a page named 'Data'. Many people will try to insert data into the table at a given time. So the reference no other than the primary key will get duplicated which should not be permitted. For inserting the data into the DB table, I am using a stored procedure in SQL. In my vb.net web application I am using Business layer with enterprise library for calling the stored procedure. Now I want to lock the table for inserting so that when multiple users insert, there won't be any complications. How can I do this? Please advise.

I didn't mean the primary key. I have a primary key field namely InvoiceID which is not duplicated. But along with that I need an 'InvoiceNo' which should not be duplicated as well. This is automatically populated from the previously entered 'InvoiceNo'+1 which will be duplicated when multiple users try to insert at the same time.

Regards

Answer 1

What complications are you concerned about? Concurrent INSERTs usually work fine without any explicit locking.

Answer 2

Hi. Could you just clarify exactly the problem. Maybe I'm just not understanding it. however, if you have a primary key set with Identity Insert in the Database then you can call your Insert stored procedure as many times as you want (within reason) and SQL Server will make sure that each one is inserted OK. As long as you aren't too worried about the exact order they are inserted or anything like that.

If you start locking the table you start to get timout problems and waiting issues. Much better to throw everything at SQL Server and let it handle all the inserts - it's more than capable of doing that for you.

Apologies if that didn't answer the question.

Answer 3

Don't. Don't even think about it. You'll kill off any performance and concurrency you have.

You need to find out why you're having duplicate PK values. If you leave that up to the database itself to handle, by using a INT IDENTITY column for instance, you don't have to worry about anything, really. SQL Server will take care of making sure those values are indeed always guaranteed to be unique.

So really, the recommendation is: re-architect your solution and let the database handle the uniqueness of the ID's - then you won't have any need at all for any locking or anything.