• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

How do i lock a table to prevent reading whilst data is populated


I have a legacy business application that outputs data to DBF files.
One facet of the system is Stock Control, and i am writing a number of Crystal Reports based around the stock data.
In the absence of any real-time connection to SQL, I have an SSIS package that runs every 5 minutes, taking approximately 40 seconds to complete.

1. Launches legacy Export application to output stock data to a DBF file
2. Imports DBF content into temp table.
3. Truncates destination table in SQL
4. Copies data from temp table into destination table using a oledb data flow.

All quite simple. (I'm not a programmer by any stretch of the imagination!)

My problem is that, during step 4, I want to lock the destination table from being read whilst data is being imported.  The reason being that, if a report were run whilst rows were being written, it could result in a very misleading report; and with the frequency the routine is running this is quite a likely scenario.

I've placed step 4 within a transaction but I am still able to read from the table whilst rows are being written.

Can someone offer any suggestion as to how i can lock the table from reads whilst rows are being written?  I would rather the report 'pauses' for a few seconds before reading complete data, rather than potentially returning incomplete data.

Many thanks!
1 Solution
You can exclusively lock the table with TABLOCKX hint. Check the following link:-
CAMSYSTEMSAuthor Commented:
Thank you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now