Problem with Data Refresh

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there

I have an .asp application that uses an accounting package for some of it's
data. This links to a warehouse stock system. I am not running an ODBC link
straight to the accounting system, but instead run a data refresh every 5
minutes that synchronises all the stock in the warehouse in to an SQL table -
my .asp application links to this.

I believe I am having issues with queries that are executed during this
scheduled refresh of data - the application does not fail, but I am getting
reports of inaccurate data resulting from queries at apparently random times
of the day.

Does anyone know if I am able to either lock the table during a refresh (and
how my ado programming will recognise this status) so that I can catch the
query and display a message along the lines of "Data refreshing, please wait"
and then requery once the refresh has completed - any more practical
suggestions would be musch appreciated.

Thanks very much for your time

Stuart
 
Stuart,

There is more to this problem than meets the eye. Yes you can lock the table
using the SELECT WITH (HOLDLOCK) statement, or escalating the isolation
level of the transaction to repeatableread or serializable.

However by doing this, your other clients will simply timeout or keep
waiting - i.e. you can't display a nice looking message. (Unless of course
you checked for the lock first, which means you need to query the master
database first - pain in the booty).

Now that might be a good solution for you - if the refreshes donot take
terribly wrong.

But if the refreshes DO take terribly long, the better solution may be to
implement some business logic to first transactionally (high isolation
level), update a column to basically reflect - Hey I'm locking these rows,
so quit reading from them. And then work on them - and then re-update and
effectively "release" the rows.

This is explained very very well in Chapters 9,10,11 of my upcoming book (2
more months - sorry).

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/
 
Back
Top