Q

Blocking inserts during procedure

I'm using SQL Server 2000. I want to write an INSERT procedure that:
  1. starts by blocking inserts on a table,
  2. selects rows on the same table and validates that an INSERT does not violate business rules,
  3. performs the insert,
  4. updates another table for a specific row (this update only occurs on an insert), and
  5. commits the transaction.
This INSERT procedure will be called by an ASP page, and I'm thinking that I should not have IIS create a connection per browser session. What is the best way to do this? How should I use TRANSACTION ISOLATION LEVEL and record locking to block inserts during the execution of the procedure?

Why do you want to block all inserts into a table when you are inserting another row into that table? This is a...

serious scalability problem and should only be used in very explicit situations and off the top of my head, I can't come up with a single application amongst the thousands of applications I've worked on that needed to lock an entire table from inserts.

As far as the code goes, I'll refer you to Books Online. This is nothing different than writing insert, update and select statements.

 

For More Information

This was last published in March 2003

Dig Deeper on Microsoft SQL Server Installation

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close