Lock data trickle feed while admin in progress.
Posted on 2009-07-09
I have two applications accessing a private database.
Feeder - Acts on events arriving from elsewhere and makes changes/additions to the data. Somewhere between once per day and hundreds per minute.
Admin - Presents reports on data and allows acknowledgement of states detected by Feeder. Acknowledgement results in deletion of records.
Both applications are in-house. Feeder will be running on or close to the server. Admin could be run anywhere on the estate but there will only be one copy of either component.
I need some way of locking out the feeder from making updates to the database while Admin is running.
I have seen in other comments:
SELECT * FROM ATable (holdlock, tablockx) where 1 = 0
-- Do stuff for a while.
If I run this from Admin am I right that Feeder will be locked out from any access to the table, even reading?
Why the 1 = 0? Is it to ensure we don't get any records back?
What if Admin gets switched off while the lock is in place? Can the lock be torn down?
How long will the lock hold? Admin may take an hour or more to process the results before committing its changes.
Would it be better to use a special record in a special table and hold a lock on that as a semaphore? If so how?
Thanks in advance. :)