How to lock a table for inserting in sql?

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

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.

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

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.

I totally understand dealing with accountants who don't want the invoice number shown to the client to be the primary key -- wouldn't be surprised to find that your "invoice number" is actually supposed to be a string composed of, say, customer # followed by dash followed by their invoice number, or something like that. Perhaps you could use an additional table that you reference inside an instead of insert trigger on your invoices table. You would, alas, have to allow the trigger to slow your inserts down to cursor-level performance; but most invoicing business cases I know of wouldn't be generating massive numbers of invoices simultaneously with critical and sub-minute time constraints. You'd do something like this...

create table invoiceNumbers( invNumber int not null identity primary key )

Then you would do an instead of insert trigger on your invoices table that would iterate through the inserted table with a cursor; for each row it would insert a new row on invoiceNumbers, retrieve the scope_identity() value, and then insert the invoice (with the new invoice number) into the invoices table. This would slow down your invoices insert some but you would NOT get yourself into a concurrency problem, which IMHO is way worse than a cursor-speed-rather-than-set-logic-speed problem.